文章详情页_Oracle 对象类型与管道化表函数

对象类型

在 PL/SQL 中，面向对象的程序设计是基于对象类型来完成的，对象类型是用户自定义的一种复合数据类型，它封装了数据结构和用于操纵这些数据结构的过程和函数。

对象类型的组成

对象类型由两部分组成：对象类型规范和对象类型体

对象类型规范用于定义对象类型的公有属性和方法；
对象类型体用于实现对象类型规范所定义的公有方法。

对象类型属性

定义对象类型最少要包含一个属性，最多可包含 1000 个属性，定义时必须提供属性名和数据类型，但不能指定默认值和 NOT NULL，数据类型不能包括 LONG、LONG RAW、ROWID、UROWID 和 PL/SQL 特有类型，如：BOOLEAN、%TYPPE、%ROWTYPE、REF CURSOR 等。

对象类型的方法

定义对象类型可以包含也可以不包含方法，可以定义构造方法、member 方法、static 方法、map 方法和 order 方法。

构造方法

当定义了一个对象类型后，系统会提供一个接收与每个属性相对应的参数的构造函数，因此大多数情况下不需要自己再编写构造函数，不过我们也可以出于如下的目的来自定义构造函数：为对象提供初始化功能，以避免许多具有特别用途的过程只初始化对象的不同部分，可以通过构造函数进行统一初始化；
可以在构造函数中为某些属性提供默认值；
出于维护性的考虑，在新的属性添加到对象中时，避免要更改调用构造函数的应用程序中的代码，这样可以使已经存在的构造函数调用继续工作。
自定义构造函数使用 CONSTRUCTOR 关键字进行声明：

--定义对象类型规范
SQL> create or replace type salary_obj as object (
  --定义对象属性
  percent      number(10,4),       
  sal          number(10,2),
  --自定义构造函数
  constructor function salary_obj(p_sal number) return self as result
)
instantiable         --可实例化对象
final;               --不可以继承


--定义对象类型体
SQL> create or replace type body salary_obj
as
  --实现重载的构造函数
  constructor function salary_obj (p_sal number)
    return self as result
  as
  begin
    self.sal := p_sal;          --设置属性值
    self.percent := 1.12;       --为属性指定初值
    return;
  end;
end;

member 方法

用于访问对象实例的数据。当使用 member 方法时，可以使用内置参数 self 访问当前对象实例。
当定义 member 方法时，无论是否定义 self 参数，它都会被作为第一个参数传递给 member 方法，但如果要定义参数 self，那么其类型必须要使用当前对象类型，member 方法只能由对象实例调用，而不能由对象类型调用。

static 方法

用于访问对象类型，可以在对象类型上执行全局操作，而不需要访问特定对象实例的数据，因此 static 方法引用 self 参数。
static 方法只能由对象类型调用，不能由对象实例调用。

map 方法

map 和 order 方法不能同时定义，使用原则是可不用则均不用；
map 函数会将对象实例根据一定的调用规则返回 DATE、NUMBER、VARCHAR2 类型的标量类型，在映射对象类型为标量类型后，就可以通过对标量函数的比较来得到结果了。

--定义一个对象类型规范，该规范中包含map方法
SQL> create or replace type emp_map as object (
  --定义对象类型属性
  empno    number (4),
  sal      number (10, 2),
  comm     number (10, 2),
  deptno   number (4),
  map member function convert return real   --定义一个map方法
)
not final;  


--定义一个对象类型体，实现map函数
SQL> create or replace type body emp_map as
  map member function convert return real  is   --定义一个map方法
  begin
     return sal + comm;                         --返回标量类型的值
  end;
end;

在定义了 map 函数后，PL/SQL 会隐式地通过调用 map 函数在多个对象间进行排序或比较。
例如下面创建了一个 emp_map_tab 的对象表，向这个对象表插入多个对象，然后就可以对这个对象表进行对象的排序：

--创建emp_map类型的对象表
SQL> create table emp_map_tab of emp_map;


