PLSQL基础培训

1、Restricted Confidential.(Procedural Language/Structured Query Language)Restricted Confidential.目 录 PL/SQL PL/SQL 程序设计简介程序设计简介 PL/SQLPL/SQL块结构和组成元素块结构和组成元素 PL/SQLPL/SQL流程控制语句流程控制语句 游标的使用游标的使用 异常错误处理异常错误处理 存储过程和函数存储过程和函数 包的创建和应用包的创建和应用 触发器触发器Restricted Confidential.1.PL/SQL 1.PL/SQL 程序设计简介程序设计简介 Restr

2、icted Confidential. PL/SQL是 Procedure Language & Structured Query Language 的缩写。ORACLE的SQL是支持ANSI(American national Standards Institute)和ISO92 (International Standards Organization)标准的产品。PL/SQL是对SQL语言存储过程语言的扩展。从ORACLE6以后，ORACLE的RDBMS附带了PL/SQL。它现在已经成为一种过程处理语言。目前的PL/SQL包括两部分，一部分是数据库引擎部分；另一部分是可嵌入到许多

3、产品（如C语言，JAVA语言等）工具中的独立引擎。可以将这两部分称为：数据库PL/SQL和工具PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。工具PL/SQL另外还增加了用于支持工具（如ORACLE Forms）的句法，如：在窗体上设置按钮等。本章主要介绍数据库PL/SQL内容。 什么是什么是PL/SQLPL/SQL Restricted Confidential.PL/SQLPL/SQL与网络传输与网络传输OracleOracle数据库服务器数据库服务器SQLSQLSQLSQLSQLSQL客户应用客户应用OracleOracle数据库服务器数据库服务器客户应用客户应用SQL

4、SQLSQLSQLSQLSQL使用使用SQLSQL使用使用PL/SQLPL/SQL对于客户对于客户/服务器环境来说，真正的瓶颈是网络上。无论网络多快，只要客户端与服服务器环境来说，真正的瓶颈是网络上。无论网络多快，只要客户端与服务器进行大量的数据交换务器进行大量的数据交换,应用运行的效率自然就会受到影响。应用运行的效率自然就会受到影响。在执行期间，将所有在执行期间，将所有的的SQLSQL语句传递给语句传递给SQLSQL语句执行器组件执行。相对于逐条发送一组语句执行器组件执行。相对于逐条发送一组SQLSQL语句，语句，PL/SQLPL/SQL的的打包传输，减少网络流量。打包传输，减少网络流量。R

5、estricted Confidential.PL/SQL PL/SQL 可用的可用的SQLSQL语句语句: : PL/SQL PL/SQL是是ORACLEORACLE系统的核心语言，系统的核心语言，现在现在ORACLEORACLE的许多部件都是由的许多部件都是由PL/SQLPL/SQL写成。写成。在在PL/SQLPL/SQL中可以使用的中可以使用的SQLSQL语句有：语句有：INSERTINSERT，UPDATEUPDATE，DELETEDELETE，SELECT INTOSELECT INTO，COMMITCOMMIT，ROLLBACKROLLBACK，SAVEPOINTSAVEPOINT

6、。提示：在提示：在 PL/SQLPL/SQL中只能用中只能用 SQLSQL语句中的语句中的 DML DML 部分，部分，不能用不能用 DDL DDL 部分，如果要在部分，如果要在PL/SQLPL/SQL中使用中使用DDLDDL( (如如CREATE table CREATE table 等等) )的话，只能以动态的方式来使用。的话，只能以动态的方式来使用。Restricted Confidential.2.PL/SQL块结构和组成元素块结构和组成元素 Restricted Confidential.PL/SQLPL/SQL块块PL/SQLPL/SQL的的程序结构程序结构PL/SQLPL/SQL

7、程序都是以块（程序都是以块（blockblock）为基本单位。）为基本单位。PL/SQL块可以分为三类：块可以分为三类：1.无名块：动态构造，只能执行一次。无名块：动态构造，只能执行一次。2.子程序：存储在数据库中的存储过程、函数及包子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中等。当在数据库上建立好后可以在其它程序中调用它们。调用它们。3.触发器：当数据库发生操作时，会触发一些事件，触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。从而自动执行相应的程序。 整个整个PL/SQL块分三部分：声明部分（用块分三部分：声明部分（用declare

