Oracle Procedure & Cursor & Array

块 Block

块的结构

declare
  定义部分(可选)
begin
  可执行部分(必选)
exception
  异常处

块的分类

无名块(也叫匿名块)：动态构造并只能执行一次，常用来测试或执行存储过程。
命名块：加了用<<>>括起了带标号的无名块。
子程序：包括存储过程、函数和包等。这些块一旦被定义便会存储在数据库中，可随时调用。
触发器：根据触发的事件调用。

存储过程 Procedure

创建

create or replace procedure test(var_name_1 in type,var_name_2 out type) as
    --声明变量(变量名 变量类型)
begin
    --存储过程的执行体
end test;

CREATE OR REPLACE PROCEDURE 存储过程名字
(
    参数1 IN NUMBER,
    参数2 IN NUMBER
) AS
    变量1 INTEGER :=0;
    变量2 DATE;
BEGIN
    ...
END 存储过程名字

示例：

CREATE OR REPLACE PROCEDURE proc_test AS
  CURSOR c1 IS SELECT * FROM dat_trade;
BEGIN
  FOR x IN c1 LOOP
    DBMS_OUTPUT.put_line(x.id);
  END LOOP;
END proc_test;

调用

在PL/SQL块内运行存储过程，不需要使用exec，直接 procedure_name[(parameter,…n)]

BEGIN
   proc_helloworld;
END;

外部程序调用：

exec proc_helloworld

事物 Transaction

procedure中既有commit也有rollback commit之前的就提交,commit和rollback之间的就回滚程序出错,commit之前的就已经提交了,commit和出错之间的强制回滚.

嵌套出错: 出错前commit的就提交了,未commit的强制退出程序并回滚把一个procedure中所有的程序和语句看成顺序执行,不管是嵌套多少层,commit的就起效,未commit的,如果出错则从出错的地方强制退出程序,如果不出错,退出session时默认提交.

CREATE OR REPLACE procedure SCOTT.t1
as
begin
  for i in 1..10000 loop
        insert into test1(id,name) values(i,'leng'||i);
        commit;
        if i=20 then
             rollback;
             exit;
        end if;
  end loop;
end;

设置出错就rollback :

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
SET SERVEROUTPUT ON;

DECLARE
    ...
BEGIN
    ...
END;
/
commit;
exit;

异常 Exception

DECLARE   
    v_empno employees.employee_id%TYPE := &empno;   
    v_sal   employees.salary%TYPE;
BEGIN   
    SELECT salary INTO v_sal FROM employees WHERE employee_id = v_empno;   
    IF v_sal<=1500 THEN        
        UPDATE employees SET salary = salary + 100 WHERE employee_id=v_empno;        
        DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已更新!');       
    ELSE        
        DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已经超过规定值!');   
    END IF;
EXCEPTION   
    WHEN NO_DATA_FOUND THEN      
        DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_empno||'的员工');   
    WHEN TOO_MANY_ROWS THEN      
        DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');   
    WHEN OTHERS THEN      
        DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

declare
    v_cnt:=800;
    v_name emp.ename%type;
begin
    begin
        select ename into v_name from emp where sal=v_cnt;
    exception
         when no_data_found then
              v_name:='';
         when too_many_rows then
              v_name:='';
              dbms_output.put_line('存在多个雇员具有该工资');
    end;
    dbms_output.put_line('姓名：' || v_name);
exception
     when others then
          rollback;
          dbms_output.put_line('异常回滚退出');
end;

自定义异常：

SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
SET SERVEROUTPUT ON

Declare
  ex_test Exception;
  PRAGMA EXCEPTION_INIT( ex_test, -20001 );
  l_version number;
Begin
    dbms_output.put_line('Start!');
    update table_a set object_version_number=object_version_number+1 where name = 'test';
    select max(object_version_number) into l_version from table_a where name= 'test';    -- 使用聚合函数，则在没有数据时不会导致NO_DATA_FOUND异常
    if l_version is null then
        dbms_output.put_line('NO data!');
    end if;
    dbms_output.put_line('version:'||l_version);
    raise_application_error( -20001, 'This is a custom error' );        -- 抛出自定义异常
    dbms_output.put_line('Finish!');