--向对象表中插入员工薪资信息对象。
SQL> insert into emp_map_tab(empno, sal, comm, deptno) values(7123, 3000, 200, 20);
SQL> insert into emp_map_tab(empno, sal, comm, deptno) values(7124, 2000, 800, 20);
SQL> insert into emp_map_tab(empno, sal, comm, deptno) values(7125, 5000, 800, 20);
SQL> insert into emp_map_tab(empno, sal, comm, deptno) values(7129, 3000, 400, 20);

SQL> commit;


SQL> select value(e) val, e.sal + e.comm from emp_map_tab e order by 1;

VAL(EMPNO, SAL, COMM, DEPTNO)            E.SAL+E.COMM
---------------------------------------- ------------
EMP_MAP(7124, 2000, 800, 20)                     2800
EMP_MAP(7123, 3000, 200, 20)                     3200
EMP_MAP(7129, 3000, 400, 20)                     3400
EMP_MAP(7125, 5000, 800, 20)                     5800

order 方法

order 方法只能对两个对象进行比较，且必须是返回数值型结果的函数，根据结果返回正数、负数或0。该方法只有两个参数，SELF 和另一个要比较的对象类型，如果传递该参数为 NULL，则返回 NULL。

--定义一个对象规范，该规范中包含order方法
SQL> create or replace type emp_order as object (
  --定义对象类型属性
  empno    number (4),
  sal      number (10, 2),
  comm     number (10, 2),
  deptno   number (4),
  order member function match(r emp_order) return integer   --定义一个order方法
)
not final; 


--定义一个对象类型体，实现order函数
SQL> create or replace type body emp_order as
  order member function match(r emp_order) return integer is   
  begin
     if ((self.sal + self.comm) < (r.sal + r.comm)) then
        return -1;      --可为任何负数
     elsif ((self.sal + self.comm) > (r.sal + r.comm)) then
        return 1;       --可为任何正数
     else 
        return 0;       --如果相等则为0
     end if;
  end match;
end;

定义了ORDER函数后，就可以对两个对象进行比较：

declare
   emp1 emp_order := emp_order(7112, 3000, 200, 20);    --定义员工1
   emp2 emp_order := emp_order(7113, 3800, 100, 20);    --定义员工2
begin
   --对员工1和2进行比较，获取返回结果
   if emp1 > emp2 then
      dbms_output.put_line('员工1的薪资加提成比员工2大！');
   elsif emp1 < emp2 then
      dbms_output.put_line('员工1的薪资加提成比员工2小！');
   else
      dbms_output.put_line('员工1的薪资加提成与员工2相等！');
   end if;
end;
/
员工1的薪资加提成比员工2小！

PL/SQL 过程已成功完成。

同样，使用 order 成员方法的对象也可以插入到对象表中，使用对象表的排序功能利用 order 成员方法进行排序：

--创建emp_order类型的对象表
SQL> create table emp_order_tab of emp_order;


--向对象表中插入员工薪资信息对象。
SQL> insert into emp_order_tab(empno, sal, comm, deptno) values(7123, 3000, 200, 20);
SQL> insert into emp_order_tab(empno, sal, comm, deptno) values(7124, 2000, 800, 20);
SQL> insert into emp_order_tab(empno, sal, comm, deptno) values(7125, 5000, 800, 20);
SQL> insert into emp_order_tab(empno, sal, comm, deptno) values(7129, 3000, 400, 20);

SQL> commit;


SQL> select value(e) val, e.sal + e.comm from emp_order_tab e order by 1;

VAL(EMPNO, SAL, COMM, DEPTNO)            E.SAL+E.COMM
---------------------------------------- ------------
EMP_ORDER(7124, 2000, 800, 20)                   2800
EMP_ORDER(7123, 3000, 200, 20)                   3200
EMP_ORDER(7129, 3000, 400, 20)                   3400
EMP_ORDER(7125, 5000, 800, 20)                   5800

对象类型的基本应用

