NewKids 大师帮忙写个PLSQL

newkid · 发表于 2023-2-23 08:56

DECLARE
CURSOR C_STR IS SELECT STR FROM STR_TABLE;
TYPE SEG_ARRAY IS TABLE OF REF_TABLE%ROWTYPE;
L_SEG_ARRY    SEG_ARRAY;
V_INPUT_STR STR_TABLE.STR%TYPE;
V_SEG       REF_TABLE.SEG%TYPE;
V_SEG_LENGTH REF_TABLE.SEG_LENGTH%TYPE;
V_SUB_STR    VARCHAR2 (100);
L_COUNTER    NUMBER := 1;
BEGIN
OPEN C_STR;
SELECT *
   BULK COLLECT INTO L_SEG_ARRY
   FROM REF_TABLE;

LOOP
      FETCH C_STR INTO V_INPUT_STR;
      EXIT WHEN C_STR%NOTFOUND;
      WHILE V_INPUT_STR IS NOT NULL
      LOOP
         L_COUNTER :=1;  --------------- 漏掉了这一句
         V_SEG := L_SEG_ARRY (L_COUNTER).SEG;
         WHILE L_COUNTER <= L_SEG_ARRY.COUNT AND SUBSTR (V_INPUT_STR, 1, 2) != V_SEG
         LOOP
            V_SEG := L_SEG_ARRY (L_COUNTER).SEG;
            L_COUNTER := L_COUNTER + 1;
         END LOOP;
         IF L_COUNTER <= L_SEG_ARRY.COUNT
         THEN
            V_SEG_LENGTH := L_SEG_ARRY (L_COUNTER).SEG_LENGTH;
            V_SUB_STR := SUBSTR (V_INPUT_STR, 1, V_SEG_LENGTH);
            DBMS_OUTPUT.PUT_LINE (V_SUB_STR);
            V_INPUT_STR := SUBSTR (V_INPUT_STR, 1 + V_SEG_LENGTH);
         ELSE
            V_INPUT_STR := NULL;
         END IF;
      END LOOP;
END LOOP;
CLOSE C_STR;
END;
/

KEN6503 · 发表于 2023-2-23 09:49

newkid 发表于 2023-2-22 15:12
先简单批改一下，回答你帖子中提出的问题：DECLARE CURSOR C_SEGMENTS IS SELECT SEG, SEG_LENGTH FROM ...

修改了一下，但是只读第一条记录，结果也是对的，
DECLARE
CURSOR C_STR IS SELECT STR FROM STR_TABLE;
TYPE SEG_ARRAY IS TABLE OF REF_TABLE%ROWTYPE;
L_SEG_ARRY    SEG_ARRAY;
V_INPUT_STR STR_TABLE.STR%TYPE;
V_SEG       REF_TABLE.SEG%TYPE;
V_SEG_LENGTH REF_TABLE.SEG_LENGTH%TYPE;
V_SUB_STR    VARCHAR2 (100);
L_COUNTER    NUMBER := 1;
BEGIN
OPEN C_STR;
SELECT *
   BULK COLLECT INTO L_SEG_ARRY
   FROM REF_TABLE;
LOOP
      FETCH C_STR INTO V_INPUT_STR;
      EXIT WHEN C_STR%NOTFOUND;
      WHILE V_INPUT_STR IS NOT NULl
      LOOP
   V_SEG := L_SEG_ARRY (L_COUNTER).SEG;
WHILE    L_COUNTER <= L_SEG_ARRY.COUNT
AND SUBSTR (V_INPUT_STR, 1, 2) != V_SEG
         LOOP
V_SEG := L_SEG_ARRY (L_COUNTER).SEG;
      END LOOP;
V_SEG_LENGTH := L_SEG_ARRY (L_COUNTER).SEG_LENGTH;
V_SUB_STR := SUBSTR (V_INPUT_STR, 1, V_SEG_LENGTH);
DBMS_OUTPUT.PUT_LINE (V_SUB_STR);
V_INPUT_STR := SUBSTR (V_INPUT_STR, 1 + V_SEG_LENGTH);
L_COUNTER := L_COUNTER + 1;
L_SEG_ARRY.EXTEND;
      END LOOP;
