PL/SQL程序设计
一 PL/SQL简介
到目前为止，在数据库上一直使用单一的SQL语句进行数据操作，没有流程控制，无法开发复杂的应用。Sql是结构化语言;
Oracle PL/SQL语言（Procedural Language/SQL）是结合了结构化查询与Oracle自身过程控制为一体的强大语言，PL/SQL不但支持更多的数据类型，拥有自身的变量声明、赋值语句，而且还有条件、循环等流程控制语句。过程控制结构与SQL数据处理能力无缝的结合形成了强大的编程语言，可以创建过程和函数以及程序包。
PL/SQL是一种块结构的语言，它将一组语句放在一个块中，一次性发送给服务器，PL/SQL引擎分析收到PL/SQL语句块中的内容，把其中的过程控制语句由PL/SQL引擎自身去执行，把PL/SQL块中的SQL语句交给服务器的SQL语句执行器执行。如图所示：
PL/SQL体系结构
PL/SQL块发送给服务器后，先被编译然后执行，对于有名称的PL/SQL块（如子程序）可以单独编译，永久的存储在数据库中，随时准备执行。
1.1 为什么要用PL/SQL?
- 不能进行模块化编程
淘宝下订单,要多条语句,需要模块化. - 执行速度上,传统的SQL.耗时,(网络传输时间,dbms编译的时间)
PL/SQL sql语句是写在数据库中的,只需要编译一次,省去了网络传输时间,效率高. - 安全性的问题.在网络传输的过程中,可能被截获.
- 传统SQL浪费带宽.
注意:主要的是1 2 3
1.2 PL/SQL的优点：
- 支持SQL
SQL是访问数据库的标准语言，通过SQL命令，用户可以操纵数据库中的数据。PL/SQL支持所有的SQL数据操纵命令、游标控制命令、事务控制命令、SQL函数、运算符和伪列。同时PL/SQL和SQL语言紧密集成，PL/SQL支持所有的SQL数据类型和NULL值。
- 支持面向对象编程
PL/SQL支持面向对象的编程，在PL/SQL中可以创建类型，可以对类型进行继承，可以在子程序中重载方法等。
- 更好的性能
SQL是非过程语言，只能一条一条执行，而PL/SQL把一个PL/SQL块统一进行编译后执行，同时还可以把编译好的PL/SQL块存储起来，以备重用，减少了应用程序和服务器之间的通信时间，PL/SQL是快速而高效的。
- 可移植性
使用PL/SQL编写的应用程序，可以移植到任何操作系统平台上的Oracle服务器，同时还可以编写可移植程序库，在不同环境中重用。
- 安全性
可以通过存储过程对客户机和服务器之间的应用程序逻辑进行分隔，这样可以限制对Oracle数据库的访问，数据库还可以授权和撤销其他用户访问的能力。
1.3 PL/SQL的缺点:
- 可移植性不好,在其他数据库中不可以用.
1.4 PL/SQL 可用的SQL语句
PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成。在PL/SQL中可以使用的SQL语句有：
INSERT，UPDATE，DELETE，SELECT INTO，COMMIT，ROLLBACK，SAVEPOINT。
二 PL/SQL语法基础
2.1 PL/SQL块
PL/SQL是一种块结构的语言，一个PL/SQL程序包含了一个或者多个逻辑块，逻辑块中可以声明变量，变量在使用之前必须先声明。除了正常的执行程序外，PL/SQL还提供了专门的异常处理部分进行异常处理。每个逻辑块分为三个部分，语法是：
语法结构：PL/SQL块的语法
[DECLARE --declaration statements] 1）声明部分 变量,常量,游标,异常 BEGIN --executable statements 2）执行部分 sql语句 [EXCEPTION --exception statements] 3）异常处理部分 END;
语法解析：
1）声明部分：声明部分包含了变量和常量的定义。这个部分由关键字DECLARE开始，如果不声明变量或者常量，可以省略这部分。
2）执行部分：执行部分是 PL/SQL块的指令部分，由关键字BEGIN开始，关键字END结尾。所有的可执行PL/SQL语句都放在这一部分，该部分执行命令并操作变量。其他的PL/SQL块可以作为子块嵌套在该部分。PL/SQL块的执行部分是必选的。注意END关键字后面用分号结尾。
3）异常处理部分：该部分是可选的，该部分用EXCEPTION关键字把可执行部分分成两个小部分，之前的程序是正常运行的程序，一旦出现异常就跳转到异常部分执行。
- 举例：输出‘Hello World!’；
begin dbms_output.put_line('hello world'); end;
dbms_output是Oracle所提供的包（类似java开发包），该包包含一些过程，put_line就是该包下的一个过程。
- plsql说明：
1）PL/SQL是一种编程语言，与Java和C#一样，除了有自身独有的数据类型、变量声明和赋值以及流程控制语句外，PL/SQL还有自身的语言特性；
2）PL/SQL对大小写不敏感，为了良好的程序风格，开发团队都会选择一个合适的编码标准。比如有的团队规定：关键字全部大些，其余的部分小写。
3）PL/SQL块中的每一条语句都必须以分号结束，SQL语句可以是多行的，但分号表示该语句结束。一行中可以有多条SQL语句，他们之间以分号分隔，但是不推荐一行中写多条语句。
- PL/SQL中的特殊符号说明：
|
类型 |
符号 |
说明 |
|
赋值运算符 |
:= |
Java和C#中都是等号，PL/SQL的赋值是：= |
|
特殊字符 |
|| |
字符串连接操作符。 |
|
-- |
PL/SQL中的单行注释。 |
|
|
/*,*/ |
PL/SQL中的多行注释，多行注释不能嵌套。 |
|
|
<<,>> |
标签分隔符。只为了标识程序特殊位置。 |
|
|
.. |
范围操作符，比如：1..5 标识从１到５ |
|
|
算术运算符 |
+，-，*，/ |
基本算术运算符。 |
|
** |
求幂操作，比如：3**2=9 |
|
|
关系运算符 |
>，<,>=,<=,= |
基本关系运算符，=表示相等关系，不是赋值。 |
|
<>,!= |
不等关系。 |
|
|
逻辑运算符 |
AND,OR,NOT |
逻辑运算符。 |
2.2 PL/SQL块分类
1无名块
动态构造，只能执行一次。
2子程序
存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。
3触发器
当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。
2.3 声明
变量声明
PL/SQL支持SQL中的数据类型，PL/SQL中正常支持NUMBER,VARCHAR2,DATE等Oracle SQL数据类型。声明变量必须指明变量的数据类型，也可以声明变量时对变量初始化，变量声明必须在声明部分。声明变量的语法是：
语法格式：
变量名 数据类型[ :=初始值]
语法解析：数据类型如果需要长度，可以用括号指明长度，比如：varchar2(20)。
变量的命名规范：
（1）变量名首字母必须是英文字母，其后可以是字母、数字或者特殊字符$、#和_
（2）变量名字的长度不能超过30
（3）变量名中不能有空格
标识符号和命名规范：
（1）当定义变量时，建议使用v_作为前缀，如v_sal；
（2）当定义常量时，建议使用c_作为前缀，如c_rate；
（3）当定义游标时，建议使用_cursor作为后缀，如emp_cursor；
（4）当定义异常时，建议使用e_作为前缀，例如e_error；
声明变量并赋值
DECLARE sname VARCHAR2(20) :='jerry'; ① BEGIN sname:=sname||' and tom'; ② dbms_output.put_line(sname); ③ END; /
说明：
① 声明一个变量sname，初始化值是“jerry”。字符串用单引号，如果字符串中出现单引号可以使用两个单引号（’’）来表示，即单引号同时也具有转义的作用。初始化赋值可以’:=’，也可以使用default关键词。
② 对变量sname重新赋值，赋值运算符是“:=”。
③ dbms_output.put_line是输出语句，可以把一个变量的值输出，在SQL*Plus中输出数据时，如果没有结果显示，可以使用命令：set serveroutput on设置显示输出到SQL*Plus控制台上的内容。
从数据库中查询出结果赋给变量
对变量赋值还可以使用SELECT…INTO 语句从数据库中查询数据对变量进行赋值。但是查询的结果只能是一行记录，不能是零行或者多行记录。
DECLARE sname VARCHAR2(20) DEFAULT 'jerry'; --（1） BEGIN SELECT ename INTO sname FROM emp WHERE empno=7934; --（2） dbms_output.put_line(sname); END;
说明：
（1）变量初始化时，可以使用DEFAULT关键字对变量进行初始化。
（2）使用select…into语句对变量sname赋值，要求查询的结果必须是一行，不能是多行或者没有记录。
从控制台输入变量值
在SQL*Plus也可以像Java一样从控制台输入变量的值。
格式：&变量名
示例：
declare --定义变量 v_ename varchar2(10); v_empno number; begin --执行部分 v_empno := &no; select ename into v_ename from emp where empno=v_empno; dbms_output.put_line('用户名'||v_ename); exception when no_data_found then dbms_output.put_line('未发现该编号'); end; /
说明：
（1）使用&给empno赋值。
（2）no_data_found ：PL/SQL的预定义异常。
声明常量
常量在声明时赋予初值，并且在运行时不允许重新赋值。使用CONSTANT关键字声明常量。
例子：计算圆的面积。
DECLARE pi CONSTANT number :=3.14; --圆周率长值 r number DEFAULT 3; --圆的半径默认值3 area number; --面积。 BEGIN area:=pi*r*r; --计算面积 dbms_output.put_line(area); --输出圆的面积 END; /
案例 根据用户输入的雇员编号，输出该雇员的名字
declare v_ename varchar2(10); begin select ename into v_ename from emp where empno=&no; dbms_output.put_line('姓名是'||v_ename); end; --变量的定义：变量名 变量类型（长度）; --select ename into v_ename from emp where empno=&no --select...into...from是对变量v_ename赋值 --empno=&no:&no符号表示需要从键盘接受一个empno
案例：将上述PL/SQL块改为一个过程
create or replace procedure pro3(v_empno number) is v_ename emp.ename%type; begin select ename into v_ename from emp where empno=v_empno; dbms_output.put_line('姓名是：'||v_ename); end; exec pro3(1001); --总结：过程中，变量定义是出现在is和begin之间的，没有declare --emp.ename%type:表示v_ename的类型和emp表的ename的数据类型和长度完全相同
说明：
声明常量时使用关键字CONSTANT，常量初值可以使用赋值运算符（:=）赋值，也可以使用DEFAULT关键字赋值。
2.4 PL/SQL数据类型
前面在建表时，学习过Oracle SQL的数据类型，PL/SQL不但支持这些数据类型，还具备自身的数据类型。PL/SQL的数据类型包括标量数据类型，引用数据类型和存储文本、图像、视频、声音等非结构化的大数据类型（LOB数据类型）等。下面列举一些常用的类型。
标量数据类型
标量数据类型的变量只有一个值，且内部没有分量。标量数据类型包括数字型，字符型，日期型和布尔型。这些类型有的是Oracle SQL中定义的数据类型，有的是PL/SQL自身附加的数据类型。字符型和数字型又有子类型，子类型只与限定的范围有关，比如NUMBER类型可以表示整数，也可以表示小数，而其子类型POSITIVE只表示正整数。
|
类型 |
说明 |
|
VARCHAR2(长度) |
可变长度字符串，Oracle SQL定义的数据类型，在PL/SQL中使用时最常32767字节。在PL/SQL中使用没有默认长度，因此必须指定。 |
|
NUMBER(精度，小数) |
Oracle SQL定义的数据类型，见第二章。 |
|
DATE |
Oracle SQL定义的日期类型，见第二章。 |
|
TIMESTAMP |
Oracle SQL定义的日期类型，见第二章。 |
|
CHAR(长度) |
Oracle SQL定义的日期类型，固定长度字符，最长32767字节，默认长度是1，如果内容不够用空格代替。 |
|
LONG |
Oracle SQL定义的数据类型，变长字符串基本类型，最长32760字节。在Oracle SQL中最长2147483647字节。 |
|
BOOLEAN |
PL/SQL附加的数据类型，逻辑值为TRUE、FALSE、NULL |
|
BINARY_INTEGER |
PL/SQL附加的数据类型，介于-231和231之间的整数。 |
|
PLS_INTEGER |
PL/SQL附加的数据类型，介于-231和231之间的整数。类似于BINARY_INTEGER，只是PLS_INTEGER值上的运行速度更快。 |
|
NATURAL |
PL/SQL附加的数据类型，BINARY_INTEGER子类型，表示从0开始的自然数。 |
|
NATURALN |
与NATURAL一样，只是要求NATURALN类型变量值不能为NULL。 |
|
POSITIVE |
PL/SQL附加的数据类型，BINARY_INTEGER子类型，正整数。 |
|
POSITIVEN |
与POSITIVE一样，只是要求POSITIVE的变量值不能为NULL。 |
|
REAL |
Oracle SQL定义的数据类型，18位精度的浮点数 |
|
INT,INTEGER,SMALLINT |
Oracle SQL定义的数据类型，NUMBERDE的子类型，38位精度整数。 |
|
SIGNTYPE |
PL/SQL附加的数据类型，BINARY_INTEGER子类型。值有：1、-1、0。 |
|
STRING |
与VARCHAR2相同。 |
属性数据类型
当声明一个变量的值是数据库中的一行或者是数据库中某列时，可以直接使用属性类型来声明。Oracle中存在两种属性类型：%TYPE和%ROWTYPE。
- % ROWTYPE
引用数据库表中的一行作为数据类型，即RECORD类型（记录类型），是PL/SQL附加的数据类型。表示一条记录，就相当于Java中的一个对象。可以使用“.”来访问记录中的属性。
代码演示：
|
DECLARE myemp EMP%ROWTYPE; ① BEGIN SELECT * INTO myemp FROM emp WHERE empno=7934; ② dbms_output.put_line(myemp.ename); ③ END; / |
代码解析：
① 声明一个myemp对象，该对象表示EMP表中的一行。
②从EMP表中查询一条记录放入myemp对象中。
③访问该对象的属性可以使用“.”。
案例
declare v_emp emp%rowtype; begin select * into v_emp from emp where empno=7839; dbms_output.put_line('姓名是：'||v_emp.ename||' 工资是：'||v_emp.sal); end; --总结：%rowtype表示记录类型，v_ename emp%rowtype --使用：v_emp.ename
- %TYPE
引用某个变量或者数据库的列的类型作为某变量的数据类型。
代码演示：%TYPE应用
|
DECLARE v_sal emp.sal%TYPE; ① v_mysal number(4):=3000; v_totalsal v_mysal%TYPE; ② BEGIN SELECT SAL INTO v_sal FROM emp WHERE empno=7934; v_totalsal:=v_sal+v_mysal; dbms_output.put_line(v_totalsal); END; / |
代码解析：
①定义变量v_sal为emp表中sal列的类型。
②定义v_totalsal是变量v_mysal的类型。
%TYPE可以引用表中的某列作的类型为变量的数据类型，也可以引用某变量的类型作为新变量的数据类型。
2.4 PL/SQL流程控制
PL/SQL程序可通过条件或循环结构来控制命令执行的流程。PL/SQL提供了丰富的流程控制语句，与Java一样也有三种控制结构：
- 顺序结构
- 条件结构
- 循环结构
条件控制
Java中的条件控制使用关键字if和switch。PL/SQL中关于条件控制的关键字有IF-THEN、IF-THEN-ELSE、IF-THEN-ELSIF和多分枝条件CASE。
IF-THEN
该结构先判断一个条件是否为TRUE，条件成立则执行对应的语句块，与Java中的if语句很相似，具体语法是：
|
Java中if语法 |
PL/SQL中IF语法 |
|
if (条件){ //条件结构体 } |
IF 条件 THEN --条件结构体 END IF; |
表3 PL/SQL中条件语法
说明：
① 用IF关键字开始，END IF关键字结束，注意END IF后面有一个分号。
② 条件部分可以不使用括号，但是必须以关键字THEN来标识条件结束，如果条件成立，则执行THEN后到对应END IF之间的语句块内容。如果条件不成立，则不执行条件语句块的内容。
③ Java结构用一对大括号来包含条件结构体的内容。PL/SQL中关键字THEN到END IF之间的内容是条件结构体内容。
案例1：查询JAMES的工资，如果大于900元，则发奖金800元。
代码演示：IF-THEN应用
|
DECLARE newSal emp.sal % TYPE; BEGIN SELECT sal INTO newSal FROM emp WHERE ename='JAMES'; IF newSal>900 THEN ① UPDATE emp SET comm=800 WHERE ename='JAMES'; END IF; COMMIT ; ② END; |
代码解析：
①先判断条件，如果条件为TRUE，则执行条件结构体内部的内容。
②在PL/SQL块中可以使用事务控制语句，该COMMIT同时也能把PL/SQL块外没有提交的数据一并提交，使用时需要注意。
IF-THEN-ELSE
语法格式：IF-THEN-ELSE
|
Java中if语法 |
PL/SQL中IF语法 |
|
if (条件){ //条件成立结构体 } else{ //条件不成立结构体 } |
IF 条件 THEN --条件成立结构体 ELSE --条件不成立结构体 END IF; |
表4 PL/SQL中条件语法
语法解析：
把ELSE与IF-THEN连在一起使用，如果IF条件不成立则执行ELSE部分的语句。
案例2：查询JAMES的工资，如果大于900元，则发奖金800元，否则发奖金400元。
代码演示：IF-THEN-ELSE应用
|
DECLARE newSal emp.sal % TYPE; BEGIN SELECT sal INTO newSal FROM emp WHERE ename='JAMES'; IF newSal>900 THEN UPDATE emp SET comm=800 WHERE ename='JAMES'; ELSE UPDATE emp SET comm=400 WHERE ename='JAMES'; END IF; END; |
IF-THEN-ELSIF
语法格式：IF-THEN-ELSIF
|
Java中if语法 |
PL/SQL中IF语法 |
|
if (条件2){ //条件成立结构体 } else if(条件2){ //条件不成立结构体 } else{ //以上条件都不成立结构体 } |
IF 条件1 THEN --条件1成立结构体 ELSIF 条件2 THEN --条件2成立结构体 ELSE --以上条件都不成立结构体 END IF; |
表5 PL/SQL中多分枝条件语法
语法解析：
PL/SQL中的再次条件判断中使用关键字ELSIF，而Java使用else if。
案例3：查询JAMES的工资，如果大于1500元，则发放奖金100元，如果工作大于900元，则发奖金800元，否则发奖金400元。
代码演示：IF-THEN-ELSIF应用
|
DECLARE newSal emp.sal % TYPE; BEGIN SELECT sal INTO newSal FROM emp WHERE ename='JAMES'; IF newSal>1500 THEN UPDATE emp SET comm=1000 WHERE ename='JAMES'; ELSIF newSal>900 THEN UPDATE emp SET comm=800 WHERE ename='JAMES'; ELSE UPDATE emp SET comm=400 WHERE ename='JAMES'; END IF; END; |
CASE
CASE是一种选择结构的控制语句，可以根据条件从多个执行分支中选择相应的执行动作。也可以作为表达式使用，返回一个值。类似于Java中的switch语句。语法是：
语法格式：CASE
|
CASE [selector] WHEN 表达式1 THEN 语句序列1； WHEN 表达式2 THEN 语句序列2； WHEN 表达式3 THEN 语句序列3； …… [ELSE 语句序列N]； END CASE; |
语法解析：
如果存在选择器selector，选择器selector与WHEN后面的表达式匹配，匹配成功就执行THEN后面的语句。如果所有表达式都与selector不匹配，则执行ELSE后面的语句。
案例4：输入一个字母A、B、C分别输出对应的级别信息。
代码演示：CASE中存在selector，不返回值
|
DECLARE v_grade CHAR(1):=UPPER('&p_grade'); ① BEGIN CASE v_grade ② WHEN 'A' THEN dbms_output.put_line('Excellent'); WHEN 'B' THEN dbms_output.put_line('Very Good'); WHEN 'C' THEN dbms_output.put_line('Good'); ELSE dbms_output.put_line('No such grade'); END CASE; END; |
代码解析：
① & grade表示在运行时由键盘输入字符串到grade变量中。
② v_grade分别于WHEN后面的值匹配，如果成功就执行WHEN后的程序序列。
CASE语句还可以作为表达式使用，返回一个值。
代码演示：CASE中存在selector，作为表达式使用
|
DECLARE v_grade CHAR(1):=UPPER('&grade'); p_grade VARCHAR(20) ; BEGIN p_grade := ① CASE v_grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Very Good' WHEN 'C' THEN 'Good' ELSE 'No such grade' END; dbms_output.put_line('Grade:' ||v_grade||',the result is '||p_grade); END; |
代码解析：
① CASE语句可以返回一个结果给变量p_grade
PL/SQL还提供了搜索CASE语句。也就是说，不使用CASE中的选择器，直接在WHEN后面判断条件，第一个条件为真时，执行对应THEN后面的语句序列。
代码演示：搜索CASE
DECLARE v_grade CHAR(1):=UPPER('&grade'); p_grade VARCHAR(20) ; BEGIN p_grade := CASE WHEN v_grade='A' THEN 'Excellent' WHEN v_grade='B' THEN 'Very Good' WHEN v_grade='C' THEN 'Good' ELSE 'No such grade' END; dbms_output.put_line('Grade:' ||v_grade||',the result is '||p_grade); END;
循环结构
PL/SQL提供了丰富的循环结构来重复执行一些列语句。Oracle提供的循环类型有：
(1)无条件循环LOOP-END LOOP语句
(2)WHILE循环语句
(3)FOR循环语句
在上面的三类循环中用EXIT强制结束循环，相当于Java循环中的break。
LOOP循环
语法格式：LOOP循环
|
LOOP --循环体 EXIT WHEN<条件> END LOOP； |
语法格式：
- 循环体在LOOP和END LOOP之间，在每个LOOP循环体中，首先执行循环体中的语句序列，执行完后再重新开始执行。
- 在LOOP循环中可以使用EXIT WHEN 条件的形式终止循环。
案例5：执行1+2+3+…+100的值
代码演示：LOOP循环
|
DECLARE counter number(3):=0; sumResult number:=0; BEGIN LOOP counter := counter+1; sumResult := sumResult+counter; EXIT WHEN counter>=100; ② END LOOP; dbms_output.put_line('result is :'||to_char(sumResult)); END; |
代码解析：
① LOOP循环中可以使用IF结构嵌套EXIT关键字退出循环
② 注释行，该行可以代替①中的循环结构，WHEN后面的条件成立时跳出循环。
WHILE循环
与Java中的while循环很类似。先判断条件，条件成立再执行循环体。
语法格式：WHILE
|
Java中while语法 |
PL/SQL中WHILE语法 |
|
while (条件){ //循环体体 } |
WHILE 条件 LOOP --循环体 END LOOP; |
表5 PL/SQL中LOOP语法
案例6：WHILE循环
代码演示：WHILE循环
|
DECLARE counter number(3):=0; sumResult number:=0; BEGIN WHILE counter<100 LOOP counter := counter+1; sumResult := sumResult+counter; END LOOP; dbms_output.put_line('result is :'||sumResult); END; |
FOR循环
FOR循环需要预先确定的循环次数，可通过给循环变量指定下限和上限来确定循环运行的次数，然后循环变量在每次循环中递增（或者递减）。FOR循环的语法是：
语法格式：FOR循环
|
FOR 循环变量 IN [REVERSE] 循环下限..循环上限 LOOP --循环体 END LOOP； |
语法解析：
循环变量：该变量的值每次循环根据上下限的REVERSE关键字进行加1或者减1。
REVERSE：指明循环从上限向下限依次循环。
案例7：FOR循环
代码演示：FOR循环
|
DECLARE counter number(3):=0; sumResult number:=0; BEGIN FOR counter IN 1..100 LOOP sumResult := sumResult+counter; END LOOP; dbms_output.put_line('result is :'||sumResult); END; |
顺序结构
在程序顺序结构中有两个特殊的语句。GOTO和NULL
GOTO语句
GOTO语句将无条件的跳转到标签指定的语句去执行。标签是用双尖括号括起来的标示符，在PL/SQL块中必须具有唯一的名称，标签后必须紧跟可执行语句或者PL/SQL块。GOTO不能跳转到IF语句、CASE语句、LOOP语句、或者子块中。
NULL语句
NULL语句什么都不做，只是将控制权转到下一行语句。NULL语句是可执行语句。NULL语句在IF或者其他语句语法要求至少需要一条可执行语句，但又不需要具体操作的地方。比如GOTO的目标地方不需要执行任何语句时。
案例8：GOGO 和 NULL
代码演示：GOTO和NULL
|
DECLARE sumsal emp.sal%TYPE; BEGIN SELECT SUM(sal) INTO sumsal FROM EMP; IF sumsal>20000 THEN GOTO first_label; ① ELSE GOTO second_label; ② END IF; <<first_label>> ③ dbms_output.put_line('ABOVE 20000:' || sumsal); <<second_label>> ④ NULL; END; |
代码解析：
① 跳转到程序first_label位置，就是②的位置，first_label是一个标签，用两个尖括号包含。
② 无条件跳转到sedond_label位置，就是④的位置。④处不执行任何内容，因此是一个NULL语句。
2.5 异常处理概念
异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件,程序块的异常处理预定义的错误和自定义错误,由于PL/SQL程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行.
有三种类型的异常错误：
1． 预定义 ( Predefined )错误
ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。
2． 非预定义 ( Predefined )错误
即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。
3． 用户定义(User_define) 错误
程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。
异常处理部分一般放在 PL/SQL 程序体的后半部,结构为:
EXCEPTION WHEN first_exception THEN <code to handle first exception > WHEN second_exception THEN <code to handle second exception > WHEN OTHERS THEN <code to handle others exception > END;
异常处理可以按任意次序排列,但 OTHERS 必须放在最后。
常见预定义异常
|
错误号 |
异常错误信息名称 |
说明 |
|
ORA-0001 |
Dup_val_on_index |
试图破坏一个唯一性限制 |
|
ORA-0051 |
Timeout-on-resource |
在等待资源时发生超时 |
|
ORA-0061 |
Transaction-backed-out |
由于发生死锁事务被撤消 |
|
ORA-1001 |
Invalid-CURSOR |
试图使用一个无效的游标 |
|
ORA-1012 |
Not-logged-on |
没有连接到ORACLE |
|
ORA-1017 |
Login-denied |
无效的用户名/口令 |
|
ORA-1403 |
No_data_found |
SELECT INTO没有找到数据 |
|
ORA-1422 |
Too_many_rows |
SELECT INTO 返回多行 |
|
ORA-1476 |
Zero-divide |
试图被零除 |
|
ORA-1722 |
Invalid-NUMBER |
转换一个数字失败 |
|
ORA-6500 |
Storage-error |
内存不够引发的内部错误 |
|
ORA-6501 |
Program-error |
内部错误 |
|
ORA-6502 |
Value-error |
转换或截断错误 |
|
ORA-6504 |
Rowtype-mismatch |
缩主游标变量与 PL/SQL变量有不兼容行类型 |
|
ORA-6511 |
CURSOR-already-OPEN |
试图打开一个已存在的游标 |
|
ORA-6530 |
Access-INTO-null |
试图为null 对象的属性赋值 |
|
ORA-6531 |
Collection-is-null |
试图将Exists 以外的集合( collection)方法应用于一个null pl/sql 表上或varray上 |
|
ORA-6532 |
Subscript-outside-limit |
对嵌套或varray索引得引用超出声明范围以外 |
|
ORA-6533 |
Subscript-beyond-count |
对嵌套或varray 索引得引用大于集合中元素的个数. |
案例：
更新指定员工工资，如工资小于1500，则加100；
DECLARE v_empno emp.empno%TYPE :=&empno; v_sal emp.sal%TYPE; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=v_empno; IF v_sal<=1500 THEN UPDATE emp SET sal=sal+100 WHERE empno=v_empno; DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已更新!'); ELSE DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已经超过规定值!'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_empno||'的员工'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('发生其它错误!'); END;
案例
declare v_ename emp.ename%type; begin select ename into v_ename from emp where empno=&no; dbms_output.put_line('姓名是'||v_ename); exception when no_data_found then dbms_output.put_line('你好，你输入的编号不存在！'); end;
案例,异常用于逻辑跳转
--有些时候，异常还用来做一些逻辑跳转 --案例：输入员工编号，显示员工姓名，如果输入了不存在的员工编号，就在emp中添加一条记录（1002，'马大哈'） declare v_ename emp.ename%type; begin select ename into v_ename from emp where empno=&no; dbms_output.put_line('姓名是'||v_ename); exception when no_data_found then insert into emp(empno,ename)values(1002,'马大哈'); end;
declare v_ename emp.ename%type; v_empno emp.empno%type:=&no; begin select ename into v_ename from emp where empno=v_empno; dbms_output.put_line('姓名是'||v_ename); exception when no_data_found then insert into emp(empno,ename)values(v_empno,'张无忌'); end;