记录收集到sys_refcursor中 [英] collection of records to out sys_refcursor

问题描述

Oracle 11g

Oracle 11g

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

This seems harder than it should be so I might be on the wrong path here.

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

I have an application that generates user defined forms, my data is a bit more complicated than this but the idea is -- I have a data table which contains all the data input from the user defined forms

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')

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

I have a table which contains the field options for the dynamic forms

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页面，该页面包含如下数据:

I want to create a procedure in my package that will return a cursor to my .net page that contains data that looks like:

其中ID = 3(常规输出)

where ID=3 (general output)

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

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

or where ID=1 (birthday output)

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

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

I'm unsure if I can do this in a (pivot?) query. I started toying with a collection of records built by processing the data but how would I get a collection of records into an out sys_refcursor if that's the solution? Perhaps I'm over thinking this and it can be done with a few sub queries? A shove in the right direction would be perfect, thanks.

推荐答案

假设您的formData表结构是固定的并且是已知的，则只需使用case表达式将formOption.fName转换为匹配的列值即可:

Assuming your formData table structure is fixed and known, you can just use a case expression to translate the formOption.fName to the matching column value:

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值的参数值，使过程打开该查询的ref游标.

You can then have your procedure open a ref cursor for that query, using an argument value for the ID value.

如果formData结构未知或不是静态的，那么您可能会遇到更大的问题；但是为此，您需要使用动态SQL.首先，您可以执行以下操作:

If the formData structure isn't known, or isn't static, then you probably have bigger problems; but for this you'd need to fall back to dynamic SQL. As a starting point, you could do something like:

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;
/

这将使用表中实际定义的所有列在运行时创建案例表达式；因为您的fieldName的大小写可能与数据字典不匹配，所以我将所有内容都强制使用小写字母进行比较.我还限制字符串列以简化大小写，但是如果您需要其他数据类型的列，则case表达式的每个when ... then子句都需要检查该列的数据类型(可以将其添加到r游标)，然后将实际的列值适当地转换为字符串.所有的值都必须以相同的数据类型结尾，所以实际上它必须是字符串.

This uses all the columns actually defined in the table to create the case expression at run time; because the case of your fieldName may not match the data dictionary, I'm forcing everything to lowercase for comparison. I'm also restricting to string columns to make the case simpler, but if you need columns which are other data types then each when ... then clause of the case expressions would need to check that column's data type (which you can add to the r cursor) and convert the actual column value to a string appropriately. All of the values have to end up the same data type, so it has to be strings, really.

无论如何，可以通过SQL * Plus对此进行测试:

Anyway, testing this from 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字段实际上都是字符串，则为:

You could query fieldOptions to get the possible column names instead, but you still may have the data type conversion issue, wich would be harder to deal with; but if all the referenced formData fields are actually strings then that would be:

  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;

这篇关于记录收集到sys_refcursor中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！