8、开头）、执开头）、执行部分（以行部分（以begin开头开头,以以end结尾）和异常处理部分（以结尾）和异常处理部分（以exception开头）。其中执行部分是必须的，其他两个部分可选。开头）。其中执行部分是必须的，其他两个部分可选。 Restricted Confidential.DECLARE n number(10,2);BEGIN DECLARE v varchar2(8); BEGIN END; END;建议少使用建议少使用“嵌套嵌套”DECLARE 变量、常量、游标、自定义异常变量、常量、游标、自定义异常 BEGIN SQL语句语句 PL/SQL控制语句控制语句 EXCEPTION

9、错误发生时执行的动作错误发生时执行的动作 END；Restricted Confidential.变量声明变量声明在大多数在大多数PL/SQLPL/SQL块中，都需要使用变量来包含在程序逻块中，都需要使用变量来包含在程序逻辑中使用的值。无论需要什么类型的变量，能够在辑中使用的值。无论需要什么类型的变量，能够在BEGINBEGIN部分中使用它之前，都必须在块的部分中使用它之前，都必须在块的DECLAREDECLARE段中声段中声明这些变量。明这些变量。 声明变量必须提供变量名称和数据类型。声明变量必须提供变量名称和数据类型。 变量名称遵循以下约定：变量名称遵循以下约定： （1 1）以一个字母字符

10、开头）以一个字母字符开头 （2 2）最多包含）最多包含3030个字符个字符 （3 3）可以包含大写和小写字母、数字以及）可以包含大写和小写字母、数字以及_ _、$ $、# #及其它特殊字符。及其它特殊字符。Restricted Confidential.变量声明变量声明变量命名在变量命名在PL/SQL中有特别的讲究，建议在系统的设计阶段就要求所有中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求，使得整个系统的文档在规范上达到要求。编程人员共同遵守一定的要求，使得整个系统的文档在规范上达到要求。下面是建议的命名方法：下面是建议的命名方法：标识符命名规则例子程序变量V_na

11、me V_name 程序常量C_Name C_company_name 游标变量 Name_cursor Emp_cursor 异常标识 E_name E_too_many 表类型 Name_table_type Emp_table_type 表 Name_table Emp_table 记录类型 Name_record Emp_record SQL*Plus 替代变量 P_name P_sal 绑定变量 G_name G_year_sal Restricted Confidential.基本数据类型变量基本数据类型变量类型标识符说明number数字型int 整数型pls_integer整数型

12、，产生溢出时出现错误binary_integer整数型，表示带符号的整数char定长字符型，最大255个字符varchar2变长字符型，最大2000个字符long变长字符型，最大2GBdate日期型boolean布尔型(true、false、null三者取一)变量类型变量类型Restricted Confidential./ /* *声明部分，以声明部分，以declaredeclare开头开头* */ /declare declare v_id integer;v_id integer;v_name varchar(20);v_name varchar(20);cursor c_emp is

13、select cursor c_emp is select * * from employee where emp_id=3; from employee where emp_id=3; / /* *执行部分，以执行部分，以beginbegin开头开头* */ /begin begin open c_emp;open c_emp; -打开游标打开游标looploopfetch c_emp into v_id,v_name;fetch c_emp into v_id,v_name;-从游标取数据从游标取数据exit when c_emp%notfound ;exit when c_emp%not

14、found ;end loop ;end loop ;close c_emp;close c_emp; -关闭游标关闭游标dbms_output.PUT_LINE(v_name);dbms_output.PUT_LINE(v_name);/ /* *异常处理部分，以异常处理部分，以exceptionexception开始开始* */ /exceptionexceptionwhen no_data_found then when no_data_found then dbms_output.PUT_LINE(dbms_output.PUT_LINE(没有数据没有数据););end ; end ;

