[Ref: http://www.yesky.com/20020819/1625866.shtml ]
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; |
引用记录
记录由域组成,访问记录中的域使用点(.)符号。我们使用上面的例子看看
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(subscrīpt)
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 --合法 |
但可以比较在集合内的两个元素。
使用正确的循环控制 [Ref: http://www.itgoogle.com/web/Show_Art.asp?ID=2008 ]
当你在 PL/SQL 中处理一个被索引的表时,很多情况下你不能确定表上是否存在索引,所以你不能使用最明显的 FOR 循环方式来循环表中的值。例如:
Declare
type my list_type is table of number index by pls_integer;
mylist mylist_type;
begin
-- start at 2, instead of 1
mylist(2) := 2;
mylist(3) := 3;
-- skip 4 and 5
mylist(6) := 6;
for i in 1..mylist.count loop
dbms_output.put_line(mylist(i));
end loop;
end;
/
前面的代码将返回 ORA-01403 错误:由于缺少索引1,没有发现数据。
如果你尝试使用 .FIRST/.LAST 修正这个问题,那么你将自动地从第一个索引开始循环,直到到达最后一个索引,如下:
Declare
type mylist_type is table of number index by pls_integer;
mylist mylist_type;
begin
mylist(2) := 2;
mylist(3) := 3;
mylist(5) := 6;
for i in mylist.first .. mylist.last loop
dbms_output.put_lin(mylist(i));
end loop;
end;
/
然而
然而,你依然得到一个 ORA-01403 错误:由于跳过值,没有发现数据。要进一步避免这种错误,你可以在访问索引前使用 EXISTS 方法测试它是否存在:
Declare
type mylist_type is table of number index by pls_integer;
mylist mylist_type;
begin
mylist(2) := 2;
mylist(3) := 3;
mylist(6) := 6;
for i in mylist.first..mylist.last loop
if (mylist.exists(i)) then
dbms_output.put_line(mylist(i));
end if;
end loop;
end;
/
或者,你可以通过.NEXT 属性使用常规的 LOOP 循环来迭代一个表上存在的索引——但是你必须声明循环计数器:
Declare
type mylist_type is table of number index by pls_integer;
mylist mylist_type;
i pls_integer;
begin
mylist(2) := 2;
mylist(3) := 3;
mylist(6) := 6;
i := mylist.first;
loop
dbms_output.put_line(mylist(i));
exit when i = mylist.last;
i := mylist.next(i);
end loop;
end;
/
当你不确定哪些值已经被删除或装载而导致缺少索引时,可以使用上面这两个有效的方法作为默认方法。但是对于使用 FORALL 语句的大量***作,这两种方法都不能正确运行,原因是 FORALL 语句并不是一个真正的循环,而且其语法只能带一个下加界,一个上边界和一个 SQL DML 语句。
然而,Oracle 10g中引入了两个新子句,这两个子句允许你避免这种局限性。向 FORALL 语法添加一个 INDICES OF 子句允许你自动地循环一个表中的所有值,而无须担心索引是否存在:
Declare
type mylist_type is table of number index by pls_integer;
mylist mylist_type;
begin
mylist(2) := 2;
mylist(3) := 3;
mylist(6) := 6;
foralli in indices of mylist
insert into mynumtable values(mylist(i));
end;
/
另外还有一个 valueS OF 子句,该子句使用你的嵌套表的值,或者被表索引的值作为循环的下标:
Declare
type mysubs_type is table of pls_integer index by pls_integer;
type mylist_type is table of number index by pls_integer;
mysubs mylist_type;
mylist mylist_type;
begin
mylist(2) := 2;
mysubs(10) := 2; -- point to mylist(2)
mylist(3) := 3;
mysubs(20) := 3; -- point to mylist(3)
mylist(6) := 6;
mysubs(30) := 6; -- point to mylist(6)
mysubs(40) := 3; -- point to mylist(3), again!
foralli in values ofmysubs
insert into mynumtable values(mylist(i));
end;
/
注意这个语法允许你控制对一个特定记录的顺序访问,甚至重复访问或者略过对它的访问。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9099175/viewspace-907143/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9099175/viewspace-907143/