oracle中字符串分解为数组并遍历数组插入表中

最新推荐文章于 2023-06-08 11:20:19 发布

didige5994

最新推荐文章于 2023-06-08 11:20:19 发布

阅读量411

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/liuweihua/archive/2012/05/09/2491480.html

版权

oracle中字符串分解为数组并遍历数组插入表中

1、定义一个自定义类型

CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);

2、定义一个函数

CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
      RETURN ty_str_split PIPELINED
IS
      j INT := 0;
    i INT := 1;
    len INT := 0;
    len1 INT := 0;
    str VARCHAR2 (4000);
BEGIN
      len := LENGTH (p_str);
    len1 := LENGTH (p_delimiter);

      WHILE j < len
      LOOP
          j := INSTR (p_str, p_delimiter, i);

          IF j = 0
          THEN
              j := len;
            str := SUBSTR (p_str, i);
            PIPE ROW (str);

              IF i >= len
              THEN
                EXIT;
            END IF;
          ELSE
            str := SUBSTR (p_str, i, j - i);
            i := j + len1;
            PIPE ROW (str);
        END IF;
    END LOOP;

      RETURN;
END fn_split;

3、字符串数组的应用

/*
  description:车机联控下发到责任单位
  */
  PROCEDURE CJLK_UNIT_DOWN(
            P_ERRCD IN OUT NUMBER,
            P_ERRMSG IN OUT VARCHAR2,
            P_CJLK_XH IN VARCHAR2,
            P_JSDWBH IN VARCHAR2,
            P_JSDWMC IN VARCHAR2,
            P_ZT IN VARCHAR DEFAULT 'A'
            )AS
    CURSOR L_JSDWBH IS SELECT * FROM TABLE (CAST (FN_SPLIT(P_JSDWBH,';') AS ty_str_split));
    BEGIN
      P_ERRCD:=0;
      P_ERRMSG:='';
      UPDATE AQGL.CJLK SET JSDWBH=P_JSDWBH,JSDWMC=P_JSDWMC,ZT='B' WHERE XH=P_CJLK_XH;
      FOR I IN L_JSDWBH LOOP
        INSERT INTO AQGL.CJLK_DWFK (XH,PID,JSDWBH,JSDWMC,ZT)
        VALUES (sys_guid(),P_CJLK_XH,I.COLUMN_VALUE,GET_DWMC(i.column_value),P_ZT);
      END LOOP;
      COMMIT;
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
          P_ERRCD:=SQLCODE;
          P_ERRMSG:=SQLERRM;
    END;