该怎么写呢?
比如:
select * from employee where orgid = iv_orgid and name = iv_name;
其中iv_orgid为number变量,iv_name为varchar2变量,这个语句要用动态语
句怎么写呢?(在存储过程里面)
谢谢
...全文
885打赏收藏
关于动态sql的问题(急)
请问大家在oracle里面写动态sql时,要用到一个number变量和一个varchar2变量, 该怎么写呢? 比如: select * from employee where orgid = iv_orgid and name = iv_name; 其中iv_orgid为number变量,iv_name为varchar2变量,这个语句要用动态语 句怎么写呢?(在存储过程里面) 谢谢
对了,我这个是个循环的的动态sql语句,通过一个游标循环,即
loop
fetch c_cursor into ic_orgid,ic_name;
exit when c_cursor%notfound;
--动态语句
end loop
这样就没办法用execute immediate lv_sqlstatement using iv_orgid了。
能不能用to_char把iv_orgid变成varchar2呢?
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/
create procedure pro(iv_orgid in number,iv_name in varchar2,p_rc out pkg_test.myrctype)
as
str varchar2(50);
begin
str:='select * from employee where orgid ='|| iv_orgid||' and name ='||iv_name;
open p_rc for str;
end;
/