17,086
社区成员
发帖
与我相关
我的任务
分享关于动态sql的问题(急)
请问大家在oracle里面写动态sql时，要用到一个number变量和一个varchar2变量，
该怎么写呢？
比如：
select * from employee where orgid = iv_orgid and name = iv_name;
其中iv_orgid为number变量，iv_name为varchar2变量，这个语句要用动态语
句怎么写呢？（在存储过程里面)
谢谢
该怎么写呢？
比如：
select * from employee where orgid = iv_orgid and name = iv_name;
其中iv_orgid为number变量，iv_name为varchar2变量，这个语句要用动态语
句怎么写呢？（在存储过程里面)
谢谢
...全文
请发表友善的回复…
发表回复
logzgh 2003-08-26
- 打赏
- 举报
beckhambobo(beckham) :我上面只是举个例子而已。
我试了一下to_char是可以的。呵呵。。谢了
我试了一下to_char是可以的。呵呵。。谢了
beckhambobo 2003-08-25
- 打赏
- 举报
干嘛用游标取，把两个表关联就可以了
str:='select * from employee a,dept b where a.orgid =b.orgid and a.name =b.name';
open p_rc for str;
str:='select * from employee a,dept b where a.orgid =b.orgid and a.name =b.name';
open p_rc for str;
jiezhi 2003-08-25
- 打赏
- 举报
to_char(iv_orgid)
logzgh 2003-08-25
- 打赏
- 举报
对了，我这个是个循环的的动态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呢？
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呢？
beckhambobo 2003-08-25
- 打赏
- 举报
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;
/
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;
/
如：order by #user_id#，如果传入的值是111,那么解析成sql时的值为order by ‘111’,如果传入的值是id，则解析成的sql为order by “id”.原文链接：...
