有两个表：
表一：tableA
num id yesorno
1 1,5,8
2 2,5,9
3 4,22
4 3,5,-9
5 2,4,3,6
表二：TABLEB
id2
1
2
3
4
5
6
7
8
9
要求：若tableA的id都不能能在tableB中找到，则标志yesorno为‘y'，否则为’n‘ 如1,5,8都能在在tableB中找到，则标为’n'
------解决方案--------------------
可以不用游标都可以，两条Update语句就OK了
- SQL code
CREATE TABLE tableA(num NUMBER(5),id VARCHAR2(50),yesorno VARCHAR2(1));INSERT INTO tablea(num,id) VALUES(1,'1,5,8');INSERT INTO tablea(num,id) VALUES(2,'2,5,9');INSERT INTO tablea(num,id) VALUES(3,'4,22');INSERT INTO tablea(num,id) VALUES(4,'3,5,-9');INSERT INTO tablea(num,id) VALUES(5,'2,4,3,6');INSERT INTO tablea(num,id) VALUES(5,'2,4,3,6');INSERT INTO tablea(num,id) VALUES(6,'10,20'); CREATE TABLE tableb(id2 number(2));INSERT INTO tableb VALUES(1);INSERT INTO tableb VALUES(2);INSERT INTO tableb VALUES(3);INSERT INTO tableb VALUES(4);INSERT INTO tableb VALUES(5);INSERT INTO tableb VALUES(6);INSERT INTO tableb VALUES(7);INSERT INTO tableb VALUES(8);INSERT INTO tableb VALUES(9);UPDATE tablea SET yesorno='y' WHERE NOT EXISTS (SELECT 1 FROM TABLEb WHERE InStr(','||tablea.id||',',','||tableb.id2||',')>0);UPDATE TABLEa SET yesorno='n' WHERE yesorno IS NULL;
------解决方案--------------------