创建包 CREATE OR REPLACE PACKAGE pack_test AS TYPE mycur IS REF CURSOR; END pack_test; 创建存储过程 create or replace procedure p_test(v_tablename in varchar2, p_rc out pack_test.mycur)as v_sql varchar2(2000); begin v_sql := 'select * from '||v_tablename ; open p_rc for v_sql; end p_test;
有记录集返回的存储过程要写在包中,而且要在包头定义记录集类型 type g_rs is ref cursor; 然后在包体中使用 create or replace procedure test(tabname in varchar2,rs out g_rs) as begin open rs for 'select * from '||tabname||' where ...'; end;
用字符串拼接一下就可以了啊
v_syntax varchar2(256);
v_Table varchar2(64);
begin
v_syntax := 'Select * From ' || v_Table ;
EXECUTE IMMEDIATE v_syntax ;
END;
动态SQL还可以取得执行SQL的值,如INTO一个变量。就看你做什么用了。
CREATE OR REPLACE PACKAGE pack_test
AS
TYPE mycur IS REF CURSOR;
END pack_test;
创建存储过程
create or replace procedure p_test(v_tablename in varchar2, p_rc out pack_test.mycur)as
v_sql varchar2(2000);
begin
v_sql := 'select * from '||v_tablename ;
open p_rc for v_sql;
end p_test;
type g_rs is ref cursor;
然后在包体中使用
create or replace procedure test(tabname in varchar2,rs out g_rs)
as
begin
open rs for 'select * from '||tabname||' where ...';
end;