oracle - 收集记录到 sys_refcursor

Question

甲骨文 11g

这似乎比它应该的更难，所以我可能在这里走错了路。

我有一个生成用户定义表单的应用程序，我的数据比这更复杂，但想法是——我有一个数据表，其中包含从用户定义的表单输入的所有数据

create table formData(
      id number
    , fName varchar(100)
    , lName varChar(100)
    , mName varChar(100)
    , formType varchar(100)
    ...
);

insert all 
    into formData(id,fName,lName,mName,formType)values(1,'Bob','Smith',NULL,'birthday')
    into formData(id,fName,lName,mName,formType)values(2,'Jim','Jones','Wilber','birthday')
    into formData(id,fName,lName,mName,formType)values(3,'Frank','Peterson',NULL,'general')
    into formData(id,fName,lName,mName,formType)values(4,'Alex','Anderson',NULL,'general')

我有一个表格，其中包含动态表单的字段选项

create table fieldOptions(
      id number
    , fieldName varchar(100)
    , fieldLabel varChar(100)
    , formType varchar(10)
    , fieldUsed number
    , ...
);
insert all 
    into fieldOptions (fieldName,fieldLabel,formType,fieldUsed)values('fName','First Name','birthday',1)
    into fieldOptions (fieldName,fieldLabel,formType,fieldUsed)values('lName','Last Name','birthday',1)
    into fieldOptions (fieldName,fieldLabel,formType,fieldUsed)values('mName','Middle','birthday',1)
    into fieldOptions (fieldName,fieldLabel,formType,fieldUsed)values('fName','First','general',1)
    into fieldOptions (fieldName,fieldLabel,formType,fieldUsed)values('lName','Surname','general',1)
    into fieldOptions (fieldName,fieldLabel,formType,fieldUsed)values('mName','Middle Initial','general',0)

我想在我的包中创建一个过程，它将返回一个光标到我的 .net 页面，其中包含如下所示的数据:

其中 ID=3(一般输出)

  Label | Value
--------+---------
First   | Frank
Surname | Peterson

或其中 ID=1(生日输出)

  Label     | Value
------------+---------
First Name  | Bob
Last Name   | Smith
Middle      | NULL

我不确定是否可以在(枢轴？)查询中执行此操作。我开始玩弄通过处理数据构建的记录集合，但是如何将记录集合放入 out sys_refcursor如果那是解决方案？也许我想多了，可以通过几个子查询来完成？朝着正确的方向插入将是完美的，谢谢。

Answer 1

假设您的 formData表结构是固定的和已知的，你可以只用一个case表达式来翻译formOption.fName到匹配的列值:

select fo.fieldLabel as label,
  case fo.fieldName
    when 'fName' then fd.fName
    when 'lName' then fd.lName
    when 'nName' then fd.mName
  end as value
from formData fd
join fieldOptions fo
on fo.formType = fd.formtype
where fd.id = 3;

LABEL                VALUE               
-------------------- --------------------
First                Frank               
Surname              Peterson            
Middle Initial                           

...
where fd.id = 3;

LABEL                VALUE               
-------------------- --------------------
First Name           Bob                 
Last Name            Smith               
Middle                                   

然后，您可以让您的过程为该查询打开一个引用游标，使用 ID 值的参数值。

如果 formData结构未知，或者不是静态的，那么你可能有更大的问题；但为此，您需要回退到动态 SQL。作为起点，您可以执行以下操作:

create procedure p42 (p_id number, p_refcursor out sys_refcursor) as
  l_stmt varchar2(32767);
begin
  l_stmt := 'select fo.fieldLabel as label, case lower(fo.fieldName) ';
  for r in (
    select column_name from user_tab_columns
    where table_name = 'FORMDATA'
    and data_type = 'VARCHAR2'
  )
  loop
    l_stmt := l_stmt || ' when ''' || lower(r.column_name) || ''' then fd.' || r.column_name;
  end loop;
  l_stmt := l_stmt || ' end as value '
    || 'from formData fd '
    || 'join fieldOptions fo '
    || 'on fo.formType = fd.formtype '
    || 'where fd.id = :d1';
  open p_refcursor for l_stmt using p_id;
end p42;
/

这将使用表中实际定义的所有列在运行时创建 case 表达式；因为你的情况 fieldName可能与数据字典不匹配，我强制所有内容都小写以进行比较。我也限制使用字符串列以使情况更简单，但是如果您需要其他数据类型的列，那么每个 when ... then case 表达式的子句需要检查该列的数据类型(您可以将其添加到 r 游标中)并将实际的列值适本地转换为字符串。所有的值都必须以相同的数据类型结束，所以它必须是字符串，真的。

无论如何，从 SQL*Plus 测试这个:

var rc refcursor
exec p42(1, :rc);

PL/SQL procedure successfully completed.

print rc

LABEL                VALUE
-------------------- --------------------
First Name           Bob
Last Name            Smith
Middle

3 rows selected.

您可以查询 fieldOptions取而代之的是获取可能的列名，但您仍然可能遇到数据类型转换问题，这将更难处理；但如果所有引用 formData字段实际上是字符串，那将是:

  for r in (
    select fo.fieldName
    from formData fd
    join fieldOptions fo
    on fo.formType = fd.formtype
    where fd.id = p_id
  )
  loop
    l_stmt := l_stmt || ' when ''' || r.fieldName || ''' then fd.' || r.fieldName;
  end loop;