第四章 - PL/SQL基础
第四章 - PL/SQL基础
4.1 PL/SQL程序设计概述
SQL语言与过程化的程序语言相比，比较简单，不需要关心底层的数据结构和算法。
然而，对于有些复杂的业务流程，SQL语言就有些无能为力了，比如业务处理中需要循环，需要逻辑判断、分支、需要异常处理等。
PL/SQL的出现正是为了解决这一问题。
4.1.1 PL/SQL
PL/SQL是（Procedure Language & Structured Query Language）的缩写。
它与C,Java等语言一样关注于处理细节，因此可以用来实现比较复杂的业务逻辑。PL/SQL通过增加了用在其它过程性语言中的结构来对SQL进行了扩展，把SQL语言的易用性、灵活性同过程化结构融合在一起。
PL/SQL是一种高级数据库程序设计语言，由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。
目前的PL/SQL包括两部分：
一部分是数据库引擎部分；
另一部分是可嵌入到许多产品（如C语言，Java语言等）工具中的独立引擎。可以将这两部分称为数据库PL/SQL和工具PL/SQL。
4.1.2 PL/SQL的优点
PL/SQL具备了SQL的功能和SQL语言所不具备的过程化功能。优点如下：
模块化
可移植性（可重用性）
安全性（错误处理）
便利性
提高应用程序的运行性能
4.1.3 PL/SQL 块结构
1. PL/SQL程序块
PL/SQL程序的基本结构是块。所有的PL/SQL程序都是由块组成的，一般由三部分组成：声明部分，可执行部分，错误处理部分。
PL/SQL的块结构如下所示:
[DECLARE]
/* 声明部分 -- 这部分包括PL/SQL变量，常量，游标,
用户自定义异常等的定义 */
BEGIN
/* 可执行部分 -- 这部分包括SQL语句及过程化的语句，
这部分是程序的主体 */
[EXCEPTION]
/* 错误处理部分 -- 这部分包括错误处理语句 */
END;
PL/SQL块可以嵌套使用，嵌套块结构如下所示：
[DECLARE]
...... /*说明部分*/
BEGIN
...... /*主块的语句执行部分*/
BEGIN
...... /*子块的语句执行部分*/
[EXCEPTION]
...... /*子块的出错处理程序*/
END;
[EXCEPTION]
...... /*主块的出错处理程序*/
END;
4.1.4 PL/SQL 的注释样式
PL/SQL 支持两种注释样式。
1．单行注释
单行注释以两个连字符“--”开始，可以扩展到行尾。
例如：
v_department_name VARCHAR2(30); --这个变量用来处理部门名称
2．多行注释
这些注释以“/*”开始并以“*/”结束 ，可以跨越多行。建议采用多行注释。
【例4-1】编写一个简单的PL/SQL程序，该程序输出两行文字：“我喜欢学习数据库课程”和“我尤其喜欢Oracle数据库”。程序运行效果如下图所示。
BEGIN
dbms_output.put_line('我很喜欢学习数据库课程！');
dbms_output.put_line('我尤其喜欢Oracle数据库！');
END;
【例4-2】编写一个PL/SQL程序，该程序输出长方形的面积，其中长和宽的值由键盘随机输入。程序运行效果如下图所示。
DECLARE
v_length NUMBER := &length;
v_width NUMBER := &width;
v_area NUMBER;
BEGIN
v_area := v_length * v_width;
dbms_output.put_line('该长方形的面积为：'||v_area);
END;
【例4-3】编写带有嵌套块的PL/SQL程序。
编写一个PL/SQL程序，在PL/SQL主块中输出长方形的面积，在PL/SQL子块中输出长方形的周长，其中长和宽的值由键盘随机输入。程序运行效果如下图所示。
DECLARE /*主块的说明部分*/
v_length NUMBER:=&length;
v_width NUMBER:=&width;
v_area NUMBER;
BEGIN /*主块的语句执行部分*/
DECLARE /*子块的说明部分*/
v_cir number;
BEGIN /*子块的语句执行部分*/
v_cir:=(v_length+v_width)*2;
DBMS_OUTPUT.PUT_LINE('子块中：该长方形的周长为:'||v_cir);
END; /*子块的语句结束*/
v_area:=v_width*v_length;
DBMS_OUTPUT.PUT_LINE('主块中：该长方形的面积为:'||v_area);
END; /*主块的语句结束*/
4.2 变量
4.2.1 PL/SQL 字符集
在 使用PL/SQL进行程序设计时，可以使用的有效字符包括以下3类：
1．所有的大写和小写英文字母。
2. 数字0—9。
3. 符号()、+、-、*、/等。
4.2.2 标识符定义
PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同。要求和限制有：
1．不能超过30个字符。
2．首字符必须为字母。
3．不区分大小写。
4．不能使用SQL保留字。
5．对标识符的命名最好遵循实际项目中相关命名规范。采用的命名规范要求变量以“v_”开头，常量以“c_”开头，以标识符用途来为其命名。
例如：v_lastname表示一个处理名字的变量, c_birthday表示一个处理出生日期的常量。
4.2.3 常量和变量的声明语法
PL/SQL中出现的变量在DECLARE部分定义。
语法如下：
变量名 [CONSTANT] 数据类型 [NOT NULL][ := | DEFAULT PL/SQL表达式]
说明：
1．声明常量时必须加关键字CONSTANT，常量在声明时必须初始化，否则在编译时会出错。
例如：
c_pi CONSTANT NUMBER(8,7) := 3.1415926;
-- 如果没有后面的 “:= 3.1415926” 是没有办法通过编译的。
2．如果一个变量没有进行初始化，它将缺省的被赋值为NULL。如果使用了非空约束，就必须给这个变量赋一个值。
例如：
v_flag VARCHAR2(20) NOT NULL :=‘true’;
-- 而不能
v_flag VARCHAR2(20) NOT NULL;
-- 声明标识符时，要注意每行声明一个标识符。
v_firstname VARCHAR2(20);
v_job VARCHAR2(20);
-- 而不能
v_firstname, v_job VARCHAR2(20);
3．初始化变量可以用 “:=” 或“DEFAULT”，如果没有设置初始值，变量默认被赋值为NULL。
4．变量名称不要和数据库中表名或字段名相同，否则可能会产生意想不到的结果。
建议的命名方法如下表所示：
| 标识符 | 命名规则 | 例子 |
|---|---|---|
| 变量 | v_name | v_name |
| 常量 | c_name | c_name |
| 游标类型变量 | name_cursor | emp_cursor |
| 异常类型变量 | e_name | e_too_many |
| 记录类型变量 | name_record | emp_record |
4.2.4 数据类型
PL/SQL编程中常用的数据类型有标量类型、参考类型、 LOB类型和用户自定义类型。
1．标量类型
可以分为以下四种：
（1）数值型
NUMBER [(precision, scale)]：可存储整数或实数值，这里precision是精度，即数值中所有数字位的个数, scale是刻度范围，即小数点右边数字位的个数。
常用的数值类型为NUMBER。
例如：
v_a1 NUMBER;
v_a2 NUMBER(2):=12;
v_a3 NUMBER(3) DEFAULT 12;
c_a4 CONSTANT NUMBER:=0;
（2）字符型
CHAR [(maximum_length)] ：描述定长的字符串。
VARCHAR2 (maximum_length)：描述变长的字符串。
常用的数值类型为CHAR,VARCHAR2。
例如：
v_c1 CHAR(10);
v_c2 VARCHAR2(10):=‘hello’;
（3）日期型
常用的日期类型为DATE。日期默认格式为DD-MON-YY，分别对应日、月、年。
例如：
v_d1 DATE;
v_d2 DATE :=‘20-12月-2023’;
（4）布尔型
存储逻辑值TRUE或FALSE。
例如：
v_b1 BOOLEAN;
v_b2 BOOLEAN := FALSE;
v_b3 BOOLEAN := TRUE;
2．参考类型
参考类型分为两种，%TYPE和%ROWTYPE。
（1）%TYPE类型
定义一个变量，其数据类型可以与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。
例如：
v_a1 NUMBER;
v_a2 v_a1%TYPE; --v_a2参照自v_a1变量的类型
v_sno student.sno%TYPE;
/* v_sno参照自student.sno表中sno列的类型。*/
（2）%ROWTYPE类型
定义一个变量的类型参照自基本表或视图中记录的类型、或游标的结构类型，这时可以使用%ROWTYPE。
%ROWTYPE前面是表名或游标名。
例如向SC表中插入一条学生的选课成绩信息
Declare
v_sc sc%rowtype; /*v_sc参照自sc表中记录的类型*/
begin
v_sc.sno:='20180001';
v_sc.cno:='c2';
v_sc.grade:=95;
insert into sc
values(v_sc.sno,v_sc.cno,v_sc.grade);
Commit;
end;
说明：由于v_sc能代表sc表中的某一条记录类型，所以在访问该记录中某个特定字段时，可以通过变量名.字段名的方式调用。
【例10-5】 从学生表中查询05880102学生的信息付给变量v_sc并输出
Declare
v_sc student%rowtype;
begin
select * into v_sc from student
where sno='05880102';
dbms_output.put_line('学号='||v_sc.sno||‘
，姓名为'||v_sc.sname);
end;
3．LOB类型
用于存储大的数据对象的类型。Oracle目前主要支持BFILE, BLOB, CLOB 及 NCLOB 类型。
（1）BFILE
存放大的二进制数据对象，这些数据文件不放在数据库里，而是放在操作系统的某个目录里，数据库的表里只存放文件的目录。
（2）BLOB
存储大的二进制数据类型。变量存储大的二进制对象的位置。大二进制对象的大小<=4GB。
（3）CLOB
存储大的字符数据类型。每个变量存储大字符对象的位置，该位置指到大字符数据块。大字符对象的大小<=4GB。
（4）NCLOB
存储大的NCHAR字符数据类型。每个变量存储大字符对象的位置，该位置指到大字符数据块。大字符对象的大小<=4GB。
4．用户自定义类型
根据用户自己的需要，用现有的PL/SQL标量类型组合成一个用户自定义的类型。
例如：定义用户自定义的数据类型student_type
create or replace type student_type
as object(
id number(5),
firstname varchar2(20),
lastname varchar2(20));
例：引用用户自定义的数据类型 v_s1 STUDENT_TYPE;
4.2.5 变量赋值
在PL/SQL程序中可以通过两种方式给变量赋值。
1．直接赋值
-- 变量名 := 常量或表达式;
v_num NUMBER:=5;
2．通过SELECT..INTO赋值
-- SELECT 字段 INTO 变量名
-- 例如：
declare
v_age number;
begin
SELECT AGE INTO v_age FROM STUDENT WHERE SNO=‘05880101';
dbms_output.put_line(‘v_age='||v_age);
end;
【例4-4】查找标量类型变量或常量在声明过程中可能出现的错误，并进行改正。
运行给定的PL/SQL程序代码，进行调试改错，指出错误总数，并最终看到正确的运行结果。程序代码如下：
DECLARE
123_sno CHAR(8);----------------------------------①
sum NUMBER; ----------------------------------- --②
v_date DATE; ----------------------------------------③
v_num NUMBER NOT NULL; -----------------④
c_pi CONSTANT NUMBER(8,7); ----------------⑤
v_cname, v_tname VARCHAR2(10); -----------⑥
BEGIN
DBMS_OUTPUT.PUT_LINE(‘我把所有错误都改正了，真棒！’);
END;
【例4-6】编写带有SELECT…INTO赋值语句的PL/SQL程序编写一个PL/SQL程序，输出学号为20180006的学生姓名和出生年份。程序运行效果如下图所示。
DECLARE
v_sname student.sname%TYPE;
v_birth NUMBER;
BEGIN
SELECT sname,2018-age INTO v_sname,v_birth FROM student WHERE sno=’20180006’;
DBMS_OUTPUT.PUT_LINE('学号为20180006的学生姓名为:'||v_sname||’ 出生年份:’||v_birth);
END;
4.3 PL/SQL中的运算符和函数
4.3.1 PL/SQL运算符
PL/SQL有一组运算符，可以分为3类：算术运算符、关系运算符和逻辑运算符。
1、算术运算符
+（加）、-（减）、*（乘）、/（除）、**（指数）和 ||（连接）。
其中 +（加）和 -（减）运算符也可用于对DATE（日期）数据类型的值进行运算。
2、关系运算符
关系运算符（又称为比较运算符），其运算结果为逻辑值TRUE和FALSE。
关系运算符有以下几种:
（1）=（等于）、<>或！=（不等于）、<（小于）、 >（大于）、>=（大于等于）、<=（小于等于）；
（2）BETWEEN…AND…（检索两值之间的内容）；
（3）IN（检索匹配列表中的值）；
（4）LIKE（检索匹配字符样式的数据）；
（5）IS NULL（检索空数据）。
3、逻辑运算符
逻辑运算符用于对某个条件进行测试，运算结果为TRUE和FALSE。
逻辑运算符有：
（1）AND（两个表达式为真则结果为真）；
（2）OR（只要有一个表达式为真则结果为真）；
（3）NOT（取相反的逻辑值）。
4.3.2 PL/SQL中的函数
在PL/SQL中支持所有SQL中的单行数字型的函数、单行字符型的函数、数据类型的转换函数、日期型的函数和其他各种函数，但不支持聚组函数（如：AVG、COUNT、MIN、MAX、SUM等）。在PL/SQL块内只能在SQL语句中使用聚组函数。
举例说明PL/SQL块中可以引用的各种函数
v_date:=SYSDATE;
v_name:=UPPER(‘Tom’)||LOWER(‘Jerry’);
v_sal:=SUM(salary); /*错误，不能在有赋值运算符的语句中使用聚组函数*/
SELECT SUM(salary) INTO v_sal FROM employees;/*正确，这是SQL语句*/
4.4 PL/SQL 条件结构
4.4.1 条件语句结构
根据另一条语句或表达式的结果执行一个操作或一条语句。分为IF条件语句与CASE条件语句。
1、IF条件语句
语法结构：
IF 条件1 THEN
语句体1;
[ELSIF 条件2 THEN
语句体2;]
…
[ELSE
语句体n;]
END IF;
-- 其中：条件是一个布尔型的变量或表达式。
说明：如果条件1成立，就执行语句体1中的内容，否则判断条件2是否成立，如果条件2成立，执行语句体2的内容，依次类推。如果所有条件都不满足，执行ELSE中语句体n的内容。
注意：每个IF语句以相应的END IF语句结束，IF语句后必需有THEN语句，IF…THEN后不跟语句结束符“;”，一个IF语句最多只能有一个ELSE语句。条件是一个布尔型的变量或表达式。IF条件语句最多只能执行一个条件分支，执行之后跳出整个语句块。
2、CASE条件语句
含SELECTOR(选择符)的CASE语句
语法如下：
CASE SELECTOR
WHEN 表达式1 THEN 语句序列1;
[WHEN 表达式2 THEN 语句序列2;]
...
[WHEN 表达式N THEN 语句序列N;]
[ELSE 语句序列N+1;]
END CASE ;
搜索CASE语句
语法如下：
CASE
WHEN 搜索条件1 THEN 语句序列1;
WHEN 搜索条件2 THEN 语句序列2;
...
WHEN 搜索条件N THEN 语句序列N;
[ELSE 语句序列N+1;]
END CASE;
【例4-8】编写含有IF条件语句的PL/SQL程序，根据某一个学生的平均成绩，判断学生获得的奖学金等级，并输出结果。学号由键盘随机输入，输出等级说明：如果平均成绩>85分，则输出此同学的平均成绩，一等奖学金；如果平均成绩在75分至85分之间，则输出此同学的平均成绩，二等奖学金；否则，输出此同学的平均成绩，无奖学金。
程序运行效果如下图所示。
declare
v_sno student1.sno%TYPE := &sno;
v_grade sc1.grade%TYPE;
begin
select avg(grade) into v_grade from sc1 where sno = v_sno;
if v_grade > 85 then
dbms_output.put_line('此同学平均成绩为：' || v_grade || '，一等奖学金。');
elsif v_grade >= 75 then
dbms_output.put_line('此同学平均成绩为：' || v_grade || '，二等奖学金。');
else
dbms_output.put_line('此同学平均成绩为：' || v_grade || '，无奖学金。');
end if;
end;
【例4-9】编写含有IF条件嵌套语句的PL/SQL程序,完善一个PL/SQL程序，判断随机输入的3个数中的最大值。程序运行效果如下图所示。
declare
-- 定义三个变量储存输入的值
v_a number := &a;
v_b number := &b;
v_c number := &c;
-- 定义一个最大值变量来保存最大值
v_x number ;
begin
-- 判断a是否都比两个数值大，如果都比两个数值大则a就是最大值，如果不是则最大值在剩下的两个变量中
if(v_a > v_b) and (v_a > v_c) then
v_x := v_a;
else
-- 在判断剩下两个
if v_b > v_c then
v_x := v_b;
else
v_x := v_c;
end if;
end if;
dbms_output.put_line('最大值为：' || v_x);
end;
【例4-10】编写含有SELECTOR(选择符)CASE语句的PL/SQL程序，根据给定的PL/SQL程序代码，用CASE语句判断v_grade变量的值是否等于A、B、C、D，并分别处理。如果程序能正常运行，请说明运行结果；如果程序不能正常运行，请说明原因。程序代码如下：
DECLARE
v_grade VARCHAR2(10) := 'B';
BEGIN
CASE v_grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Poor');
ELSE
DBMS_OUTPUT.PUT_LINE('No such grade');
END CASE;
END;
4.5 PL/SQL循环结构
4.5.1 简单循环
简单循环的特点，循环体至少执行一次。
其语法如下：
LOOP
循环体;
[EXIT;] -- 满足退出条件，退出循环
END LOOP
退出循环的语法如下：
IF 条件表达式 THEN
EXIT;
END IF;
EXIT WHEN 条件表达式;
4.5.2 WHILE循环
语法如下：
WHILE 条件表达式
LOOP
循环体;
END LOOP;
说明：当条件表达式为TRUE时，执行循环体中的内容，如果结果为FALSE，则结束循环。
4.5.3 数字式FOR循环
语法如下：
FOR counter IN [REVERSE] start_range..end_range
LOOP
循环体;
END LOOP;
说明：
-
简单LOOP循环和WHILE循环的循环次数都是不确定的，FOR循环的循环次数是固定的；
-
counter是一个隐式声明的变量，不需要在DECLARE部分定义。
-
start_range和end_range指明了循环的次数。
-
REVERSE表示循环变量从最大值向最小值叠代。
【例4-12】利用简单循环LOOP语句实现输出1-10之间的平方数。
declare
i number := 1;
begin
loop
dbms_output.put_line(i || '的平方数为' || i*i);
i := i+1;
if i>10 then
exit;
end if;
end loop;
end;
declare
i number := 1;
begin
loop
dbms_output.put_line(i || '的平方数为' || i*i);
i := i+1;
exit when i>10;
end loop;
end;
declare
i number := 1;
begin
while i<=10
loop
dbms_output.put_line(i || '的平方数为' || i*i);
i := i+1;
end loop;
end;
declare
i number := 1;
begin
for i in 1..10
loop
dbms_output.put_line(i || '的平方数为' || i*i);
end loop;
end;
【例4-13】利用WHILE循环结构求10的阶乘。
declare
n number := 1;
i number := 1;
begin
while i<=10
loop
n := n * i;
i := i + 1;
end loop;
dbms_output.put_line('10的阶乘为：' || n);
end;
declare
n number := 1;
begin
for i in 2..10
loop
n := n * i;
end loop;
dbms_output.put_line('10的阶乘为：' || n);
end;
用反向FOR循环结构求10的阶乘。
declare
n number := 1;
begin
for i in reverse 2..10
loop
n := n * i;
end loop;
dbms_output.put_line('10的阶乘为：' || n);
end;
说明：IN表示循环变量i从小到大依次取值。IN REVERSE表示循环变量i从大到小依次取值。