Exception        -- 这里定义了Exception捕获，则上面的WHENEVER ERROR ROLLBACK将无法捕获到异常Rollback
    When others then
        dbms_output.put_line('execute fail:'||SQLCODE||'---'||SQLERRM);
End;
/
commit;
Exit;

测试：

没有name='test'的记录的情况下:

 select * from table_A;
 name  object_version_number
 tom        1
 lucy        2
 -----------------------------------------

 PL/SQL procedure successfully completed.
 Start!
 NO data!
 version:
 execute fail:-20001---ORA-20001: This is a custom error

 Commit complete.
 Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

有name='test'的记录的情况下：

 select * from table_A;
 name  object_version_number
 tom        1
 lucy        2
 test        2
 -----------------------------------------

 PL/SQL procedure successfully completed.
 Start!
 version:3
 execute fail:-20001---ORA-20001: This is a custom error

 Commit complete.
 Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 ----------------------------------------
 -- 执行后，name='test'的object_version_number更新了
 select * from table_A;
 name  object_version_number
 tom        1
 lucy        2
 test        3

读取记录到变量

SELECT INTO STATEMENT

将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)

BEGIN
  SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
      xxxx;
END;

游标 Cursor

隐性Cursor

 for 变量名 in （SELECT 语句）loop 
     .....
 END LOOP;

显性Cursor

 -- 无参数的显性游标：
 CURSOR 游标名 IS SELECT 语句;

 -- 带参数的显性游标
 -- parameter 参数形式：
 -- 1.  参数名 数据类型
 -- 2.  参数名 数据类型 DEFAULT 默认值
 -- eg:  Cursor MyCursor(pSal  Number  Default   800)  Select   JOB  From  empa Where  SAL >  pSal ;
 CURSOR 游标名(parameter[,parameter],...) IS SELECT 语句;

Cursor状态属性

 %Found            -- Fetch语句（获取记录）执行情况 True or False。
 %NotFound        -- 最后一条记录是否提取出 True or False。
 %ISOpen            -- 游标是否打开True or False。
 %RowCount        -- 游标当前提取的行数

使用

While 循环

  WHILE 游标名%found LOOP
       ...
  END LOOP;

For 循环: 隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标

  For  变量名  In  游标名  
  Loop
        数据处理语句；
  End Loop；

  -- 示例：
  Declare
       Cursor myCur is select * from emp;
  Begin
       for varA in myCur
       loop
           dbms_output.put_line(myCur%rowCount || '    ' || varA.empno || '    ' || varA.ename || '  ' || varA.sal);
       end loop;
  End;

Loop 循环: 需手动打开，关闭游标

  open 游标名;
  Loop
       Fetch  游标名 InTo  临时记录或属性类型变量；
       Exit  When  游标名%NotFound；
        数据处理语句；
  End   Loop；
  Close 游标名;

  -- 示例：
  Declare
       Cursor myCur is select ename,job,sal,empno from emp;
       varE myCur%rowType;
  Begin

       if myCur%isopen = false then
          open myCur;
          dbms_output.put_line('Opening...');
       end if;

       loop
          fetch myCur into varE;
          exit when myCur%notfound;
          dbms_output.put_line(myCur%rowCount || '    ' || vare.empno || '    ' || vare.ename || '    ' || vare.sal);
       end loop;

       if myCur%isopen then
          Close myCur;
          dbms_output.put_line('Closing...');
       end if;

  End;

嵌套Cursor

 declare  
    v_id char(2);  
    cursor c1 is select id,name from  a;  
    cursor c2 is select * from  b where id0000=v_id;  
 begin  
    for i in c1 loop  
         v_id:=i.id;  
         for j in c2 loop  
              ...  
         end loop;  
    end loop;  
 end;