15、 Restricted Confidential.PL/SQLPL/SQL结构结构在块的声明部分对变量进行声明。声明一个变量的语法是：在块的声明部分对变量进行声明。声明一个变量的语法是：Variable_name CONSTANT type NOT NULL:=value; 如果在变量声明中使用了如果在变量声明中使用了CONSTANTCONSTANT，那么该变量必须要被初始化，并且，那么该变量必须要被初始化，并且它的取值不能被改变为与其初始值不同的数值。常量变量被作为只读的方式它的取值不能被改变为与其初始值不同的数值。常量变量被作为只读的方式进行处理。在声明中可以用关键字进行处理。在声明中可以

16、用关键字DEFAULTDEFAULT替换替换:=:=。DECLARE n1 number(10,2); v1 varchar2(20) DEFAULT 铜陵; v2 varchar2(20):=铜陵; v3 CONSTANT varchar2(20):=铜陵; v4 CONSTANT varchar2(20) DEFAULT 铜陵;BEGIN END;Restricted Confidential. 注意：在声明部分，每一行只能有一个变量声明，如以下的注意：在声明部分，每一行只能有一个变量声明，如以下的声明是错误的：声明是错误的： DeclareDeclare v_firstname , v_

17、lastname varchar2(20); v_firstname , v_lastname varchar2(20); 正确的声明为：正确的声明为： DeclareDeclare v_firstname varchar2(20); v_firstname varchar2(20); v_last_name varchar2(20); v_last_name varchar2(20); Restricted Confidential.另外还可以在变量中加入另外还可以在变量中加入NOT NULLNOT NULL 如果在声明时指明如果在声明时指明not nullnot null，那么应该给该变量

18、赋初值，那么应该给该变量赋初值，下面声明是错误的：下面声明是错误的： DeclareDeclare v_tempvar number not null; v_tempvar number not null; 正确的声明为：正确的声明为： DeclareDeclare v_tempvar number not null:=1; v_tempvar number not null:=1;注意：注意：CONSTANT关键字是在变量类型之前列出的，而关键字是在变量类型之前列出的，而NOT NULL是在是在数据类型之后列出的。数据类型之后列出的。Restricted Confidential.另外还可以

19、在另外还可以在PL/SQLPL/SQL块中包括块中包括SQLSQL： declare declare lv_qty_num number(3); lv_qty_num number(3); begin begin select count( select count(* *) into lv_qty_num ) into lv_qty_num from emp; from emp; dbms_output.put_line(lv_qty_num ); dbms_output.put_line(lv_qty_num ); End; End;Restricted Confidential.PL/S

20、QLPL/SQL结构结构变量类型变量类型NUMBER(P,S)P是长度，是长度，S是精度。是精度。 长度是数值中所有数字位的个数，长度是数值中所有数字位的个数， 而精度而精度是小数点右边的数字位的个数（如果精度是个负数，那么就由小数点是小数点右边的数字位的个数（如果精度是个负数，那么就由小数点开始向左边进行计算数字位的个数）。开始向左边进行计算数字位的个数）。定义赋值存储值NUMBER;1234.56781234.5678NUMBER(3);123123NUMBER(3);1234错NUMBER(4,3);123.4567错NUMBER(4,3);1.2345671.235NUMBER(7,2

21、);12345.6712345.67NUMBER(4,-3);12341000NUMBER(4,-1);1234(1235)1230(1240)Restricted Confidential.PL/SQLPL/SQL结构结构变量类型变量类型使用记录类型使用记录类型记录类型是把逻辑相关的数据作为一个单记录类型是把逻辑相关的数据作为一个单元存储起来。元存储起来。DECLARE TYPE test_rec IS RECORD( Code VARCHAR2(10), Name VARCHAR2(30) NOT NULL :=a book); V_book test_rec;BEGIN V_book.c

22、ode :=123; V_ :=C+ Programming; DBMS_OUTPUT.PUT_LINE(v_book.code|v_);END;初始化记录初始化记录定义记录型变量定义记录型变量Restricted Confidential.PL/SQLPL/SQL结构结构变量类型变量类型使用使用%TYPE%TYPE定义一个变量，其数据类型与已经定义的某个数据变量的类型相定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。 TYPE t

