全面探讨PL/SQL的复合数据类型

全面探讨PL/SQL的复合数据类型

[文章导读]

 

　PL/SQL有两种复合数据结构：记录和集合。记录由不同的域组成，集合由不同的元素组成

PL/SQL有两种复合数据结构：记录和集合。记录由不同的域组成，集合由不同的元素组成。在本文中我们将讨论记录和集合的类型、怎样定义和使用记录和集合。

　　PL/SQL 记录

　　记录是PL/SQL的一种复合数据结构，scalar数据类型和其他数据类型只是简单的在包一级进行预定义，但复合数据类型在使用前必须被定义，记录之所以被称为复合数据类型是因为他由域这种由数据元素的逻辑组所组成。域可以是scalar数据类型或其他记录类型，它与c语言中的结构相似，记录也可以看成表中的数据行，域则相当于表中的列，在表和虚拟表（视图或查询）中非常容易定义和使用，行或记录中的每一列或域都可以被引用或单独赋值，也可以通过一个单独的语句引用记录所有的域。在存储过程或函数中记录也可能有参数。

　　创建记录

　　在PL/SQL中有两种定义方式：显式定义和隐式定义。一旦记录被定义后，声明或创建定义类型的记录变量，然后才是使用该变量。隐式声明是在基于表的结构或查询上使用%TYPE属性，隐式声明是一个更强有力的工具，这是因为这种数据变量是动态创建的。

　　显式定义记录

　　显式定义记录是在PL/SQL程序块中创建记录变量之前在声明部分定义。使用type命令定义记录，然后在创建该记录的变量。语法如下：

TYPE record_type IS RECORD (field_definition_list);

　　field_definition_list是由逗号分隔的列表。

　　域定义的语法如下：

field_name data_type_and_size [NOT NULL][{:=|DEFAULT} default_value]

　　域名必须服从与表或列的命名规则相同的命名规则。下面我们看一个例子：

DELCARE
TYPE stock_quote_rec IS RECORD
(symbol stock.symbol%TYPE
,bid NUMBER(10,4)
,ask NUMBER(10,4)
,volume NUMBER NOT NULL:=0
,exchange VARCHAR2(6) DEFAULT 'NASDAQ'
);

real_time_quote stock_quote_rec;
variable

　　域定义时的%TYPE属性用于引用数据库中的表或视图的数据类型和大小,而在此之前程序不知道类型和大小。在上面的例子中记录域在编译时将被定义为与列SYMBOL相同的数据类型和大小,当代码中要使用来自数据库中的数据时，在变量或域定义中最好使用%TYPE来定义。

　　隐式定义记录

　　隐式定义记录中，我们不用描述记录的每一个域。这是因为我们不需要定义记录的结构，不需要使用TYPE语句，相反在声明记录变量时使用%ROWTYPE命令定义与数据库表，视图，游标有相同结构的记录，与TYPE命令相同的是它是一种定义获得数据库数据记录的好方法。

DECLARE

accounter_info accounts%ROWTYPR;

CURSOR xactions_cur(acct_no IN VARCHAR2) IS
SELECT action,timestamp,holding
FROM portfolios
WHERE account_nbr='acct_no'
;
xaction_info xactions_cur%ROWTYPE;
variable

　　有一些PL/SQL指令在使用隐式定义记录时没有使用%ROWTYPE属性，比如游标FOR循环或触发器中的:old和:new记录。

DELCARE

CURSOR xaction_cur IS
SELECT action,timeamp,holding
FROM portfolios
WHERE account_nbr='37'
;

BEGIN
FOR xaction_rec in xactions_cur
LOOP
IF xactions_rec.holding='ORCL'
THEN
notify_shareholder;
END IF;
END LOOP;

使用记录

　　用户可以给记录赋值、将值传递给其他程序。记录作为一种复合数据结构意味作他有两个层次可用。用户可以引用整个记录，使用select into或fetch转移所有域，也可以将整个记录传递给一个程序或将所有域的值赋给另一个记录。在更低的层次，用户可以处理记录内单独的域，用户可以给单独的域赋值或者在单独的域上运行布尔表达式，也可以将一个或更多的域传递给另一个程序。

　　引用记录

　　记录由域组成，访问记录中的域使用点（.）符号。我们使用上面的例子看看

DELCARE
TYPE stock_quote_rec IS RECORD
(symbol stock.symbol%TYPE
,bid NUMBER(10,4)
,ask NUMBER(10,4)
,volume NUMBER NOT NULL:=0
,exchange VARCHAR2(6) DEFAULT 'NASDAQ'
);