示例：

  declare
      v_0 number; 
      v_1 number; 
      cursor c1 is select productordernumber from his_productorder@pro_crm where productid in (9000045516); 
      cursor c2 is select cust_order_id from suf_cust_order_q_his where cust_order_num like v_0||'%'; 
      cursor c3 is select * from suf_work_order_q_his where cust_order_id=v_1; 
  begin 
      for i in c1 loop 
          v_0:=i.productordernumber; 
          for j in c2 loop 
              v_1:=j.cust_order_id; 
              for k in c3 loop 
                   dbms_output.put_line(k.work_order_id||' '||k.status); 
              end loop; 
          end loop; 
      end loop;   
  end;

  create or replace procedure proc_nest_cursor
  as
      cursor cur_acctinactprocess is select mediumid,acctnbr,vouchnbr from acctinactprocess;
      v_mediumid ACCTINACTPROCESS.MEDIUMID%type;
      v_acctnbr  ACCTINACTPROCESS.Acctnbr%type;
      v_vouchnbr ACCTINACTPROCESS.Vouchnbr%type;
      v_vouchname vouchinfo.vouchname%type;
      cursor cur_vouchinfo(lvsvouchnbr vouchinfo.vouchnbr%type) is select vouchnbr,vouchname from vouchinfo where vouchnbr=lvsvouchnbr;
  begin
      open cur_acctinactprocess;
      loop
            fetch cur_acctinactprocess into v_mediumid,v_acctnbr,v_vouchnbr;
            exit when cur_acctinactprocess%notfound;
            dbms_output.put_line('外层循环开始: '||'介质号:'||v_mediumid||' 账号:'||v_acctnbr||' 凭证号:'||v_vouchnbr);
            -- 内层游标以外层游标的数据为基础，如果匹配外层游标，就进入内层游标进行处理
            open cur_vouchinfo(v_vouchnbr);
            loop
                fetch cur_vouchinfo into v_vouchnbr,v_vouchname;
                exit when cur_vouchinfo%notfound;
                dbms_output.put_line('内层循环的值: '||' 凭证号:'||v_vouchnbr||' 凭证名称'||v_vouchname);
            end loop;
            close cur_vouchinfo;
        end loop;
        close cur_acctinactprocess;
  end;

Bulk Collect & Forall

BLUK COLLECT

一次取出一个数据集合，比用游标条取数据效率高，尤其是在网络不大好的情况下，但需要大量内存

可将查询结果一次性地加载到collections中，而不是通过cursor一条一条地处理
可以在select into ，fetch into ， returning into语句中使用
使用BULK COLLECT时，所有的INTO变量都必须是collections
可以限制BULK COLLECT 提取的记录数，eg：FETCH cursor BULK COLLECT INTO ...[LIMIT rows];

示例：

可变数组取表（bulk collect into），执行效率高于cursor

 declare
      type type_array is table of t_user%rowtype index by binary_integer;   -- 类似二维数组
      var_array type_array;
 begin
      select * bulk collect into var_array from t_user;
      for i in 1..var_array.count loop
          dbms_output.put_line(var_array(i).user_id);                          -- 二维数组的访问
          dbms_output.put_line(var_array(i).username);
      end loop;
 end;

在returning into中使用bulk collect :（有了returning子句后，我们可以轻松地确定刚刚完成的DML操作的结果，无须再做额外的查询工作）

 CREATE TABLE test_forall2 AS SELECT * FROM test_forall;  
 ----在returning into中使用bulk collect  
 DECLARE  
    TYPE IdList IS TABLE OF test_forall.User_Id%TYPE;  
    enums IdList;  
    TYPE NameList IS TABLE OF test_forall.user_name%TYPE;  
    names NameList;  
 BEGIN  
    DELETE FROM test_forall2 WHERE user_id = 10100  
         RETURNING user_id, user_name BULK COLLECT INTO enums, names;  
    dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');  
    FOR i IN enums.FIRST .. enums.LAST  
    LOOP  
      dbms_output.put_line('User #' || enums(i) || ': ' || names(i));  
    END LOOP;  
    COMMIT;  

 EXCEPTION  
     WHEN OTHERS THEN  
         ROLLBACK;  
 END;