基本应用也属于最常规、最简单的应用，讲述如何建立和使用独立的并且与其他对象类型无关的对象类型。包括语法、建立带方法和不带方法的对象类型。

建立和使用不包含任何方法的对象类型

定义对象类型规范 DEPT_OBJ：

SQL> create or replace type dept_obj as object(deptno number, dname varchar2(10));

类型已创建。

创建列对象表：

SQL> create table dept_obj_tab(dept dept_obj, loc varchar2(10));

表已创建。

写入数据：

SQL> insert into dept_obj_tab(dept, loc) values(dept_obj(10, 'Sales'), 'CHINA');

已创建 1 行。

SQL> commit;

建立和使用包含方法的对象类型

定义对象类型规范 EMP_OBJ：

SQL> create or replace type emp_obj as object (
  --声明对象类型属性
  empno   number(4),
  ename   varchar2(20),
  sal     number(10,2),
  deptno  number(4),
  --声明对象类型实例方法
  member procedure change_sal(p_empno number, p_sal number),
  member procedure change_deptno(p_empno number, p_deptno number),
  member function get_sal(p_empno number) return number,
  member function get_deptno(p_empno number) return integer,
  --声明对象类型静态方法
  static function get_ename (p_empno number) return varchar2
);

定义对象类型体：

SQL> create or replace type body emp_obj
as
  --定义对象成员方法，更改员工薪资
  member procedure change_sal (p_empno number, p_sal number)
  is
  begin
    update emp
      set sal = p_sal
    where empno = p_empno;
  end;
  --定义对象成员方法，更改员工部门
  member procedure change_deptno (p_empno number, p_deptno number)
  is
  begin
    update emp
      set deptno = p_deptno
    where empno = p_empno;
  end;
  --定义对象成员方法，获取员工薪资
  member function get_sal (p_empno number)
    return number
  is
    v_sal   number (10, 2);
  begin
    select sal
      into v_sal
    from emp where empno = p_empno;
    return v_sal;
  end;
  --定义对象成员方法，获取员工部门
  member function get_deptno (p_empno number)
    return integer
  is
    v_deptno   int;
  begin
    select deptno
      into v_deptno
    from emp where empno = p_empno;
    return v_deptno;
  end;
  --声明对象类型静态方法
  static function get_ename (p_empno number)
  return varchar2
  is
    v_ename   varchar2(50);
  begin
    select ename
      into v_ename
    from emp where empno = p_empno;
    return v_ename;
  end;
end;

建立行对象表：

SQL> create table emp_obj_tab of emp_obj;

写入数据：

SQL> insert into emp_obj_tab(empno, ename, sal, deptno) values(7123, 'King', 200, 10);
SQL> insert into emp_obj_tab(empno, ename, sal, deptno) values(7124, 'Scott', 800, 20);
SQL> insert into emp_obj_tab(empno, ename, sal, deptno) values(7125, 'John', 800, 20);
SQL> insert into emp_obj_tab(empno, ename, sal, deptno) values(7129, 'Smith', 1000, 30);

SQL> commit;

对象类型的高级应用

嵌套对象类型

嵌套对象类型是指在一个对象中嵌入另一个对象类型。

--定义地址对象类型规范
SQL> create or replace type address_type as object (
  street_addr1  varchar2(25),   --街道地址1
  street_addr2  varchar2(25),   --街道地址2
  city          varchar2(30),   --城市
  state         varchar2(20),    --省份
  zip_code      number,         --邮政编码
  --实例方法，返回地址字符串
  member function tostring return varchar2,
  --map方法提供地址比较函数
  map member function mapping_function return varchar2
);


--定义地址对象类型体，实现实例方法与map函数
SQL> create or replace type body address_type
as
  --将地址属性转换为字符形式显示
  member function tostring
    return varchar2                    
  is
  begin
    if (street_addr2 is not null) then
      return street_addr1 || chr(10) || 
             street_addr2 || chr(10) || 
             city || ',' || state || ' ' || zip_code;
    else
      return street_addr1 || chr (10) || 
             city || ',' || state || ' ' || zip_code;
    end if;
  end;
  --定义地址对象map函数的实现，返回varchar2类型
  map member function mapping_function    
    return varchar2
  is
  begin
    return to_char(nvl(zip_code, 0), 'fm00000') || 
           lpad(nvl(city, ''), 30) || 
           lpad(nvl(street_addr1, ''), 25) || 
           lpad(nvl(street_addr2, ''), 25);
  end;
