本节要点:
- 什么是PL/SQL
- PL/SQL简介
- PL/SQL 的优点
- PL/SQL 的体系结构
- PL/SQL 块简介
- PL/SQL中的变量和常量
- 变量和常量的定义
- PL/SQL 支持的数据类型
- 表达式
- 数值表达式
- 关系表达式
- 逻辑表达式
- 异常处理
- 处理异常的语法
- 预定义异常
- 预定义异常
- 自定义异常
1 什么是PL/SQL
1.1 PL/SQL简介
PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言,是对 SQL 的扩展。PL/SQL完全可以像Java语言一样实现逻辑判断、条件循环以及异常处理等,这是标准SQL很难做到的。
1.2 PL/SQL 的优点
PL/SQL同传统的SQL语言相比有以下几个优点:
1) 可以提高程序的运行性能
标准的SQL被执行时,只能一条一条地向Oracle服务器发送。PL/SQL语句块可以包含多条SQL语句,这样用户只需要一次连接就可以执行多条SQL语句,大大节省了网络资源开销,减少访问数据库次数。
2) 可以使程序模块化
使用块后,可以把对多张表的操作都放到一个块内,而对外只提供一个调用方式和需要传入的参数,这样减少程序员的工作量。使用块也可以把数据库数据同客户程序隔离开来,使得数据库表结构发生变化时,对调用者的影响减小到最低程度。
3) 可以采用逻辑控制语句来控制程序结构
使PL/SQL更加实用化,更符合现实业务逻辑的需求。
4) 利用处理运行时的错误信息
利用流程控制语句,可以判断一些逻辑问题,提高检错能力;并且可以对错误信息进行处理,不至于出现生硬的错误提示。
5) 良好的可移植性
PL/SQL可以成功的运行到不同的服务器中。
1.3 PL/SQL 的体系结构
PL/SQL 引擎驻留在 Oracle 服务器中,该引擎接受 PL/SQL 块并对其进行编译执行。
1.4 PL/SQL 块简介
PL/SQL 块是构成 PL/SQL 程序的基本单元;将逻辑上相关的声明和语句组合在一起。PL/SQL 分为三个部分,声明部分、可执行部分和异常处理部分:
[DECLARE declarations]—声明开始关键字
/*声明部分,包括PL/SQL中的变量、常量以及类型等*/
BEGIN —执行部分开始的标志
executable statements /*这里是执行部分,是整个PL/SQL块的主体部分*/
[EXCEPTION handlers] –异常开始部分的关键字
/*这里是异常处理部分*/
END; --执行结束标志
示例:
2 PL/SQL中的变量和常量
2.1 变量和常量的定义
定义方式:
- l 在声明部分声明,使用前必须先声明
- l 声明时必须指定数据类型,每行声明一个标识符
- l 在可执行部分的 SQL 语句和过程语句中使用
声明变量和常量的语法:
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];
给变量赋值有两种方法:
- l 使用赋值语句 :=
- l 使用 SELECT INTO 语句
示例:
set serveroutput on
declare
v_name char(8);
c_name constant char(10) := '学生姓名:';
begin
select f_name into v_name from martin.t_student where f_id='001';
dbms_output.put_line(c_name||v_name);
exception /* 异常处理语句 */
when others then
dbms_output.put_line('出错:'||SQLERRM);
end;
/
2.2 PL/SQL 支持的数据类型
1) 数值类型
主要用来存放数字型的数据。主要有以下几种:
- l NUMBER类型可以表示整数、实数和浮点数,改类型以十进制存储。其通用格式是NUMBER(precision,scale),其中precision表示精度,也就是数字的位数,最高38位;scale表示小数点后的位数。例如,NUMBER(3,1)可以存储-99.9~99.9之间的值。定义的时候precision和scale可以省略,如number按最大38位表示,number(8)表示最大8位,都不能包含小数位。
- l PLS_INTEGER和BINARY_INTEGER类型,BINARY_INTEGER存储有符号整数,所需存储空间少于NUMBER类型值;PLS_INTEGER存储有符号整数。通常认为PLS_INTEGER和BINARY_INTEGER是同样的数据类型,区别是BINARY_INTEGER溢出时能为其指派一个number类型而不至于发生异常,PLS_INTEGER如果遇到溢出直接发生异常。
2) 字符数据类型
字符数据类型用来存储单个的字符或字符串的类型。主要包括:
- l CHAR:用来描述固定长度的字符串
- l VARCHAR2:表示可变长的字符串
- l LONG:表示可变长的字符串,与varchar2类似
- l NCHAR 和NVARCHAR2 :n表示Unicode字符,即所有字符都占两个字节,nchar,nvarchar字符中,英文字符只需要一个字节存储就足够了,但汉字众多,需要两个字节存储,英文与汉字同时存在时容易造成混乱,Unicode字符集就是为了解决字符集这种不兼容的问题而产生的,它所有的字符都用两个字节表示,即英文字符也是用两个字节表示。
3) 日期时间类型
用来存储日期和时间数据。常用的两种日期时间类型:
- DATE:可以存储月、年、日、世纪、时、分、秒
- TIMESTAMP:由DATE演变而来,可以存储月、年、日、世纪、时、分和秒以及小数的秒
4) 布尔数据类型
此类别只有一种类型,即BOOLEAN类型,用于存储逻辑值(TRUE、FALSE和NULL),不能向数据库中插入BOOLEAN数据,即不能用作定义表中的数据类型,只能对BOOLEAN变量执行逻辑操作。
5) LOB 数据类型
用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。LOB 数据类型可存储最大 4GB的数据。LOB 类型包括:
- BLOB 将大型二进制对象存储在数据库中
- CLOB 将大型字符数据存储在数据库中
- NCLOB 存储大型UNICODE字符数据
- BFILE 将大型二进制对象存储在操作系统文件中
LOB 类型的数据库列仅存储定位符,该定位符指向大型对象的存储位置。DBMS_LOB程序包用于操纵 LOB 数据:
declare
content CLOB;
beginPosition integer;
amount integer;
outputString varchar(100);
begin
/*从表中选择 CLOB 定位符到 content变量中*/
select f_content into content from t_booktext where f_chapterid='001';
beginPosition := 1;
amount := 22;
/*从CLOB数据中读取22个字符存储到 outputString 变量中*/
dbms_lob.read(content,amount,beginPosition,outputString);
dbms_output.put_line(outputString);
end;
/
6) 属性类型
用于引用数据库列的数据类型,以及表示表中一列或者一行的记录类型。不需要知道被引用的表列的具体类型,如果被引用对象的数据类型发生改变,PL/SQL 变量的数据类型也随之改变。有%TYPE和%ROWTYPE:
- l %TYPE:为了使一个变量的数据类型与另一个已经定义了的变量(尤其是表的某一列)的数据类型相一致,Oracle提供了%TYPE定义方式。当被参照的那个变量的数据类型改变了之后,这个新定义的变量的数据类型会自动跟随其改变,容易保持一致,也不用修改PL/SQL程序了。
示例:
DECLARE
V_ORG_NAME SF_ORG.ORG_NAME%TYPE; --定义V_ORG_NAME与ORG_NAME类型相同
V_PARENT_ID SF_ORG.PARENT_ID%TYPE;--定义V_PARENT_ID与PARENT_ID类型相同
BEGIN
/*将SF_ORG中的ORG_NAME,PARENT_ID放入定义好的V_ORG_NAME和V_PARENT_ID*/
SELECT ORG_NAME,PARENT_ID INTO V_ORG_NAME,V_PARENT_ID
FROM SF_ORG SO
WHERE SO.ORG_ID=1234;
/*输出V_ORG_NAME和V_PARENT_ID的值*/
DBMS_OUTPUT.PUT_LINE('部门名称:' || V_ORG_NAME);
DBMS_OUTPUT.PUT_LINE('上级部门编码:' || TO_CHAR(V_PARENT_ID));
END;
- l %ROWTYPE:如果一个表有较多的列,使用%ROWTYPE来定义一个表示表中一行记录的变量
例:
DECLARE
/*定义一个变量t_emp使其与EMP 表具有一样的数据类型. 也就是说EMP 表有哪里数据类型的字段,那么这个t_ emp变量也就能够存储什么类型的数据,而且大小范围也是一样的. 形象点说,t_emp就像是一个收纳盒,能装下EMP 表的一行记录. */
t_emp EMP %rowtype;
BEGIN
SELECT * INTO t_emp FROM EMP WHERE EMPNO=7639;
DBMS_OUTPUT.PUT_LINE(t_emp.EMPNO);
DBMS_OUTPUT.PUT_LINE(t_emp.SAL);
END;
3 表达式
数据库中经常使用表达式来计算结果,尤其在变量和常量的使用过程中。它和普通编程语言的表达式很类似。表达式根据操作数据类型的不同可以分为如下几类:
- 数值表达式
- 关系表达式
- 逻辑表达式
3.1 数值表达式
数值表达式就是对数值类型的常量、变量以及函数,由算术运算符连接而成。在PL/SQL里可以使用的算术运算符有:
- 加号+
- 减号-
- 乘号*
- 除号/
- 乘方**
3.2 关系表达式
由关系运算符连接起来的字符或数值称为关系表达式。其中关系运算符主要有以下几种:
关系运算符 |
说明 |
= |
比较两个变量是否相等,如果值相当,则返回 True |
<>, != |
比较两个变量,如果不相等,则返回 True |
< |
比较两个变量,检查值 1 是否小于值 2 |
> |
比较两个变量,检查值 1 是否大于 值 2 |
<= |
比较两个变量,检查变量 1 是否小于等于变量 2 |
>= |
比较两个变量,检查变量 1 是否大于等于变量 2 |
关系表达式最后的结果是一个布尔类型值。
3.3 逻辑表达式
逻辑表达式就是由逻辑符号和常量或变量等组成的表达式。逻辑符号通常有逻辑与AND、逻辑或OR和逻辑非NOT。
4 异常处理
在运行程序时出现的错误叫做异常。发生异常后,语句将停止执行,控制权转移到 PL/SQL 块的异常处理部分。异常有三种类型:
- 预定义异常:当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
- 非预定义异常:Oracle定义好名称的常用异常
- 用户定义异常:用户可以在 PL/SQL 块的声明部分定义异常,自定义的异常通过 RAISE 语句显式引发
其中,预定义异常和非预定义异常都是和Oracle中的错误有关,比如违反数据完整性等;而用户定义异常是人为的为某种特殊情况定义的异常,比如说年龄不能大于150。
异常演示:
declare
v_rslt number(10) := 0;
begin
v_rslt := 100 / 0;
dbms_output.put_line('result is: ' || v_rslt);
end;
/
输出:
4.1 处理异常的语法
为了让程序有更好的阅读性和健壮性,PL/SQL采用了捕获并统一处理异常的方式。语法如下:
EXCEPTION
WHEN exception1 [OR exception2 . . .] THEN
--处理语句。。
[WHEN exception3 [OR exception4 . . .] THEN
--处理语句。。
[WHEN OTHERS THEN --其他异常,省事
--处理语句。。
]
- l EXCEPTION:声明,是异常处理部分开始的标志;
- l WHEN后面接异常名称列表,THEN后面接语句序列,也就是说发生的异常和异常列表里的异常相匹配时,执行指定的语句序列,以完成善后操作。
- l 允许多个WHEN关键词
- l WHEN OTHERS THEN用来处理没有匹配成功的异常。
4.2 预定义异常
Oracle中为每一个错误提供一个错误号,而捕获异常则需要异常有名称。Oracle提供了一些已经定义好名称的常用异常,这就是预定义异常。
上例中使用预定义异常进行捕获:
declare
v_rslt number(10) := 0;
begin
v_rslt := 100 / 0;
dbms_output.put_line('result is: ' || v_rslt);
exception
when zero_divide then
dbms_output.put_line('divide zero! default instead one,so the result is :' || 100 / 1);
end;
/
注:如果是使用PL/SQL Developer工具的话,在SQL window的SQL里面运行,在SQL window的Output查看结果,结果如下:
下面是Oracle预定义异常部分信息表供参考:
异常 |
错误 |
何时出现 |
ACCESS_INTO_NULL |
ORA-06530 |
试图访问未初始化对象的时候出现 |
CASE_NOT_FOUND |
ORA-06592 |
如果定义了一个没有ELSE子句的CASE语句,而且没有CASE语句满足运行时条件时出现该异常 |
COLLECTION_IS_NULL |
ORA-06531 |
当程序去访问一个没有进行初始化的NESTED TABLE或者是VARRAY的时候,会出现该异常 |
CURSOR_ALREADY_OPEN |
ORA-06511 |
游标已经被OPEN,如果再次尝试打开该游标的时候,会出现该异常 |
DUP_VAL_ON_INDEX |
ORA-00001 |
如果插入一列被唯一索引约束的重复值的时候,就会引发该异常(该值被INDEX认定为冲突的) |
INVALID_CURSOR |
ORA-01001 |
不允许的游标操作,比如关闭一个已经被关闭的游标,就会引发 |
INVALID_NUMBER |
ORA-01722 |
给数字值赋非数字值的时候,该异常就会发生,这个异常也会发生在批读取时候LIMIT子句返回非正数的时候 |
LOGIN_DENIED |
ORA-01017 |
程序中,使用错误的用户名和密码登录的时候,就会抛出这个异常 |
NO_DATA_FOUND |
ORA_06548 |
在使用SELECT INTO 结构,并且语句返回NULL值的时候;访问嵌套表中已经删除的表或者是访问INDEX BY表(联合数组)中的未初始化元素就会出现该异常 |
NOT_LOGGED_ON |
ORA-01012 |
当程序发出数据库调用,但是没有连接的时候(通常,在实际与会话断开连接之后) |
PROGRAM_ERROR |
ORA-06501 |
当Oracle还未正式捕获的错误发生时常会发生,这是因为数据库大量的Object功能而发生 |
ROWTYPE_MISMATCH |
ORA-06504 |
如果游标结构不适合PL/SQL游标变量或者是实际的游标参数不同于游标形参的时候发生该异常 |
SELF_IS_NULL |
ORA-30625 |
调用一个对象类型非静态成员方法(其中没有初始化对象类型实例)的时候发生该异常 |
STORAGE_ERROR |
ORA-06500 |
当内存不够分配SGA的足够配额或者是被破坏的时候,引发该异常 |
SUBSCRIPT_BEYOND_COUNT |
ORA-06533 |
当分配给NESTED TABLE或者VARRAY的空间小于使用的下标的时候,发生该异常(类似于java的ArrayIndexOutOfBoundsException) |
SUBSCRIPT_OUTSIDE_LIMIT |
ORA-06532 |
使用非法的索引值来访问NESTED TABLE或者VARRAY的时候引发 |
SYS_INVALID_ROWID |
ORA-01410 |
将无效的字符串转化为ROWID的时候引发 |
TIMEOUT_ON_RESOURCE |
ORA-00051 |
当数据库不能安全锁定资源的时候引发 |
TOO_MANY_ROWS |
ORA-01422 |
常见错误,在使用SELECT INTO 并且查询返回多个行时引发。如果子查询返回多行,而比较运算符为相等的时候也会引发该异常。 |
USERENV_COMMITSCN_ERROR |
ORA-01725 |
只可使用函数USERENV('COMMITSCN')作为INSERT语句的VALUES子句中的顶级表达式或者作为UPDATE语句的SET子句中的右操作数 |
VALUE_ERROR |
ORA-06502 |
将一个变量赋给另一个不能容纳该变量的变量时引发 |
ZERO_DIVIDE |
ORA-01476 |
将某个数字除以0的时候,会发生该异常 |
4.3 非预定义异常
Oracle中为每一个错误提供一个错误号,而捕获异常需要有异常名称,如上例中的ZERO_DIVIDE。而Oracle中更多的是非预定义异常,也就是他们只有错误编号和相关的错误描述,Oracle并没有给他们定义名称,所以不能被捕获。Oracle允许开发人员为这样的异常添加一个名称,使得他们可以被捕获。
为一个非预定义异常定义名称需要两步:
- l 声明一个异常的名称
- l 把这个名称和异常的编号相互关联
非预定义异常示例:
给雇员设置一个不存在的部门编号,违背了数据的完整性,也就是导致ORA-02291错误。
DECLARE
e_integrity EXCEPTION;
PRAGMA EXCEPTION_INIT(e_integrity,-2291);
--2291为Oracle定义的错误号,违背了完整性约束条件
–-给-2291定义一个异常名称叫e_integrity
BEGIN
UPDATE emp SET deptno = 99 WHERE empno = 7788; --发生ORA-02291异常
EXCEPTION
WHEN e_integrity THEN --将异常与e_integrity进行匹配
Dbms_Output.put_line('该部门不存在');
END;
4.4 自定义异常
如果开发当中遇到与实际业务相关的错误,如产品数量不允许为负数,生产日期必须保证在质保日期之前等,这些和业务相关的问题不能算系统错误,也不能使用预定义和非预定义异常来捕获它们。如果想要用异常的方式处理这些问题,那么这样的异常需要开发人员自己编写,而且在调用的时候也需要显示的触发。
如果M_USER表中的数据记录小于20条则抛出数量小于20的异常。示例:
declare
v_qunty number;
my_exp exception;
pragma exception_init(my_exp,-20001);
begin
select count(*) into v_qunty from M_USER t;
if v_qunty < 20 then
raise my_exp;
end if;
exception
when my_exp then
dbms_output.put_line('数量小于20');
dbms_output.put_line(sqlerrm);
when others then
dbms_output.put_line('其他异常');
end;
注:错误号的范围是-20999~-20000的负整数,该范围内的错误是可以随便用的,不用担心被占用。其实定不定义错误信息的编号跟异常的抛出没有关系,个人认为定义编号只是让机器更容易识别和传输,也是为了更好的统一编程规则等。