oracle 记录和数组,数组、记录、嵌套表的使用

Oracle学习：PL/SQL中的集合类型分析

三种集合分类的区别之一是， Nested

table与VARRY既可以被用于PL/SQL，也可以被直接用于数据库中，但是Associative

array不行，也就是说，Associative array是不能通过CREATE

TYPE语句进行单独创建，只能在PL/SQL块(或Package)中进行定义并使用(即适用范围是PL/SQL

Block级别)，而Nested table与VARRAY则可以使用CREATE

TYPE进行创建(即适用范围是Schema级别)，它们还可以直接作为数据库表中列的类型。

Associative array

CREATE OR REPLACE TYPE ib_planguage IS TABLE OF VARCHAR2(10)

INDEX BY PLS_INTEGER;

通不过

PLS-00355: use of pl/sql table not allowed in this context

在块中

TYPE ib_planguage IS TABLE OF VARCHAR2(10)INDEX BY

PLS_INTEGER;

--下标是BY PLS_INTEGER，数据是varchar2(10)。

可以用。

Nested table (嵌套表)

CREATE OR REPLACE TYPE nt_planguage IS TABLE

OF VARCHAR2(10);

定义可以在使用。

------------

TYPE varTabType IS TABLE

OF varchar2(10) INDEX BY

PLS_INTEGER;

--下标是BY PLS_INTEGER，数据是varchar2(10)。

TYPE population_type IS TABLE OF

NUMBER INDEX BY

VARCHAR2(64);--下标是字符串，数据是number。

country_population

population_type;声明后可以直接用，

continent_population('Australia') :=

30000000;

continent_population('Antarctica') := 1000;

-- 新下标

continent_population('Antarctica') := 1001;

-- 更新以前的

DECLARE

TYPE nested_type IS TABLE OF

VARCHAR2(30); --

一维，数据为VARCHAR2(30); TYPE varray_type IS VARRAY(5)

OF INTEGER;

TYPE assoc_array_num_type IS

TABLE OF NUMBER INDEX BY PLS_INTEGER;

TYPE assoc_array_str_type IS

TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;

TYPE assoc_array_str_type2 IS

TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);

v1 nested_type;

v2 varray_type;

v3 assoc_array_num_type;

v4 assoc_array_str_type;

v5

assoc_array_str_type2;

BEGIN

-- an arbitrary number of strings can be inserted v1

v1 :=

nested_type('Shipping','Sales','Finance','Payroll');

v2 := varray_type(1, 2, 3, 4,

5); -- Up to 5 integers

v3(99) := 10; -- Just start

assigning to elements

v3(7) := 100; -- Subscripts

can be any integer values

v4(42) := 'Smith'; -- Just

start assigning to elements

v4(54) := 'Jones'; --

Subscripts can be any integer values

v5('Canada') := 'North

America'; -- Just start assigning to elements

v5('Greece') :=

'Europe'; -- Subscripts can be string values

END;

--可以这样在包头里定义类型，然后使用。

CREATE PACKAGE personnel AS

TYPE staff_list IS TABLE OF

employees.employee_id%TYPE;

PROCEDURE award_bonuses

(empleos_buenos IN staff_list);

END personnel;

/

CREATE PACKAGE BODY personnel AS

PROCEDURE award_bonuses (empleos_buenos staff_list) IS

BEGIN

FOR i IN

empleos_buenos.FIRST..empleos_buenos.LAST

LOOP

UPDATE employees SET salary = salary + 100

WHERE employees.employee_id = empleos_buenos(i);

END LOOP;

END;

END;

/

DECLARE

good_employees personnel.staff_list;

BEGIN

good_employees := personnel.staff_list(100, 103, 107);

personnel.award_bonuses (good_employees);

END;

/

--类似二维数组的定义。

DECLARE

TYPE name_rec IS

RECORD ( first_name VARCHAR2(20), last_name

VARCHAR2(25) );

TYPE names IS VARRAY(250) OF

name_rec; --以name_rec为数据的数组，有250个下标。

BEGIN

NULL; --怎么使用呢？？

END;

/

DECLARE

TYPE dnames_tab IS TABLE OF

VARCHAR2(30); --

一维下标为数字，内容为varchar2(30)

dept_names dnames_tab;

BEGIN

dept_names :=

dnames_tab('Shipping','Sales','Finance','Payroll'); --需要初始化

END;

/

DECLARE

-- In the varray, we put an upper limit on the number of

elements

TYPE dnames_var IS VARRAY(20)

OF VARCHAR2(30); -- 一维下标为数字，内容为字符串

dept_names dnames_var;

