将表名传递给游标 [英] Passing the tablename to the cursor

问题描述

可以将表名从一个游标传递到另一个游标.请让我换种方式.

Guys is it possible to pass the table name from one cursor to another. kindly let me the other way.

CURSOR  R IS SELECT TABLE_NAME FROM RESOURCE ;


CURSOR S(TAB VARCHAR2) IS SELECT  username from TAB where sid=1291;

还有另一种将表传递给游标的方法.

is there another way to pass the table to a cursor.

推荐答案

要扩展JackPDouglas的答案，您不能将参数名称用作游标中的[table]名称.您必须将动态SQL用作REF CURSOR

To expand on JackPDouglas' answer, you cannot utilize a param name as the [table] name in a cursor. You must utilize dynamic sql into a REF CURSOR

http://download.oracle. com/docs/cd/B10500_01/appdev.920/a96590/adg09dyn.htm#24492

CREATE OR REPLACE PROCEDURE dynaQuery(
       TAB IN VARCHAR2, 
       sid in number ,
       cur OUT NOCOPY sys_refcursor) IS
 query_str VARCHAR2(200);
BEGIN
    query_str := 'SELECT USERNAME FROM ' || tab
      || ' WHERE sid= :id';
dbms_output.put_line(query_str);
    OPEN cur FOR query_str USING sid;
END ;
/

开始示例

create table test1(sid number, username varchar2(50));
insert into test1(sid, username) values(123,'abc');
insert into test1(sid, username) values(123,'ddd');
insert into test1(sid, username) values(222,'abc');
commit;
/



 declare 
  cur  sys_refcursor ;
  sid number ;
  uName varchar2(50) ;
  begin
  sid := 123; 
  dynaQuery('test1',sid, cur);
   LOOP
     FETCH cur INTO uName;
     DBMS_OUTPUT.put_line(uName);
     EXIT WHEN cur%NOTFOUND;
     -- process row here
   END LOOP;
CLOSE CUR;


  end ;

输出:

SELECT USERNAME FROM test1 WHERE sid= :id
abc
ddd
abc
ddd
ddd

添加了@JackPDouglas正确建议的Close CUR

这篇关于将表名传递给游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！