Oracle高手看過來﹐根據動態SQL返回一個Ref Cursor,高分求救!

SQL 語句是動態生成的﹐比如
i := 0;
iCount  := 12;
--Sql的構造很復雜,這里只是舉例
LOOP
    strSql = strSql || 'FieldName' || TO_CHAR(i) || ',';

    if i<iCount then
         exit;
    end if
END LOOP;

strSql := 'SELECT ' || strSql || ' FROM myTable';
我要返回一個Ref Cursor來被Oracle 的Develope的6I里面的Report Builder用
應該怎么做啊﹐求救﹗

6 个解决方案

#1

多了一个‘,’
LOOP
    strSql = strSql || 'FieldName' || TO_CHAR(i);

    if i<iCount then
         exit;
    end if
    strSql = strSql || ','
END LOOP;

#2

看看下面两个例子，会对你有帮助的。

例子1：
--创建测试表：
SQL> create table a (id number,name varchar2(50),doctime date);

Table created.

--插入六条测试数据：
SQL> insert into a values (1,'aaa',to_date('2002-07-01','yyyy-mm-dd'));

1 row created.

SQL> insert into a values (2,'bbb',to_date('2002-07-02','yyyy-mm-dd'));

1 row created.

SQL> insert into a values (3,'ccc',to_date('2002-07-03','yyyy-mm-dd'));

1 row created.

SQL> insert into a values (4,'ddd',to_date('2002-07-04','yyyy-mm-dd'));

1 row created.

SQL> insert into a values (5,'eee',to_date('2002-07-05','yyyy-mm-dd'));

1 row created.

SQL> insert into a values (6,'fff',to_date('2002-07-06','yyyy-mm-dd'));

1 row created.

SQL> commit;

Commit complete.

--创建两个type
SQL> create or replace type myobjectype as object (x int,y date,z varchar2(50));
  2  /

Type created.

SQL> create or replace type mytabletype as table of myobjectype
  2  /

Type created.

--创建可以返回纪录集的函数(不传入表名参数)
SQL> create or replace function testrerecordnotabname (tableid in number)
  2  return mytabletype
  3  as
  4    l_data mytabletype :=mytabletype();
  5  begin
  6    for i in (select * from a where id>=tableid)  loop
  7      l_data.extend;
  8      l_data(l_data.count) := myobjectype(i.id,i.doctime,i.name);
  9      exit when i.id = 62;
 10    end loop;  
 11    return l_data;     
 12  end;   
 13  /

Function created.

SQL> commit;

Commit complete.

--创建可以返回纪录集的函数(可以传入表名参数)
SQL> create or replace function testrerecordtabname (tablename in varchar2,tableid in number)
  2  return mytabletype
  3  as
  4    l_data mytabletype :=mytabletype();
  5    strsql varchar2(50);
  6    type v_cursor is ref cursor;
  7    v_tempcursor v_cursor;
  8    i1 number;
  9    i2 varchar2(50);
 10    i3 date;
 11  begin
 12    strsql := 'select * from ' || tablename || ' where id>=' || tableid;
 13    open v_tempcursor for strsql;
 14    loop 
 15      fetch v_tempcursor into i1,i2,i3;
 16      l_data.extend;
 17      l_data(l_data.count) := myobjectype(i1,i3,i2);
 18      exit when v_tempcursor%NOTFOUND;
 19    end loop;  
 20    return l_data;     
 21  end;   
 22  /

Function created.

SQL> commit;

Commit complete.

--测试不传表名参数的function(testrerecorenotabname)
SQL> set serveroutput on     
SQL> declare
  2    testre mytabletype :=mytabletype();
  3    i number :=0;
  4  begin
  5    testre := testrerecordnotabname(1);
  6    loop
  7      i := i+1;
  8      dbms_output.put_line(';' || testre(i).x || ';' || testre(i).y || ';' || testre(i).z || ';');
  9      exit when i = testre.count;
 10    end loop;
 11  end;
 12  /
;1;01-7?? -02;aaa;
;2;02-7?? -02;bbb;
;3;03-7?? -02;ccc;
;4;04-7?? -02;ddd;
;5;05-7?? -02;eee;
;6;06-7?? -02;fff;

PL/SQL procedure successfully completed.

--测试传表名参数的function(testrerecoretabname)
SQL> set serveroutput on     
SQL> declare
  2    testre mytabletype :=mytabletype();
  3    i number :=0;
  4  begin
  5    testre := testrerecordtabname('a',1);
  6    loop
  7      i := i+1;
  8      dbms_output.put_line(';' || testre(i).x || ';' || testre(i).y || ';' || testre(i).z || ';');
  9      exit when i = testre.count;
 10    end loop;
 11  end;
 12  /
;1;01-7?? -02;aaa;
;2;02-7?? -02;bbb;
;3;03-7?? -02;ccc;
;4;04-7?? -02;ddd;
;5;05-7?? -02;eee;
;6;06-7?? -02;fff;
;6;06-7?? -02;fff;

PL/SQL procedure successfully completed.

例子2：

测试过程：
1、建立测试表
CREATE TABLE student
 (
  id                         NUMBER,
  name                       VARCHAR2(30),
  sex                        VARCHAR2(10),
  address                    VARCHAR2(100),
  postcode                   VARCHAR2(10),
  birthday                   DATE,
  photo                      LONG RAW
 )
/

2、建立带ref cursor定义的包和包体及函数：
CREATE OR REPLACE
package pkg_test as
/* 定义ref cursor类型
   不加return类型，为弱类型，允许动态sql查询，
   否则为强类型，无法使用动态sql查询;
*/
  type myrctype is ref cursor; 

--函数申明
  function get(intID number) return myrctype;
end pkg_test;
/

CREATE OR REPLACE
package body pkg_test as
--函数体
   function get(intID number) return myrctype is
     rc myrctype;  --定义ref cursor变量
     sqlstr varchar2(500);
   begin
     if intID=0 then
        --静态测试，直接用select语句直接返回结果
        open rc for select id,name,sex,address,postcode,birthday from student;
     else
        --动态sql赋值，用:w_id来申明该变量从外部获得
        sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id';
        --动态测试，用sqlstr字符串返回结果，用using关键词传递参数
        open rc for sqlstr using intid;
     end if;

     return rc;
   end get;

end pkg_test;
/

3、用pl/sql块进行测试：
declare
  w_rc       pkg_test.myrctype; --定义ref cursor型变量

  --定义临时变量，用于显示结果
  w_id       student.id%type;
  w_name     student.name%type;
  w_sex      student.sex%type;
  w_address  student.address%type;
  w_postcode student.postcode%type;
  w_birthday student.birthday%type;

begin
  --调用函数，获得记录集
  w_rc := pkg_test.get(1);

  --fetch结果并显示
  fetch w_rc into w_id,w_name,w_sex,w_address,w_postcode,w_birthday;
  dbms_output.put_line(w_name);
end;

4、测试结果：
通过。

#3

search:ref cursor.

#4

--包的定义    
    
TYPE t_CurList  IS REF CURSOR ; 

--包体的过程
/************************************************************/
    PROCEDURE sp_Get_Cur(……，  
        io_cursor   IN OUT   t_CurList)
    IS
    BEGIN
       ……
       strSql := 'SELECT ' || strSql || ' FROM myTable'; 
       OPEN io_cursor FOR strSql ;
    EXCEPTION
      WHEN OTHERS THEN
    END sp_Get_Cur;

#5

向这样的Procedure，我的VB,VC或JAVA可以取到吗？

#6

当然可以了。