sql-plus执行过程如下,请指教SQL> CREATE OR REPLACE function "SUPER"."SP_GETTABLE"(tablename in varchar)
2 return userstype.ref_cursor
3 as
4 strresult varchar2(500);
5 tablenames_cursor userstype.ref_cursor;
6
7 BEGIN
8 strresult:='select birthday,code,identification from :1';
9 OPEN tablenames_cursor FOR strresult using tablename;
10 return tablenames_cursor;
11 END SP_GETTABLE;
12 /函数已创建。SQL> var results refcursor
SQL> exec :results := SP_GETTAble('TEST');
BEGIN :results := SP_GETTAble('TEST'); END;*
ERROR 位于第 1 行:
ORA-00903: 表名无效
ORA-06512: 在"SUPER.SP_GETTABLE", line 9
ORA-06512: 在line 1
2 return userstype.ref_cursor
3 as
4 strresult varchar2(500);
5 tablenames_cursor userstype.ref_cursor;
6
7 BEGIN
8 strresult:='select birthday,code,identification from :1';
9 OPEN tablenames_cursor FOR strresult using tablename;
10 return tablenames_cursor;
11 END SP_GETTABLE;
12 /函数已创建。SQL> var results refcursor
SQL> exec :results := SP_GETTAble('TEST');
BEGIN :results := SP_GETTAble('TEST'); END;*
ERROR 位于第 1 行:
ORA-00903: 表名无效
ORA-06512: 在"SUPER.SP_GETTABLE", line 9
ORA-06512: 在line 1
... ...
8 strresult:='select birthday,code,identification from ' || tablename;
9 OPEN tablenames_cursor FOR strresult;
... ...
--给LZ一个返回记录集的例子:sql>select select * from test; A B
---------- ----------
1 1
2 2
2 6
3 10sql>create or replace procedure p_test(in_var number,out_var out sys_refcursor)
2 as
3 begin
4 open out_var for select * from test where a=in_var;
5 end;
6 /Procedure created.sql>var abc refcursor
sql>exec p_test(2,:var_test)PL/SQL procedure successfully completed.sql>print :var_test A B
---------- ----------
2 2
2 6sql>
能否改为: strresult:='select birthday,code,identification from tablename'? 2.对于 exec :results := SP_GETTAble('TEST')而言,能否改为:
select SP_GETTAble('TEST') from dual;
return userstype.ref_cursor as
strresult varchar2(500);
tablenames_cursor userstype.ref_cursor;BEGIN
strresult := 'select birthday,code,identification from ' || tablename;
OPEN tablenames_cursor FOR strresult;
return tablenames_cursor;
END SP_GETTABLE;
/