end;

--定义一个对象类型规范，该规范中包含地址对象类型的属性
SQL> create or replace type employee_addr as object (
  empno    number (4),
  sal      number (10, 2),
  comm     number (10, 2),
  deptno   number (4),
  addr     address_type,
  member function get_emp_info return varchar2   
)
not final; 


--定义对象类型体，实现get_emp_info方法
SQL> create or replace type body employee_addr
as
  member function get_emp_info
    return varchar2                    --返回员工的详细信息
  is
  begin
    return '员工' || self.empno || '的地址为：' || self.addr.tostring;
  end;
end;

--调用
declare
  o_address address_type;
  o_emp employee_addr; 
begin
  o_address := address_type('玉兰一街', '二巷', '深圳', 'DG', 523343);
  o_emp := employee_addr(7369, 5000, 800, 20, o_address); 
  dbms_output.put_line('员工信息为' || o_emp.get_emp_info);
end;
/
员工信息为员工7369的地址为：玉兰一街
二巷
深圳,DG 523343

PL/SQL 过程已成功完成。

对象继承

为了从一个父对象中继承，在子对象中使用 UNDER 关键字，指定一个父对象名称，该父对象必须是使用 NOT FINAL 关键字定义的对象。

--创建父对象
SQL> create or replace type person_obj as object (
  person_name varchar (20),   
  gender      varchar2 (10),          
  birthdate   date,                 
  address     varchar2 (50),         
  member function get_info return varchar2                 
)
not final;                            

SQL> create or replace type body person_obj   
as
  member function get_info
    return varchar2
  is
  begin
    return '姓名：' || person_name || ', 家庭住址：' || address;
  end;
end;

--对象类型使用under语句从person_obj中继承
SQL> create or replace type under_person_obj under person_obj (
  empno   number (6),
  sal     number (10, 2),
  job     varchar2 (10),
  member function get_emp_info return varchar2
);


SQL> create or replace type body under_person_obj as
  member function get_emp_info  return varchar2 is
  begin
    --在对象类型体中可以直接访问在父对象中定义的属性
    return '员工编号：' || self.empno ||' 员工名称：' ||
           self.person_name || ' 职位：' || self.job;
  end;
end;

--调用
declare
   o_emp under_person_obj;         --定义员工对象类型的变量
begin
   --使用构造函数实例化员工对象
   o_emp := under_person_obj('张小五', 'F', to_date('1983-01-01','YYYY-MM-DD'),
                             '中信', 7981, 5000, 'Programmer');
   dbms_output.put_line(o_emp.get_info);          --输出父对象的人员信息
   dbms_output.put_line(o_emp.get_emp_info);      --输出员工对象中的员工信息
end;
/
姓名：张小五, 家庭住址：中信
员工编号：7981 员工名称：张小五 职位：Programmer

PL/SQL 过程已成功完成。

可以看到，由于 under_person_obj 合并了 person_obj 对象，因此在构造函数中初始化对象时，必须先对父对象中的属性进行初始化，然后初始化子对象类型中的属性。

方法重载

所谓的重载就是定义多个具有同名的函数或过程，但是参数类型或个数不同，由编译器根据调用参数确定执行哪一个子程序。这种重载方式也称为静态多态。在使用对象继承时，也可以使用方法重载，这种重载使用了动态方法调用的能力，也称为动态多态或运行时多态。
对象方法的重载使用 OVERRIDING 关键字，不是根据参数的个数或类型来决定调用哪一个方法，而是根据优先级进行调用，也就是总是先调用子类的方法。