23、_Record IS RECORD( T_no emp.empno%TYPE, T_name emp.ename%TYPE, T_sal emp.sal%TYPE ); - 声明接收数据的变量 v_emp t_Record;BEGIN SELECT empno, ename, sal INTO v_emp FROM emp WHERE empno=7788; DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_emp.t_no)|v_emp.t_name|TO_CHAR(v_emp.t_sal);END;使用%TYPE特性的优点在于：n所引用的数据库列的数据类型可以不必知道；n所引用

24、的数据库列的数据类型可以实时改变。表的某一个字段的类型表的某一个字段的类型Restricted Confidential.PL/SQLPL/SQL结构结构变量类型变量类型使用使用%ROWTYPE%ROWTYPE可以将变量类型定义为与具有相同类型的数据库行。可以将变量类型定义为与具有相同类型的数据库行。%ROWTYPE将返回一个基于表定义的类型。将返回一个基于表定义的类型。DECLARE v_empno emp.empno%TYPE :=&empno; rec emp%ROWTYPE;BEGIN SELECT * INTO rec FROM emp WHERE empno=v_empno

25、; DBMS_OUTPUT.PUT_LINE(姓名:|rec.ename|工资:|rec.sal|工作时间:|rec.hiredate); END; 使用%ROWTYPE特性的优点在于：n所引用的数据库中列的个数个数和数据类型可以不必知道；n所引用的数据库中列的个数个数和数据类型可以实时改变。Restricted Confidential.PL/SQLPL/SQL结构结构DECLAREDECLARE n number(10); n number(10);BEGINBEGIN n:=1; n:=1; insert into w_test1(bh,n) values(a,n); insert in

26、to w_test1(bh,n) values(a,n); DECLARE DECLARE n number(10); n number(10); BEGIN BEGIN insert into w_test1(bh,n) values(b,n); insert into w_test1(bh,n) values(b,n); n:=100; n:=100; insert into w_test1(bh,n) values(b,n); insert into w_test1(bh,n) values(b,n); END; END; insert into w_test1(bh,n) values

