orcale存储过程 处理参数是以逗号组成的数据集

1.create or replace PROCEDURE kqdelete(
    kqid VARCHAR2)
AS
   CURSOR kqbids--游标
  IS
    SELECT *
    FROM THE
      (SELECT CAST(STR2NUMLIST123(kqid) AS VARTABLETYPE) FROM DUAL
      );--将包含逗号的参数集通过STR2NUMLIST123函数进行处理
  v_flowid VARCHAR2(1000);--自由流表id
  v_flowsl VARCHAR2(1000);--自由流表的数据
  --国美 考勤模块 我的考勤 删除按钮
BEGIN
  COMMIT;
   
  FOR kqbid IN kqbids
  LOOP
    DELETE FROM T_FREEFLOW_TEMP WHERE DOCID=kqbid.COLUMN_VALUE;
    DELETE FROM T_FREEFLOW WHERE DOCID=kqbid.COLUMN_VALUE;
    SELECT COUNT(*) INTO v_flowsl FROM T_FREEFLOW_APPROVAL WHERE flowid=v_flowid;
    IF(v_flowsl>0)THEN
      SELECT id INTO v_flowid FROM T_FREEFLOW WHERE DOCID =kqbid.COLUMN_VALUE;
      IF(v_flowid IS NOT NULL) THEN
        DELETE FROM T_FREEFLOW_APPROVAL WHERE flowid=v_flowid;
        DELETE
        FROM T_FREEFLOW_COUNTER
        WHERE USERID IN
          (SELECT USERID FROM T_FREEFLOW_APPROVAL WHERE flowid=v_flowid
          );
        DELETE FROM T_FREEFLOW_LOG WHERE FREEFLOWID=v_flowid;
        DELETE FROM T_FREEFLOW_NODE WHERE flowid=v_flowid;
        DELETE
        FROM T_FREEFLOW_OPINION
        WHERE APPROVALID IN
          (SELECT id FROM T_FREEFLOW_APPROVAL WHERE flowid=v_flowid
          );
        DELETE FROM T_FREEFLOW_SCORE WHERE FREEFLOWID=v_flowid;
      END IF;
    END IF;
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  ROLLBACK;
  RAISE_APPLICATION_ERROR(-20010, 'ERROR：违反唯一索引约束！');

END;

2. 创建切割字符串的函数

create or replace function str2numList123( p_string in varchar2 ) return
          varTableType
           as
           v_str long default p_string || ',';
           v_n number;
           v_data varTableType := varTableType();
           begin
           loop
          v_n := to_number(instr( v_str, ',' ));
          exit when (nvl(v_n,0) = 0);
          v_data.extend;
          v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
          v_str := substr( v_str, v_n+1 );
          end loop;
          return v_data;
          end;

3.创建type类型

create or replace type varTableType as table
   of nvarchar2(40)