--对象类型使用under语句从person_obj中继承
SQL> create or replace type overriding_person_obj under person_obj (
  empno  number(6),
  sal    number(10, 2),
  job    varchar2(10),
  member function get_emp_info return varchar2,
  --定义重载方法
  overriding member function get_info return varchar2
)


SQL> create or replace type body overriding_person_obj as
  member function get_emp_info  return varchar2 is
  begin
    --在对象类型体中可以直接访问在父对象中定义的属性
    return '员工编号：' || self.empno || ' 员工名称：' ||
           self.person_name || ' 职位：' || self.job;
  end;
  --实现重载方法
  overriding member function get_info return varchar2 as
  begin
    return '员工编号：' || self.empno || ' 员工名称：' ||
           self.person_name || ' 职位：' || self.job; 
  end;          
end;

对象类型的维护

显示对象类型信息

SQL> select type_name, attributes, final from user_types;

增删对象类型的属性

SQL> alter type under_person_obj add attribute email varchar2(50) cascade;

SQL> alter type under_person_obj drop attribute email cascade;

cascade 关键字级联更新依赖对象类型的对象类型和对象表。

管道化表函数

表函数是从 Oracle 9i 开始提供的函数，所谓表函数就是能够产生一组作为输出行集合的函数，其核心思想是：将一个运算的结果直接输送到下一运算中，不需要创建临时关系表来保存中间结果。表函数可以直接用 SQL 语句进行查询，如： SELECT * FROM table(func()); 等，使用起来就好像是一个真正的数据库表，但是由于在内存中，速度比表要快很多。
管道函数其实就是表函数的一种形式，它必须返回一个集合，在函数中，PIPE ROW 语句被用来返回该集合的单个元素，该函数必须以一个空的 RETURN 语句结束，以表明它已经完成，一旦创建了上述函数，就可以使用 TABLE 操作符从 SQL 查询中调用它。

定义对象类型

SQL> create or replace type employee_obj as object(
  f_id number,
  f_empno number,
  vc_ename varchar2(50),
  vc_job varchar2(50),
  d_hiredate date,
  f_sal number, 
  f_deptno number
);

类型已创建。

创建TABLE类型

SQL> create or replace type employee_obj_tab as table of employee_obj;

类型已创建。

创建管道化函数

create or replace function f_emp_pipe(i_deptno in number)
return employee_obj_tab   --返回table类型
pipelined as
  v_deptno            number := i_deptno;
  v_employee_obj      employee_obj;  --记录类型变量
begin
  for x in (
    select
      rownum f_id,
      empno f_empno,
      ename vc_ename,
      job vc_job,
      hiredate d_hiredate,
      sal f_sal,
      deptno f_deptno
    from emp
    where deptno = v_deptno
    order by empno
  ) loop

    v_employee_obj := employee_obj(
      x.f_id,
      x.f_empno,
      x.vc_ename,
      x.vc_job,
      x.d_hiredate,
      x.f_sal,
      x.f_deptno
    );

    pipe row(v_employee_obj);

  end loop;

  return;

exception
  when no_data_needed then
    dbms_output.put_line('运行结束，未输出管道所有记录。');
    return;
end;

调用函数

SQL> select * from table(f_emp_pipe(20));

      F_ID    F_EMPNO VC_ENAME   VC_JOB     D_HIREDATE      F_SAL   F_DEPTNO
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1       7369 Python     CLERK      1980-12-17        800         20
         2       7566 JONES      MANAGER    1981-04-02       2975         20
         3       7788 SCOTT      ANALYST    1987-04-19       3000         20
         4       7876 ADAMS      CLERK      1987-05-23       1100         20
         5       7902 FORD       ANALYST    1981-12-03       3000         20


SQL> select * from table(f_emp_pipe(100));

未选定行

参考资料

https://blog.csdn.net/bbliutao/article/details/9765469
https://blog.csdn.net/indexman/article/details/27580517
https://blog.csdn.net/lianjiww/article/details/81592293

原创文章，转载请注明出处：http://www.opcoder.cn/article/20/