oracle集合添加元素,oracle集合与记录

集合。集合是相同类型元素的组合。数据库中相当于"多行单列", 类似于数组, 使用唯一的下标来标识其中的每个元素

In a collection, the internal components always have the same data type, and are called elements. You can access each element of a collection variable by its unique index, with this syntax: variable_name(index). To create a collection variable, you either define a collection type and then create a variable of that type or use %TYPE.

关联数组(索引表)Associative array(or index-by table)，下标无限制，可以为负数，元素个数无限制

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY key_type;

type_name：用户自定义数据类型的名字

element_type：索引表中元素类型

key_type：索引表元素下标的数据类型(BINARY_INTEGER，PLS_INTEGER,VARCHAR2)

declare

--显式游标

cursor cur_dept is

select department_name from departments where rownum < 6;

--关联数组

type type_dept is table of departments.department_name%type index by pls_integer;

v_dept type_dept;

i pls_integer := 0;

begin

for c in cur_dept loop

i := i + 1;

v_dept(i) := c.department_name;

dbms_output.put_line('插入' || v_dept(i));

end loop;

end;

嵌套表(Nested table)，下标从1开始，通过构造器初始化，大小可以自动增长。用extend方法可以扩展元素个数

DECLARE

CURSOR cur_dept IS

SELECT department_name FROM departments WHERE ROWNUM < 6;

TYPE type_dept IS TABLE OF departments.department_name%TYPE;

--构造器，可以无参也可带参

v_dept type_dept := type_dept();

i PLS_INTEGER := 0;

BEGIN

FOR c IN cur_dept LOOP

--extend方法

v_dept.extend;

i := i + 1;

v_dept(i) := c.department_name;

dbms_output.put_line('插入' || v_dept(i));

END LOOP;

--据说v_dept.first..v_dept.last这种方式不好当数据出现null值时。

--first/last方法返回集合上下标，count方法返回集合个数。

FOR j IN 1 .. v_dept.count LOOP

dbms_output.put_line('展示' || v_dept(j));

END LOOP;

END;

变长数组VARRAY(variable-size array)，元素之间是紧密排列，通过构造器初始化，元素有序排列。

type type_name IS {varray|varying array}(maxinum_size) OF element_type[not null];

type_name:可变数组的类型名

maxinum_size：可变数组元素个数的最大值

element_type:数组元素的类型

DECLARE

TYPE type_var IS VARRAY(10) OF INT;

v_var type_var := type_var();

BEGIN

--dbms_output.put_line(varr.count);

FOR i IN 1 .. 5 LOOP

v_var.extend;

v_var(i) := i + 1;

END LOOP;

FOR i IN 1 .. 5 LOOP

dbms_output.put_line(v_var(i));

END LOOP;

END;

区别

记录。单行多列的标量构成的复合结构。可以看做是一种用户自定义数据类型。组成类似于多维数组。将一个或多个标量封装成一个对象进行操作。是一种临时复合对象类型。 记录可以直接赋值。RECORD1 :=RECORD2; 记录不可以整体比较。 记录不可以整体判断为空。

In a record, the internal components can have different data types, and are called fields. You can access each field of a record variable by its name, with this syntax: variable_name.field_name. To create a record variable, you either define a RECORD type and then create a variable of that type or use %ROWTYPE or %TYPE.

declare

type rec_emp is record(

v_city locations.city%type,

v_deptname departments.department_name%type);

rec1 rec_emp;

rec2 rec_emp;

begin

SELECT loc.CITY, dept.DEPARTMENT_NAME

INTO rec1

FROM LOCATIONS loc, departments dept

where loc.LOCATION_ID = dept.LOCATION_ID

and dept.DEPARTMENT_ID = 20;

SELECT loc.CITY, dept.DEPARTMENT_NAME

INTO rec2

FROM LOCATIONS loc, departments dept

where loc.LOCATION_ID = dept.LOCATION_ID

and dept.DEPARTMENT_ID = 30;

DBMS_OUTPUT.put_line(rec1.v_city || '---' || rec1.v_deptname);

DBMS_OUTPUT.put_line(rec2.v_city || '---' || rec2.v_deptname);

end;

DECLARE

v_dept departments%rowtype;

BEGIN

SELECT * INTO v_dept FROM departments WHERE department_id = 30;

dbms_output.put_line(v_dept.department_name || v_dept.manager_id);

END;

3.多行多列用记录+集合。

declare

type rec_dept is record(

v_id departments.department_id%type,

v_name departments.department_name%type,

v_city locations.city%type);

type coll_dept is table of rec_dept index by pls_integer;

v_dept coll_dept;

i pls_integer := 1;

begin

for j in (select dept.department_id,dept.department_name,loc.city from

departments dept,locations loc where dept.location_id=loc.location_id

and dept.department_id<50) loop

v_dept(i) := j;

i := i + 1;

end loop;

for k in 1 .. v_dept.count loop

dbms_output.put_line(v_dept(k).v_id || v_dept(k).v_name || v_dept(k).v_city);

end loop;

end;