oracle分隔字符串，循环添加数据

聂柔洁

CREATE OR REPLACE PROCEDURE SP_INS_ROLE(
                                        --添加角色
                                        V_ROLEID     TB_ROLE.ROLEID%TYPE,
                                        V_ROLENAME   TB_ROLE.ROLENAME%TYPE, --名称
                                        V_PARENTID   TB_ROLE.PARENTID%TYPE, --上级编号
                                        V_LEVELNUM   TB_ROLE.LEVELNUM%TYPE, --级别
                                        V_CREATEUSER TB_ROLE.CREATEUSER%TYPE, --创建人
                                        V_NOTE       TB_ROLE.NOTE%TYPE, --备注
                                        V_PERMISSION IN VARCHAR2, --菜单信息
                                        V_RESULT     OUT NUMBER) AS
  CURSOR V_PERMISSION_C IS
    SELECT * FROM TABLE(CAST(FN_SPLIT(V_PERMISSION, ',') AS TY_STR_SPLIT));
  V_CREATETIME VARCHAR2(20);
  V_COUNT      INT;
  V_ORDERNUM   INT;

BEGIN
  --获取时间
  V_CREATETIME := TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS');

  --校验名称是否重复
  SELECT COUNT(ROLEID)
    INTO V_COUNT
    FROM TB_ROLE
   WHERE ROLENAME = V_ROLENAME
     AND PARENTID = V_PARENTID;
  IF V_COUNT > 0 THEN
    V_RESULT := 1; --名称重复
    RETURN;
  END IF;
  --获取当前位置
  SELECT COUNT(ROLEID)
    INTO V_ORDERNUM
    FROM TB_ROLE
   WHERE PARENTID = V_PARENTID;
  --添加菜单信息
  INSERT INTO TB_ROLE
    (ROLEID,
     ROLENAME,
     STATUS,
     PARENTID,
     LEVELNUM,
     CREATETIME,
     CREATEUSER,
     NOTE,
     ORDERNUM)
  VALUES
    (V_ROLEID,
     V_ROLENAME,
     0,
     V_PARENTID,
     V_LEVELNUM,
     V_CREATETIME,
     V_CREATEUSER,
     V_NOTE,
     NVL(V_ORDERNUM, 0) + 1);

  --循环添加角色与权限关联
  FOR I IN V_PERMISSION_C LOOP
    --添加关联数据
    INSERT INTO TB_ROLE_PERMISSION
      (ID, PERID, ROLEID)
    VALUES
      (SYS_GUID(), I.COLUMN_VALUE, V_ROLEID);
  END LOOP;

  COMMIT;
  --添加成功
  V_RESULT := 0;
EXCEPTION
  WHEN OTHERS THEN
    --添加失败
    V_RESULT := -1;
    ROLLBACK;
END;





--分隔字符串的方法
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;




--创建一个table存储临时数据
CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000)