Chapter 1: Introduction

Size: px

Start display at page:

Download "Chapter 1: Introduction"

妙郗
4 years ago
Views:

1 数据库系统及应用第 5 章 PL/SQL

2 课程知识结构 Chp.1 数据库系统概述 Chp.2 数据库系统体系结构 Chp.4 SQL Chp.3 关系数据模型 Chp.6 关系数据库模式设计 Chp.9 完整性 Chp.10 安全性 Chp.5 PL/SQL Chp.7 数据库设计 Chp.8 数据库应用系统设计 Chp.11 事务与恢复 Chp.12 并发控制 Chp.13 高级主题数据库系统及应用 2

3 本章主要内容 PL/SQL vs. SQL PL/SQL 编程游标 (Cursor) 存储过程 (Stored Procedure) 触发器 (Trigger) 数据库系统及应用 3

4 一 PL/SQL 与 SQL SQL 是描述性语言,PL/SQL 是过程性语言 PL/SQL 是 Oracle 对 SQL 的一个扩展, 是一种过程化的程序设计语言 SQL 本身并不能建立数据库应用程序 PL/SQL 是包含 SQL 的一种过程性语言, 它不仅支持 SQL, 还支持一些过程性语言特性其它商用 DBMS 一般也都提供类似的扩展 Microsoft T-SQL Informix E-SQL 数据库系统及应用 4

5 一 PL/SQL 与 SQL 二者均可以在 Oracle 数据库中运行, 可以相互调用 Oracle SQL 引擎 SQL 查询 PL/SQL 调用 PL/SQL 引擎数据库系统及应用 5

6 一 PL/SQL 与 SQL 从 A 账号转帐 100 到 B 账号 SQL Total 5 客户端多次计算多次网络传输 SQL Result 客户机 1 通过 SQL 查询账号 A 是否存在 2 通过 SQL 查询账号 B 是否存在 3 查询账号 A 上的余额 4 修改 A 上的余额 5 修改 B 上的余额客户端网络 SQL 结果 Oracle 数据库文件客户端 SQL Oracle DBMS 数据库系统及应用 6

7 一 PL/SQL 与 SQL 从 A 账号转帐 100 到 B 账号 Total 1 客户端更少计算更少网络传输客户机执行一个预先编写好并存储在服务器上的 PL/SQL 程序完成转帐 SQL 调用 PL/SQL 程序 Result 任务都加在这儿了客户端 SQL 网络结果 Oracle 数据库文件客户端 SQL Oracle DBMS 数据库系统及应用 7

8 一 PL/SQL 与 SQL 在程序中使用 SQL 客户机计算任务多网络传输重服务器计算任务少在程序中使用 PL/SQL 可以完成一些 SQL 不能完成的复杂计算客户机计算任务少服务器计算任务加重网络传输少胖客户机瘦服务器瘦客户机胖服务器数据库系统及应用 8

9 二 PL/SQL 程序的结构 DECLARE -- 变量声明, 必须放在首部 BEGIN EXCEPTION 程序体 END 数据库系统及应用 9

10 1 一个例子 : 返回 001 学生的姓名 DECLARE name varchar2(20); BEGIN Select sname Into name From Student Where s#= 001 ; DBMS_OUTPUT.PUT_LINE( 学号 001 的学生姓名是 : sname) EXCEPTION When NO_DATA_FOUND Then DBMS_OUTPUT.PUT_LINE( 学号为 001 的学生不存在 ); When others Then DBMS_OUTPUT.PUT_LINE( 发生了其它错误 ); END; 数据库系统及应用 10

11 赋值注释 2 PL/SQL 的编程 := Select Into < 变量 > From -- 运行 / 数据库系统及应用 11

12 必须放在 DECLARE 段 < 变量名 > < 类型 > 三变量声明如果编写过程或函数, 则过程首部将取代 Declare 段 Create Procedure IS 变量声明 BEGIN 数据库系统及应用 12

13 例 1: 一般性声明 1 变量声明例子 Declare sno Number(3); name Varchar2(10); 例 2: 声明为正在使用的表中的某个列类型 Declare sno student.s#%type; name student.sname%type; 可以保证代码与数据库结构之间的独立性数据库系统及应用 13

14 1 变量声明例子例 3: 声明一个记录类型记录类型是由多个相关变量构成的一个单元 TYPE < 记录名 > IS RECORD ( 变量 1 类型 1, 变量 2 类型 2, ) 通过定义记录类型, 可以保存表中的记录数据库系统及应用 14