27、(a,n); insert into w_test1(bh,n) values(a,n);END;END; BH N- - a 1 b b 100 a 1CREATE TABLE W_TEST1(BH VARCHAR2(2),N NUMBER(10);变量作用域变量作用域Restricted Confidential.PL/SQLPL/SQL结构结构SELECTSELECTSELECT SELECT 字段名字段名1,1,字段名字段名2 2FROM FROM 表名表名WHERE WHERE 条件条件GROUP BY GROUP BY 字段名字段名ORDER BY ORDER BY 字段名字段名;

28、 ;SELECT BH,SUM(N) S FROM W_TEST1WHERE N8GROUP BY BHORDER BY BHBH N- -a 1a 2a 3a 4a 5a 6a 7a 8a 9C 10000C 10001d 10002d 10002c 10003c 10004d 10004d 10004BH S- -C 20001a 9c 20007d 40012SELECT BH,N INTO v_name,v_amount FROM W_TEST1WHERE N=1Restricted Confidential.PL/SQLPL/SQL结构结构INSERTINSERTDECLARE nu

29、m1 number;BEGIN num1:=8; INSERT INTO W_TEST1(BH,N) VALUES(e,10); INSERT INTO W_TEST1(BH,N) VALUES(e,num1);END;INSERT INTO INSERT INTO 表名表名( (字段字段1,1,字段字段2) VALUES(2) VALUES(值值1,1,值值2);2);Restricted Confidential.PL/SQLPL/SQL结构结构INSERTINSERTINSERT INTO INSERT INTO 表表1( 1( 字段字段1,1,字段字段2)2)SELECT SELECT

30、 字段字段1,1,字段字段2 2FROM FROM 表表2 2WHERE WHERE 条件条件INSERT INTO W_TEST1(BH,N)SELECT BH,NFROM W_TEST1WHERE N5 and n9Restricted Confidential.PL/SQLPL/SQL结构结构DELETEDELETEDELETE FROM 表名表名 WHERE 条件条件;DELETE FROM W_TEST1 WHERE n=2;Restricted Confidential.PL/SQLPL/SQL结构结构动态动态SQLSQLPL/SQL不直接支持不直接支持DDL，因此需要使用动态，因

31、此需要使用动态SQLSET SERVEROUTPUT ONDECLAREv_statement VARCHAR2(500);CURSOR trigger_curISSELECT trigger_nameFROMuser_triggers;BEGINFOR y IN trigger_curLOOP-Build the statementv_statement:=ALTER TRIGGER|y.trigger_name|DISABLE;-Run the statementEXECUTE IMMEDIATE v_statement;END LOOP;END;Restricted Confidenti

32、al.PL/SQLPL/SQL结构结构TABLESPACETABLESPACE/创建临时表空间(临时表空间,用于临时数据的存放)create temporary tablespace user_tempTempfile D:oracleoradataOracle9iuser_temp.dbfsize 50mautoextend onnext 50m maxsize 20480mextent management local;一个表空间就是一片磁盘区域一个表空间就是一片磁盘区域, 它有一个或者多个磁盘文件组成它有一个或者多个磁盘文件组成,一个表空间可以容纳许多表、索引或者一个表空间可以容纳许多表

33、、索引或者簇等簇等 每个表空间有一个初始区间（每个表空间有一个初始区间（initial extent）用完这个区间后再用下一个，直到用完表空间，这时候）用完这个区间后再用下一个，直到用完表空间，这时候需要对表空间进行扩展，增加数据文件或者扩大已经存在的数据文件。需要对表空间进行扩展，增加数据文件或者扩大已经存在的数据文件。/创建数据表空间(用于用户数据的存放)create temporary tablespace test_dataTempfile D:oracleoradataOracle9iuser_data.dbfsize 50mautoextend onnext 50m maxsize

34、 20480mextent management local;本地管理本地管理(LOCAL): 用二进制的方式管理磁盘用二进制的方式管理磁盘,有很高的效率有很高的效率,能最大限度的使用能最大限度的使用磁盘磁盘. 同时能够自动跟踪记录临近空闲空间的情况，避免进行空闲区的合并操作。同时能够自动跟踪记录临近空闲空间的情况，避免进行空闲区的合并操作。 Restricted Confidential.PL/SQLPL/SQL结构结构TABLETABLECREATE TABLE ST_BJ_JXBZ2(BGDMC VARCHAR2(20), - 报告单名称SBLX VARCHAR2(20), - 设备类型

35、SBXH VARCHAR2(40), - 设备型号JXXZ VARCHAR2(10), - 检修性质(年检/安装/大修)JSGSSM VARCHAR2(60), - 检修工时说明CONSTRAINT PK_ST_BJ_JXBZ2 PRIMARY KEY(BGDMC,SBXH),CONSTRAINT FK_ST_BJ_JXBZ2 FOREIGN KEY(BGDMC) REFERENCES ST_BJ_JXBZ)tablespace CUST_GENERAL_D pctfree 10 -用于指定BLOCK中必需保留的最小空间的比例。 pctused 40 -为一个百分比数值，当BLOCK中已经使用

36、的空间降低到该数值以下 时，该BLOCK才是可用的，达到或是超过这个数值的BLOCK是不可 用的。 initrans 1-指定可以并发操作该表的事务的数目。 maxtrans 255指定最大并发操作该表的事务处理条目。 storage ( initial 40K next 40K minextents 1 maxextents unlimited pctincrease 0-表示每个扩展Extents的增长率 );CREATE TABLE 表名（表名（字段名字段名1 数据类型，数据类型，字段名字段名2 数据类型，数据类型，CONSTRAINT 约束名约束名 PRIMARY KEY(字段名字段名

37、1,字段名字段名2),CONSTRAINT约束名约束名 FOREIGN KEY(字段名字段名) REFERENCESS 表名表名);Restricted Confidential.PL/SQLPL/SQL结构结构ALTERALTERALTER TABLE W_TEST1 MODIFY(BH VARCHAR2(4),N NUMBER(15);ALTER TABLE 表名表名 MODIFY（字段字段1 数据类型，数据类型，字段字段2 数据类型数据类型）；）；Restricted Confidential.PL/SQLPL/SQL结构结构INDEXINDEX（索引）（索引）CREATE INDEX

38、SI_W_TEST1 ON W_TEST1(BH,DH) TABLESPACE TDMIS;CREATE INDEX 索引名索引名 on 表名表名TABLESPACE 表空间名表空间名;Restricted Confidential.PL/SQLPL/SQL结构结构序列序列序列序列(sequence)(sequence)是一种是一种oracleoracle对象，用来产生唯一的数字。对象，用来产生唯一的数字。其中其中sequence.CURRVALsequence.CURRVAL表示序列当前值表示序列当前值 sequence.NEXTVALsequence.NEXTVAL使序列增值并返回新的取值

39、使序列增值并返回新的取值CREATE SEQUENCE W_TEST1_S1 INCREMENT BY 1 - 每次加几个 START WITH 1 - 从1开始计数 NOMAXVALUE - 不设置最大值 NOCYCLE - 一直累加，不循环 CACHE 10; Insert into w_test1(bh,n) values(c,W_TEST1_S1.NEXTVAL)Insert into w_test1(bh,n) values(c,W_TEST1_S1.NEXTVAL)Insert into w_test1(bh,n) values(d,W_TEST1_S1.CURRVAL)Inser

40、t into w_test1(bh,n) values(d,W_TEST1_S1.CURRVAL)Restricted Confidential.PL/SQLPL/SQL结构结构同义词同义词CREATE PUBLIC SYNONYM CREATE PUBLIC SYNONYM 同义词名同义词名 FOR FOR 表名或视图名表名或视图名CREATE PUBLIC SYNONYM CX_FXTJ FOR CX_FXTJ;Restricted Confidential.PL/SQLPL/SQL结构结构快照快照CREATE SNAPSHOT KZ_FH_YZDZSTORAGE (INITIAL 100

41、K NEXT 100K PCTINCREASE 0)TABLESPACE TDMISREFRESH COMPLETE -FAST,FORCE START WITH SYSDATE NEXT SYSDATE+1ASSELECT PID,ND,YF,MAX(FH) ZDZFROM KZ_FHGROUP BY PID,ND,YF;EXECUTE DBMS_SNAPSHOT.REFRESH(KZ_FH_YZDZ,C);Oracle数据库的快照是一个表，它包含有对一个本地或远程数据库上一个或多个表数据库的快照是一个表，它包含有对一个本地或远程数据库上一个或多个表或视图的查询的结果。也就是说快照根本的原理

42、就是将本地或远程数据库上的一个或视图的查询的结果。也就是说快照根本的原理就是将本地或远程数据库上的一个查询结果保存在一个表中。查询结果保存在一个表中。 Restricted Confidential.PL/SQLPL/SQL结构结构ROLEROLE角色名被授予的权限CONNECTALTER SESSION，CREATE CLUSTER，CREATE DATABASE LINK，CREATE SEQUENCE，CREATE TABLE，CREATE VIEW，CREATE SYNONYMRESOURCECREATE CLUSTER，CREATE PROCEDURE，CREATE SEQUENCE

43、，CREATE TABLE，CREATE PROCEDUREDBA所有系统权限CREATE ROLE CREATE ROLE 角色名角色名; ;CREATE CREATE 权限权限 ON ON 对象对象 TO TO 角色名角色名CREATE ROLE RW_TEST1;GRANT SELECT ON W_TEST1 TO RW_TEST1;GRANT SELECT ON W_TEST1 TO PUBLIC;Restricted Confidential.PL/SQLPL/SQL结构结构USERUSERcreate user 用户名用户名 identified by 密码密码 default t

44、ablespace 表空间表空间;create user study identified by study default tablespace data_test;grant connect,resource to study; Restricted Confidential.PL/SQLPL/SQL结构结构GRANT GRANT 和和 REVOKEREVOKEGRANT GRANT 权限权限 ON ON 对象对象 TO TO 用户用户/ /角色角色REVOKE REVOKE 权限权限 ON ON 对象对象 FROM FROM 用户用户/ /角色角色对象权限对象类型ALTER表、序列DEL

45、ETE表、视图EXECUTE过程、函数、包INDEX表INSERT表、视图REFERENCES表SELECT表、视图、序列、快照UPDATE表、视图grant alter on table to study; revoke alter on table from study;grant connect,resource to study; Restricted Confidential.PL/SQLPL/SQL结构结构COMMITCOMMIT、ROLLBACKROLLBACK、SAVEPOINTSAVEPOINTDELETE FRO W_TEST1WHERE n=10000;SAVEPOINT

46、 A;DELETE FRO W_TEST1WHERE n=10001;SAVEPOINT B;DELETE FRO W_TEST1WHERE n=10002;SAVEPOINT C;ROLLBACK TO BCOMMIT;Restricted Confidential.3.PL/SQL流程控制语句流程控制语句Restricted Confidential.PL/SQLPL/SQL结构结构控制结构控制结构DECLARE n number;BEGIN . IF n50 then . ELSIF n=7 then v:=是;a ELSE v:=否; END IF; IF n=7 THENc v:=是

47、; ELSE v:=否; END IF; END;如果如果n n为一个确定的值为一个确定的值则则a,ba,b的结果是一样的的结果是一样的如果如果n n为为NULLNULL则则a,ba,b的结果就是不一样的的结果就是不一样的, ,在条件判断时要注意在条件判断时要注意NULLNULL的情况。的情况。Restricted Confidential.PL/SQLPL/SQL结构结构控制结构控制结构CASE selectorWHEN expression1 THEN result1WHEN expression2 THEN result2WHEN expressionN THEN resultN ELS

48、E resultN+1END; DECLAREV_grade char(1) := UPPER(&p_grade);V_appraisal VARCHAR2(20);BEGINV_appraisal :=CASE v_gradeWHEN A THEN ExcellentWHEN B THEN Very GoodWHEN C THEN GoodELSE No such gradeEND;DBMS_OUTPUT.PUT_LINE(Grade:|v_grade| Appraisal: | v_appraisal);END;Restricted Confidential.PL/SQLPL/SQ

49、L结构结构循环结构循环结构DECLARE n number(10);BEGIN n:=1; LOOP insert into w_test1(bh,n) values(a,n); n:=n+1; EXIT WHEN n10; END LOOP;END;DECLARE n number(10);BEGIN n:=1; WHILE n10 LOOP insert into w_test1(bh,n) values(a,n); n:=n+1; END LOOP;END;DECLARE n number(10);BEGIN FOR n IN 1.9 LOOP insert into w_test1(b

50、h,n) values(a,n); END LOOP;END;FORFOR循环循环WHILEWHILE循环循环简单简单循环循环FOR n IN REVERSE 1.9 LOOPFOR n IN REVERSE 1.9 LOOP( (表示从大到小循环表示从大到小循环) )Restricted Confidential.PL/SQLPL/SQL结构结构循环结构循环结构GOTO label; . . . . . ./*标号是用括起来的标识符 */DECLARE V_counter NUMBER := 1;BEGIN LOOP DBMS_OUTPUT.PUT_LINE(V_counter的当前值为:|

51、V_counter); V_counter := v_counter + 1; IF v_counter 10 THEN GOTO l_ENDofLOOP; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE(V_counter的当前值为:|V_counter);END ;当程序运行到当程序运行到GOTO I_ENDofLOOPGOTO I_ENDofLOOP语句时，会自动跳转到语句时，会自动跳转到标签处。标签处。Restricted Confidential.4.游标的使用游标的使用Restricted Confidential.为了处理 SQL 语句，ORACLE

52、 必须分配一片叫上下文( context area )的区域来处理所必需的信息，其中包括要处理的行的数目，一个指向语句被分析以后的表示形式的指针以及查询的活动集(active set)。游标是一个指向上下文的句柄( handle)或指针。通过游标，PL/SQL可以控制上下文区和处理语句时上下文区会发生些什么事情。对于不同的SQL语句，游标的使用情况不同：SQL语句 游标非查询语句 隐式的 结果是单行的查询语句 隐式的或显示的 结果是多行的查询语句 显示的 Restricted Confidential.l定义游标：就是定义一个游标名，以及与其相对应的SELECT语句。CURSOR cursor

53、_name(parameter, parameter) IS select_statement;在指定数据类型时，不能使用长度约束。如NUMBER(4)、CHAR(10) 等都是错误的。l打开游标：OPEN cursor_name(parameter = value, parameter = value);l提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中。FETCH cursor_name INTO variable_list | record_variable ; 对该记录进行处理 ，直到活动集合中没有记录。关闭游标，当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该

54、游标所占用的系统资源。CLOSE cursor_name；游标属性 %FOUND 布尔型属性，当最近一次读记录时成功返回,则值为TRUE； %NOTFOUND 布尔型属性，与%FOUND相反； %ISOPEN 布尔型属性，当游标已打开时返回 TRUE； %ROWCOUNT 数字型属性，返回已从游标中读取的记录数。 Restricted Confidential.DECLAREDeptRec dept%ROWTYPE;Dept_name dept.dname%TYPE;Dept_loc dept.loc%TYPE;CURSOR c1 IS SELECT dname, loc FROM dept

55、WHERE deptno = 30;CURSOR c2(dept_no NUMBER DEFAULT 10) ISSELECT dname, loc FROM dept WHERE deptno = dept_no;CURSOR c3(dept_no NUMBER DEFAULT 10) IS SELECT * FROM dept WHERE deptno 20); -或者是或者是 OPEN c3(20);LOOPFETCH c3 INTO deptrec;EXIT WHEN c3%NOTFOUND;DBMS_OUTPUT.PUT_LINE(deptrec.deptno|-|deptrec.d

56、name |-|deptrec.loc);END LOOP;CLOSE c3;END;Restricted Confidential.显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL，这是由ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的