BEGIN

-- Because dnames is declared as VARRAY(20), we can put up to 10--

elements in the constructor

-- 因为dnames被宣布为VARRAY(20),我们可以将10——元素在构造函数中

dept_names :=

dnames_var('Shipping','Sales','Finance','Payroll');

END;

/

DECLARE

TYPE dnames_tab IS TABLE OF

VARCHAR2(30);

dept_names dnames_tab;

TYPE dnamesNoNulls_type IS

TABLE OF VARCHAR2(30) NOT NULL;

BEGIN

dept_names :=

dnames_tab('Shipping', NULL,'Finance',

NULL);

-- If dept_names was of type dnamesNoNulls_type, we could not

include null values in the constructor

-- 如果部门dnamesNoNulls_type类型的名字,我们不能包含null值在构造函数中

END;

/

DECLARE

TYPE dnames_tab IS TABLE OF

VARCHAR2(30);

dept_names dnames_tab :=

dnames_tab('Shipping','Sales','Finance','Payroll');

BEGIN

NULL;

END;

/

DECLARE

TYPE dnames_var IS VARRAY(20)

OF VARCHAR2(30); --20个元素，每个为VARCHAR2(30)

dept_names

dnames_var;

BEGIN

IF dept_names IS NULL

THEN

DBMS_OUTPUT.PUT_LINE('初始化以前，数组是空的.');

-- While the varray is null, we cannot check its COUNT

attribute.

-- DBMS_OUTPUT.PUT_LINE('有 ' ||

dept_names.COUNT || ' 元素.');

ELSE

DBMS_OUTPUT.PUT_LINE('初始化前，数组不是空的。');

END IF;

dept_names := dnames_var(); --

initialize empty varray

IF dept_names IS NULL

THEN

DBMS_OUTPUT.PUT_LINE('初始化以后，数组是空的.');

ELSE

DBMS_OUTPUT.PUT_LINE('初始化后，数组不是空的。');

DBMS_OUTPUT.PUT_LINE('有 ' || dept_names.COUNT || ' 元素.');

END IF;

dept_names := dnames_var('12','323'); --

IF dept_names IS NULL THEN

DBMS_OUTPUT.PUT_LINE('初始化后，加2个元素，数组是空的.');

ELSE

DBMS_OUTPUT.PUT_LINE('初始化后，加2个元素');

DBMS_OUTPUT.PUT_LINE('有 ' || dept_names.COUNT || ' 元素.');

END IF;

END;

/

运行结果

初始化以前，数组是空的.

初始化后，数组不是空的。

有 0 元素.

初始化后，加2个元素

有 2 元素.

DECLARE

TYPE sum_multiples IS TABLE OF PLS_INTEGER INDEX

BY PLS_INTEGER;

n PLS_INTEGER :=

5; -- number of multiples to

sum for display

sn PLS_INTEGER := 10; --

number of multiples to sum

m PLS_INTEGER :=

6; -- multiple

FUNCTION get_sum_multiples(multiple IN PLS_INTEGER, num IN

PLS_INTEGER)

RETURN sum_multiples IS

s sum_multiples;

BEGIN

FOR i IN 1..num LOOP

s(i) := multiple * ((i * (i + 1)) / 2) ; -- sum of multiples

END LOOP;

RETURN

s;

END get_sum_multiples;

BEGIN

-- call function to retrieve the element identified by subscript

(key)

for i in 1..n loop

DBMS_OUTPUT.PUT_LINE('Sum of the first ' ||

TO_CHAR(i) || ' multiples of ' ||

TO_CHAR(m) || ' is ' || TO_CHAR(get_sum_multiples (m,

sn)(i)));

end loop;

END;

/

结果

SQL>

Sum of the first 1 multiples of 3 is 3

Sum of the first 2 multiples of 3 is

9 3+3*2

Sum of the first 3 multiples of 3 is

18 9+3*3

Sum of the first 4 multiples of 3 is

30 18+3*4

Sum of the first 5 multiples of 3 is

45 30+3*5

PL/SQL procedure successfully completed

SQL>

Sum of the first 1 multiples of 6 is 6

Sum of the first 2 multiples of 6 is

18 6+6*2

Sum of the first 3 multiples of 6 is

36 18+6*3

Sum of the first 4 multiples of 6 is

60 36+6*4

Sum of the first 5 multiples of 6 is

90 60+6*5

DECLARE

TYPE last_name_typ IS

VARRAY(3) OF VARCHAR2(64);

TYPE surname_typ IS VARRAY(3)

OF VARCHAR2(64);

-- 定义的一样，但是被看做是两种类型，之后不能相互赋值。

