PL/SQL
PL/SQL
PL/SQL:Procedural Language/SQL
目的：弥补交互式SQL的不足，支持过程
化
应用：
编写存储过程（Procedure）
存储过程由用户、应用程序或触发器显式调用
编写触发器（Trigger）
触发器则是当触发事件发生时由系统触发执行
PL/SQL的块结构
定义部分
DECLARE
……变量，常量 游标 异常等
执行部分
BEGIN
……SQL 语句、流程控制语句
EXCEPTION
……异常处理部分
END;
例题1
向关系Student中插入10个学生，学号从
1001-1010
DECLARE
sno INT;
例题1（续）
BEGIN
sno:=1001;
WHILE sno; --会执行吗？？
END IF;
PL/SQL 基本语法结构（3）
控制结构
– 条件语句
IF THEN
SequenceOfStatements1 >
END IF;
IF THEN
SequenceOfStatements1 >
ELSE
END IF;
– 只有在条件为假或NULL时，ELSE后的语句序列才会被执行
PL/SQL 基本语法结构（4）
例如：检查学生是否在注册表中，若在，
则登陆次数增加一次，否则把该学生插
入注册表中
who logon_num
Peter 3
create table mylog(
who varchar (30), John 4
logon_num int
); Moshe 2
PL/SQL 基本语法结构（5）
DECLARE
cnt int;
user VARCHAR(20) := 'Levi';
BEGIN
select count(*)
into cnt
from mylog
where who = user;
if cnt > 0 then
update mylog
set logon_num = logon_num + 1
where who = user;
else
insert into mylog values(user, 1);
end if;
end;
PL/SQL 基本语法结构（6）
create table
number_table(
循环结构 num int
– 简单的Loop );
– While-Loop
– For-Loop
退出语句（与循环结构搭配）
– Exit --强制无条件退出循环
– Exit－When --满足条件退出循环
举例
PL/SQL 基本语法结构（7）
• 举例： 简单Loop
• Create or replace procedure loop1() as
DECLARE
i int := 1;
BEGIN
LOOP
INSERT INTO number_table
VALUES(i);
i := i + 1;
EXIT WHEN i > 10;
END LOOP;
END;
• Perform procedure loop1();
PL/SQL 基本语法结构（8）
• 举例： While-Loop
• Create or replace procedure loop2() as
DECLARE
TEN int:=10;
i int :=1;
BEGIN
WHILE i );
综合例子：把一个表中的数据复制到另一个表中
CREATE PROCEDURE proc_isopen() AS
DECLARE
c_no CHAR(4);
c_name CHAR(20);
CURSOR mycursor FOR
SELECT lno,lname FROM leader;
BEGIN
OPEN mycursor;
IF mycursor%ISOPEN THEN
LOOP
FETCH mycursor INTO c_no,c_name;
EXIT WHEN (mycursor%NOTFOUND);
INSERT INTO temp vALUES(c_no,c_name);
END LOOP;
END IF;
CLOSE mycursor;
END;
如何在存储过程中应用PL/SQL
创建Procedure：
CREATE [ OR REPLACE ] PROCEDURE
ProcedureName ( [[, ...n]] )AS
[DECLARE [] []]
BEGIN END;
执行Procedure：
PERFORM PROCEDURE ProcedureName( [] );
例1(续)
创建存储过程
CREATE OR REPLACE PROCEDURE Insertsno() AS
DECLARE
snum INT;
BEGIN
snum:=1001;
WHILE snum[, ...n]] )
AS
[DECLARE
[]
[]
]
BEGIN
END;
存储过程的定义（2）
参数定义
{[IN | OUT | INOUT] ParameterName }
[, ...n]
使用说明：
IN，OUT，INOUT参数模式，定义了形式参数的行为。
IN将值传给被调用的子程序; OUT参数将返回值传
给调用者; INOUT参数将初始值传给被调用的子程
序，并将更新后的值返回给调用者; 缺省为IN类型。
注意，OUT 参数在存储过程执行开始时，自动初始
化为NULL值，所以OUT参数参与存储过程的运算，
首先需要赋值。
存储过程的定义（4）
执行Procedure：
PERFORM PROCEDURE ProcedureName( [] );
删除 procedure：
Drop PROCEDURE ProcedureName( [] );
创建触发器（1）
CREATE TRIGGER TriggerName
{ BEFORE | AFTER }
{ | AS };
意义：
– BEFORE 表示在触发事件之前执行触发器。
– AFTER 表示在触发事件之后执行触发器.
– DmlEventClause 表示触发事件，即激活触发器的
DML语句。
• ::={ DELETE | INSERT | UPDATE }
[OR ...n]ON TableName FOR EACH { ROW |
STATEMENT }
Trigger的作用和意义
提供保证数据完整性的方法
– 维护数据完整性
提高共享度，提高效率
– 把多个应用程序访问数据库遵循的规则通过
触发器实现，便于推行规则的实施
Trigger中的临时专用表
DBMS为每个触发器建立两个临时专用表
– Kingbase： new表和old表
– SQLServer：inserted表和deleted表
操作 new表 old表
Insert 存放新插入的新行
delete 存放被插入的行
Update 存放要更新的新行 存放更新前的行
Trigger中的after和before
S(sno,sname,sage,scsum)、
SC(sno,sco,grade)、C(cno,cname)
例1：根据学生选课信息自动修改S表中
的scnum
例2：插入选课记录前检查该学生是否在
S的sno表中
创建触发器说明（2）
说明：
a. DELETE 当从TableName中删除记录时激
活触发器。
b. INSERT 当向TableName中插入记录时激
活触发器。
c. UPDATE 当TableName中的任意column值
发生改变时激活触发器。
d. OR 在指定对象上设置多个激活事件。
创建触发器说明（2）
– TableName 确定创建的触发器所在的表名。
– FOR EACH ROW 表示触发器为行级触发器，
即对于触发语句影响到的每条记录都执行一
次触发器动作。
– FOR EACH STATEMENT 表示触发器为语
句级触发器，即只要相关触发语句执行便激
活触发器进行执行。
–执行存储过程语句
PERFORM PROCEDURE ProcedureName ()
触发器举例(1)
CREATE TABLE tab1(col int);
CREATE TABLE tab2(col int);
INSERT INTO tab1 VALUES(10);
INSERT INTO tab1 VALUES(20);
行级触发
CREATE TRIGGER insert_salary_changes BEFORE
UPDATE ON tab1
FOR EACH ROW
AS
DECLARE
valuediff INT;
BEGIN
valuediff := new.col - old.col;
INSERT INTO tab2 VALUES(valuediff);
END;
执行：UPDATE tab1 SET col = 100;
触发器举例(2)
CREATE TABLE tab1(col int);
CREATE TABLE tab2(col char(15));
INSERT INTO tab1 VALUES(10);
INSERT INTO tab1 VALUES(20);
CREATE PROCEDURE InsertInTab() AS
BEGIN
INSERT INTO tab2 VALUES('tab1 deleted!');
END;
CREATE TRIGGER MyTrigger1 AFTER
DELETE ON tab1
FOR EACH ROW
EXECUTE PROCEDURE InsertInTab();
执行 delete from tab1；结果如何？
触发器举例(3)
语句级触发
CREATE TRIGGER MyTrigger1 AFTER
DELETE ON tab1 FOR
EACH STATEMENT
EXECUTE PROCEDURE InsertInTab();
执行 delete from tab1；结合例子2，结果如何？