oracle集合&对象

一，集合

集合，遵循面向对象风格的现代编程，包含三种类型：

1. 变长数组（varry），类似于java，c++中的数组，可以用于存储有序的元素集合，每个元素 一个索引，记录元素在数组的位置；需要注意的是，变长数组修改只能整体修改，有最大大小，初始化设置，但是可以修改
   
2. 嵌套表 嵌套在另外一个表中的表，可以插入，更新，删除桥套标中的单个元素，比变长数组灵活，并且没有最大大小
3. 关联数组 类似于java的哈西表，是一个键值对集合。不过只能用在PLSQL中，不能存储在数据库

1, 变长数组

SQL> set serveroutput on
SQL> edit
已写入 file afiedt.buf

  1  DECLARE
  2    -- 定义varray 定义size ，of varchar2(10) 定义类型
  3    TYPE arry_var IS VARRAY(3) OF VARCHAR2(10);
  4    arry_name arry_var;
  5  BEGIN
  6    -- init array
  7    arry_name := arry_var('china', 'japan','usa');
  8    dbms_output.put_line(arry_name(1));
  9* END;
 10
 11  /
china

PL/SQL 过程已成功完成。

修改长度

SQL> create  TYPE arry_var IS VARRAY(3) OF VARCHAR2(10);
  2  /

类型已创建。

SQL> alter type arry_var modify limit 10 cascade;

类型已变更。

SQL> create table table_array1(id int,name arry_var);

表已创建。

最后使用变长数组类型定义表列

2, 嵌套表

SQL> edit
已写入 file afiedt.buf

  1  create type t_obj1 as object(
  2* id int,name varchar2(10));
SQL> l
  1  create type t_obj1 as object(
  2* id int,name varchar2(10));
SQL> /

类型已创建。

SQL> create type t_nested_table1 as table of t_obj1;
  2  /

类型已创建。

SQL> edit
已写入 file afiedt.buf

  1  create table t_nested_table (id int,desc1 t_nested_table1)
  2  nested table
  3  desc1
  4* store as desc2
SQL> /

表已创建。

这里创建了嵌套表类型t_nested_table1,他使用了 t_obj1的对象类型，

最后使用嵌套表类型定义表列，表后面用nested table字句标识了嵌套表列名称，store as指定实际嵌套表的名称

其他例子跟record区别的：

--例子1：
drop table emp;
create table emp(empno int,ename varchar2(20),hiredate date,deptno int);

DECLARE  
   TYPE emp_rec_type IS RECORD  
   (  
      empno      emp.empno%TYPE  
     ,ename      emp.ename%TYPE  
     ,hiredate   emp.hiredate%TYPE  
   );  
   TYPE nested_emp_type IS TABLE OF emp_rec_type;  
   emp_tab   nested_emp_type;  
BEGIN  
   DELETE FROM emp  
   WHERE  deptno = 20  
   RETURNING empno, ename, hiredate     -->使用returning 返回这几个列   
   BULK   COLLECT INTO emp_tab;         -->将前面返回的列的数据批量插入到集合变量     
end;


--对比1：
create or replace type emp_rec_type IS object
   (
      empno      NUMBER(4)
     ,ename      VARCHAR2(10)
     ,hiredate   DATE
   );

DECLARE  
   TYPE nested_emp_type IS TABLE OF emp_rec_type;  
   emp_tab   nested_emp_type;  
BEGIN  
   DELETE FROM emp  
   WHERE  deptno = 20  
   RETURNING emp_rec_type(empno, ename, hiredate)     -->使用returning 返回这几个列   
   BULK   COLLECT INTO emp_tab;         -->将前面返回的列的数据批量插入到集合变量     
end;

-- 例子2
create table t1 (id int,name varchar2(100));
insert into t1 values(1,'1234');
insert into t1 values(2,'1234');

