DECLARE j INT; aa VARCHAR2(300); bb VARCHAR2(10); cc VARCHAR2(20); BEGIN j:=1; aa:='aa,bb,cc,dd'; bb:=','; WHILE j<>0 LOOP j:=INSTR(aa,bb); IF j=0 THEN cc:=SUBSTR(aa,j+1,LENGTH(aa)); ELSE cc:=SUBSTR(aa,1,j-1); aa:=SUBSTR(aa,j+1,LENGTH(aa)); END IF; --DBMS_OUTPUT.PUT_LINE(aa); --DBMS_OUTPUT.PUT_LINE(j); DBMS_OUTPUT.PUT_LINE(cc); END LOOP; END;
substring('aa,bb,cc,dd',4,2)
substring('aa,bb,cc,dd',7,2)
substring('aa,bb,cc,dd',10,2)
字符串有可能是('aa','dfsd','sdfaw','dsfdsfadsf')
DECLARE
j INT;
aa VARCHAR2(300);
bb VARCHAR2(10);
cc VARCHAR2(20);
BEGIN
j:=1;
aa:='aa,bb,cc,dd';
bb:=',';
WHILE j<>0
LOOP
j:=INSTR(aa,bb);
IF j=0 THEN
cc:=SUBSTR(aa,j+1,LENGTH(aa));
ELSE
cc:=SUBSTR(aa,1,j-1);
aa:=SUBSTR(aa,j+1,LENGTH(aa));
END IF;
--DBMS_OUTPUT.PUT_LINE(aa);
--DBMS_OUTPUT.PUT_LINE(j);
DBMS_OUTPUT.PUT_LINE(cc);
END LOOP;
END;