有两个表:
表一: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'
表一: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'
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;
DECLARE
CURSOR cur_a IS SELECT * FROM tablea;
CURSOR cur_b IS SELECT * FROM tableb;
v_location NUMBER(3):=0;
BEGIN
FOR i IN cur_a
LOOP
FOR j IN cur_b
LOOP
v_location:=0;
SELECT InStr(','||i.id||',',','||j.id2||',') INTO v_location FROM dual;
Dbms_Output.PUT_LINE(i.id||'=='||j.id2||'=='||v_location);
EXIT WHEN v_location>0;
END LOOP;
UPDATE tablea SET yesorno=Decode(v_location,0,'y','n') WHERE num=i.num AND id=i.id;
END LOOP;
COMMIT;
END;
不知这是什么原因呢??
则
SELECT InStr(','||'8'||',',','||'8'||',') FROM dual;
instr结果仍>0
表一:xt_xtcs00_zlcs
name00 syxt00 ecqrzd (这是错误的结果 ) a E,Z y
b E,Z y
c E,Z y
d E,Z y
e 3,k1 n
f -1 y
g -1 y
h -1 y
i 5 y
g 9 y
k 7 y
l -1 y
m 5 y
n -1 y
o CP n
p CP n
表二:XT_YYXT00 XTDM00
E
Z
CP
3
5
7
9
k1
我相对于这个表的修改的代码如下:
DECLARE
CURSOR cur_a IS SELECT * FROM xt_xtcs00_zlcs;
CURSOR cur_b IS SELECT * FROM xt_yyxt00;
v_location NUMBER(3):=0;
BEGIN
FOR i IN cur_a
LOOP
FOR j IN cur_b
LOOP
v_location:=0;
SELECT InStr(','||i.syxt00||',',','||j.XTDM00||',') INTO v_location FROM dual;
Dbms_Output.PUT_LINE(i.id||'=='||j.id2||'=='||v_location);
EXIT WHEN v_location>0;
END LOOP;
UPDATE xt_xtcs00_zlcs SET ecqrzd=Decode(v_location,0,'y','n') WHERE name00=i.name00 and syxt00=i.syxt00;
END LOOP;
COMMIT;
END;
问题:语法都没有错,就是得不到希望的结果。如:原本ecqrad一列的的值应该是n,n,n,n,y,y,y,n,n,n,y,n,y,n,n。可是运行后得不到期望的结果
name00 syxt00 ecqrzd (这是错误的结果 ) a E,Z y
b E,Z y
c E,Z y
d E,Z y
e 3,k1 n
f -1 y
g -1 y
h -1 y
i 5 y
g 9 y
k 7 y
l -1 y
m 5 y
n -1 y
o CP n
p CP n
CREATE TABLE xt_xtcs00_zlcs(name00 VARCHAR2(5),syxt00 VARCHAR2(20),ecqrzd VARCHAR2(1));
CREATE TABLE XT_YYXT00(XTDM00 VARCHAR2(5));
INSERT INTO xt_xtcs00_zlcs(name00,syxt00) VALUES ('a','E,Z');
INSERT INTO xt_xtcs00_zlcs(name00,syxt00) VALUES ('b','E,Z');
INSERT INTO xt_xtcs00_zlcs(name00,syxt00) VALUES ('c','E,Z');
INSERT INTO xt_xtcs00_zlcs(name00,syxt00) VALUES ('d','E,Z');
INSERT INTO xt_xtcs00_zlcs(name00,syxt00) VALUES ('e','3,k1');
INSERT INTO xt_xtcs00_zlcs(name00,syxt00) VALUES ('f','-1');
INSERT INTO xt_xtcs00_zlcs(name00,syxt00) VALUES ('g','-1');
INSERT INTO xt_xtcs00_zlcs(name00,syxt00) VALUES ('h','-1');
INSERT INTO xt_xtcs00_zlcs(name00,syxt00) VALUES ('i','5');
INSERT INTO xt_xtcs00_zlcs(name00,syxt00) VALUES ('g','9');
INSERT INTO xt_xtcs00_zlcs(name00,syxt00) VALUES ('k','7');
INSERT INTO xt_xtcs00_zlcs(name00,syxt00) VALUES ('1','-1');
INSERT INTO xt_xtcs00_zlcs(name00,syxt00) VALUES ('m','5');
INSERT INTO xt_xtcs00_zlcs(name00,syxt00) VALUES ('n','-1');
INSERT INTO xt_xtcs00_zlcs(name00,syxt00) VALUES ('o','CP');
INSERT INTO xt_xtcs00_zlcs(name00,syxt00) VALUES ('p','CP' );INSERT INTO XT_YYXT00 VALUES ('E');
INSERT INTO XT_YYXT00 VALUES ('Z');
INSERT INTO XT_YYXT00 VALUES ('CP');
INSERT INTO XT_YYXT00 VALUES ('3');
INSERT INTO XT_YYXT00 VALUES ('5');
INSERT INTO XT_YYXT00 VALUES ('7');
INSERT INTO XT_YYXT00 VALUES ('9');
INSERT INTO XT_YYXT00 VALUES ('k1');DECLARE
CURSOR cur_a IS SELECT * FROM xt_xtcs00_zlcs;
CURSOR cur_b IS SELECT * FROM xt_yyxt00;
v_location NUMBER(3):=0;
BEGIN
FOR i IN cur_a
LOOP
FOR j IN cur_b
LOOP
v_location:=0;
SELECT InStr(','||i.syxt00||',',','||j.XTDM00||',') INTO v_location FROM dual;
--Dbms_Output.PUT_LINE(i.syxt00||'=='||j.XTDM00||'=='||v_location);
EXIT WHEN v_location>0;
END LOOP;
UPDATE xt_xtcs00_zlcs SET ecqrzd=Decode(v_location,0,'y','n') WHERE name00=i.name00 and syxt00=i.syxt00;
END LOOP;
COMMIT;
END;output:
NAME00, SYXT00, ECQRZD
a E,Z n
b E,Z n
c E,Z n
d E,Z n
e 3,k1 n
f -1 y
g -1 y
h -1 y
i 5 n
g 9 n
k 7 n
1 -1 y
m 5 n
n -1 y
o CP n
p CP n
如何按照指定的路径进行查找指定的关键字。例如指定路径F:/YANG/D7里查找关键子thinkof。
这个该用哪个函数呢?具体实现是怎样的。
有关全文检索的问题:
已知路径,从本地文件中查找关键字。例如 F:/aa/d5 中查找关键字“中国”,这个的代码改如何写呢??