oracle创建存储过程并返回查询结果示例

create or replace procedure TEST --存储过程名
(
arg1 in varchar2,--参数1
arg2  in varchar2,--参数2 
v_out_result    out sys_refcursor --返回游标值(结果集)
) is
selectsql CLOB;
sqlone CLOB;
sqltwo CLOB;
begin
sqlone :='SELECT REGEXP_SUBSTR ('''||arg1||''', ''[^,]+'', 1,rownum) from dual connect by rownum<=LENGTH ('''||arg1||''') - LENGTH (regexp_replace('''||arg1||''', '','', ''''))+1 ';
sqltwo :='SELECT REGEXP_SUBSTR (''123,456,789'', ''[^,]+'', 1,rownum) from dual connect by rownum<=LENGTH (''123,456,789'') - LENGTH (regexp_replace(''123,456,789'', '','', ''''))+1 ';
if (arg2='1' or arg2 is null)  then selectsql := sqlone ;elsif arg2='2' then selectsql := sqltwo ;  end if; --根据参数2进行判断sql展示见过
open v_out_result for selectsql; --运行拼接好的sql脚本
end TEST;