CREATE OR REPLACE PROCEDURE getdata_from_tabletest ( in_ia IN NUMBER, in_ib IN NUMBER, out_cp OUT NUMBER ) is vs_sql varchar2(50); begin vs_sql := 'select c from tabletest where b=' || to_char(in_ib); if (in_ia is not null) then vs_sql := vs_sql || ' and a=' || to_char(in_ia); end if; EXECUTE IMMEDIATE vs_sql into out_cp; commit;
EXCEPTION WHEN others THEN null; end;
IF ia is not null then select c into cp from tabletest where a=ia and b=ib; else select c into cp from tabletest where b=ib; End if;
select c into cp from tabletest where a=NVL(ia,A) and b=ib
(
in_ia IN NUMBER,
in_ib IN NUMBER,
out_cp OUT NUMBER
)
is
vs_sql varchar2(50);
begin
vs_sql := 'select c from tabletest where b=' || to_char(in_ib); if (in_ia is not null) then
vs_sql := vs_sql || ' and a=' || to_char(in_ia);
end if; EXECUTE IMMEDIATE vs_sql into out_cp;
commit;
EXCEPTION WHEN others THEN null;
end;
select c into cp from tabletest where a=ia and b=ib;
else
select c into cp from tabletest where b=ib;
End if;
这样写不错,高!