错了点,如果传入参数为1,那么sql语句就是:select * from tb_table where name='zhangdan' and age='30'
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;
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;这种写法只是把语句拼出来
(
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;
v_sql := v_sql || ' ' || v_paraname || ' = ' || v_paravalue;
漏了个and
改成
v_sql := v_sql || ' and ' || v_paraname || ' = ' || v_paravalue;
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;这种写法只是把语句拼出来