procedure pro_select(p_para in number) as begin if p_para is not null then select name from student where age> p_para; end if; end;
这是带返回数据集的:procedure pro_select(p_para in number,p_dataset out dataset) as l_sql varchar(200); begin l_sql := 'select name from student '; if p_para is not null then l_sql := l_sql || 'where age > '|| p_para; end if; open p_dataset for l_sql; end;
首先你要创建一个type类存储结果 create or replace type type_jax_varc2tab is table of varchar2(2000) 然后创建一个如下的function来返回结果 CREATE OR REPLACE FUNCTION f_jax_returntab(p_str IN VARCHAR2 ) RETURN type_jax_varc2tab IS /****************************************************************** Ver1.0 Created by jaxzhang on 2011-01-13 create or replace type type_jax_varc2tab is table of varchar2(2000); 测试用例:SELECT * FROM TABLE(f_jax_returntab('ACCTG_TRANS')); ******************************************************************/ v_numtab type_jax_varc2tab := type_jax_varc2tab(); --返回内存表 BEGIN FOR rec IN (select rownum r1,table_name from user_tables where table_name like p_str||'%' ) LOOP v_numtab.EXTEND; v_numtab(rec.r1) := rec.table_name; END LOOP; RETURN v_numtab; EXCEPTION WHEN OTHERS THEN v_numtab.DELETE; END; 查询样式如下SELECT * FROM TABLE(f_jax_returntab('ACCTG_TRANS'));
是函数,create or replace function......多谢回复!
那就把2楼包体里的sql直接写在函数里就ok了
SQL> SQL> create or replace function lxljut(i_age in number) return sys_refcursor as 2 r_cursor sys_refcursor; 3 begin 4 open r_cursor for 5 select name from student where age > i_age; 6 return r_cursor; 7 end; 8 /
procedure pro_select(p_para in number)
as
begin
if p_para is not null then
select name from student where age> p_para;
end if;
end;
as
l_sql varchar(200);
begin
l_sql := 'select name from student ';
if p_para is not null then
l_sql := l_sql || 'where age > '|| p_para;
end if;
open p_dataset for l_sql;
end;
create or replace type type_jax_varc2tab is table of varchar2(2000)
然后创建一个如下的function来返回结果
CREATE OR REPLACE FUNCTION f_jax_returntab(p_str IN VARCHAR2
) RETURN type_jax_varc2tab IS
/******************************************************************
Ver1.0 Created by jaxzhang on 2011-01-13 create or replace type type_jax_varc2tab is table of varchar2(2000);
测试用例:SELECT * FROM TABLE(f_jax_returntab('ACCTG_TRANS'));
******************************************************************/
v_numtab type_jax_varc2tab := type_jax_varc2tab(); --返回内存表
BEGIN FOR rec IN (select rownum r1,table_name from user_tables where table_name like p_str||'%' ) LOOP
v_numtab.EXTEND;
v_numtab(rec.r1) := rec.table_name;
END LOOP; RETURN v_numtab;
EXCEPTION
WHEN OTHERS THEN
v_numtab.DELETE;
END;
查询样式如下SELECT * FROM TABLE(f_jax_returntab('ACCTG_TRANS'));
SQL>
SQL> create or replace function lxljut(i_age in number) return sys_refcursor as
2 r_cursor sys_refcursor;
3 begin
4 open r_cursor for
5 select name from student where age > i_age;
6 return r_cursor;
7 end;
8 /