想定义一个变量,保存查询满足条件的表的外键ID,然后根据这个ID,更新相关联的表。select billid into bill_id from
(select min(failflg) as failflg ,billid from chargerecord where id in(133463,179764) and GATHERINGTYPE=0 and delflg=0 and
( to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd') - to_date(to_char(faildate,'yyyymmdd') ,'yyyymmdd'))>60 group by billid
)where failflg=1;
bill_id 保存满足条件的chargerecord表中的billid.update bill set CANCELEDFLG=1 ,lastmodifyuser='system' ,lastmodifydate=sysdate where id in (bill_id) and
CANCELEDFLG=0 and DELFLG=0 and loseeffectedflg=0 and tmcanceledflg=0;
commit;
根据bill_id ,批量更新bill表。该如何定义 bill_id的类型,才能使用 where id in bill_id 这个条件语句
(select min(failflg) as failflg ,billid from chargerecord where id in(133463,179764) and GATHERINGTYPE=0 and delflg=0 and
( to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd') - to_date(to_char(faildate,'yyyymmdd') ,'yyyymmdd'))>60 group by billid
)where failflg=1;
bill_id 保存满足条件的chargerecord表中的billid.update bill set CANCELEDFLG=1 ,lastmodifyuser='system' ,lastmodifydate=sysdate where id in (bill_id) and
CANCELEDFLG=0 and DELFLG=0 and loseeffectedflg=0 and tmcanceledflg=0;
commit;
根据bill_id ,批量更新bill表。该如何定义 bill_id的类型,才能使用 where id in bill_id 这个条件语句
update bill set CANCELEDFLG=1 ,lastmodifyuser='system' ,lastmodifydate=sysdate
where CANCELEDFLG=0 and DELFLG=0 and loseeffectedflg=0 and tmcanceledflg=0
and exists (
select 1 from
(select min(failflg) as failflg ,billid from chargerecord where id in(133463,179764) and GATHERINGTYPE=0 and delflg=0 and
( to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd') - to_date(to_char(faildate,'yyyymmdd') ,'yyyymmdd'))>60 group by billid
) t where failflg=1
and instr(','||t.billid||',',','||bill.id||',')>0
);
commit;參考下面的
http://topic.csdn.net/u/20120904/14/8a8f5b16-fd4a-4c5c-bb67-ffbd48327459.html
连接我看了,那楼主的问题是出在 in 后面是字符串的问题上了。可我的是
select billid into bill_id from
(select min(failflg) as failflg ,billid from chargerecord where id in(133463,179764) and GATHERINGTYPE=0 and delflg=0 and
( to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd') - to_date(to_char(faildate,'yyyymmdd') ,'yyyymmdd'))>60 group by billid
)where failflg=1;
就会报错的。原因就是 bill_id我定义成 bill.id%type。我想知道如何将bill_id 定义为一个list(int)
我把 instr(','||t.billid||',',','||bill.id||',')>0直接写出 t.billid=bill.id,不可以吗