TYPE detailed_quote_rec IS RECORD
(quote stock_quote_rec
,timestamp date
,bid_size NUMBER
,ask.size NUMBER
,last_tick VARCHAR2(4)
);

real_time_detail detail_quote_rec;

BEGIN

real_time_detail.bid_size:=1000;
real_time_detail.quote.volume:=156700;
log_quote(real_time_detail.quote);

　　给记录赋值

　　给记录或记录中的域赋值的方法有几种，可以使用SELECT INTO或FETCH给整个记录或单独的域赋值， 可以将整个记录的值赋给其他记录，也可以通过给每个域赋值来得到记录，以下我们通过实例讲解每一种赋值方法。

　　1、使用SELECT INTO

　　使用SELECT INTO给记录赋值要将记录或域放在INTO子串中，INTO子串中的变量与SELECT中列的位置相对应。

　　例：

DECLARE

stock_info1 stocks%ROWTYPE;
stock_info2 stocks%ROWTYPE;

BEGIN

SELECT symbol,exchange
INTO stock_info1.symbol,stock_info1.exchange
FROM stocks
WHERE symbol='ORCL';

SELECT * INTO stock_info2 FROM stocks
WHERE symbol='ORCL';

　　2、使用FETCH

　　如果SQL语句返回多行数据或者希望使用带参数的游标，那么就要使用游标，这种情况下使用FETCH语句代替INSTEAD INTO是一个更简单、更有效率的方法，但在安全性较高的包中FETCH的语法如下：

FETCH cursor_name INTO variable;

　　我们改写上面的例子：

DECLARE
CURSOR stock_cur(symbol_in VARCHAR2) IS
SELECT symbol,exchange,begin_date
FROM stock
WHERE symbol=UPPER(symbol_in);

stock_info stock_cur%ROWTYPE

BEGIN
OPEN stock_cur('ORCL');
FETCH stock_cur INTO stock_info;

　　使用赋值语句将整个记录复制给另一个记录是一项非常有用的技术，不过记录必须精确地被声明为相同的类型，不能是基于两个不同的TYPE语句来获得相同的结构。

　　例：

DECLARE

TYPE stock_quote_rec IS RECORD
(symbol stocks.symbol%TYPE
,bid NUMBER(10,4)
,ask number(10,4)
,volume NUMBER
);

TYPE stock_quote_too IS RECORD
(symbol stocks.symbol%TYPE
,bid NUMBER(10,4)
,ask number(10,4)
,volume NUMBER
);
--这两个记录看上去是一样的，但实际上是不一样的
stock_one stocks_quote_rec;
stock_two stocks_quote_rec;
--这两个域有相同的数据类型和大小

stock_also stock_rec_too；--与stock_quote_rec是不同的数据类型
BEGIN
stock_one.symbol:='orcl';
stock_one.volume:=1234500;
stock_two：=stock_one;--正确
syock_also：=stock_one;--错误，数据类型错误

stock_also.symbol:=stock_one.symbol;
stock_also.volume:=stock_one.volume;

　　记录不能用于INSERT语句和将记录直接用于比较，下面两种情况是错误的：

　　　INSERT INTO stocks VALUES (stock_record);

　　和

　　　IF stock_rec1>stock_rec2 THEN

　　要特别注意考试中试题中有可能用%ROWTYPE来欺骗你，但这是错误的，记住这一点。还有可能会出现用记录排序的情况，ORACLE不支持记录之间的直接比较。对于记录比较，可以采用下面的两个选择：

　　. 设计一个函数，该函数返回scalar数据类型，使用这个函数比较记录，如

IF sort_rec(stock_one)>sort_rec(stock_two) THEN

　　. 可以使用数据库对象，数据库对象可以使用order或map方法定义，允许oracle对复合数据类型进行比较。关于数据库对象的讨论已经超越了本文的范围，要详细了解数据库对象，可以查阅oracle手册。

PL/SQL集合

　　集合与其他语言中的数组相似，在ORACLE7.3及以前的版本中只有一种集合称为PL/SQL表，这种类型的集合依然保留，就是索引（INDEX_BY）表，与记录相似，集合在定义的时候必须使用TYPE语句，然后才是创建和使用这种类型的变量。

　　集合的类型

　　PL/SQL有三种类型的集合

　　. Index_by表

　　. 嵌套表

　　. VARRAY

　　这三种类型的集合之间由许多差异，包括数据绑定、稀疏性(sparsity)、数据库中的存储能力都不相同。绑定涉及到集合中元素数量的限制，VARRAY集合中的元素的数量是有限，Index_by和嵌套表则是没有限制的。稀疏性描述了集合的下标是否有间隔，Index_by表总是稀疏的，如果元素被删除了嵌套表可以是稀疏的，但VARRAY类型的集合则是紧密的，它的下标之间没有间隔。

