- 浏览: 147148 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
hk_581:
这个有java实现吗
SSDP协议 -
堕落星辰:
太谢谢了! 找了好久 就你这个例子能显示
Extjs TreeGrid加载数据报未组织好 -
lizhuang:
毫秒数算起来应该比这个好
Java 返回两个时间段的小时数和分钟数 -
love_nana:
这个方法是有问题的吧,2012-05-16 12:30 ~~~ ...
Java 返回两个时间段的小时数和分钟数 -
猫小小质:
找了好几天 就你这个例子能运行起来,爱死你了
Extjs TreeGrid加载数据报未组织好
六、集合的赋值
集合可以用INSERT、UPDATE、FETCH或SELECT语句来赋值,也可以用赋值语句或调用子程序来赋值。
我们可以使用下面的语法来为某个指定的集合元素进行赋值:
collection_name(subscript) := expression;
其中expression的值和被指定的元素类型必须一致。下面我们来看三个例子。
- 例一:数据的兼容性
例子中的集合与集合之间互相赋值,但必须是两个集合类型相同的才可以,光是元素的类型相同是不够的。
DECLARE
TYPE clientele IS VARRAY(100) OF customer;
TYPE vips IS VARRAY(100) OF customer;
-- These first two variables have the same datatype.
group1 clientele := clientele(...);
group2 clientele := clientele(...);
-- This third variable has a similar declaration,
-- but is not the same type.
group3 vips := vips(...);
BEGIN
-- Allowed because they have the same datatype
group2 := group1;
-- Not allowed because they have different datatypes
group3 := group2;
END ;
- 例二:为嵌套表赋空值
当我们把一个被自动初始化为空的嵌套表或变长数组赋给另外一个嵌套表或变长数组时,被赋值的集合就会被重新初始化,结果也为NULL。
DECLARE
TYPE clientele IS TABLE OF VARCHAR2 (64);
-- This nested table has some values.
group1 clientele := clientele('Customer 1' , 'Customer 2' );
-- This nested table is not initialized ("atomically null").
group2 clientele;
BEGIN
-- At first, the test IF group1 IS NULL yields FALSE .
-- Then we assign a null nested table to group1.
group1 := group2;
-- Now the test IF group1 IS NULL yields TRUE .
-- We must use another constructor to give it some values.
END ;
- 例三:集合赋值时可能引起的异常
在下面几种给集合元素赋值的情况下,可能会引起多种异常。
- 如果下标索引不存在或无法转换成正确的数据类型,PL/SQL就会抛出预定义异常VALUE_ERROR。通常,下标是一个整数。但关联数组的下标也可以是VARCHAR2类型。
- 如果所给下标索引指向了一个未初始化的元素时,PL/SQL就会抛出SUBSCRIPT_BEYOND_COUNT异常。
- 如果集合被自动初始化为空值并且程序引用了其中的一个元素,PL/SQL会抛出COLLECTION_IS_NULL异常。
DECLARE
TYPE wordlist IS TABLE OF VARCHAR2 (5);
words wordlist;
BEGIN
/* Assume execution continues despite the raised exceptions. */
-- Raises COLLECTION_IS_NULL. We haven't used a constructor yet.
-- This exception applies to varrays and nested tables, but not
-- associative arrays which don't need a constructor.
words(1) := 10;
-- After using a constructor, we can assign values to the elements.
words := wordlist(10,
20,
30
);
-- Any expression that returns a VARCHAR2(5) is OK.
words(1) := 'yes' ;
words(2) := words(1) || 'no' ;
-- Raises VALUE_ERROR because the assigned value is too long.
words(3) := 'longer than 5 characters' ;
-- Raises VALUE_ERROR because the subscript of a nested table must
-- be an integer.
words('B' ) := 'dunno' ;
-- Raises SUBSCRIPT_BEYOND_COUNT because we only made 3 elements
-- in the constructor. To add new ones, we must call the EXTEND
-- method first.
words(4) := 'maybe' ;
END ;
七、比较集合
我们可以检查一个集合是不是空,但不能判断两个集合是不是相同。像大于、小于这样的操作都是不允许使用的。
- 例一:检查集合是否为空
嵌套表和变长数组都能被自动初始化为空值,所以它们可以做判空操作:
DECLARE
TYPE staff IS TABLE OF employee;
members staff;
BEGIN
-- Condition yields TRUE because we haven’t used a constructor.
IF members IS NULL THEN ...
END ;
- 例二:比较两个集合
集合不能直接进行等或不等的比较。例如下面的IF条件表达式就是不允许的。
DECLARE
TYPE clientele IS TABLE OF VARCHAR2 (64);
group1 clientele := clientele('Customer 1' , 'Customer 2' );
group2 clientele := clientele('Customer 1' , 'Customer 3' );
BEGIN
-- Equality test causes compilation error.
IF group1 = group2 THEN
...
END IF ;
END ;
这个约束也适用于隐式的比较。所以,集合不能出现在DISTINCT、GROUP BY或ORDER BY中。
如果我们想对集合进行比较的话,就得自定义等于、小于、大于比较规则。同时还要编写一个或多个函数来检查集合和它们的元素并返回真假值。
八、在SQL语句中使用PL/SQL的集合类型
集合允许我们用PL/SQL来操作复杂的数据类型。我们的程序能计算下标索引值,并在内存中处理指定的元素,然后用SQL语句把结果保存到数据库中。
1、关于嵌套表的例子
- 例一:创建与PL/SQL嵌套表对应的SQL类型
在SQL*Plus中,我们可以创建与PL/SQL嵌套表和变长数组相对应的SQL类型:
SQL > CREATE TYPE CourseList AS TABLE OF VARCHAR2 (64);
我们可以把这些类型当作数据库字段来使用:
SQL > CREATE TABLE department (
2 name VARCHAR2 (20),
3 director VARCHAR2 (20),
4 office VARCHAR2 (20),
5 courses CourseList)
6 NESTED TABLE courses STORE AS courses_tab;
字段COURSES中的每一个值都是一个嵌套表类型,能够保存系(department)所提供的课程。
- 例二:向数据库中插入嵌套表
现在,我们可以数据表填充了。嵌套表的构造函数为字段COURSES提供了值:
BEGIN
INSERT INTO department
VALUES ('English' , 'Lynn Saunders' , 'Breakstone Hall 205' ,
courselist ('Expository Writing' ,
'Film and Literature' ,
'Modern Science Fiction' ,
'Discursive Writing' ,
'Modern English Grammar' ,
'Introduction to Shakespeare' ,
'Modern Drama' ,
'The Short Story' ,
'The American Novel'
));
END ;
- 例三:从数据库中检索嵌套表
我们可以把英语系所提供的所有课程放到PL/SQL嵌套表中:
DECLARE
english_courses CourseList;
BEGIN
SELECT courses
INTO english_courses
FROM department
WHERE name = 'English' ;
END ;
在PL/SQL中,我们可以循环遍历嵌套表的元素并使用TRIM或EXTEND方法来更新嵌套表中部分或全部的元素。然后,在把更新后的结果保存到数据库中去。
- 例四:用嵌套表更新数据库中
我们可以修改英语系所提供的课程列表:
DECLARE
new_courses courselist
:= courselist ('Expository Writing' ,
'Film and Literature' ,
'Discursive Writing' ,
'Modern English Grammar' ,
'Realism and Naturalism' ,
'Introduction to Shakespeare' ,
'Modern Drama' ,
'The Short Story' ,
'The American Novel' ,
'20th-Century Poetry' ,
'Advanced Workshop in Poetry'
);
BEGIN
UPDATE department
SET courses = new_courses
WHERE NAME = 'English' ;
END ;
2、变长数组的一些例子
假设我们在SQL*Plus中定义了对象类型Project:
SQL > CREATE TYPE Project AS OBJECT (
2 project_no NUMBER (2),
3 title VARCHAR2 (35),
4 cost NUMBER (7,2));
下一步,定义VARRAY类型的ProjectList,用来存放Project对象:
SQL > CREATE TYPE ProjectList AS VARRAY(50) OF Project;
最后,创建关系表department,其中的一个字段类型为ProjectList:
SQL > CREATE TABLE department (
2 dept_id NUMBER (2),
3 name VARCHAR2 (15),
4 budget NUMBER (11,2),
5 projects ProjectList);
在字段projects中的每一项都是一个用于存放给定系的项目计划的变长数组。
现在让我们准备插入一些测试数据。注意一下,在下面的例子中,变长数组的构造函数ProjectList()是如何为字段projects提供数据的:
BEGIN
INSERT INTO department
VALUES (30, 'Accounting' , 1205700,
projectlist (project (1, 'Design New Expense Report' , 3250),
project (2, 'Outsource Payroll' , 12350),
project (3, 'Evaluate Merger Proposal' , 2750),
project (4, 'Audit Accounts Payable' , 1425)
));
INSERT INTO department
VALUES (50, 'Maintenance' , 925300,
projectlist (project (1, 'Repair Leak in Roof' , 2850),
project (2, 'Install New Door Locks' , 1700),
project (3, 'Wash Front Windows' , 975),
project (4, 'Repair Faulty Wiring' , 1350),
project (5, 'Winterize Cooling System' , 1125)
));
INSERT INTO department
VALUES (60, 'Security' , 750400,
projectlist (project (1, 'Issue New Employee Badges' , 13500),
project (2, 'Find Missing IC Chips' , 2750),
project (3, 'Upgrade Alarm System' , 3350),
project (4, 'Inspect Emergency Exits' , 1900)
));
END ;
现在,让我们对Security系做个更新操作:
DECLARE
new_projects projectlist
:= projectlist (project (1, 'Issue New Employee Badges' , 13500),
project (2, 'Develop New Patrol Plan' , 1250),
project (3, 'Inspect Emergency Exits' , 1900),
project (4, 'Upgrade Alarm System' , 3350),
project (5, 'Analyze Local Crime Stats' , 825)
);
BEGIN
UPDATE department
SET projects = new_projects
WHERE dept_id = 60;
END ;
接下来,对Accounting系做一次查询操作,并把结果放到本地变量中去:
DECLARE
my_projects projectlist;
BEGIN
SELECT projects
INTO my_projects
FROM department
WHERE dept_id = 30;
END ;
最后,删除记录Accounting:
BEGIN
DELETE FROM department
WHERE dept_id = 30;
END ;
3、使用SQL语句操作特定的集合元素
默认情况下,SQL操作会一次性的保存或检索整个集合而不是一个独立的元素。要用SQL语句操作集合中的独立的元素,可以使用TABLE操作符。 TABLE操作符用一个子查询把变长数组或嵌套表的内容提取出来,这样的话,INSERT、UPDATE或DELETE语句就可以作用于嵌套表,而不是整张数据表了。
下面,让我们看看一些具体的操作实例。
- 例一:向嵌套表中插入元素
首先,我们向历史系的嵌套表COURSES插入一条记录:
BEGIN
-- The TABLE operator makes the statement apply to the nested
-- table from the 'History' row of the DEPARTMENT table.
INSERT INTO TABLE (SELECT courses
FROM department
WHERE NAME = 'History' )
VALUES ('Modern China' );
END ;
- 例二:更新嵌套表中的元素
然后对嵌套表的学分进行调整:
BEGIN
UPDATE TABLE (SELECT courses
FROM department
WHERE NAME = 'Psychology' )
SET credits = credits + adjustment
WHERE course_no IN (2200, 3540);
END ;
- 例三:从嵌套表中检索一个元素
下面,我们从历史系检索出一个特定课程名称:
DECLARE
my_title VARCHAR2 (64);
BEGIN
-- We know that there is one history course with 'Etruscan'
-- in the title. This query retrieves the complete title
-- from the nested table of courses for the History department.
SELECT title
INTO my_title
FROM TABLE (SELECT courses
FROM department
WHERE NAME = 'History' )
WHERE NAME LIKE '%Etruscan%' ;
END ;
- 例四:从嵌套表中删除元素
最后,我们从英语系中删除所有那些学分为5的课程:
BEGIN
DELETE TABLE (SELECT courses
FROM department
WHERE NAME = 'English' )
WHERE credits = 5;
END ;
- 例五:从变长数组中检索元素
下面例子演示了从变长数组类型的字段projects中检索出公务处第四个项目的名称和费用:
DECLARE
my_cost NUMBER (7, 2);
my_title VARCHAR2 (35);
BEGIN
SELECT COST, title
INTO my_cost, my_title
FROM TABLE (SELECT projects
FROM department
WHERE dept_id = 50)
WHERE project_no = 4;
...
END ;
- 例六:对变长数组应用INSERT、UPDATE和DELETE操作
目前,我们还不能在INSERT、UPDATE和DELETE语句中引用变长数组中的元素,必须先检索整个变长数组,使用PL/SQL来添加、删除或更新元素,然后把修改结果重新放回数据库中。
下面的存储过程ADD_PROCEDURE演示了如何按给定的位置向department中插入一个新的project。
CREATE PROCEDURE add_project (
dept_no IN NUMBER ,
new_project IN project,
POSITION IN NUMBER
) AS
my_projects projectlist;
BEGIN
SELECT projects
INTO my_projects
FROM department
WHERE dept_no = dept_id
FOR UPDATE OF projects;
my_projects.EXTEND; -- make room for new project
/* Move varray elements forward. */
FOR i IN REVERSE POSITION .. my_projects.LAST - 1 LOOP
my_projects (i + 1) := my_projects (i);
END LOOP ;
my_projects (POSITION) := new_project; -- add new project
UPDATE department
SET projects = my_projects
WHERE dept_no = dept_id;
END add_project;
下例代码为一个指定的工程更新数据:
CREATE PROCEDURE update_project (
dept_no IN NUMBER ,
proj_no IN NUMBER ,
new_title IN VARCHAR2 DEFAULT NULL ,
new_cost IN NUMBER DEFAULT NULL
) AS
my_projects projectlist;
BEGIN
SELECT projects
INTO my_projects
FROM department
WHERE dept_no = dept_id
FOR UPDATE OF projects;
/* Find project, update it, then exit loop immediately. */
FOR i IN my_projects.FIRST .. my_projects.LAST LOOP
IF my_projects (i).project_no = proj_no THEN
IF new_title IS NOT NULL THEN
my_projects (i).title := new_title;
END IF ;
IF new_cost IS NOT NULL THEN
my_projects (i).COST := new_cost;
END IF ;
EXIT ;
END IF ;
END LOOP ;
UPDATE department
SET projects = my_projects
WHERE dept_no = dept_id;
END update_project;
- 例七:对嵌套表应用INSERT、UPDATE和DELETE操作
为了能对一个PL/SQL嵌套表使用DML操作,我们需要使用TABLE和CAST操作符。这样,我们就可以直接使用SQL标志对嵌套表进行集合操作而不用把更改过的嵌套表保存在数据库中。
CAST的操作数可以是PL/SQL集合变量和SQL集合类型(使用CREATE TYPE语句创建)。CAST可以把PL/SQL集合转成SQL类型的。
下面的例子用来计算修改后的课程列表和原始课程列表的不同点的数量(注意,课程3720的学分从4变成3):
DECLARE
revised courselist
:= courselist (course (1002, 'Expository Writing' , 3),
course (2020, 'Film and Literature' , 4),
course (2810, 'Discursive Writing' , 4),
course (3010, 'Modern English Grammar ' , 3),
course (3550, 'Realism and Naturalism' , 4),
course (3720, 'Introduction to Shakespeare' , 3),
course (3760, 'Modern Drama' , 4),
course (3822, 'The Short Story' , 4),
course (3870, 'The American Novel' , 5),
course (4210, '20th-Century Poetry' , 4),
course (4725, 'Advanced Workshop in Poetry' , 5)
);
num_changed INTEGER ;
BEGIN
SELECT COUNT (*)
INTO num_changed
FROM TABLE (CAST (revised AS courselist)) NEW ,
TABLE (SELECT courses
FROM department
WHERE NAME = 'English' ) OLD
WHERE NEW .course_no = OLD.course_no
AND (NEW .title != OLD.title OR NEW .credits != OLD.credits);
DBMS_OUTPUT.put_line (num_changed);
END ;
九、使用多级集合
除了标量类型或对象类型集合之外,我们也可以创建集合的集合。例如,我们可以创建元素是变长数组类型的变长数组,元素是嵌套表类型的变长数组等。
在用SQL创建字段类型为嵌套表类型的嵌套表时,Oracle会检查CREATE TABLE语句的语法,看如何定义存储表。
这里有几个例子演示了多级集合的语法。
- 多级VARRAY
DECLARE
TYPE t1 IS VARRAY(10) OF INTEGER ;
TYPE nt1 IS VARRAY(10) OF t1; -- multilevel varray type
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);
-- add a new varray element to nva
nva.EXTEND;
nva(5) := t1(56, 32);
-- replace an inner varray element
nva(4) := t1(45, 43, 67, 43345);
-- 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);
TYPE ntb1 IS TABLE OF tb1; -- table of table elements
TYPE tv1 IS VARRAY(10) OF INTEGER ;
TYPE ntb2 IS TABLE OF tv1; -- table of varray elements
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 ;
/
- 多级关联数组
DECLARE
TYPE tb1 IS TABLE OF INTEGER
INDEX BY BINARY_INTEGER ;
-- the following is index-by table of index-by tables
TYPE ntb1 IS TABLE OF tb1
INDEX BY BINARY_INTEGER ;
TYPE va1 IS VARRAY(10) OF VARCHAR2 (20);
-- the following is index-by table of varray elements
TYPE ntb2 IS TABLE OF va1
INDEX BY BINARY_INTEGER ;
v1 va1 := va1('hello' , 'world' );
v2 ntb1;
v3 ntb2;
v4 tb1;
v5 tb1; -- empty table
BEGIN
v4(1) := 34;
v4(2) := 46456;
v4(456) := 343;
v2(23) := v4;
v3(34) := va1(33, 456, 656, 343);
-- assign an empty table to v2(35) and try again
v2(35) := v5;
v2(35)(2) := 78; -- it works now
END ;
/
- 多级集合和批量SQL
CREATE TYPE t1 IS VARRAY(10) OF INTEGER ;
/
CREATE TABLE tab1 (c1 t1);
INSERT INTO tab1
VALUES (t1(2, 3, 5));
INSERT INTO tab1
VALUES (t1(9345, 5634, 432453));
DECLARE
TYPE t2 IS TABLE OF t1;
v2 t2;
BEGIN
SELECT c1
BULK COLLECT INTO v2
FROM tab1;
DBMS_OUTPUT.put_line(v2.COUNT); -- prints 2
END ;
/
十、集合的方法
集合提供了以下几个方法,能帮助我们更加方便维护和使用它:
- EXISTS
- COUNT
- LIMIT
- FIRST和LAST
- PRIOR和NEXT
- EXTEND
- TRIM
- DELETE
一个集合方法就是一个内置于集合中并且能够操作集合的函数或过程,可以通过点标志来调用。使用方法如下:
collection_name.method_name[(parameters)]
集合的方法不能在SQL语句中使用。并且,EXTEND和TRIM方法不能用于关联数组。EXISTS,COUNT,LIMIT,FIRST, LAST,PRIOR和NEXT是函数;EXTEND,TRIM和DELETE是过程。EXISTS,PRIOR,NEXT,TRIM,EXTEND和 DELETE对应的参数是集合的下标索引,通常是整数,但对于关联数组来说也可能是字符串。
只有EXISTS能用于空集合,如果在空集合上调用其它方法,PL/SQL就会抛出异常COLLECTION_IS_NULL。
1、检测集合中的元素是否存在(EXISTS)
函数EXISTS(n)在第n个元素存在的情况下会返回TRUE,否则返回FALSE。我们主要使用EXISTS和DELETE来维护嵌套表。其中EXISTS还可以防止引用不存在的元素,避免发生异常。下面的例子中,PL/SQL只在元素i存在的情况下执行赋值语句:
IF courses.EXISTS (i) THEN
courses(i) := new_course;
END IF ;
当下标越界时,EXISTS会返回FALSE,而不是抛出SUBSCRIPT_OUTSIDE_LIMIT异常。
2、计算集合中的元素个数(COUNT)
COUNT能够返回集合所包含的元素个数。例如,当下面的变长数组projects中含有25个元素时,IF条件就为TRUE:
IF projects.COUNT = 25 THEN ...
COUNT函数很有用,因为集合的当前大小不总是能够被确定。例如,如果我们把一个字段中的值放入嵌套表中,那么嵌套表中会有多少个元素呢?COUNT会给我们答案。
我们可以在任何可以使用整数表达式的地方使用COUNT函数。下例中,我们用COUNT来指定循环的上界值:
FOR i IN 1 .. courses.COUNT LOOP ...
对于变长数组来说,COUNT值与LAST值恒等,但对于嵌套表来说,正常情况下COUNT值会和LAST值相等。但是,当我们从嵌套表中间删除一个元素,COUNT值就会比LAST值小。
计算元素个数时,COUNT会忽略已经被删除的元素。
3、检测集合的最大容量(LIMIT)
因为嵌套表和关联数组都没有上界限制,所以LIMIT总会返回NULL。但对于变长数组来说,LIMIT会返回它所能容纳元素的个数最大值,该值是在变长数组声明时指定的,并可用TRIM和EXTEND方法调整。例如下面的变长数组projects在最大容量是25的时候,IF的条件表达式值为真:
IF projects.LIMIT = 25 THEN ...
我们可以在任何允许使用整数表达式的地方使用LIMIT函数。下面的例子中,我们使用LIMIT来决定是否可以为变长数组再添加15个元素:
IF (projects.COUNT + 15) < projects.LIMIT THEN ...
4、查找集合中的首末元素(FIRST和LAST)
FIRST和LAST会返回集合中第一个和最后一个元素在集合中的下标索引值。而对于使用VARCHAR2类型作为键的关联数组来说,会分别返回最低和最高的键值;键值的高低顺序是基于字符串中字符的二进制值,但是,如果初始化参数NLS_COMP被设置成ANSI的话,键值的高低顺序就受初始化参数NLS_SORT所影响了。
空集合的FIRST和LAST方法总是返回NULL。只有一个元素的集合,FIRST和LAST会返回相同的索引值。
IF courses.FIRST = courses.LAST THEN ... -- only one element
下面的例子演示了使用FIRST和LAST函数指定循环范围的下界和上界值:
FOR i IN courses.FIRST .. courses.LAST LOOP ...
实际上,我们可以在任何允许使用整数表达式的地方使用FIRST或LAST函数。下例中,我们用FIRST函数来初始化一个循环计数器:
i := courses.FIRST;
WHILE i IS NOT NULL LOOP ...
对于变长数组来说,FIRST恒等于1,LAST恒等于COUNT;但对嵌套表来说,FIRST正常情况返回1,如果我们把第一个元素删除,那么FIRST的值就要大于1,同样,如果我们从嵌套表的中间删除一个元素,LAST就会比COUNT大。
在遍历元素时,FIRST和LAST都会忽略被删除的元素。
5、循环遍历集合中的元素(PRIOR和NEXT)
PRIOR(n)会返回集合中索引为n的元素的前驱索引值;NEXT(n)会返回集合中索引为n的元素的后继索引值。如果n没有前驱或后继,PRIOR(n)或NEXT(n)就会返回NULL。
对于使用VARCHAR2作为键的关联数组来说,它们会分别返回最低和最高的键值;键值的高低顺序是基于字符串中字符的二进制值,但是,如果初始化参数NLS_COMP被设置成ANSI的话,键值的高低顺序就受初始化参数NLS_SORT所影响了。
这种遍历方法比通过固定的下标索引更加可靠,因为在循环过程中,有些元素可能被插入或删除。特别是关联数组,因为它的下标索引可能是不连续的,有可能是(1,2,4,8,16)或('A','E','I','O','U')这样的形式。
PRIOR和NEXT不会从集合的一端到达集合的另一端。例如,下面的语句把NULL赋给n,因为集合中的第一个元素没有前驱:
n := courses.PRIOR (courses.FIRST); -- assigns NULL to n
PRIOR是NEXT的逆操作。比如说,存在一个元素i,下面的语句就是用元素i给自身赋值:
projects(i) := projects.PRIOR (projects.NEXT(i));
我们可以使用PRIOR或NEXT来遍历集合。在下面的例子中,我们使用NEXT来遍历一个包含被删除元素的嵌套表:
i := courses.FIRST; -- get subscript of first element
WHILE i IS NOT NULL LOOP
-- do something with courses(i)
i := courses.NEXT(i); -- get subscript of next element
END LOOP ;
在遍历元素时,PRIOR和NEXT都会忽略被删除的元素。
6、扩大集合的容量(EXTEND)
为了扩大嵌套表或变长数组的容量,可以使用EXTEND方法。但该方法不能用于索引表。该方法有三种形式:
- EXTEND 在集合末端添加一个空元素
- EXTEND(n) 在集合末端添加n个空元素
- EXTEND(n,i) 把第i个元素拷贝n份,并添加到集合的末端
例如,下面的语句在嵌套表courses的末端添加了元素1的5个副本:
courses.EXTEND(5,1);
不能使用EXTEND初始化一个空集合。同样,当我们对TABLE或VARRAY添加了NOT NULL约束之后,就不能再使用EXTEND的前两种形式了。
EXTEND操作的是集合内部大小,其中也包括被删除的元素。所以,在计算元素个数的时候,EXTEND也会把被删除的元素考虑在内。PL/SQL会为每一个被删除的元素保留一个占位符,以便在适当的时候让我们重新使用。如下例:
DECLARE
TYPE courselist IS TABLE OF VARCHAR2 (10);
courses courselist;
BEGIN
courses := courselist('Biol 4412' , 'Psyc 3112' , 'Anth 3001' );
courses.DELETE (3); -- delete element 3
/* PL/SQL keeps a placeholder for element 3. So, the
next statement appends element 4, not element 3. */
courses.EXTEND; -- append one null element
/* Now element 4 exists, so the next statement does
not raise SUBSCRIPT_BEYOND_COUNT. */
courses(4) := 'Engl 2005' ;
END ;
当包含被删除元素时,嵌套表的内部大小就不同于COUNT和LAST返回的值了。举一个例子,假如我们初始化一个长度为5的嵌套表,然后删除第二个和第五个元素,这时的内部长度是5,COUNT返回值是3,LAST返回值是4。EXTEND方法会把所有的被删除的元素都一样对待,无论它是第一个,最后一个还是中间的。
7、缩减集合的空间(TRIM)
TRIM有两种形式:
- TRIM 从集合末端删除一个元素
- TRIM(n) 从集合末端删除n个元素
例如,下面的表达式从嵌套表courses中删除最后三个元素:
courses.TRIM(3);
如果n值过大的话,TRIM(n)就会抛出SUBSCRIPT_BEYOND_COUNT异常。
同EXTEND相似,TRIM也不会忽略被删除的元素。看一下下面的例子:
DECLARE
TYPE courselist IS TABLE OF VARCHAR2 (10);
courses courselist;
BEGIN
courses := courselist('Biol 4412' , 'Psyc 3112' , 'Anth 3001' );
courses.DELETE (courses.LAST); -- delete element 3
/* At this point, COUNT equals 2, the number of valid
elements remaining. So, you might expect the next
statement to empty the nested table by trimming
elements 1 and 2. Instead, it trims valid element 2
and deleted element 3 because TRIM includes deleted
elements in its tally. */
courses.TRIM(courses.COUNT);
DBMS_OUTPUT.put_line(courses(1)); -- prints 'Biol 4412'
END ;
一般的,不要同时使用TRIM和DELETE方法。最好是把嵌套表当作定长数组,只对它使用DELETE方法,或是把它当作栈,只对它使用TRIM和EXTEND方法。PL/SQL对TRIM掉的元素不再保留占位符。这样我们就不能简单地为被TRIM掉的元素赋值了。
8、删除集合中的元素(DELETE)
DELETE方法有三种形式:
- DELETE 删除集合中所有元素
- DELETE(n) 从以数字作主键的关联数组或者嵌套表中删除第n个元素。如果关联数组有一个字符串键,对应该键值的元素就会被删除。如果n为空,DELETE(n)不会做任何事情。
- DELETE(m,n) 从关联数组或嵌套表中,把索引范围m到n的所有元素删除。如果m值大于n或是m和n中有一个为空,那么DELETE(m,n)就不做任何事。
例如:
BEGIN
courses.DELETE (2); -- deletes element 2
courses.DELETE (7, 7); -- deletes element 7
courses.DELETE (6, 3); -- does nothing
courses.DELETE (3, 6); -- deletes elements 3 through 6
projects.DELETE ; -- deletes all elements
nicknames.DELETE ('Chip' ); -- deletes element denoted by this key
nicknames.DELETE ('Buffy' , 'Fluffy' );
-- deletes elements with keys
-- in this alphabetic range
END ;
变长数组是密集的,我们不能从中删除任何一个元素。如果被删除的元素不存在,DELETE只是简单地忽略它,并不抛出异常。PL/SQL会为被删除的元素保留一个占位符,以便我们可以重新为被删除的元素赋值。
DELETE方法能让我们维护有间隙的嵌套表。下面的例子中,我们把嵌套表prospects的内容放到临时表中,然后从中删除一部分元素后,再重新把它存入数据库中:
DECLARE
my_prospects prospectlist;
revenue NUMBER ;
BEGIN
SELECT prospects
INTO my_prospects
FROM customers
WHERE ...
FOR i IN my_prospects.FIRST .. my_prospects.LAST LOOP
estimate_revenue(my_prospects(i), revenue); -- call procedure
IF revenue < 25000 THEN
my_prospects.DELETE (i);
END IF ;
END LOOP ;
UPDATE customers
SET prospects = my_prospects
WHERE ...
END ;
分配给嵌套表的内存是动态的,删除元素时内存会被释放。
9、使用集合类型参数的方法
在子程序中,我们可以对集合类型的参数直接调用它的内置方法,如下例:
CREATE PACKAGE personnel AS
TYPE staff IS TABLE OF employee;
...
PROCEDURE award_bonuses(members IN staff);
END personnel;
CREATE PACKAGE BODY personnel AS
PROCEDURE award_bonuses(members IN staff) IS
...
BEGIN
...
IF members.COUNT > 10 THEN -- apply method
...
END IF ;
END ;
END personnel;
注意:对于变长数组参数来说,LIMIT的值与参数类型定义相关,与参数的模式无关。
十一、避免集合异常
大多情况下,如果我们引用了一个集合中不存在的元素,PL/SQL就会抛出一个预定义异常。例如:
DECLARE
TYPE numlist IS TABLE OF NUMBER ;
nums numlist; -- atomically null
BEGIN
/* Assume execution continues despite the raised exceptions. */
nums(1) := 1; -- raises COLLECTION_IS_NULL (1)
nums := numlist(1, 2); -- initialize table
nums(NULL ) := 3; -- raises VALUE_ERROR (2)
nums(0) := 3; -- raises SUBSCRIPT_OUTSIDE_LIMIT (3)
nums(3) := 3; -- raises SUBSCRIPT_BEYOND_COUNT (4)
nums.DELETE (1); -- delete element 1
IF nums(1) = 1 THEN
... -- raises NO_DATA_FOUND (5)
END ;
第一句,嵌套表是空的;第二句,下标为空;三四句,下标超出合法范围之外;第五句,下标指向了一个被删除的元素。下表是一些异常情况的说明:
COLLECTION_IS_NULL | 调用一个空集合的方法 |
NO_DATA_FOUND | 下标索引指向一个被删除的元素,或是关联数组中不存在的元素 |
SUBSCRIPT_BEYOND_COUNT | 下标索引值超过集合中的元素个数 |
SUBSCRIPT_OUTSIDE_LIMIT | 下标索引超过允许范围之外 |
VALUE_ERROR | 下标索引值为空,或是不能转换成正确的键类型。当键被定义在 PLS_INTEGER的范围内,而下标索引值超过这个范围就可能抛 出这个异常 |
在某些情况下,如果我们为一个方法传递了一个无效的下标,并不会抛出异常。例如在使用DELETE方法的时候,我们向它传递NULL,它只是什么都没做而已。同样,用新值替换被删除的元素也不会引起NO_DATA_FOUND异常,如下例:
DECLARE
TYPE numlist IS TABLE OF NUMBER ;
nums numlist := numlist(10, 20, 30); -- initialize table
BEGIN
nums.DELETE (-1); -- does not raise SUBSCRIPT_OUTSIDE_LIMIT
nums.DELETE (3); -- delete 3rd element
DBMS_OUTPUT.put_line(nums.COUNT); -- prints 2
nums(3) := 30; -- allowed; does not raise NO_DATA_FOUND
DBMS_OUTPUT.put_line(nums.COUNT); -- prints 3
END ;
打包集合类型和本地集合类型总是不兼容的。假设我们想调用下面的打包过程:
CREATE PACKAGE pkg1 AS
TYPE NumList IS VARRAY(25) OF NUMBER (4);
PROCEDURE delete_emps (emp_list NumList);
END pkg1;
CREATE PACKAGE BODY pkg1 AS
PROCEDURE delete_emps (emp_list NumList) IS ...
...
END pkg1;
在运行下面PL/SQL块时,第二个过程调用会因参数的数量或类型错误(wrong number or types of arguments error)而执行失败。这是因为打包VARRAY和本地VARRAY类型不兼容,虽然它们的定义形式都是一样的:
DECLARE
TYPE numlist IS VARRAY(25) OF NUMBER (4);
emps pkg1.numlist := pkg1.numlist(7369, 7499);
emps2 numlist := numlist(7521, 7566);
BEGIN
pkg1.delete_emps(emps);
pkg1.delete_emps(emps2); -- causes a compilation error
END ;
发表评论
-
PL\SQL用户指南与参考12结束篇 转载
2011-04-22 17:02 944第十二章 PL/SQL应用程序性能调优 一、P ... -
PL\SQL用户指南与参考11 转载
2011-04-22 17:01 994第十一章 本地动态SQL 一、什么是动态SQL 大多数PL ... -
PL\SQL用户指南与参考10.2 转载
2011-04-22 17:00 9375、对象类型实例:实数 有理数能够表现成两个整数相除的形式, ... -
PL\SQL用户指南与参考10.1 转载
2011-04-22 16:59 689第十章 PL/SQL对象类型 一、抽象的角色 抽象是对一 ... -
PL\SQL用户指南与参考9.2 转载
2011-04-22 16:58 926九、系统包一览 Oracle和各种Oracle工具都提供了系 ... -
PL\SQL用户指南与参考9.1 转载
2011-04-22 16:57 903第九章 PL/SQL包 一、什么是PL/SQL包 ... -
PL\SQL用户指南与参考8 转载
2011-04-22 16:56 911第八章 PL/SQL子程序 一、什么是子程序 ... -
PL\SQL用户指南与参考7.2 转载
2011-04-22 16:55 866八、处理PL/SQL异常 异常抛出时,PL/SQL块或子程序 ... -
PL\SQL用户指南与参考7.1 转载
2011-04-22 16:54 854第七章 控制PL/SQL错误 ... -
PL\SQL用户指南与参考6.3 转载
2011-04-22 16:53 1390八、PL/SQL中的事务处理 ... -
PL\SQL用户指南与参考6.2 转载
2011-04-22 16:52 1575其它的OPEN-FOR语句可以使用不同的查询打开同样的游标变量 ... -
PL\SQL用户指南与参考6.1 转载
2011-04-22 16:49 1068第六章 PL/SQL与Oracle间交互 一、PL/SQ ... -
PL\SQL用户指南与参考5.2.2 转载
2011-04-22 09:53 999十五、什么是记录 记录就是相关的数据项集中存储在一个单元中, ... -
PL\SQL用户指南与参考5.2.1 转载
2011-04-22 09:52 794六、集合的赋值 集合可以用INSERT、UPDATE、FET ... -
PL\SQL用户指南与参考5.1.1 转载
2011-04-22 09:50 988第五章 PL/SQL集合与记录 ... -
PL\SQL用户指南与参考4 转载
2011-04-21 11:26 891第四章 PL/SQL的控制结构 一、PL/SQL控制结构一览 ... -
PL\SQL用户指南与参考3 转载
2011-04-20 17:41 1431第三章 PL/SQL数据类型 ... -
PL\SQL用户指南与参考2 转载
2011-04-19 09:25 1106第二章 PL/SQL基础 一、字符集 ... -
PL\SQL用户指南与参考 转载
2011-04-18 17:58 993第一章 PL/SQL一览 一、理解PL/SQL的主要特性 ...
相关推荐
**5.1.2 PL/SQL编程与UML活动图** 虽然本章不会深入讨论PL/SQL编程细节,但会通过示例代码展示如何在触发器中实现基本任务。结合第11章和第12章的内容,可以构建更复杂的逻辑。UML的活动图是表示进程工作流的有效...
8.3.1 存储的与匿名的PL/SQL程序块 282 8.3.2 PL/SQL对象 282 8.4 监视和解决锁定冲突 286 8.4.1 共享锁与排他锁 287 8.4.2 排队机制 287 8.4.3 锁定争用 288 8.4.4 死锁 290 8.5 撤销概述 291 8.6 事务与...
§11.2 SQL与PL/SQL 231 §11.2.1 什么是PL/SQL? 231 §11.2.1 PL/SQL的好处 232 §11.2.1.1 有利于客户/服务器环境应用的运行 232 §11.2.1.2 适合于客户环境 232 §11.2.1.3 客户及服务器端的好处 232 §11.2.2 PL...
##### 3.3 Knowledge Expert™ for PL/SQL (PL/SQL 知识专家) - **功能描述**:提供关于 PL/SQL 代码的最佳实践建议,帮助开发者改进代码质量。 - **应用场景**:提升 PL/SQL 代码的可读性和可维护性。 ##### 3.4 ...
- **简介**:PL/SQL 调试器是一种强大的工具,用于调试复杂的 PL/SQL 代码。 - **特点**: - 支持断点、单步执行等标准调试功能。 - 提供变量监视窗口,实时查看变量值的变化。 - 支持条件断点设置。 ##### 3.2 ...
- **3.1 PL/SQL Debugger(PL/SQL调试器)**:提供强大的PL/SQL代码调试功能,包括断点设置、单步执行、变量监控等,有助于快速定位和解决问题。 - **3.2 Code Profiling(代码剖析)**:通过对代码执行路径和性能...
8.3.1 存储的与匿名的PL/SQL程序块 282 8.3.2 PL/SQL对象 282 8.4 监视和解决锁定冲突 286 8.4.1 共享锁与排他锁 287 8.4.2 排队机制 287 8.4.3 锁定争用 288 8.4.4 死锁 290 8.5 撤销概述 291 8.6 事务与...
《Oracle11g宝典》是Oracle数据库管理员、安全管理员、网络管理员、应用开发人员的参考指南,还是Oracle技术支持和培训机构、Oracle学习班、高等院校计算机专业数据库课程的参考教材和上机指导教材。每类人员都...
- **目的**:确保数据库中所有的PL/SQL和Java对象在升级后仍然有效。 - **步骤**: - 通过执行`utlrp.sql`脚本来自动重编译失效的对象。 ```sql @utlrp.sql ``` ##### 5.1.3 重新创建数据字典CATALOG.SQL - **...