bulk collection limit

 declare
   type v_t_oid is table of ljz_all_objects.object_id%type;
   v_oid_lst      v_t_oid;
   type v_t_cur_oid is ref cursor;
   v_cur_oid      v_t_cur_oid;
   v_cnt          simple_integer := 1000;
 begin
   open v_cur_oid for 'select object_id from ljz_all_objects where object_id>:1'
     using 1;
   fetch v_cur_oid bulk collect into v_oid_lst limit v_cnt;
   while v_oid_lst.count > 0 loop
     for i in 1 .. v_oid_lst.count loop
       --业务处理逻辑
       v_oid_lst(i) := v_oid_lst(i) + 1;
     end loop;
     forall i in v_oid_lst.first .. v_oid_lst.last
       insert into ljz_test (col) values (v_oid_lst(i));
     fetch v_cur_oid bulk collect into v_oid_lst limit 1000;
     commit;
   end loop;
   close v_cur_oid;
 end;

Forall

比FOR效率高，只切换一次上下文 ( FOR 在循环次数一样多个上下文间切换 )

只允许一条 sql 语句
INDICES OF 当绑定数组为稀疏数组或者包含有间隙时，会有用
VALUES OF 只想使用该数组中元素的一个子集时，会有用

语法：

FORALL index_name IN
    { lower_bound .. upper_bound
        | INDICES OF collection_name [ BETWEEN lower_bound AND upper_bound ]
        | VALUES OF index_collection
    }
[ SAVE EXCEPTIONS ] dml_statement;

说明：

index_name : 一个无需声明的标识符，作为集合下标使用
lower_bound .. upper_bound : 数字表达式，来指定一组连续有效的索引数字下限和上限。该表达式只需解析一次
INDICES OF collection_name : 用于指向稀疏数组的实际下标。跳过没有赋值的元素，例如被 DELETE 的元素，NULL 也算值
VALUES OF index_collection : 把该集合中的值当作下标，且该集合值的类型只能是PLS_INTEGER/BINARY_INTEGER
SAVE EXCEPTIONS : 可选关键字，表示即使一些DML语句失败，直到FORALL LOOP执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息
dml_statement : 静态语句，例如：UPDATE或者DELETE；或者动态（EXECUTE IMMEDIATE）DML语句

例如：

FORALL i IN dr_table.first .. dr_table.last 
FORALL i IN INDICES OF  demo_table
FORALL i IN VALUES OF index_poniter

示例:

lower_bound .. upper_bound

 declare  
   type dr_type is table of test1%ROWTYPE index by binary_integer;  
   dr_table dr_type;  
 begin  
   select id, name BULK COLLECT into dr_table from test1;  
   FORALL i IN dr_table.first .. dr_table.last  
     insert into test2 values dr_table (i);  
   --error statement  
   --1.insert into test2 values(dr_table(i));报没有足够的值错误，此处外面不可以加括号，当有多个字段的时候，单个字段可以加括号  
   --2.insert into test2 values(dr_table(i).id,dr_table(i).name);集合的field不可以在forall中使用，必须是整体使用  
   --3.insert into test2 values dr_table(i+1);错误，不可以对索引变量进行运算  
   --4.insert into test2 values dr_table(i);dbms_output.put_line(i);不正确，找不到i，因为forall中只能使用单条语句可以引用索引变量  
 end;

indices of

 DECLARE
   TYPE demo_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;
   demo_table demo_table_type;
 BEGIN
   FOR i IN 1..10 LOOP
     demo_table(i).id:=i;
     demo_table(i).name:='NAME'||i;
   END LOOP;
   -- 使用集合的delete方法移除第3、6、9三个成员
    demo_table.delete(3);
    demo_table.delete(6);
    demo_table.delete(9);
    FORALL i IN INDICES OF  demo_table
      INSERT INTO tmp_tab VALUES demo_table(i);
  END ;

values of

 DECLARE
   TYPE index_poniter_type IS TABLE OF pls_integer;
   index_poniter index_poniter_type;
   TYPE demo_table_type IS TABLE OF tmp_tab%rowtype INDEX BY BINARY_INTEGER;
   demo_table demo_table_type;
 BEGIN 
   index_poniter := index_poniter_type(1,3,5,7);
   FOR i IN 1..10 LOOP 
     demo_table(i).id:=i;
      demo_table(i).name:='NAME'||i;
    END LOOP;
    FORALL i IN VALUES OF index_poniter
      INSERT INTO  tmp_tab VALUES demo_table(i);
  END;

动态SQL

declare
  compDetail_cur number;
  vQuery varchar2(500);
  vStat NUMBER;
  vId Number;
  vColDesc dbms_sql.desc_tab;
  vColCnt Number;