15 例 3: 声明记录类型 1 变量声明例子通过记录类型变量. 成员名访问内部成员 DECLARE BEGIN END; TYPE stu IS RECORD( s# varchar2(10), name varchar2(10), age number ); val stu; -- 声明了一个记录类型的变量 val Select * Into val From Student Where s#= 001 ; DBMS_OUTPUT.PUT_LINE( 学号 001 的学生姓名是 : val.name) 数据库系统及应用 15

16 1 变量声明例子例 4: 声明为一个表的行类型 Declare stu Student%ROWTYPE; Stu 被声明为与 student 表相匹配的记录类型, student 表的列自动称为 stu 的成员 Stu.s# Stu.sname Stu.age 数据库系统及应用 16

17 四分支控制语句 IF < 表达式 > THEN < 语句 > ELSEIF < 表达式 > THEN < 语句 > IF x=5 THEN ELSE < 语句 > END IF; END IF; DBMS_OUTPUT.PUT_LINE( x=5 ); 数据库系统及应用 17

18 五循环语句 WHILE 循环 FOR 循环 LOOP 循环数据库系统及应用 18

19 1 WHILE 循环 While < 表达式 > Loop < 语句 > End Loop; 当表达式为真时执行语句 Declare Begin END ; x Number; total Number; x:=1; total:=0; While x<=100 Loop End Loop total:=total+x; x:=x+1; 数据库系统及应用 19

20 2 FOR 循环 For < 计数变量 > In [Reverse] < 开始值 >..< 结束值 > Loop < 语句 > End Loop; 循环体每执行一次计数变量自动加 1 若有 Reverse, 则每次循环计数变量自动减 1 Declare Begin END ; x Number; total Number; total:=0; For x In Loop End Loop total:=total+x; 数据库系统及应用 20

21 3 LOOP 循环 Loop < 语句 > End Loop 无内部控制结构的循环结构, 循环执行其中的 < 语句 > 必须在循环体中显式地结束循环 Exit 和 Exit When 两种方式退出循环 Declare x Number:=1; total Number:=0; Begin Loop If x<=100 Then total:=total+x; x:=x+1; Else Exit; End If End Loop END ; Declare x Number:=1; total Number:=0; Begin Loop total:=total+x; x:=x+1; Exit When x>100 End Loop END ; 数据库系统及应用 21

22 Exception 六处理异常可以捕捉程序运行中出现的错误或意外情况, 并加以处理 Exception When < 错误名 1> OR < 错误名 2> Then < 错误处理语句 > When < 错误名 1> OR < 错误名 2> Then < 错误处理语句 > When Others Then < 错误处理语句 > 数据库系统及应用 22

PUT_LINE( 学号 001 的学生姓名是 : sname) EXCEPTION When NO_DATA_FOUND Then

23 1 例子 : 返回 001 学生的姓名 DECLARE name varchar2(20); BEGIN Select sname Into name From Student Where s#= 001 ; DBMS_OUTPUT.PUT_LINE( 学号 001 的学生姓名是 : sname) EXCEPTION When NO_DATA_FOUND Then 系统定义的标准异常 DBMS_OUTPUT.PUT_LINE( 学号为 001 的学生不存在 ); When others Then DBMS_OUTPUT.PUT_LINE( 发生了其它错误 ); END; 数据库系统及应用 23

24 2 系统定义的标准异常 ( 共 20 种 ) NO_DATA_FOUND 执行 Select Into 语句却没有找到匹配记录 TOO_MANY_ROWS 执行 Select Into 语句却返回了多行记录 VALUE_ERROR 变量赋值错误, 可能是类型不匹配, 或者是值太大或太长 ZERO_DIVIDE 除零错误 TIMEOUT_ON_RESOURCE 资源等待超时数据库系统及应用 24

25 直接生成异常 3 人工生成异常 Raise_application_error 声明并触发一个自定义异常 Raise Raise_application_error 数据库系统及应用 25

26 (1) 直接生成异常 Raise_application_error ( 自定义错误号 ~-20999, 错误信息 ) -- 插入一个新学生 001 DECLARE sno varchar2(20); BEGIN Select s# Into sno From Student Where s#= 001 ; If SQL%FOUND Then raise_application_error(-20001, 学生已存在 ); Else Insert Into student(s#) values( 001 ); End If END; 数据库系统及应用 26

27 (2) 声明并触发一个自定义异常 -- 插入一个新学生 001 DECLARE sno varchar2(20); exp Exception; -- 声明一个 Exception 变量 BEGIN Select s# Into sno From Student Where s#= 001 ; If SQL%FOUND Then raise exp; -- 生成一个异常 Else Insert Into student(s#) values( 001 ); End If EXCEPTION END; Raise_application_error 返回一个异常, 而 PUT_LINE 直接输出异常信息 When exp Then raise_application_error(-20001, 学生已存在 ); When others Then DBMS_OUTPUT.PUT_LINE( 发生了其它错误 ); 数据库系统及应用 27

28 DECLARE SQLCODE BEGIN 4 两个系统参数代表标准异常的错误号 SQLERRM EXCEPTION 代表标准异常的错误描述 When NO_DATA_FOUND Then DBMS_OUTPUT.PUT_LINE( 数据不存在 ); When others Then DBMS_OUTPUT.PUT_LINE ( 错误号 : SQLCODE 错误描述 : SQLERRM); END; 数据库系统及应用 28

29 游标概念游标操作游标属性使用游标 FOR 循环操纵游标的当前行七游标数据库系统及应用 29

30 1 游标概念动机 PL/SQL 是过程性语言, 每次只能处理单个记录 ; 而 SQL 是描述性语言, 每次可以处理多行记录问题 : PL/SQL 如何支持多行记录的操作? 解决方法 : 游标游标是客户机或数据库服务器上开辟的一块内存, 用于存放 SQL 返回的结果游标可以协调 PL/SQL 与 SQL 之间的数据处理矛盾 PL/SQL 中可以通过游标来过程化存取 SQL 返回的结果 SQL:select 游标指针游标对应的内存 PL/SQL: 操作游标数据库系统及应用 30

31 2 游标操作声明一个游标打开游标读取游标中的记录关闭游标一般的操作顺序数据库系统及应用 31

32 (1) 声明游标 Declare Cursor < 名称 > IS <Select 语句 > 声明中的 SQL 语句在声明时并不执行, 只是给出了游标对应的数据定义 -- 声明一个游标, 用于存放所有学生记录 DECLARE Cursor cs_stu IS select * from student; 数据库系统及应用 32

33 Open < 游标名 > (2) 打开游标打开游标时,SELECT 语句被执行, 其结果放入了游标中 -- 声明一个游标, 用于存放所有学生记录 DECLARE Cursor cs_stu IS select * from student; BEGIN Open cs_stu; 数据库系统及应用 33

34 (3) 读取游标中的记录 Fetch < 游标名 > Into < 变量表或记录变量 > 打开游标后, 游标指向了第一条记录 Fetch 后指向下一条记录若要读取游标中的数据, 一般需使用一个循环 -- 返回所有学生记录 DECLARE Cursor cs_stu IS select * from student; stu student%rowtype; BEGIN Open cs_stu; Fetch cs_stu Into stu; While cs_stu%found Loop DBMS_OUTPUT.PUT_LINE( ); Fetch cs_stu Into stu; End Loop; 数据库系统及应用 34

35 (4) 关闭游标 Close < 游标名 > -- 返回所有学生记录的学号和姓名 DECLARE Cursor cs_stu IS select s#,sname from student; sno student.s#%type; name student.sname%type; BEGIN Open cs_stu; Loop Fetch cs_stu Into sno,name; Exit When cs_stu%notfound; DBMS_OUTPUT.PUT_LINE( ); End Loop; Close cs_stu; END; 数据库系统及应用 35

36 3 游标属性 PL/SQL 使用游标属性判断游标的当前状态 Cursor%FOUND 布尔型, 当前 FETCH 返回一行时为真 Cursor%NOTFOUND 布尔型, 当前 FETCH 没有返回一行时为真 Cursor%ISOPEN 布尔型, 若游标已经打开则为真 Cursor%ROWCOUNT 数值型, 显示目前为止已从游标中取出的记录数数据库系统及应用 36

37 4 使用游标 FOR 循环由于游标总是使用循环处理, 因此可以简化这种处理过程游标 FOR 循环 : 简化了游标操作自动声明一个与游标中的数据记录类型一致的变量, 并自动打开游标, 读取游标, 并在读完后自动关闭游标 -- 返回所有学生记录 DECLARE Cursor cs_s IS select * from student; BEGIN For s IN cs_s Loop DBMS_OUTPUT.PUT_LINE( ); End Loop; END; 自动声明 s 打开游标读取数据并关闭游标数据库系统及应用 37

38 5 带参游标 Declare Cursor < 名称 >( 参数表 ) IS <Select 语句 > -- 返回给定年龄的学生记录 DECLARE Cursor cs_s( val Number(3) ) IS select * from student where DeptID=val; BEGIN For s IN cs_s(11) Loop DBMS_OUTPUT.PUT_LINE( ); End Loop; END; 数据库系统及应用 38

39 八 PL/SQL 的输入输出输出 : 使用 DBMS_OUTPUT 包输入 PUT_LINE( ): 输出并换行 PUT( ): 输出但不换行 NEWLINE: 生成一个新行 -- 返回给定年龄的学生记录 DECLARE Cursor cs_s IS select * from student where age=&val; BEGIN For s IN cs_s Loop DBMS_OUTPUT.PUT_LINE( ); End Loop; END; 运行时将提示输入 val 的值数据库系统及应用 39

40 匿名 PL/SQL 块九存储过程和函数以 DECLARE/BEGIN 开始, 每次运行都要编译, 程序在数据库中不保存命名 PL/SQL 块可以存储在数据库中, 可以随时运行, 也可以被 SQL 或其它程序调用存储过程函数触发器包数据库系统及应用 40

41 1 存储过程概念一类存储在数据库中的 PL/SQL 程序, 可以通过过程名调用数据库系统及应用 41

42 1 存储过程概念 Client/Server 计算模式 : 客户端将业务处理任务交给服务器上的存储过程完成 SQL 存储过程位于数据库服务器端客户端 SQL 网络结果 Oracle 数据库文件客户端 SQL Oracle DBMS 数据库系统及应用 42

43 Create or Replace Procedure AddStudent( v_s# IN varchar2, v_sname IN varchar2, v_age IN number) AS BEGIN Insert Into student(s#,sname,age) Values(v_s#,v_sname,v_age); END; 可在其它 PL/SQL 程序中使用可在 SQL PLUS 中直接调用运行 SQL>Execute AddStudent( 001, John,21); BEGIN. AddStudent(s,n,a); --s, n, a 是变量 END; 数据库系统及应用 43

44 2 存储过程定义 Create or Replace Procedure < 名称 >( 参数表 ) AS IS 变量定义 BEGIN PL/SQL 代码 EXCEPTION 错误处理 END; 与匿名 PL/SQL 程序格式相同数据库系统及应用 44

45 3 参数定义参数名 IN OUT IN OUT 数据类型 [:= 默认值 ] 例 name IN varchar2, result OUT number IN 参数输入参数, 在程序中不能修改 OUT 参数输出参数, 在程序中只能对其赋值 IN OUT 既可作为 IN 参数使用, 也可作为 OUT 参数使用数据库系统及应用 45

46 4 删除存储过程 Drop Procedure < 存储过程名 > 数据库系统及应用 46

47 具有返回值的存储过程 5 函数 Create or Replace Function < 名称 >( 参数表 ) RETURN < 类型 > AS IS 变量定义 BEGIN PL/SQL 代码 EXCEPTION 错误处理 END; 数据库系统及应用 47

48 例 : 创建返回一个系的学生总人数的函数 Create or Replace Function StudentCount(DeptNo IN varchar2) Return Number AS v_count Number:=0; BEGIN select count(s#) Into v_count From Student where dept=deptno; return v_count; END; 使用函数 Declare a number:=0; BEGIN a:=studentcount( cs ); DBMS_OUTPUT.PUT_LINE( cs 系的学生人数为 a); END; 数据库系统及应用 48

49 参数类型 6 使用过程和函数的注意点不能指定长度 ( 与变量定义不同 ) 变量 :name varchar2(20) 必须指定长度参数 :name IN varchar2 不能指定长度可使用 %TYPE 参数传递按位置 :sam(1,2,3,4) 命名传递 :sam(b=>2,a=>1,d=>4,c=>3) 与位置无关 IN,OUT,IN OUT 参数的使用数据库系统及应用 49

50 总结 : 存储过程的主要作用增强了 SQL 语言的功能和灵活性, 可以完成复杂的判断和运算可增强数据库的安全性通过存储过程可以使没有权限的用户在控制之下间接地存取数据库, 从而保证数据的安全可增强数据库的完整性在运行存储过程前, 数据库已对其进行了语法和句法分析, 并给出了优化执行方案由于执行 SQL 语句的大部分工作已经完成, 所以存储过程能以较快的速度执行可以降低网络的通信量使体现企业规则的运算程序放入数据库服务器中, 以便集中控制缺点 : 编写调试和使用较复杂数据库系统及应用 50

51 十触发器 (Trigger) 触发器的概念触发器的种类触发器的创建触发器的触发顺序 :old 和 :new 系统变量数据库系统及应用 51

52 1 触发器的概念与特定表关联的存储过程当在该表上执行 DML 操作时, 可以自动触发该存储过程执行相应的操作触发操作 :Update Insert Delete 通过触发器可以定制数据库对应用程序文件的反应一个触发器只能属于一个表, 一个表可有多个触发器数据库系统及应用 52

53 2 触发器概念示例 Student(s#, sname, age, status) Sc( s#, c#, score) 规定当学生有 3 门课不及格时, 将该学生的 status 标记为不合格通过 SC 上的触发器实现 : 当在 SC 中插入或更新记录时, 自动检查是否有学生满足不合格条件 S# Sname age status 01 aaa 22 合格 02 bbb 21 合格插入该记录后 01 学生的 status 自动改为不合格 S# C# Score 01 c c c c3 55 数据库系统及应用 53

54 3 Oracle 触发器的种类按执行先后先触发器 (Before Trigger): 在 DML 语句执行之前触发后触发器 (After Trigger): 在 DML 语句执行之后触发按执行方式行级触发器 : 对由触发的 DML 语句所导致变更的每一行触发一次 ( 一个 DML 语句可能触发多次 ) 语句级触发器 : 一个 DML 语句触发一次 ( 只触发一次 ) 数据库系统及应用 54

55 4 触发器的创建 Create or Replace Trigger < 名称 > [Before After Delete Insert Update [Of < 列名表 >] [OR 定义触发动作 Before After Delete Insert Update [Of < 列名表 >] ] ON < 表名 > [For Each Row] <PL/SQL 块 > END; 先触发器还是后触发器是否定义为行级触发器注意 : 没有参数因为触发器是自动执行的, 不能向它传参数数据库系统及应用 55

56 5 系统变量 :old 和 :new 对于行级触发器, 系统变量 :old 和 :new 存储每一行的更新前值 (:old) 和更新后值 ( :new) 可以在触发器程序中需要时访问它们操作变量下 Insert Update Delete :old 的值空原记录删除的记录 :new 的值新记录新记录空数据库系统及应用 56

57 6 触发器例子 : 自动更新学生状态 Create or Replace Trigger SetStatus After Insert Or Update Of score on SC For Each Row Declare Begin End; a Number:=0; Select count(*) into a From SC where s#:= :new. s# and score<60; If a>=3 Then Else End If 行级触发器 Why? Update student Set status= 不合格 Where s#= :new. s#; Update student Set status= 合格 Where s#= :new. s#; 数据库系统及应用 57

58 7 触发器例子 : 自动统计学生人数学校表 :University(U#,uname, s_count) 学生表 :Student(U#, s#, sname, age) Create or Replace Trigger TotalStudent After Insert or Delete On Student Declare a Number:=0; Begin Select count(*) into a From Student ; Update University Set s_count=a; End; 语句级触发器数据库系统及应用 58

59 8 触发器的触发顺序 1. 语句级先触发器 2. 对于受语句影响的每一行行级先触发器执行语句行级后触发器 3. 语句级后触发器数据库系统及应用 59

60 总结 : 触发器的主要作用强化约束 : 触发器能够实现复杂的约束跟踪变化 : 触发器可以侦测数据库内的操作, 可以用来实施审计, 以及不允许数据库中未经许可的更新和变化级联运行 : 触发器可以侦测数据库内的操作, 并自动地级联影响整个数据库的各项内容缺点 : 影响性能 ; 潜在的运行错误风险谨慎使用! 数据库系统及应用 60

61 PL/SQL 与 SQL PL/SQL 程序要素游标存储过程触发器本章小结数据库系统及应用 61

第 7 章扩展实践教学 SQL Server 数据库项目化教程 ( 主编 : 李蕾北京师范大学出版社 ) 扩展实践以随书赠送的素材库中的数据库实例 : 教务管理系统为例, 要求 : 附加教务管理系统数据库至 SQL Server 2008 数据库服务器中扩展实践 7-1 在教务管理系统数

第 7 章扩展实践教学 SQL Server 数据库项目化教程 ( 主编 : 李蕾北京师范大学出版社 ) 扩展实践以随书赠送的素材库中的数据库实例 : 教务管理系统为例, 要求 : 附加教务管理系统数据库至 SQL Server 2008 数据库服务器中扩展实践 7-1 在教务管理系统数扩展实践以随书赠送的素材库中的数据库实例 : 教务管理系统为例, 要求 : 附加教务管理系统数据库至 SQL Server 2008 数据库服务器中扩展实践 7-1 在教务管理系统数据库的学生信息表中定义一个基于 set 赋值语句, 将学生信息统计查询出的学生总人数赋值给局部变量 @num, 并用 print 语句输出 declare @num int set @num=(select