declare
cnt int;
type type1 is record(id int,name varchar2(10)); -- 改为object错误
type v_loop_tab1 is table of type1;
v1_lt v_loop_tab1;
begin
cnt :=1000;
while(cnt>0) loop
select * bulk collect into v1_lt from t1;
cnt:=cnt-1;
end loop;
end;

3,关联数组

关联数组其实是在oracle10g中加入的，是一个键值对集合，可以使用一个键值或者整数访问数组中的值

使用1

set serveroutput on
declare
  type nest_table1  is table of varchar2(15) index by binary_integer;
  -- 指定是一个集合的表的数组类型, 简单的来说就是一个可以存储一列多行的数据类型
  -- INDEX BY BINARY_INTEGER  指索引组织类型
  v_arr_table nest_table1;
  cnt int;
begin
  v_arr_table(1) := 'china';
  v_arr_table(2) := 'USA';
  
  cnt := v_arr_table.count;
  for i in 1 .. cnt loop
    dbms_output.put_line(v_arr_table(i));
    end loop;
end;

使用2

reate table country
(
  id int,
  name varchar2(15)
);
insert into country values(1,'china');
insert into country values(2,'USA');
commit;

set serveroutput on
declare
type t_r_country is record(
  id country.id%type,
  name country.name%type
  );
  type t_array_country is table of t_r_country index by binary_integer;
  --或者这里使用
  type t_array_country1 is table of country%rowtype index by binary_integer;
  v_array_country t_array_country;
  v_array_country1 t_array_country1;
begin
  select id,name bulk collect into v_array_country from country;
  for i in 1 .. v_array_country.count loop
    dbms_output.put_line(v_array_country(i).name);
    end loop;
    
  for i in 1 .. v_array_country.count loop
    dbms_output.put_line(v_array_country(i).name);
    end loop;
end;

使用3

declare
	type emp_table_type is table of emp%rowtype index by binary_integer;--这是类型声明
	type ename_table_type is table of emp.ename%type;--这是类型声明
	emp_table emp_table_type;--这里是定义变量
	ename_table ename_table_type;--这里是定义变量
begin
	--没有index by需要初始化才能使用,否则会报错:变量未初始化
	ename_table:= ename_table_type();
	for v_ind in 1..20 loop
	ename_table.extend;--每次添加元素都要extend
	ename_table.(v_ind):='Ename' || v_ind;

	--index by只要定义就可以直接用,无需初始化
	emp_table(v_ind).ename:='Ename' || v_ind;
	emp_table(v_ind).sal:=1000* v_ind;
	end loop;
end;

使用4

SQL> edit
已写入 file afiedt.buf

  1  declare
  2  v_table_name varchar2(100);
  3  TYPE type_array_str IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR(32);
  4  v_array_table type_array_str;
  5  begin
  6    v_array_table('china') := 'good';
  7    v_array_table('usa') := 'bad';
  8    v_table_name := v_array_table.FIRST;
  9  loop
 10       exit when v_table_name is null;
 11       dbms_output.put_line(v_array_table(v_table_name));
 12       dbms_output.put_line(v_table_name);
 13       v_table_name := v_array_table.next(v_table_name);
 14  end loop;
 15* end;
SQL> /
good
china
bad
usa

PL/SQL 过程已成功完成。

二，对象

面向对象编程语言，java，c++都允许定义类，当然oracle也支持对象操作

对象示例1

SQL> edit
已写入 file afiedt.buf

  1  create or replace type obj_country is object(
  2   id int,
  3   name varchar2(15),
  4   member function get_sysdate return date
  5* );
  6  /

类型已创建。

SQL> edit
已写入 file afiedt.buf

  1  create or replace type body obj_country as
  2   member function get_sysdate return date is
  3     v_sysdate date;
  4   begin
  5     select sysdate into v_sysdate
  6     from dual;
  7     return v_sysdate;
  8   end;
  9* end;
SQL> /

类型主体已创建。

SQL> create table obj_t1 ( id int,desc1 obj_country);

表已创建。

