17,082
社区成员
发帖
与我相关
我的任务
分享
--先打印出sql语句,然后直接执行你打印的语句看有无记录
set serveroutput on;
declare
V_CONDTION varchar2(1000):= '1,2,3,4';--换成你的值
v_str varchar2(4000):='';
begin
v_str:= 'select a.s_txm,b.content from shuibiaoxx a
inner join (
select * from words where belongid=''106''
) b on a.s_ztid=b.wordsid
where a.s_txm in('||V_CONDTION||')';
dbms_output.put_line(v_str);
end;
/
--用这个语句看下有无记录
select a.s_txm,b.content from shuibiaoxx a
inner join (
select * from words
where belongid='106'
) b on a.s_ztid=b.wordsid
where a.s_txm in(1,2,3,4); --1,2,3,4,换成你的值
--这是我写的个简单的例子,你看下
SQL> declare
2 type cur_type is ref cursor;
3 cur cur_type;
4 rec emp%rowtype;
5 str varchar2(50);
6 letter varchar2(50):= '7369,7566';
7 begin
8 str:= 'select ename from emp where empno in ('||letter||')';
9 open cur for str;
10 loop
11 fetch cur into rec.ename;
12 exit when cur%notfound;
13 dbms_output.put_line(rec.ename);
14 end loop;
15 end;
16 /
SMITH
JONES
--源码
declare
type cur_type is ref cursor;
cur cur_type;
rec emp%rowtype;
str varchar2(50);
letter varchar2(50):= '7369,7566';
begin
str:= 'select ename from emp where empno in ('||letter||')';
open cur for str;
loop
fetch cur into rec.ename;
exit when cur%notfound;
dbms_output.put_line(rec.ename);
end loop;
end;
/
open v_cursor for 'select a.s_txm,b.content from shuibiaoxx a
inner join (
select * from words where belongid=''106''--此处我写错了也可以去掉''直接106,毕竟是数字
) b on a.s_ztid=b.wordsid
where a.s_txm in('||V_CONDTION||')';
--改成下面这种试下
open v_cursor for 'select a.s_txm,b.content from shuibiaoxx a
inner join (
select * from words where belongid='106'
) b on a.s_ztid=b.wordsid
where a.s_txm in('||V_CONDTION||')';