oracle 将表名传递给游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4429428/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Passing the tablename to the cursor
提问by Dead Programmer
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.
还有另一种方法可以将表传递给游标。
回答by Harrison
To expand on HymanPDouglas' answer, you cannot utilize a param name as the [table] name in a cursor. You must utilize dynamic sql into a REF CURSOR
要扩展 HymanPDouglas 的答案，您不能将参数名称用作游标中的 [表] 名称。您必须将动态 sql 用于 REF CURSOR
http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96590/adg09dyn.htm#24492
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 ;
/
Commence Example
开始示例
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 ;
Output:
输出：
SELECT USERNAME FROM test1 WHERE sid= :id
abc
ddd
abc
ddd
ddd
EDIT: Added Close CUR that was rightly suggested by @HymanPDouglas
编辑：添加了@HymanPDouglas 正确建议的 Close CUR