begin
  vQuery := 'select * from pay_dir_comp_details_f where dir_card_comp_id=:pCompId';
  compDetail_cur := dbms_sql.open_cursor;
  dbms_sql.parse(compDetail_cur,vQuery,dbms_sql.native);
  dbms_sql.bind_variable(compDetail_cur,':pCompId','300100090712274');
  dbms_sql.define_column(compDetail_cur,1,vId);

  dbms_sql.describe_columns(compDetail_cur, vColCnt, vColDesc);
  for i in 1..vColCnt
  loop
    dbms_output.put_line(vColDesc(i).col_type || ' ' || vColDesc(i).col_name || ' ' || vColDesc(i).col_max_len);
  end loop;

  vStat := dbms_sql.execute(compDetail_cur);
  loop
      exit when dbms_sql.fetch_rows(compDetail_cur)<=0;
      dbms_sql.column_value(compDetail_cur,1,vId);
      dbms_output.put_line(vId);
  end loop;
  dbms_sql.close_cursor(compDetail_cur);
end;

declare 
    v_sql varchar2(2000);
begin
  v_sql:='insert into test values (sysdate)';        --给sql赋值
  dbms_output.put_line(v_sql);                        --打印
  execute immediate v_sql;                            --执行sql
  commit;
end;

EXECUTE IMMEDIATE

不会提交一个DML(CRUD)事务执行，应该显式提交
处理DDL命令(Create/Drop/Alter),它提交所有以前改变的数据

EXECUTE IMMEDIATE
    'CREATE TABLE '|| l_ctx_b_backup ||' AS 
    ( SELECT a.* , ''Y'' AS process_flag, systimestamp as run_date from tableA a where a.protected_flag !=''Y'' )';

EXECUTE IMMEDIATE 'UPDATE '|| l_ctx_b_backup || ' SET process_flag = ''N'' WHERE process_flag = ''Y''';

数组 Array

oracle 数组类型，没有现成的类型，但是可以自己随意定义

定长数组

DECLARE
    -- 定长字符数组，数组大小为3
    type v_arr is varray(3) of NUMBER(18);
    id_arr v_arr:=v_arr(
      300100028612143
      ,300100028612149
      ,300100028612155
     );  
BEGIN
    FOR i in 1 ..id_arr.count LOOP
        DBMS_OUTPUT.PUT_LINE('id:'||id_arr(i));
    END LOOP;
END

可变长数组

declare    
    -- 可变长字符数组，元素大小30，索引标号integer类型自增长
    -- index by binary_integer子句代表以符号整数为索引，访问表类型变量中的数据: “变量名(索引号)”
    type v_table is table of varchar2(30) index by binary_integer;     
    my_table v_table;     
begin    
     for i in 1..20     
     loop     
          my_table(i):=i;     
          dbms_output.put_line(my_table(i));     
      end loop;     
end;

复杂类型(似二维数组)

自定义

 DECLARE
   TYPE user_info_type IS RECORD(
     user_name VARCHAR2(20),
     user_age  NUMBER(3),
     user_sex  VARCHAR2(5)
   );     -- 自定义属性    
   -- 可变长数组，item的属性为定义的RECORD user_info_type
   TYPE user_info_arr_type IS TABLE OF user_info_type INDEX BY BINARY_INTEGER;
   user_info_arr user_info_arr_type;

 BEGIN
   user_info_arr(0).user_name := '张三';
   user_info_arr(0).user_age := 19;
   user_info_arr(0).user_sex := '男';
   user_info_arr(1).user_name := '李菁菁';
   user_info_arr(1).user_age := 23;
   user_info_arr(1).user_sex := '女';

   FOR i IN 0 .. 1
   LOOP
     dbms_output.put_line('User Name:' || user_info_arr(i).user_name);
     dbms_output.put_line('User Age:' || user_info_arr(i).user_age);
     dbms_output.put_line('User Sex:' || user_info_arr(i).user_sex);
   END LOOP;
 END;

输出结果：

  User Name:张三 
  User Age:19 
  User Sex:男 
  User Name:李菁菁 
  User Age:23 
  User Sex:女

