如何同时使用游标变量和动态SQL？

Question

Create or Replace package ref_types
AS
 TYPE ref_cursor is REF CURSOR;
End;
/
Create or Replace package userinfo as
procedure Get_usermenu (baduser   out boolean,
                        t_menu      out ref_types.ref_cursor
                       );
/Create or Replace package body userinfo as   
procedure Get_usermenu (baduser   out boolean,
                        t_menu      out ref_types.ref_cursor
                       ) is
v_sqltext   varchar2(100);                       
v_sql       varchar2(300);
begin
baduser:=FALSE;
v_sqltext:='''A001'',''A002'',''A003''';
  v_sql:='select MENUID,P_NAME from MENU_LIST
          where MENUID in (';
  v_sql:=v_sql||v_sqltext||') order by MENUID asc'; 
  --注：v_sqltext本来是要经过一个过程计算出来的
  open t_menu FOR v_sql;
end;
end;
/ 

Answer 1

Create or Replace package userinfo as
procedure Get_usermenu (baduser   out boolean,
                        t_menu      out ref_types.ref_cursor
                       );
End;
/--少写了一个End，是笔误

Answer 2

把以上修改如下，好好想想为什么。多看pl/sql.Create or Replace package ref_types
AS
 TYPE ref_cursor is REF CURSOR;
End;
/create or replace procedure Get_usermenu (baduser   out boolean,
                        t_menu      out ref_types.ref_cursor
                       ) is
v_sqltext   varchar2(100);                       
v_sql       varchar2(300);
begin
baduser:=FALSE;
v_sqltext:='''A001'',''A002'',''A003''';
  v_sql:='select MENUID,P_NAME from MENU_LIST
          where MENUID in (';
  v_sql:=v_sql||v_sqltext||') order by MENUID asc'; 
  --注：v_sqltext本来是要经过一个过程计算出来的
  open t_menu FOR v_sql;
end Get_usermenu;

Answer 3

我认为应该把生成sql语句的部分单独写个fuanction或者procedure，以便于维护和调试。

Answer 4

看不懂，可以讲清楚一些吗？
我的理解是：
你将我的Package改写为Procedure,
但是执行时还是在
open t_menu FOR v_sql;
处报错。

Answer 5

按道理括号内不能出三个分号,程序中oracle不能解释多了一个分号.
这样吧：
v_sqltext:='(''A001'',''A002'',''A003'')';
v_sql:='select MENUID,P_NAME from MENU_LIST
          where MENUID in ';
v_sql:=v_sql||v_sqltext||'order by MENUID asc'; 

Answer 6

建义写成一条语句可观性比较好v_sql:='select MENUID,P_NAME from MENU_LIST where MENUID in (''A001'',''A002'',''A003'') order by MENUID asc';

Answer 7

因为v_sqltext是通过一个过程来计算出来的，所以需要再次组合。即使是使用一条语句，也是同样的错误。
我本意是希望可以同时使用游标指针和动态SQL，可能是没有找到正确的途径，以上的open t_menu FOR v_sql;根本无法执行，
即使是我直接这样写：
v_sql:='……';
open t_menu FOR v_sql;
也不行。
不知道该怎样写，我在OTN上还是没有找到相关的资料。

Answer 8

/
rem 动态SQL的例子
CREATE OR REPLACE  PROCEDURE "SYS"."STATS_TABLE"  (schema_name 
    char,table_name char,cursor_num integer)is
v_rownum number;
begin
  if dbms_sql.is_open(cursor_num) then
    dbms_sql.parse(cursor_num,'alter table '||schema_name||'.'||table_name||' move tablespace',dbms_sql.native);
    v_rownum := dbms_sql.execute(cursor_num);
  end if;
exception when others then
 dbms_output.put_line('表：'||schema_name||'.'||table_name||' 不存在');
end;
/
CREATE OR REPLACE  PROCEDURE "SYS"."STATS"   (schema_name char)is
v_cursor integer;--to execute dynamic sqlcursor c_table_name is--get all table names owned by schema
  select table_name from dba_tables
  where owner=schema_name;v_table_name c_table_name%ROWTYPE;cursor c_index_name is--get all index names owned by schema
  select index_name from dba_indexes
  where owner=schema_name;v_index_name c_index_name%ROWTYPE;begin
  v_cursor:=dbms_sql.open_cursor;--create the cursor
  dbms_output.put_line('打开了公共游标');
  open c_table_name;--get tables
  loop
    FETCH c_table_name into  v_table_name;
    exit when c_table_name%NOTFOUND;
    --do something
  end loop;
  close c_table_name;  open c_index_name;--get indexs
  loop
    FETCH c_index_name into  v_index_name;
    exit when c_index_name%NOTFOUND;
    --do something
  end loop;
  close c_index_name;  dbms_sql.close_cursor(v_cursor);
  dbms_output.put_line('关闭了公共游标');
  dbms_output.put_line('完成了分析过程');
exception
  when others then
    if dbms_sql.is_open(v_cursor) then
      dbms_sql.close_cursor(v_cursor);
    end if;end;

Answer 9

第一句的动态sql语句你自己改，我的有点问题，没有加tablespace

Answer 10

希望楼上人兄针对楼主的问题，不要发一些没有帮助语句。烦！是否语句已出现错语，试试比较简单语句能否执行。
如：v_sql:='select MENUID,P_NAME from MENU_LIST'

Answer 11

比较简单语句也不行，我直接赋值
v_sql:='select MENUID,P_NAME from MENU_LIST';
然后再使用
open t_menu FOR v_sql;
调用也不行，也是Ora-01001的错误，
除非我这样写：
open t_menu FOR select MENUID,P_NAME from MENU_LIST；
这样可以得到正确的结果。这样看来，似乎是open t_menu FOR v_sql;这一句没有正确的解析。
我的数据库版本是 Oracle 8i (8.1.7.4.0)
按道理来讲应该是可以的……

Answer 12

Ora-01001的错误
Cause: Either a host language program call specified an invalid cursor or the value of the MAXOPENCURSORS option in the precompiler command were too small. All cursors must be opened using the OOPEN call before being referenced in any of the following calls: SQL, DESCRIBE, NAME, DEFINE, BIND, EXEC, FETCH, and CLOSE. The Logon Data Area (LDA) must be defined by using OLON or OLOGON. If the LDA is not defined, this message is issued for the following calls: OPEN, COM, CON, ROL, and LOGOFF.Action: Check the erroneous call statement. Specify a correct LDA area or open the cursor as required. If there is no problem with the cursor, it may be necessary to increase the MAXOPENCURSORS option value before precompiling.

Answer 13

我的用户的MAXOPENCURSORS=1000，
而且现在就我在使用。