END LOOP;
CLOSE C_STR;
END;

CREATE TABLE STR_TABLE
(
STR VARCHAR2 (200 CHAR)
);
INSERT INTO STR_TABLE (STR)
   VALUES ('AC12345BC456ACDF1234567');
INSERT INTO STR_TABLE (STR)
   VALUES ('DFXYZXYZ8BC12345');
COMMIT;
CREATE TABLE REF_TABLE
(
SEG          VARCHAR2 (20 CHAR),
SEG_LENGTH NUMBER
);
INSERT INTO REF_TABLE (SEG, SEG_LENGTH)
   VALUES ('AC', 7);
INSERT INTO REF_TABLE (SEG, SEG_LENGTH)
   VALUES ('BC', 7);
INSERT INTO REF_TABLE (SEG, SEG_LENGTH)
   VALUES ('DF', 9);
COMMIT;

EXPECTED RESULT

AC12345

BC456AC

DF1234567

DFXYZXYZ8

BC12345

KEN6503 · 发表于 2023-2-23 10:16

谢谢大师，加上那句后不报错了，但是、每条只得到第一个substring. 我下面那个，是只有第一个string的结果，不知道为什么不读第二个string

KEN6503 · 发表于 2023-2-23 19:29

KEN6503 发表于 2023-2-22 20:49
修改了一下，但是只读第一条记录，结果也是对的，DECLARE CURSOR C_STR IS SELECT STR FROM STR_TABLE; ...