取自表

 declare
      -- 可变长数组，属性来自表t_user，类似二维数组
      type type_array is table of t_user%rowtype index by binary_integer;   
      var_array type_array;
 begin
      select * bulk collect into var_array from t_user;    -- `bulk collect into`执行效率高于`cursor
      for i in 1..var_array.count loop
          dbms_output.put_line(var_array(i).user_id);          -- 二维数组的访问
          dbms_output.put_line(var_array(i).username);
      end loop;
 end;

应用: array/collection in where cause

方案一：数组type定义在代码块外面，代码块内declare数组且使用时转成table：select * from table(array_name)

create type v_arr is table of NUMBER(18);

select * from user_types where upper(type_name)='V_ARR';
select * from all_objects where object_name = 'V_ARR';
select * from user_source where name = 'V_ARR' order by line;

DECLARE
    -- type v_arr is varray(12) of NUMBER(18);  -- 注意：can't use a locally declared collection in an SQL clause ,need create outside,then could use `in (select * from table(id_arr))`
    id_arr v_arr:=v_arr(
      300100028612143
      ,300100028612149
      ,300100028612155
     );  
BEGIN
    FOR i in 1 ..id_arr.count LOOP
        DBMS_OUTPUT.PUT_LINE('id:'||id_arr(i));
    END LOOP;

    FOR i in (select * from pe_user where id in (select * from table(id_arr)) ) LOOP
        DBMS_OUTPUT.PUT_LINE('select id:'||i.id);
    END LOOP;

    FOR i in (select * from pe_user where id member of id_arr ) LOOP
         DBMS_OUTPUT.PUT_LINE('select id:'||i.id);    
    END LOOP;

    EXECUTE IMMEDIATE 'INSERT INTO ' || l_ctx_b_backup || 
    ' SELECT a.* , ''Y'' AS process_flag, systimestamp as run_date from pe_user a where a.id in (select * from table(:id_arr)) ' using id_arr;
END

方案二：直接字符串拼接出完整SQL后使用EXECUTE IMMEDIATE执行

DECLEAR
     id_strs varchar(500) := '(300100028612143,300100028612149,300100028612155)';
BEGIN
     EXECUTE IMMEDIATE 'INSERT INTO ' || l_ctx_b_backup ||
    ' SELECT a.* , ''Y'' AS process_flag, systimestamp as run_date from pe_user a where a.id in ' || id_strs ;

    EXECUTE IMMEDIATE 'select count(1) from pe_user where sguid is null and id in ' || id_strs into l_impacted_rows_cnt;
END

String

Split

splitting as columns

 -- splitting as columns
 select regexp_substr('a-b-c', '[^-]+', 1, 1) as grupo_1
 ,regexp_substr('a-b-c', '[^-]+', 1, 2) as grupo_2
 ,regexp_substr('a-b-c', '[^-]+', 1, 3) as grupo_3
 from dual;

 -- result:
 -- a b c

splitting as rows

 -- splitting as rows
 select regexp_substr('a-b-c', '[^-]+', 1, level) as one_element
 from dual
 connect by regexp_substr('a-b-c', '[^-]+', 1, level) is not null;

 -- result:
 -- a
 -- b
 -- c

应用示例：

declare
  type seed_pdb_array is varray(12) of varchar(80);
  seed_pdb seed_pdb_array:=seed_pdb_array(
   '300100028612143,40132E591C244CB6E05355B0F20A4350'
   ,'300100028612149,40132E591C284CB6E05355B0F20A4350'
   ,'300100028612155,40132E591C2C4CB6E05355B0F20A4350'
  );
  v_id NUMBER(18);
  v_sguid VARCHAR(32);
begin
  FOR i in 1..seed_pdb.count LOOP
    -- DBMS_OUTPUT.PUT_LINE(seed_pdb(i));
    v_id:=to_number(regexp_substr(seed_pdb(i),'[^,]+',1,1));
    v_sguid:=regexp_substr(seed_pdb(i),'[^,]+',1,2);
    DBMS_OUTPUT.PUT_LINE(v_id|| ':' || v_sguid);
  END LOOP;
end;

Reference

Array/Collection
Array/Collection in where clause
String
- Split single comma delimited string into rows in Oracle