PL SQL 用户指南和参考第六章PLSQL与Oracle间交互.docx
- 文档编号:293832
- 上传时间:2023-04-28
- 格式:DOCX
- 页数:66
- 大小:124.18KB
PL SQL 用户指南和参考第六章PLSQL与Oracle间交互.docx
《PL SQL 用户指南和参考第六章PLSQL与Oracle间交互.docx》由会员分享,可在线阅读,更多相关《PL SQL 用户指南和参考第六章PLSQL与Oracle间交互.docx(66页珍藏版)》请在冰点文库上搜索。
PLSQL用户指南和参考第六章PLSQL与Oracle间交互
第六章PL/SQL与Oracle间交互
一、PL/SQL支持的SQL一览
PL/SQL扩展了SQL,变得更加强大和易用。
我们可以用PL/SQL灵活安全地操作Oracle数据,因为它支持所有的SQL数据操作语句(除了EXPLAINPLAN),事务控制语句,函数,伪列和操作符。
PL/SQL还支持动态SQL,能让我们动态地执行SQL数据定义,数据控制和会话控制语句。
除此之外,PL/SQL还遵循ANSI/ISO的SQL标准。
1、数据操作
我们可以使用INSERT、UPDATE、DELETE、SELECT和LOCKTABLE来操作Oracle数据。
INSERT用于向数据表中插入新行;UPDATE能修改行;DELETE可以删除不需要的行;SELECT可以按照我们给定的查询标准检索出满足条件的行;LOCKTABLE可以临时限制对一个数据表的访问。
2、事务控制
Oracle是面向事务的,它使用事务来保证数据的完整性。
数据库事务是指作为单个逻辑工作单元执行的一系列SQL操作。
同时,Oracle还可以通过事务将我们的操作持久化或取消。
如果我们的程序在事务中有一步执行失败,Oracle就会发现错误并回滚整个事务。
这样,数据库就会自动恢复到先前的状态。
我们可以使用COMMIT、ROLLBACK、SAVEPOINT和SETTRANSACTION命令来控制事务。
COMMIT会提交事务,将数据库的变化永久性地提交;ROLLBACK会终止当前事务,并放弃自事务开启后所有发生变化的内容;SAVEPOINT能够标记当前事务的处理进度;一并使用ROLLBACK和SAVEPOINT就可以回滚部分事务。
SETTRANSACTION可以设置事务的属性,如读写访问和隔离级别。
3、SQL函数
PL/SQL能让我们使用所有的SQL聚合函数:
AVG、COUNT、GROUPING、MAX、MIN、STDDEV、SUM和VARIANCE。
除了COUNT(*)之外,所有的聚合函数都会忽略空值。
我们可以在SQL语句中使用聚合函数,但不能在过程化语句中使用。
聚合函数会作用于整个字段,除非我们使用SELECTGROUPBY语句对返回的结果行进行分组排序。
如果我们忽略GROUPBY子句,聚合函数就会把所有的结果行当作一个整体来处理。
我们可以使用下面的语法来调用聚合函数:
function_name([ALL | DISTINCT] expression)
expression会引用一个或多个数据库字段。
如果我们指定ALL(默认的),聚合函数就会考虑所有的行,也包括重复的行内容。
如果我们指定的是DISTINCT,聚合函数只会考虑有区别的值。
例如,下面的语句只返回数据表emp中不同的职别的个数:
SELECT COUNT(DISTINCT job) INTO job_count FROM emp;
函数COUNT可以让我们使用星号(*)操作符返回一个数据表中行的个数。
例如,下面的语句返回表emp中行的个数:
SELECT COUNT(*) INTO emp_count FROM emp;
如果使用默认的ALL,聚合函数会考虑所有的列值,包括重复项;如果改换DISTINCT的话,聚合函数就只会考虑重复项中的一个值。
4、SQL伪列
PL/SQL可以辨识CURRVAL、LEVEL、NEXTVAL、ROWID和ROWNUM这样能返回特殊的数据项的SQL伪列。
伪列并不是真实存在于数据表中的字段,但它们的行为看起来跟真实字段一样。
例如,我们在查询时就可以选取伪列。
但是,我们不允许在SQL语句中向伪列中插入值。
伪列只能在SQL语句中使用,不能用在过程化语句中。
∙CURRVALandNEXTVAL
序列(sequence)是一个能产生顺序编号的模式对象。
创建序列时,我们可以指定它的初始值和增量值。
CURRVAL能返回指定序列的当前值。
在使用序列前,我们必须先调用NEXTVAL,NEXTVAL的作用是生成并返回序列的下一个顺序值。
想要得到序列中的当前值或是下一个值,我们必须使用点标志,方法如下:
sequence_name.CURRVAL
sequence_name.NEXTVAL
创建序列后,我们就可以在事务处理中用它产生唯一的顺序编号了。
但是,我们只能在用SELECT列表、VALUES子句和SET子句中使用CURRVAL和NEXTVAL。
在下面的例子中,我们使用序列向两个数据表中插入相同的雇员编号:
INSERT INTO emp VALUES (empno_seq.NEXTVAL, my_ename, ...);
INSERT INTO sals VALUES (empno_seq.CURRVAL, my_sal, ...);
在事务中调用NEXTVAL时,序列的值会立即发生改变,不管我们是否使用提交或回滚语句。
∙LEVEL
我们可以用LEVEL配合SELECTCONNECTBY语句把数据表中的行组织成一个树形结构。
LEVEL能返回树形结构中节点的级数。
根节点的级数是1,根节点的子节点级数是2,孙子节点级数是3,依次类推。
在STARTWITH子句中,我们指定辨识根节点的条件。
我们可以使用PRIOR操作符指定树的遍历方向(从根向下或从枝干向上)。
∙ROWID
ROWID能够返回数据表中行的ROWID(二进制地址)。
我们可以使用UROWID类型变量来存放具有可读性的ROWID,下例中,我们声明了一个名为row_id的变量:
DECLARE
row_id UROWID;
当我们把物理rowid放到UROWID变量中时,就要使用函数ROWIDTOCHAR,它能把二进制值转成一个长度为18字节的字符串。
然后我们就可以在UPDATE或DELETE语句的WHERE子句中,把UROWID变量的值同ROWID伪列值进行比较并找出从游标中取得的最新行。
∙ROWNUM
ROWNUM能够返回从数据表中选出的行的标识。
被选取的第一行的ROWNUM是1,第二行是2,依次类推。
如果SELECT语句包含一个ORDERBY子句,ROWNUM会在排序操作之前被指定。
我们可以在UPDATE语句中用ROWNUM为每一行数据赋上一个唯一的值。
同样,也可以在SELECT语句的WHERE子句中用ROWNUM限定选取的行的个数,如下例:
DECLARE
CURSOR c1 IS
SELECT empno, sal
FROM emp
WHERE sal > 2000 AND ROWNUM <= 10; -- returns 10 rows
ROWNUM的值只是在行被检索出来的时候才会增长,所以,只有在WHERE子句中的ROWNUM才有意义:
... WHERE ROWNUM < constant;
... WHERE ROWNUM <= constant;
5、SQL操作符
PL/SQL能让我们在SQL语句中使用所有的SQL比较操作符、集合操作符和行操作符。
这里简要描述一下其中几个操作符的功能。
∙比较操作符
我们常常需要在一个数据操作语句的WHERE子句中使用比较操作符来判断一个表达式的值是TRUE、FALSE还是NULL。
下面的表格描述了各个操作符的用途:
操作符
描述
ALL
把同一个列表中的一个值与其他值或是子查询返回的值进行比较,如果所有的比较结果都为TRUE,那么结果值就为TRUE。
ANY,SOME
把同一个列表中的一个值与其它值或是子查询返回的值进行比较,只要其中有一个结果值为TRUE,那么结果值就为TRUE。
BETWEEN
测试一个值是否在指定的区间范围内。
EXISTS
如果子查询至少返回一行数据,那么EXISTS的结果就为TRUE。
IN
测试指定的值是否是集合成员。
IS
测试指定的值是否是NULL。
LIKE
测试一个字符串是否满足一个指定的样式,其中包含通配符。
∙集合操作符
集合操作符的作用是把两个查询的结果合并成一个结果。
INTERSECT返回两个结果的交集;MINUS把目标结果集与另外一个结果集比较,并把存在于另一个结果集中的记录从目标结果集中去除,然后返回目标结果集;UNION把两个结果集的内容合并,在有重复项的情况下,只保留其中一项;UNIONALL同UNION的作用类似,但它会保留所有记录,不管是否有相同的记录。
∙行操作符
行操作符能返回或引用特定的行。
ALL会保留查询或聚合表达式中的重复行;DISTINCT的作用与ALL相反,用于消除重复行;PRIOR能引用树形查询中返回的当前行的父级行。
二、管理游标
PL/SQL有两种类型的游标:
隐式游标和显式游标。
PL/SQL为所有的SQL数据操作语句都声明了一个隐式的游标,其中也包括只返回一条记录的查询。
但是,对于返回多于一条记录的查询来说,我们必须声明显式的游标,然后使用游标FOR循环或使用BULKCOLLECT子句。
1、显式游标一览
查询返回的行集合可能是由零行、一行或多行组成,这个结果取决于我们的检索条件。
当一个查询返回多行记录时,我们可以显式地声明一个游标来处理每一行数据。
游标的声明可以放到PL/SQL块、子程序或包的声明部分。
我们可以使用三种命令来控制游标:
OPEN、FETCH和CLOSE。
首先用OPEN语句初始化一个游标,然后重复执行FETCH语句取出已检索到的数据,或是使用BULKCOLLECT批量选取数据。
当处理完结果集中最后一行数据时,就可以用CLOSE语句关闭游标。
我们还可以同时打开多个游标并发处理多个查询操作。
∙声明游标
在PL/SQL中,向前引用是不允许的。
所以我们必须在其它语句引用游标之前声明它。
声明游标时,我们需要为它命名,并用下面的语法把它和一个查询相关联:
CURSOR cursor_name [(parameter[, parameter]...)]
[RETURN return_type] IS select_statement;
return_type必须是记录或是数据表的行类型,parameter的含义如下:
cursor_parameter_name [IN] datatype [{:
= | DEFAULT} expression]
例如,我们可以像下面这样声明游标c1和c2:
DECLARE
CURSOR c1 IS
SELECT empno, ename, job, sal
FROM emp
WHERE sal > 2000;
CURSOR c2 RETURN dept%ROWTYPE IS
SELECT *
FROM dept
WHERE deptno = 10;
游标名是一个未声明的标识符,而不是PL/SQL变量名。
我们不能把值赋给游标名或是在一个表达式中使用它。
但是,游标和变量有着同样的作用域规则。
虽然在数据表为游标命名是允许的,但并不推荐这样做。
游标是可以接收参数的,这些参数可以在关联查询中允许出现常量的地方使用。
游标的形式参数都必须是IN模式的。
因此,它们不能把值赋给实际参数。
并且,我们不能给游标的参数添加NOTNULL约束。
如下例所示,我们为游标参数初始化一个默认值。
这样,我们就能给游标传递不同个数的实参。
并且,我们还可以在不改变游标引用的情况下添加新的参数。
DECLARE
CURSOR c1 (low INTEGER DEFAULT 0, high INTEGER DEFAULT 99) IS
SELECT *
FROM ...
游标参数作用域对于游标来说是本地的,这也就意味着它们只能在游标声明时所指定的查询语句中使用。
游标参数的值只在游标被打开的时候被使用。
∙打开游标
游标只能在打开之后才能执行查询操作。
对于使用了FORUPDATE子句的游标来说,OPEN语句会把满足查询条件的行琐住,如下例所示:
DECLARE
CURSOR c1 IS
SELECT ename, job
FROM emp
WHERE sal < 3000;
...
BEGIN
OPEN c1;
...
END;
OPEN语句执行时结果集中的行并不被选取,只有在FETCH语句执行的时候数据才被取得。
∙传递游标参数
我们可以用OPEN语句来为游标传递参数。
如果在没有使用参数默认值的情况下,我们就必须在OPEN语句中为游标声明中的每一个形式参数指定一个对应的实际参数。
比如下面的游标声明:
DECLARE
emp_name emp.ename%TYPE;
salary emp.sal%TYPE;
CURSOR c1 (NAME VARCHAR2, salary NUMBER) IS
SELECT *
FROM ...
下面三个语句都能打开游标:
OPEN c1(emp_name, 3000);
OPEN c1('ATTLEY', 1500);
OPEN c1(emp_name, salary);
在上面的例子中,标识符salary用在游标声明的时候,它引用的是形式参数。
但是,当用在OPEN语句中时,它引用的就是PL/SQL变量,为了避免混乱,最好使用唯一的标识。
有默认值的形参不需要有对应的实参,在OPEN语句执行时程序会自动使用它们的默认值。
我们可以用位置标识法或名称标识法把OPEN语句中的实参和游标声明中的形参关联起来。
每个实参的数据类型和它对应的形参数据类型必须兼容。
∙从游标中取值
除非在FETCH时使用BULKCOLLECT子句,否则FETCH语句每次只会从结果集中取出一条记录,并把游标向下移动,指向当前记录的下一条记录。
下面演示了FETCH语句的用法:
FETCH c1 INTO my_empno, my_ename, my_deptno;
对于游标查询返回的每一个字段值,在INTO列表中都必须有一个与之对应且类型兼容的变量。
通常,我们可以像下面这样使用FETCH语句:
LOOP
FETCH c1 INTO my_record;
EXIT WHEN c1%NOTFOUND;
-- process data record
END LOOP;
查询可以在它的作用域范围内引用PL/SQL变量,但是,在查询中的任何变量只有在游标被打开时才计算它的值。
在下面的例子中,每个被检索出来的salary只和2相乘,即使factor值在后续的操作中已经发生了改变:
DECLARE
my_sal emp.sal%TYPE;
my_job emp.job%TYPE;
factor INTEGER :
= 2;
CURSOR c1 IS
SELECT factor * sal
FROM emp
WHERE job = my_job;
BEGIN
...
OPEN c1; -- here factor equals 2
LOOP
FETCH c1
INTO my_sal;
EXIT WHEN c1%NOTFOUND;
factor :
= factor + 1; -- does not affect FETCH
END LOOP;
END;
要想改变查询结果集或查询中的变量值,我们就必须关闭并重新打开含有输入变量的游标,这样才能使用新的输入变量值。
但是,我们可以为同一游标每次取得的数据指定一个不同的INTO列表。
这样每次取得的数据就会被放到目标变量中去,如下例所示:
DECLARE
CURSOR c1 IS
SELECT ename
FROM emp;
name1 emp.ename%TYPE;
name2 emp.ename%TYPE;
name3 emp.ename%TYPE;
BEGIN
OPEN c1;
FETCH c1
INTO name1; -- this fetches first row
FETCH c1
INTO name2; -- this fetches second row
FETCH c1
INTO name3; -- this fetches third row
...
CLOSE c1;
END;
如果游标的指向超过了结果集中的最后一行,那么目标变量的值就无法确定。
注意:
在结果集的最后一次执行的FETCH语句一定取不到数据,而且没有异常发生。
想要发现这种情况,我们就必须得使用游标的%FOUND或%NOTFOUND属性。
∙批量取得游标中的数据
BULKCOLLECT子句能让我们批量的绑定数据。
这样我们就能一次性从结果集中取得所有的行。
在下面的例子中,我们从游标中批量取得数据分别放到两个集合中去:
DECLARE
TYPE numtab IS TABLE OF emp.empno%TYPE;
TYPE nametab IS TABLE OF emp.ename%TYPE;
nums numtab;
names nametab;
CURSOR c1 IS
SELECT empno, ename
FROM emp
WHERE job = 'CLERK';
BEGIN
OPEN c1;
FETCH c1
BULK COLLECT INTO nums, names;
...
CLOSE c1;
END;
∙关闭游标
CLOSE语句可以关闭游标,游标被关闭后,我们还可以重新打开它。
操作一个已经关闭了的游标会抛出预定义异常INVALID_CURSOR。
∙在游标中使用子查询
子查询就是出现在另外一个SQL数据操作语句中的查询(通常用圆括号封闭)。
计算时,子查询能为语句提供一个值或是一个值集合。
通常,子查询用在WHERE子句中。
例如,下面的查询就用于返回不在芝加哥居住的雇员:
DECLARE
CURSOR c1 IS
SELECT empno, ename
FROM emp
WHERE deptno IN (SELECT deptno
FROM dept
WHERE loc <> 'CHICAGO');
再举一个在FROM子句中使用子查询的例子,下面语句能够返回含有五个或五个以上雇员的部门编号和部门名称:
DECLARE
CURSOR c1 IS
SELECT t1.deptno, dname, "STAFF"
FROM dept t1, (SELECT deptno, COUNT (*) "STAFF"
FROM emp
GROUP BY deptno) t2
WHERE t1.deptno = t2.deptno AND "STAFF" >= 5;
针对一个数据表的子查询只执行一次,而具有关联关系的子查询会为每一行结果执行一次。
如下面的查询,它的作用是找出工资超过所在部门的平均工资的雇员姓名和工资。
对于emp表中的每一行,关联子查询都会为它计算一次部门的平均工资。
DECLARE
CURSOR c1 IS
SELECT deptno, ename, sal
FROM emp t
WHERE sal > (SELECT AVG (sal)
FROM emp
WHERE t.deptno = deptno)
ORDER BY deptno;
2、隐式游标一览
Oracle会隐式地打开一个游标处理所有那些不与显式游标相关联的SQL语句。
我们可以引用这个隐式的SQL游标,虽然不能使用OPEN、FETCH和CLOSE语句来控制SQL游标,但可以利用它的属性来获取与最近执行的SQL语句的相关信息。
三、用包将游标的声明和游标体分离
我们可以将游标说明从游标体中分离出来并放到包中。
这样做的话就可以在不改变游标说明的条件下修改游标体。
我们可以在包说明中用下面语法编写游标说明:
CURSOR cursor_name [(parameter[, parameter]...)] RETURN return_type;
在下面的例子中,我们可以使用%ROWTYPE属性来代表数据表emp中的行类型:
CREATE PACKAGE emp_stuff AS
CURSOR c1 RETURN emp%ROWTYPE; -- declare cursor spec
...
END emp_stuff;
CREATE PACKAGE BODY emp_stuff AS
CURSOR c1 RETURN emp%ROWTYPE IS
SELECT *
FROM emp
WHERE sal > 2500; -- define cursor body
...
END emp_stuff;
游标说明部分并不含有SELECT语句,因为后面RETURN子句中指明了返回值的数据类型。
并且,游标体中含有的SELECT语句列表中的每一项,必须和说明部分的RETURN子句相匹配。
打包游标有着更好的灵活性。
例如,我们可以任意修改上例中声明的游标的游标体而不用修改游标说明:
CREATE PACKAGE BODY emp_stuff AS
CURSOR c1 RETURN emp%ROWTYPE IS
SELECT *
FROM emp
WHERE deptno = 20; -- new WHERE clause
...
END emp_stuff;
我们可以使用点标志从一个PL/SQL块或子程序中引用一个打包游标,如下例所示:
DECLARE
emp_rec emp%ROWTYPE;
...
BEGIN
...
OPEN emp_stuff.c1;
LOOP
FETCH emp_stuff.c1
INTO emp_rec;
EXIT WHEN emp_suff.c1%NOTFOUND;
...
END LOOP;
CLOSE emp_stuff.c1;
END;
打包游标的作用域并不局限于某个特定的PL/SQL块。
所以,打开一个打包游标后,它会一直保持打开状态直到我
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- PL SQL 用户指南和参考第六章PLSQL与Oracle间交互 用户指南 参考 第六 PLSQL Oracle 交互