PLSQL总结——19.表函数

/*
表函数的用法：将集合转成表
create type employee_obj is object
(
  employee_id   number,
  employee_name varchar2(20)
);--记录集

create type employee_nt is table of employee_obj;--嵌套表
  
注意，要在模式下建立这两个类型，否则会报一下错误
PLS-00642 在SQL语句不允许使用本地收集类型
*/

declare
  employee_t       employee_nt; --一个嵌套表变量
  employee_obj_rec employee_obj := new employee_obj(null, null); --employee_obj记录类型

  cursor c1 is
    select * from table(employee_t); --使用了表函数
begin
  employee_t := new employee_nt(); --初始化
  employee_t.extend(2); --扩展

  employee_obj_rec.employee_id := 1;
  employee_obj_rec.employee_name := 'a';
  employee_t(1) := employee_obj_rec;

  employee_obj_rec.employee_id := 2;
  employee_obj_rec.employee_name := 'b';
  employee_t(2) := employee_obj_rec;

  /*  
    employee_t(1) := employee_l(1, 'a');
    employee_t(2) := employee_l(2, 'b');
  */

  /*循环输出*/
  for rec in c1
  loop
    if c1%rowcount = 1
    then
      dbms_output.put_line('employee_id|employee_name');
    end if;
    dbms_output.put_line(rpad(rec.employee_id, 11, ' ') || '|' ||
                         rpad(rec.employee_name, 14, ' '));
  end loop;

end;