group1 last_name_typ :=

last_name_typ('Jones','Wong','Marceau');

group2 last_name_typ :=

last_name_typ('Klein','Patsos','Singh');

-- This third variable has a similar declaration, but is not the

same type.

group3 surname_typ :=

surname_typ('Trevisi','Macleod','Marquez');

BEGIN

-- Allowed because they have the same datatype

group1 := group2;

-- Not allowed because they have different datatypes

-- group3

:= group2; -- raises an error

-- PLS-00382: expression is of wrong type

END;

/

DECLARE

TYPE nested_typ IS TABLE OF NUMBER;

nt1 nested_typ := nested_typ(1,11,12);

nt2 nested_typ := nested_typ(23,32,33,3);

nt3 nested_typ :=

nested_typ(23,33,33,21); nt4 nested_typ := nested_typ(41,41,22);

answer nested_typ;

-- The results might be in a different order than you expect.

-- Remember, you should not rely on the order of elements in nested

tables.

PROCEDURE print_nested_table(the_nt nested_typ)

IS

output VARCHAR2(128);

BEGIN

IF the_nt IS NULL THEN

DBMS_OUTPUT.PUT_LINE('Results:

');

RETURN;

END IF;

IF the_nt.COUNT = 0 THEN

DBMS_OUTPUT.PUT_LINE('Results: empty set');

RETURN;

END IF;

FOR i IN the_nt.FIRST .. the_nt.LAST

LOOP

output := output || the_nt(i) || ' ';

END LOOP;

DBMS_OUTPUT.PUT_LINE('Results: ' || output);

END;

BEGIN

answer := nt1 MULTISET UNION nt4; -- 并集

print_nested_table(answer);

answer := nt1 MULTISET UNION nt3; -- 并集

print_nested_table(answer);

answer := nt1 MULTISET UNION DISTINCT nt3; --

并集后取唯一

print_nested_table(answer);

answer := nt2 MULTISET INTERSECT nt3; --

交集

print_nested_table(answer);

answer := nt2 MULTISET INTERSECT DISTINCT nt3;

-- 交集后取唯一

print_nested_table(answer);

answer := SET(nt3); -- 去重复

print_nested_table(answer);

answer := nt3 MULTISET EXCEPT nt2; --

减 print_nested_table(answer);

answer := nt3 MULTISET EXCEPT DISTINCT nt2; --

减后取唯一

print_nested_table(answer);

END;

/

Results: 1 11 12 41 41 22

Results: 1 11 12 23 33 33 21

Results: 1 11 12 23 33 21

Results: 23 33

Results: 23 33

Results: 23 33 21

Results: 33 21

Results: 21

DECLARE

TYPE obj_name_rec is

RECORD (

object_name user_objects.OBJECT_NAME%TYPE,

object_id user_objects.OBJECT_ID%TYPE,

object_type user_objects.OBJECT_TYPE%TYPE

);

-- Array type that can hold information 10 employees

TYPE

objList_arr IS VARRAY(10) OF obj_name_rec;

objs

objList_arr; -- Declare a cursor to select a subset of columns.

CURSOR

c1 IS SELECT object_name,object_id,object_type FROM

user_objects;

Type NameSet IS TABLE OF

c1%ROWTYPE;

SeniorTen NameSet;

EndCounter NUMBER :=

10;

BEGIN

objs := objList_arr();

SELECT object_name,object_id,object_type

BULK COLLECT INTO SeniorTen FROM

user_objects WHERE object_type = 'TABLE' ORDER BY object_id;

IF SeniorTen.LAST > 0

THEN

IF

SeniorTen.LAST < 10 THEN EndCounter :=

SeniorTen.LAST;

END

IF;

FOR i in

1..EndCounter LOOP

objs.EXTEND(1); objs(i) := SeniorTen(i);

DBMS_OUTPUT.PUT_LINE(objs(i).object_name || ', '

|| objs(i).object_id || ', ' ||

objs(i).object_type);

END

LOOP;

END IF;

END;

/

DECLARE

TYPE emp_name_rec is RECORD (

firstname employees.first_name%TYPE,

lastname employees.last_name%TYPE,

hiredate employees.hire_date%TYPE

);

-- Table type that can hold information about employees

TYPE EmpList_tab IS TABLE OF emp_name_rec;

SeniorSalespeople EmpList_tab;

-- Declare a cursor to select a subset of columns.

CURSOR c1 IS SELECT first_name, last_name, hire_date FROM employees;

EndCounter NUMBER := 10;

TYPE EmpCurTyp IS REF CURSOR;

emp_cv EmpCurTyp;

BEGIN

OPEN emp_cv FOR SELECT first_name, last_name, hire_date FROM employees

WHERE job_id = 'SA_REP' ORDER BY hire_date;

FETCH emp_cv BULK COLLECT INTO SeniorSalespeople;

CLOSE emp_cv;

-- for this example, display a maximum of ten employees

IF SeniorSalespeople.LAST > 0 THEN

IF SeniorSalespeople.LAST < 10 THEN EndCounter := SeniorSalespeople.LAST;

END IF;

FOR i in 1..EndCounter LOOP

DBMS_OUTPUT.PUT_LINE(SeniorSalespeople(i).lastname || ', '

|| SeniorSalespeople(i).firstname || ', ' || SeniorSalespeople(i).hiredate);

END LOOP;

END IF;

END;

/

DECLARE

TYPE nested_typ IS TABLE

OF NUMBER;

nt1 nested_typ := nested_typ(1,2,3);

nt2 nested_typ := nested_typ(3,2,1);

nt3 nested_typ := nested_typ(2,3,1,3);

nt4 nested_typ := nested_typ(1,2,5);

answer BOOLEAN;

howmany NUMBER;

PROCEDURE testify(truth BOOLEAN DEFAULT NULL,

quantity NUMBER DEFAULT NULL) IS

BEGIN

IF truth IS

NOT NULL THEN

DBMS_OUTPUT.PUT_LINE(CASE truth WHEN TRUE THEN 'True' WHEN FALSE

THEN 'False' END);

END

IF;

IF quantity

IS NOT NULL THEN

DBMS_OUTPUT.PUT_LINE(quantity);

END

IF;

END;

BEGIN

answer := nt1 IN (nt2,nt3,nt4); -- true, nt1 在

nt2，nt3，nt4的并集中,似乎不完全是。

testify(truth => answer);

answer := nt1 SUBMULTISET OF nt4; --

true, nt1 是 nt4 的子集

testify(truth => answer);

answer := nt1 NOT SUBMULTISET OF nt4; --

testify(truth => answer);

howmany := CARDINALITY(nt3); -- nt3的元素个数

testify(quantity =>

howmany);

howmany := CARDINALITY(SET(nt3)); --

nt3的基本的元素个数

testify(quantity =>

howmany);

answer := 2 MEMBER OF nt1; --

判数据是否属于nt1中的元素

testify(truth => answer);

answer := nt3 IS A SET; -- false, nt3 has

duplicates

testify(truth => answer);

answer := nt3 IS NOT A SET; -- true, nt3 has

duplicates

testify(truth => answer);

answer := nt1 IS EMPTY; -- nt1 为空

testify(truth => answer);

END;

/

DECLARE

TYPE t1 IS VARRAY(10) OF

INTEGER; -- 以整数

为数据的10个元素的数组 TYPE nt1 IS VARRAY(10) OF t1; -- multilevel varray

type 以t1为数据的10个元素的数组 va t1 :=

t1(2,3,5);

-- initialize multilevel varray

nva nt1 := nt1(va, t1(55,6,73),

t1(2,4), va);

i INTEGER;

va1 t1;

BEGIN

-- multilevel access

i := nva(2)(3); -- i will get value 73

DBMS_OUTPUT.PUT_LINE('I = ' || i);

-- add a new varray element to nva

nva.EXTEND; --扩展

-- replace inner varray elements

nva(5) := t1(56,

32); nva(4) := t1(45,43,67,43345); --可以写10个

-- replace an inner integer element

nva(4)(4) := 1; -- replaces 43345 with 1

-- add a new element to the 4th varray element

-- and store integer 89 into it.

nva(4).EXTEND;

nva(4)(5) := 89;

END;

/

DECLARE

TYPE tb1 IS TABLE OF

VARCHAR2(20); --以varchar2(20)为元素的嵌套表

TYPE Ntb1 IS TABLE OF tb1; -- table of table

elements --以 tb1 为元素的嵌套表

TYPE Tv1 IS VARRAY(10) OF

INTEGER; --以整数 为元素的一维数组

TYPE ntb2 IS TABLE OF tv1; -- table of varray

elements 以tv1

为元素的一维数组

vtb1 tb1 := tb1('one', 'three');

vntb1 ntb1 := ntb1(vtb1);

vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3)); --

table of varray elements

BEGIN

vntb1.EXTEND; vntb1(2) :=

vntb1(1);

-- delete the first element in vntb1

vntb1.DELETE(1);

-- delete the first string from the second table in the nested

table

vntb1(2).DELETE(1);

END;

/