17,078
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure GetResultProc(in_paraid in varchar2,
out_ResultSql out varchar2 ) as
sSql varchar2(2000);
sSql1 varchar2(2000);
type v_Curtype is ref cursor;
v_cur v_Curtype;
-- v_ResultCur v_curtype;
s_paraname varchar2(20);
s_paravalue varchar2(20);
iCount number;
begin
if in_paraid is null then
return;
end if;
sSql := 'select paraname,paravalue from tb_para where paraid='||in_paraid;
iCount := 0;
open v_cur for sSql;
loop
fetch v_cur into s_paraname,s_paravalue;
exit when v_cur%notfound;
if iCount = 0 then
sSql1 := 'select * from tb_table where '||s_paraname||'='''||s_paravalue||'''';
else
sSql1 := sSql1||' and '||s_paraname||'='''||s_paravalue||'''';
end if;
iCount := iCount+1;
end loop;
close v_cur;
if sSql1 is null then
return;
end if;
-- open v_ResultCur fro sSql1;
out_ResultSql := sSql1;
end;
CREATE OR REPLACE PROCEDURE pro_query
(
paraID IN int,
p_cus OUT TYPES.cursorType --我定义的返回游标,用这个返回给JAVA调用端
)
AS
vs_lx VARCHAR2(200);
v_paraname varchar2(100);
v_paravalue varchar2(100);
v_sql varchar2(1000);
cursor c_getpar(para_id number) is
select paraname,paravalue from tb_para where paraid = :para_id;
BEGIN
--根据传入的参数paraID ,去tb_para循环取得参数名和参数值
v_sql := 'select * from tb_table where 1=1 ';
open c_getpar(paraID);
fetch c_getpar into v_paraname,v_paravalue;
while c_getpar%found loop
--组合SQL语句,比如传入参数是1,那组合成的语句就是select * from tb_table where name='zhangdan' and age='30'
v_sql := v_sql || ' ' || v_paraname || ' = ' || v_paravalue;
fetch c_getpar into v_paraname,v_paravalue;
end loop;
--返回游标参数赋值
OPEN p_cus FOR v_sql; --刚才那个组合成的语句,最后返回的就是这个类型
End;