About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc
全部博文(169)
分类: Oracle
2020-07-08 09:09:19
有两种方式声明集合变量,一是在应用程序级别的声明,二是在schema级别的声明。
1. 应用程序级别的声明,指三种集合类型声明在程序中,程序可以使用,如果是声明在package中,则所有的package的执行部分可以使用。在声明部分使用type语句。
2.schema级别的声明,在应用程序之外声明,也就是建立一个数据库对象,使用create type,
当然也可以使用create or replace type,删除使用drop type name [force],这样的自定义类型,可以在数据库中表的列类型应用,以及程序中使用。
声明index by 表(associative array)
TYPEtable_type_nameIS TABLE OFdatatype[ NOT NULL ]
INDEX BYindex_type;
Index_type是管理行的内容的索引。index_type不局限于binary_integer,下面全为合法:
INDEX BY BINARY_INTEGER;
INDEX BY PLS_INTEGER;
INDEX BY POSITIVE;
INDEX BY NATURAL;
INDEX BY SIGNTYPE;
INDEX BY VARCHAR2(32767);
INDEX BYtable.column%TYPE;
INDEX BYcursor.column%TYPE;
INDEX BYpackage.variable%TYPE;
INDEX BYpackage.subtype;
如在包中声明类型,然后将excute权限给public,则所有的就可以使用这个类型。
CREATE OR REPLACE PACKAGE collection_types IS -- Associative array types TYPE boolean_aat IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
TYPE date_aat IS TABLE OF DATE INDEX BY BINARY_INTEGER;
TYPE pls_integer_aat IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;
TYPE number_aat IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE identifier_aat IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
TYPE vcmax_aat IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER; --index by表中存的是record,并且索引为varchar2类型,10g支持索引为varchar2,必须指明长度
type a is record( id number, mm varchar2(10) ); type TEST_TYPE is table of a index by varchar2(10);
-- Nested table types TYPE boolean_ntt IS TABLE OF BOOLEAN; TYPE date_ntt IS TABLE OF DATE; TYPE pls_integer_ntt IS TABLE OF PLS_INTEGER; TYPE number_ntt IS TABLE OF NUMBER; TYPE identifier_ntt IS TABLE OF VARCHAR2(30); TYPE vcmax_ntt IS TABLE OF VARCHAR2(32767) END collection_types; /
|
使用:
DECLARE
family_birthdays collection_types.date_aat;
声明nested table or arrays
Schema级别的声明:
CREATE [ OR REPLACE ] TYPEtype_nameAS | IS
TABLE OFelement_datatype[ NOT NULL ];
CREATE [ OR REPLACE ] TYPEtype_nameAS | IS
VARRAY (max_elements) OFelement_datatype[ NOT NULL ];
删除类型
DROP TYPEtype_name[ FORCE ];//force是不管有没有被使用都删除
应用程序级别的声明,create or replace不使用,直接用type声明。
对于element_type在schema级别的声明可以是不可以引用其他对象类型如%rowtype,在应用程序级别
的声明可以使用其他类型。
修改nested table or varray的声明
1. 对varray,修改存放的元素个数,如
create or replace type ts is varray(10) of varchar2(10) not null;--声明ts为数组
--修改数组中长度信息
Alter type ts modify limit 100 invalidate;
使用alter type命令,然后modify limit 长度,其中invalidate是强迫所有的引用此类型的程序或表发生改变。
2. 修改存放的元素类型
Alter type ts modify element type varchar2(100) cascade;
使用alter type命令,然后是element type 修改后的 cascade是级联所有使用此类型的程序或表。
声明和初始化集合变量
collection_name collection_type [:=collection_type(...)];
对index by表不需要构造器初始化,对于nested table和varray需要构造器初始化。如果在使用之前不初始化会报错:ORA-06531: Reference to uninitialized collection一般是在声明集合变量的时候给予空初始化,如果显示初始化多个值,则用逗号隔开。
对nested table或varray的初始化,可以在声明部分,也可以在执行部分,如
DECLARE
TYPE company_aat IS TABLE OF company%ROWTYPE;
premier_sponsor_list company_aat := company_aat( );--声明部分的初始化
BEGIN
...--
END;
DECLARE
TYPE company_aat IS TABLE OF company%ROWTYPE;
premier_sponsor_list company_aat;
BEGIN
premier_sponsor_list:= company_aat( );--执行部分的初始化
END;
CREATE OR REPLACE TYPE Color_tab_t AS TABLE OF VARCHAR2(30); /** 集合的声明和初始化 **/ DECLARE my_favorite_colors Color_tab_t := Color_tab_t( );--初始化为空 his_favorite_colors Color_tab_t := Color_tab_t('PURPLE');--初始化一个元素 her_favorite_colors Color_tab_t := Color_tab_t('PURPLE', 'GREEN');--初始化两个元素
begin dbms_output.put_line(her_favorite_colors(2));--通过下标访问 END;
|
增加新元素,必须使用extend,初始化了,使用隐extend。
通过隐性相互赋值直接初始化
相同类型的集合变量可以相互赋值,而不是存放的元素类型相同。
create or replace type st is table of varchar2(10);
declare a st:=st('ding','wang');--初始化两个值 b st:=st();--初始化为空 begin b:=a;--相同类型之间赋值,相当于拷贝 dbms_output.put_line('直接赋值b(2)='||b(2)); b(2):='jack';--修改b(2),a(2)不受影响 dbms_output.put_line('修改后的b(2)='||b(2)); end; |
通过fetch或select..into隐性初始化
如果数据库中表的列类型是集合类型,可以通过fetch或select..into到集合中实现初始化。
--建立一个schema level的集合类型用数据操纵集合
Index by表初始化不需要构造器,只要初始化一个就可以,直接赋值,索引可正可负,范围在-231 + 1 and 231 – 1.
Nested table和array初始化必须通过构造器。先初始化为空的话,要增加数据必须通过extend关键字,而且增加数据的范围由extend决定。否则会报越界错误。
给集合变量分配数据:
DECLARE
TYPE emp_copy_t IS TABLE OF emp%ROWTYPE;
l_emps emp_copy_t := emp_copy_t( );
l_emprec emp%ROWTYPE;
BEGIN
l_emprec.ename := 'Steven';
l_emprec.salary := 10000;
l_emps.EXTEND
l_emps (l_emps.LAST) := l_emprec;
END;
Index by 表在10g中接受索引类型的种类
10g中index by表又称为associate arrays,接受的索引不光是binary_integer,对于pls_integer,varchar2等也接受。见下表:
INDEX BY clause |
Minimum value |
Maximum value |
INDEX BY BINARY_INTEGER
|
-231 + 1 |
231 - 1 |
INDEX BY PLS_INTEGER
|
-231 + 1 |
231 - 1 |
INDEX BY NATURAL
|
0 |
231 - 1 |
INDEX BY POSITIVE
|
1 |
231 - 1 |
INDEX BY SIGNTYPE
|
-1 |
1 |
INDEX BY VARCHAR(N)
|
Any string within specified length 任何字符都合法 |
Any string within specified lengt |
declare type aa is table of varchar2(10) index by varchar2(10);--支持varchar2作为索引,任意字符 vb aa; begin vb('ds'):='jack'; vb('4'):='wang'; vb('5'):='ding'; dbms_output.put_line(vb('ds')); end; |