问题找到了，但是不知道该如何fix, 我在下面这两行间加了output ，打出来看到l_counter的值在第二个string出来时是4(期望值应是1），这4是因为我在fix那个init 时在后面的code中加了数组extend.
现在如果我在while之前加一个l_counter：=1，整个process就无休止的运行而不出任何结果。
麻烦大师帮忙看看，

EXIT WHEN C_STR%NOTFOUND;
WHILE V_INPUT_STR IS NOT NULl

newkid · 发表于 2023-2-23 22:32

12楼写法根本就是瞎胡闹。你的数组不放新数据扩展它干嘛？下标溢出是因为你忘记复位，这个循环每次都得从头再来，你连这个都没理解，这次作业我给零分！
10楼有个bug我上次漏掉了。我把这三句按顺序单独拿出来：

            V_SEG := L_SEG_ARRY (L_COUNTER).SEG;
            L_COUNTER := L_COUNTER + 1;
            V_SEG_LENGTH := L_SEG_ARRY (L_COUNTER).SEG_LENGTH;

看到了吗？计数器中间加了1, 导致V_SEG和V_SEG_LENGTH不配套了，牛头不对马嘴。修改如下：

DECLARE
CURSOR C_STR IS SELECT STR FROM STR_TABLE;
TYPE SEG_ARRAY IS TABLE OF REF_TABLE%ROWTYPE;
L_SEG_ARRY    SEG_ARRAY;
V_INPUT_STR STR_TABLE.STR%TYPE;
V_SEG       REF_TABLE.SEG%TYPE;
V_SEG_LENGTH REF_TABLE.SEG_LENGTH%TYPE;
V_SUB_STR    VARCHAR2 (100);
L_COUNTER    NUMBER := 1;
BEGIN
OPEN C_STR;
SELECT *
   BULK COLLECT INTO L_SEG_ARRY
   FROM REF_TABLE;

LOOP
      FETCH C_STR INTO V_INPUT_STR;
      EXIT WHEN C_STR%NOTFOUND;
      WHILE V_INPUT_STR IS NOT NULL
      LOOP
         L_COUNTER :=1;  --------------- 漏掉了这一句
         --- V_SEG := L_SEG_ARRY (L_COUNTER).SEG;  --------- 可以不要了
         WHILE L_COUNTER <= L_SEG_ARRY.COUNT AND SUBSTR (V_INPUT_STR, 1, 2) != L_SEG_ARRY (L_COUNTER).SEG  -------------- 这里修改了
         LOOP
            ---- V_SEG := L_SEG_ARRY (L_COUNTER).SEG; --------- 可以不要了
            L_COUNTER := L_COUNTER + 1;
         END LOOP;
         IF L_COUNTER <= L_SEG_ARRY.COUNT
         THEN
            V_SEG_LENGTH := L_SEG_ARRY (L_COUNTER).SEG_LENGTH;
            V_SUB_STR := SUBSTR (V_INPUT_STR, 1, V_SEG_LENGTH);
            DBMS_OUTPUT.PUT_LINE (V_SUB_STR);
            V_INPUT_STR := SUBSTR (V_INPUT_STR, 1 + V_SEG_LENGTH);
         ELSE
            V_INPUT_STR := NULL;
         END IF;
      END LOOP;
END LOOP;
CLOSE C_STR;
END;
/

上面都只是入门级的小儿科，等你彻底理解了我再来示范高级一点的写法。

KEN6503 · 发表于 2023-2-24 06:06

newkid 发表于 2023-2-23 09:32
12楼写法根本就是瞎胡闹。你的数组不放新数据扩展它干嘛？下标溢出是因为你忘记复位，这个循环每次都得从头 ...

非常感谢大师耐心的批改这个作业。又学到了很多的知识。对sql developer 来说，确实是最基本。以前只是写query,没写过plsql。所以对数组的使用没有任何概念。
再次感谢大师赐教。

newkid · 发表于 2023-2-25 01:19

下面是一种比较好的写法。
你每次为了找一个匹配的seg, 都要全表扫描所有的REF_TABLE记录。最早是反复打开游标，后来修改成数组遍历，但是仍然很低效。
因为你匹配的长度是固定的两个字符，所以可以利用关联数组的字符串索引特性，一下子就找到匹配的REF_TABLE记录，而不需要一个一个比过去。
此外我把你的显式游标换成FOR游标循环，这是更好的做法。
DECLARE
TYPE SEG_ARRAY IS TABLE OF NUMBER INDEX BY REF_TABLE.SEG%TYPE; ---- 用seg做索引
L_SEG_ARRY    SEG_ARRAY;
V_INPUT_STR STR_TABLE.STR%TYPE;
V_SEG_LENGTH REF_TABLE.SEG_LENGTH%TYPE;
V_SUB_STR    VARCHAR2 (100);
BEGIN
FOR v_rec IN (SELECT * FROM REF_TABLE) LOOP
      L_SEG_ARRY(v_rec.seg):=v_rec.SEG_LENGTH;  ---- 用seg做索引
END LOOP;

FOR v_rec IN (SELECT STR FROM STR_TABLE) LOOP
      V_INPUT_STR := v_rec.str;

      WHILE V_INPUT_STR IS NOT NULL
      LOOP
         IF L_SEG_ARRY.EXISTS(SUBSTR (V_INPUT_STR, 1, 2))  ---- 用seg做索引
         THEN
            V_SEG_LENGTH := L_SEG_ARRY(SUBSTR (V_INPUT_STR, 1, 2)); ---- 用seg做索引，立即就找到了，无需遍历数组
            V_SUB_STR := SUBSTR (V_INPUT_STR, 1, V_SEG_LENGTH);
            DBMS_OUTPUT.PUT_LINE (V_SUB_STR);
            V_INPUT_STR := SUBSTR (V_INPUT_STR, 1 + V_SEG_LENGTH);
         ELSE
            V_INPUT_STR := NULL;
         END IF;
      END LOOP;
END LOOP;

END;
/

KEN6503 · 发表于 2023-2-27 07:28

newkid 发表于 2023-2-24 12:19
下面是一种比较好的写法。你每次为了找一个匹配的seg, 都要全表扫描所有的REF_TABLE记录。最早是反复打开游 ...

谢谢大师提出这个更好的写法，一定要消化吸收，认真学习

newkid · 发表于 2023-2-28 01:07

KEN6503 发表于 2023-2-27 07:28
谢谢大师提出这个更好的写法，一定要消化吸收，认真学习

你经常写SQL应该知道HASH JOIN的原理，我后来写的这个方法其实就是HASH JOIN。

KEN6503 · 发表于 2023-3-1 10:12

newkid 发表于 2023-2-27 12:07
你经常写SQL应该知道HASH JOIN的原理，我后来写的这个方法其实就是HASH JOIN。

明白了，谢谢

[PL/SQL] NewKids 大师帮忙写个PLSQL

浏览过的版块