一道SQL实现转换的题目

Question

建表：
create table test3(text varchar2(200));数据：
insert into test3 values('a.101'||chr(10)||'b.工具');
insert into test3 values('a.102'||chr(10)||'b.水果'||chr(10)||'b.餐具');
insert into test3 values('a.103'||chr(10)||'b.时装'||chr(10)||'b.家具'||chr(10)||'b.电器');表记录：
SQL> select * from test3;TEXT
--------------------------------------------------------------------------------
a.101
b.工具a.102
b.水果
b.餐具a.103
b.时装
b.家具
b.电器试用一句SQL实现将其转换成如下结果集(可以不考虑性能问题)：柜台号   销售商品
101      工具
102      水果
102      餐具
103      时装
103      家具
103      电器

Answer 1

经过测试
1 101 工具
2 102 水果
3 102 餐具
4 103 时装
5 103 家具
6 103 电器CREATE TABLE AAAbbb(GT_NO VARCHAR2(10), GOODS VARCHAR2(50))
create or replace procedure temp000000 is  V_GT    VARCHAR2(10);
  V_goods VARCHAR2(50);
  BEGIN_N NUMBER;
  LEN_N   NUMBER;
  END_N   NUMBER;
  pos_n   NUMBER;
  pos_nl  NUMBER;
BEGIN
  --execute immediate 'CREATE TABLE AAAbbb(GT_NO VARCHAR2(10), GOODS VARCHAR2(50))';
  FOR CUR IN (SELECT * FROM test3) LOOP
    BEGIN_N := 6;
    END_N   := 1;
    LEN_N   := LENGTH(CUR.TEXT);
    V_GT    := SUBSTR(CUR.TEXT, 3, 3);
    while (BEGIN_N < LEN_N) loop
      SELECT INSTR(CUR.TEXT, 'b', BEGIN_N, END_N) into pos_n FROM DUAL;
      SELECT INSTR(CUR.TEXT, chr(10), pos_n, END_N) into pos_nl FROM DUAL;
    
      if pos_n != 0 then
        if pos_nl = 0 then
          select substr(CUR.TEXT, pos_n + 2, LEN_N - pos_n - 1)
            into V_goods
            from DUAL;
          BEGIN_N := LEN_N;
        else
          select substr(CUR.TEXT, pos_n + 2, pos_nl - pos_n - 2)
            into V_goods
            from DUAL;
          BEGIN_N := pos_nl;
        end if;
        insert into AAAbbb values (V_GT, V_goods);
        commit;
      else
        BEGIN_N := LEN_N;
      end if;
    end loop;
  END LOOP;
end temp000000;
select * from AAAbbb;

Answer 2

CREATE OR REPLACE PROCEDURE PR_TEST2
IS
V_SQL VARCHAR2(100);
V_TEST TEST2%ROWTYPE;
V_MC TEST2.MC%TYPE;
CURSOR TEST2_CUR IS SELECT * FROM TEST2;
BEGIN
     OPEN TEST2_CUR;
     FETCH TEST2_CUR INTO V_TEST;
     WHILE TEST2_CUR%FOUND LOOP
             SELECT V_TEST.MC INTO V_MC FROM TEST2 WHERE ID=V_TEST.ID;
             WHILE INSTR(V_MC,',')>0 LOOP
              V_SQL:='INSERT INTO TEST22 VALUES ('''||V_TEST.ID||''','||''||SUBSTR(V_MC,1,INSTR(V_MC,',')-1)||''||')';
              EXECUTE IMMEDIATE V_SQL;
              COMMIT;
              V_MC:=SUBSTR(V_MC,INSTR(V_MC,',')+1);
          END LOOP;
          V_SQL:='INSERT INTO TEST22 VALUES('''||V_TEST.ID||''','||''||V_MC||''||')';
          EXECUTE IMMEDIATE V_SQL;
          COMMIT;
          FETCH TEST2_CUR INTO V_TEST;
     END LOOP;
     CLOSE TEST2_CUR;
END PR_TEST2;