　　Index_by表不能存储在数据库中，但嵌套表和VARRAY可以被存储在数据库中。

　　虽然这三种类型的集合有很多不同之处，但他们也由很多相似的地方：

　　　. 都是一维的类似数组的结构

　　　. 都有内建的方法

　　　. 访问由点分隔

　　Index_by表

　　Index_by表集合的定义语法如下：

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

　　这里面重要的关键字是INDEX BY BINARY_INTERGET，没有这个关键字，那么集合将是一个嵌套表，element_type可以是任何合法的PL/SQL数据类型，包括：PLS/INTEGER、SIGNTYPE、和BOOLEAN。其他的集合类型对数据库的数据类型都有限制，但Index_by表不能存储在数据库中，所以没有这些限制。

　　一旦定义了index_by表，就可以向创建其他变量那样创建index_by表的变量：

DECLARE
TYPE symbol_tab_typ IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;
symbol_tab symbol_tab_typ;
BEGIN

　　嵌套表

　　嵌套表非常类似于Index_by表，创建的语法也非常相似。使用TYPE语句，只是没有INDEX BY BINARY_INTEGER子串。

　　TYPE type_name IS TABLE OF element_type [NOT NULL]

　　NOT NULL选项要求集合所有的元素都要有值，element_type可以是一个记录，但是这个记录只能使用标量数据类型字段以及只用于数据库的数据类型（不能是PLS_INTEGER,BOOLEAN或SIGNTYPE）。

　　嵌套表和VARRAY都能作为列存储在数据库表中，所以集合自身而不是单个的元素可以为NULL,ORACLE称这种整个集合为NULL的为"自动设置为NULL(atomically NULL)"以区别元素为NULL的情况。当集合为NULL时，即使不会产生异常，用户也不能引用集合中的元素。用户可以使用IS NULL操作符检测集合是否为NULL。

　　存储在一个数据库中的嵌套表并不与表中的其它数据存放在同一个数据块中，它们实际上被存放在第二个表中。正如没有order by子句select语句不能保证返回任何有顺序的数据，从数据库中取回的嵌套表也不保证元素的顺序。由于集合数据是离线存储的，对于大型集合嵌套表是一个不错的选择。

　　VARRAY

　　VARRAY或数据变量都有元素的限制。想起他集合一样VARRAY定义仍然使用TYPE语句，但关键字VARRAY或VARRYING ARRAY告诉ORACLE这是一个VARRAY集合。

TYPE type_name IS [VARRAY|VARYING ARRAY] (max_size) OF
element_type [NOT NULL]

　　max_size是一个整数，用于标示VARRAY集合拥有的最多元素数目。VARRAY集合的元素数量可以低于max_size,但不能超过max_size。element_type是一维元素的数据类型，如果element_type是记录，那么这个记录只能使用标量数据字段（与嵌套标相似）。NOT NULL子串表示集合中的每一个元素都必须有值。

　　与嵌套表相似，VARRAY能够自动为NULL,可以使用IS NULL操作符进行检测。与嵌套表不同的是，当VARRAY存储在数据库中时与表中的其他数据存放在同一个数据块中。正象列的排序保存在表的SELECT*中一样元素的顺序保存在VARRAY中。同样由于集合是在线存储的，VARRAY很适合于小型集合。

使用集合

　　象记录一样，集合可以在两个层面上使用：

　　　. 操作整个集合

　　　. 访问集合中的单个元素

　　第一种情况使用集合名，第二种情况使用下标：

　　　collection(subscript)

　　index_by表的下标是两为的整数，可以为正也可以为负，范围是：-2147483647--2147483647。嵌套表和VARRAY表示元素在集合中的位置，用户很难灵活设计下标，这是因为：

　　　. 嵌套表开始是紧密的（相对于疏松）

　　　. VARRAY始终保持紧密

　　　. 这两种集合的下标都由1开始

　　初始化、删除、引用集合

　　使用集合之前必须要初始化，对于Index_by表初始化是自动进行的，但是对于嵌套表和VARRAY就必须使用内建的构造函数。如果重新调用，嵌套表和VARRAY自动置NULL，这不只是元素置NULL，而是整个集合置NULL。给集合内的元素赋值需要使用下标符号。将一个集合的值赋给另一个集合，只需要简单的使用赋值操作符。

　　Index_by集合初始化是最简单的，只要涉及其中的一个元素集合就被初始化了。

　　例：　

DECLARE

TYPE symbol_tab_typ IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;
TYPE account_tab_typ IS TABLE OF account%ROWTYPE INDEX BY BINARY_INTEGER;
symbol_tab symbol_tab_typ;
account_tab account_tab_typ;
new_acct_tab account_tab_typ;

