oracle存储过程返回交叉表(使用存储过程/高级语言调用)

定义：由左边、上边两个标头和右下角交叉数据构成的表我们称之为“交叉表”。

列数：列数固定的交叉表可以使用视图直接返回表数据，在此不讨论。本文演示列数不固定的交叉表数据的返回。

1，创建存储：

create or replace procedure test(p_cursor out SYS_REFCURSOR) is
  sqlstr varchar2(32767);
begin
  sqlstr := 'select i06_feebill_id,';
  for v_cur in (select distinct i06_feebill_id
                  from t_i06_billdetail
                 where i06_feebill_id in
                       ('F200F79AA4D94367A9003139737AB3D5',
                        'F200F79A30DA45379CCFB49FC3E9857A')) loop
    sqlstr := sqlstr || 'sum(decode(i06_feebill_id' ||
              ', '''||v_cur.i06_feebill_id||''', '||
              'i06_fee_amount, 0)) a' || substr(v_cur.i06_feebill_id,0,10) || ',';
  end loop;

  sqlstr := substr(sqlstr, 0, length(sqlstr) - 1);
  sqlstr := sqlstr ||
            ' from t_i06_billdetail t  where t.i06_feebill_id in (''F200F79AA4D94367A9003139737AB3D5'',''F200F79A30DA45379CCFB49FC3E9857A'') group by i06_feebill_id';
          
  open p_cursor for sqlstr;
end;
2，测试（使用存储过程调用），缺点是列数不固定时无法实现调用。因为接收参数没法定义。

-- Created on 2010-08-24 by ADMINISTRATOR
declare
  -- Local variables here
  i         integer;
  tmpCURSOR SYS_REFCURSOR;
  bill      t_i06_billdetail.i06_billdetail_id%TYPE;
  amount    t_i06_billdetail.i06_fee_amount%TYPE;
  amount2   t_i06_billdetail.i06_fee_amount%TYPE;
begin
  -- Test statements here

  test(tmpCURSOR);

  loop
    fetch tmpCURSOR
      into bill, amount, amount2;
    exit when tmpCURSOR%notfound;
    DBMS_OUTPUT.PUT_LINE(bill);
    DBMS_OUTPUT.PUT_LINE(amount);
    DBMS_OUTPUT.PUT_LINE(amount2);
  end loop;
end;

3，测试（使用高级语言调用【C#】），通过绑定可以实现接收列数不固定的数据。

// GetConnectionString() returns a connection string for
// the data source.

string connString = GetConnectionString();
DataSet ds = new DataSet();
OracleConnection conn = new OracleConnection(connString);
OracleCommand cmd = conn.CreateCommand();

cmd.CommandText = "test";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction =
       ParameterDirection.Output;

OracleDataAdapter da = new OracleDataAdapter(cmd);
da.TableMappings.Add("Table", "Emp");

da.Fill(ds);

4，需要注意返回游标分为两种情况：

a,返回的游标是某个具体的表或视图的数据,调用的时候只要声明一个该表的ROWTYPE类型就可以了,如 R t_i06_billdetail
%ROWTYPE;

CREATE OR REPLACE PROCEDURE P_TESTA (
PRESULT OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN PRESULT FOR SELECT * FROM USERS;
END P_TESTA;

CREATE OR REPLACE PROCEDURE P_TESTB
AS
VARCURSOR SYS_REFCURSOR;
R USERS%ROWTYPE;
BEGIN
P_TESTA(VARCURSOR);
LOOP
FETCH VARCURSOR INTO R;
EXIT WHEN VARCURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(R.NAME);
END LOOP;
END P_TESTB;

b，第二种情况，我们返回的不是表的所有的列，或许只是其中一列或两列。

CREATE OR REPLACE PROCEDURE P_TESTA (
PRESULT OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN PRESULT FOR SELECT ID,NAME FROM USERS;
END P_TESTA;
CREATE OR REPLACE PROCEDURE P_TESTB
AS
VARCURSOR SYS_REFCURSOR;
CURSOR TMPCURSOR IS SELECT ID,NAME FROM USERS WHERE ROWNUM=1;
R TMPCURSOR%ROWTYPE;
BEGIN
P_TESTA(VARCURSOR);
LOOP
FETCH VARCURSOR INTO R;
EXIT WHEN VARCURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(R.ID);
END LOOP;
END P_TESTB;
与之前不同的是我们声明了一个游标类型的变量TMPCURSOR ，注意TMPCURSOR 的结构必须与存储过程P_TESTA 返回的游标结构一致，否则就会出现错误。同理只要保持两个游标类型结构一致，就可以实现自由调用。