57、显示游标无关的、最新处理的一条SQL 语句所包含的数据。格式调用为： SQL%注：INSERT, UPDATE, DELETE, SELECT 语句中不必明确定义游标。隐式游标属性SQL%FOUND 布尔型属性,当最近一次读记录时成功返回，则值为 true；SQL%NOTFOUND 布尔型属性,与%found相反；SQL%ROWCOUNT 数字型属性, 返回已从游标中读取得记录数；SQL%ISOPEN 布尔型属性, 取值总是FALSE。SQL命令执行完毕立即关 闭隐式游标。 Restricted Confidential.游标游标隐式游标用于处理INSERT、UPDATE、DELETE和单行的

58、SELECT .INTO语句因为SQL游标是通过PL/SQL引擎打开和关闭的，所以OPEN、FETCH和CLOSE命令是无关的。下面的语句在UPDATE语句没有找到任何行的时候就将执行一条INSERT语句。BEGIN UPDATE W_TEST1 SET N=200 WHERE BH=G; IF SQL%NOTFOUND THEN INSERT INTO W_TEST1(BH,N) VALUES(G,200) END IF;END;Restricted Confidential.PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；当进入

59、循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。格式：FOR index_variable IN cursor_namevalue, value LOOP- 游标数据处理代码END LOOP;其中：index_variable为游标FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，index_variable中各元素的

60、名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标FOR 循环语句中的索引变量来访问这些列数据。注：不要在程序中对游标进行人工操作；不要在程序中定义用于控制FOR 循环的记录。 PL/SQL还允许在游标FOR循环语句中使用子查询来实现游标的功能。 FOR i IN (SELECT name, id FROM testtable) LOOP Restricted Confidential.DECLARECURSOR c1(dept_no NUMBER DEFAULT 10) ISSELECT dname, loc FROM dept WHERE d