BEGIN
--初始化集合元素147和-3
SELECT * INTO account_tab(147)
FROM accounts WHERE account_nbr=147;

SELECT * INTO account_tab(-3)
FROM accounts WHERE account_nbr=3003;

IF account_tab(147).balance<500 THEN
chang_maintenance_fee(147);
END IF

new_acct_tab:=account_tab;
symbol_tab(1):="ORCL";
symbol_tab(2):="CSCO";
symbol_tab(3):="SUNM";

publish_portfolio(symbol_tab);

　　嵌套表和VARRAY由构造函数初始化，构造函数和集合的名字相同，同时有一组参数，每个参数对应一个元素，如果参数为NULL,那么对应的元素就被初始化为NULL,如果创建了元素，但没有填充数据，那么元素将保持null值，可以被引用，但不能保持数据。如果元素没有初始化，那么就不能引用该元素。

　　例：

DECLARE

TYPE stock_list IS TABLE OF stock.symbol%TYPE;
TYPE top10_list IS VARRAY (10) OF stocks.symbol%TYPE;
biotech_stocks stock_list;
tech_10 top10_list;

BEGIN
--非法，集合未初始化。
biotech_stocks(1):='AMGN';
IF biotech_stocks IS NULL THEN
--初始化集合
biotech_stocks：=('AMGN','BGEN',IMCL','GERN',CRA');
END IF;
tech_10:=top10_list('ORCL',CSCO','MSFT','INTC','SUNW','IBM',NULL,NULL);
IF tech_10(7) IS NULL THEN
tech_10(7):='CPQ';
END
tech_10(8):='DELL';

　　在这个例子中，嵌套表BIOTECH_STOCKS初始化有5个元素，VARRAY tech_10集合最多能有10 个元素，但构造函数只创建了8个元素，其中还有两个元素是NULL值，并程序中给他们赋值。

　　初始化基于记录的集合，就必须将记录传递给构造函数，注意不能只是简单的将记录的域传递给构造函数。

　　例：

DECLARE

TYPE stock_quote_rec IS RECORD
(symbol stock.symbol%TYPE
,bid NUMBER(10,4)
,ask NUMBER(10,4)
,volume NUMBER NOT NULL:=0
);
TYPE stock_tab_typ IS TABLE OF stock_quote_rec;
quote_list stock_tab_typ;
single_quote stock_quote_rec;

BEGIN
single_quote.symbol:='OPCL';
single_quote.bid:=100;
single_quote.ask:=101;
single_quote.volume:=25000;
--合法
quote_list:=stock_tab_typ(single_quote);
--不合法
quote_list:=stock_tab_typ('CSCO',75,76,3210000);
DBMS_OUTPUT.LINE(quote_list(1).bid);

集合的方法

　　除了构造函数外,集合还有很多内建函数，这些函数称为方法。调用方法的语法如下：

　　　collection.method

　　下表中列出oracle中集合的方法

方法	描述	使用限制
COUNT	返回集合中元素的个数
DELETE	删除集合中所有元素
DELETE()	删除元素下标为x的元素，如果x为null,则集合保持不变	对VARRAY非法
DELETE(,)	删除元素下标从X到Y的元素，如果X>Y集合保持不变	对VARRAY非法
EXIST()	如果集合元素x已经初始化，则返回TRUE, 否则返回FALSE
EXTEND	在集合末尾添加一个元素	对Index_by非法
EXTEND()	在集合末尾添加x个元素	对Index_by非法
EXTEND(,)	在集合末尾添加元素n的x个副本	对Index_by非法
FIRST	返回集合中的第一个元素的下标号，对于VARRAY集合始终返回1。
LAST	返回集合中最后一个元素的下标号, 对于VARRAY返回值始终等于COUNT.
LIMIT	返回VARRY集合的最大的元素个数，对于嵌套表和对于嵌套表和Index_by为null	Index_by集合无用
NEXT()	返回在元素x之后及紧挨着它的元素的值，如果该元素是最后一个元素，则返回null.
PRIOR()	返回集合中在元素x之前紧挨着它的元素的值，如果该元素是第一个元素，则返回null。
TRI M	从集合末端开始删除一个元素	对于index_by不合法
TRIM()	从集合末端开始删除x个元素	对index_by不合法

　　关于集合之间的比较

　　集合不能直接用于比较，要比较两个集合，可以设计一个函数，该函数返回一个标量数据类型。

IF stock_list1>stock_list2 ----非法
IF sort_collection(stock_list1)>sort_collection(stock_list2) THEN --合法

　　但可以比较在集合内的两个元素。