PL/SQL<七>复合数据结构

 

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

记录：

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

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

显示定义：显示定义记录是在PL/SQL程序块中创建记录变量之前的声明部分定义：

TYPE record_type IS RECORD(field_definition_list);

Field_definition_list是由逗号分隔的列表，语法如下：

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

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

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

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

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
 
DECLARE
CURSOR xactionS_cur IS
SELECT action, timestamp, holding
FROM portfolios
WHERE account_nbr = '37';
 
BEGIN
FOR xaction_rec in xactions_cur
LOOP
IF xaction_rec.holding = 'orcl'
THEN
notify_shareholder;
END IF
END LOOP;
END

使用记录：

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

引用记录：记录由域组成，访问记录中的域使用点(.)符号，我们使用上面的例子看看

DECLARE
TYPE stock_quote_rec IS RECORD(
symbol stock.symbol%TYPE,
bid NUMBER(10, 4),
ask NUMBER(10, 4),
volumn NUMBER NOT NULL := 0,
exchange VARCHAR2(6) DEFAULT 'NASDQA'
);
 
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.bin_size:=1000;
real_time_detail.quote.volumn:=156700;
log_quote(real_time_detail.quote);

给记录赋值：

使用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';
END

使用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.symbols%TYPE,
bid NUMBER(10, 4),
ask NUMBER(10, 4),
volumn NUMBER
);
TYPE stock_quote_too IS RECORD(
symbol stocks.symbols%TYPE,
bid NUMBER(10, 4),
ask NUMBER(10, 4),
volumn NUMBER
);
stock_one stocks_quote_rec;
stock_two stocks_quote_rec;
 
stock_also stock_rec_too;
 
BEGIN
stock_one.symbol:='orcl';
stock_one.volumn:=12334400;
stock_two:=stock_one; // correct
stock_also:=stock_one; // incorrect, the data type is wrong
stock_also.symbol:=stock_one.symbol;
stock_also.volumn:=stock_one.volumn;
END

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

INSERT INTO stocks VALUES(stock_record);

IF stock_rec1 > stock_rec2 THEN

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

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

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

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

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

·         都有内建的方法

·         访问由点分隔

集合的创建：

Index_by

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

Eg.

 

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]

VARRAY:

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

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

o    操作整个集合

o    访问集合中的 单个元素

第一种情况使用集合名，第二种情况使用下标collection(subscript)

初始化、删除、引用集合：

使用集合之前必须要初始化，对于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 accounts%ROWTYPE INDEX BY BINARY_INTEGER;
symbol_tab symbol_tab_typ;
account_tab account_tab_typ;
new_acct_tab account_tab_typ;
BEGIN
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
change_maintenance_fee(147);
END IF
new_acct_tab:=account_tab;
symbol_tab(1):='ORCL';
symbol_tab(2):='CSCO';
symbol_tab(3):='SUN';
 
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 stock.symbol%TYPE;
biotech_stocks stock_list;
tech_10 top10_list;
 
BEGIN
biotech_stocks(1):='AMGN'; // this is illegal, becuase biotech_stocks do not been initialize
IF biotech_stocks IS NULL THEN
biotech_stocks:=('AMGN', 'BGEN', 'IMCL', 'GERN', 'CAR');
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 IF
tech_10(8):='DELL';
END

在这个例子中，嵌套表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),
volumn 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.volumn:=230000;
quote_list:=stock_tab_typ(single_quote); // legal
quote_list:=stock_tab_typ('CSCO', 43, 323, 323000); // illegal
DBMS_OUTPUT.LINE(quote_list(1).bid);
END

集合的方法：除了构造函数外,集合还有很多内建函数，这些函数称为方法。调用方法的语法如下：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。
TRIM	从集合末端开始删除一个元素	对于index_by不合法
TRIM()	从集合末端开始删除x个元素	对index_by不合法

关于集合之间的比较：

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

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