SQL> insert into obj_t1(id,desc1)
  2  values(1,obj_country(1,'china'));

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from obj_t1;

        ID
----------
DESC1(ID, NAME)
---------------------------------------------------------------------

         1
OBJ_COUNTRY(1, 'china')


SQL> select t.desc1.get_sysdate() from obj_t1 t;

T.DESC1.GET_SY
--------------
06-1月 -18

对象示例2

create or replace type obj_country is object(id int,name varchar2(15));

create or replace type tab_country is  table of obj_country ;
set serveroutput on
declare
  v_array_country tab_country := tab_country();
begin
for cur in (select id,name from country) loop
    v_array_country.extend;
    v_array_country(v_array_country.count) :=obj_country(cur.id,cur.name);
    end loop;
end;

三，集合应用与批量提交的一些场景

使用bulk collect+forall 加速批量提交

批量提交占用较少的undo，资源快速释放，并且他适合批量操作

不管是显示游标还是隐式游标，都可以通过BULK COLLECT在数据库的单次交互中获取多行数据。BULKCOLLECT相对Cursor Loop方式减少了PL/SQL引擎和SQL引擎之间切换次数，因此也减少了提取数据时的额外开销；通过limit rows限制提取记录数，减少对PGA的消耗,避免换页产生；FORALL告诉PL/SQL引擎要先把一个或多个集合的所有成员都绑定到SQL语句中，然后再把语句发送给SQL引擎,如果for ..loop 循环，那么会发送n次，而用Forall，一次行全部发送过去

-- 环境准备
create table big_ta (id int,name varchar2(20),datavalue number);
insert into big_ta values(1,'china',1.90);
insert into big_ta values(1,'china',1.90);
insert into big_ta values(2,'usa',1.00);
insert into big_ta values(2,'usa',2.00);
insert into big_ta select rownum,object_name,1.0 from dba_objects;
insert into big_ta select * from big_ta;
commit;

create table big_tb (id int,name varchar2(20),datavalue number);

以下是批量插入，删除，更新操作

-- 插入
declare
  TYPE ARRAY1 IS TABLE OF big_tb%ROWTYPE;
  l_data ARRAY1;
  CURSOR c IS
    SELECT * FROM big_ta;
    
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT
      INTO l_data LIMIT 5000;
    FORALL i IN 1 .. l_data.COUNT 
      INSERT /*+append*/
      INTO big_tb
      VALUES l_data
        (i);
    commit;
    EXIT WHEN c%NOTFOUND;
  END LOOP;
  CLOSE c;
  end;


--delete

DECLARE
  CURSOR mycursor IS
    SELECT rowid FROM big_ta WHERE id = 1;
  TYPE rowid_table_type IS TABLE OF rowid index by pls_integer;
  v_rowid rowid_table_type;
BEGIN
  OPEN mycursor;
  LOOP
    FETCH mycursor BULK COLLECT
      INTO v_rowid LIMIT 5000;
    EXIT WHEN v_rowid.count = 0;
    FORALL i IN v_rowid.FIRST .. v_rowid.LAST
      DELETE big_ta WHERE rowid = v_rowid(i);
    COMMIT;
  END LOOP;
  CLOSE mycursor;
END;
/


-- update

DECLARE
  CURSOR mycursor IS
    SELECT id FROM big_ta WHERE name = 'usa';
  TYPE num_tab_t IS TABLE OF NUMBER;
  v_pk_tab NUM_TAB_T;
BEGIN
  OPEN mycursor;
  LOOP
    FETCH mycursor BULK COLLECT
      INTO v_pk_tab LIMIT 5000;
    EXIT WHEN v_pk_tab.count = 0;
    FORALL i IN v_pk_tab.FIRST .. v_pk_tab.LAST
      UPDATE big_ta SET datavalue = datavalue+1  WHERE id = v_pk_tab(i);
    COMMIT;
  END LOOP;
  CLOSE mycursor;
END;
/