ch5 数据库系统概念(第6版)第五章高级SQL
- 格式：pdf
- 大小：1.88 MB
- 文档页数：96
下载文档原格式
合集下载
数据库系统概论(第五版)()演示文稿
第30页，共95页。
删除模式（续）
[例3.4] DROP SCHEMA ZHANG CASCADE; 删除模式ZHANG 同时该模式中定义的表TAB1也被删除
第31页，共95页。
3.3 数据定义
3.3.1 模式的定义与删除 3.3.2 基本表的定义、删除与修改
3.3.3 索引的建立与删除
第32页，共95页。
/* 表级完整性约束条件，Sno是外码，被参照表是Student */
FOREIGN KEY (Cno)REFERENCES Course(Cno)
/* 表级完整性约束条件， Cno是外码，被参照表是Course*/
);
第36页，共95页。
2. 数据类型
❖ SQL中域的概念用数据类型来实现 ❖ 定义表的属性时需要指明其数据类型及长度 ❖ 选用哪种数据类型
课程号 Cno 1 2 3 2 3
成绩 Grade 92 85 88 90 80
第22页，共95页。
第三章 关系数据库标准语言SQL
3.1 SQL概述 3.2 学生-课程数据库 3.3 数据定义 3.4 数据查询 3.5 数据更新 3.6 空值的处理 3.7 视图 3.8 小结
第23页，共95页。
[例3.2] CREATE SCHEMA AUTHORIZATION WANG; 该语句没有指定<模式名>，<模式名>隐含为<用户名>
第27页，共95页。
定义模式（续）
❖ 定义模式实际上定义了一个命名空间。 ❖ 在这个空间中可以定义该模式包含的数据库对象，
例如基本表、视图、索引等。 ❖在CREATE SCHEMA中可以接受CREATE
❖ SQL采用集合操作方式
删除模式（续）
[例3.4] DROP SCHEMA ZHANG CASCADE; 删除模式ZHANG 同时该模式中定义的表TAB1也被删除
第31页，共95页。
3.3 数据定义
3.3.1 模式的定义与删除 3.3.2 基本表的定义、删除与修改
3.3.3 索引的建立与删除
第32页，共95页。
/* 表级完整性约束条件，Sno是外码，被参照表是Student */
FOREIGN KEY (Cno)REFERENCES Course(Cno)
/* 表级完整性约束条件， Cno是外码，被参照表是Course*/
);
第36页，共95页。
2. 数据类型
❖ SQL中域的概念用数据类型来实现 ❖ 定义表的属性时需要指明其数据类型及长度 ❖ 选用哪种数据类型
课程号 Cno 1 2 3 2 3
成绩 Grade 92 85 88 90 80
第22页，共95页。
第三章 关系数据库标准语言SQL
3.1 SQL概述 3.2 学生-课程数据库 3.3 数据定义 3.4 数据查询 3.5 数据更新 3.6 空值的处理 3.7 视图 3.8 小结
第23页，共95页。
[例3.2] CREATE SCHEMA AUTHORIZATION WANG; 该语句没有指定<模式名>，<模式名>隐含为<用户名>
第27页，共95页。
定义模式（续）
❖ 定义模式实际上定义了一个命名空间。 ❖ 在这个空间中可以定义该模式包含的数据库对象，
例如基本表、视图、索引等。 ❖在CREATE SCHEMA中可以接受CREATE
❖ SQL采用集合操作方式
数据库系统概论 高等教育出版社chp5a
5.2.1 参照完整性定义
5.2.2 参照完整性检查和违约处理
5.2.1 参照完整性定义
关系模型的参照完整性定义 – 在CREATE TABLE中用FOREIGN KEY短语定义哪些 列为外码 – 用REFERENCES短语指明这些外码参照哪些表的主码
例如，关系SC中一个元组表示一个学生选修的某门课程的成 绩，（Sno，Cno）是主码。Sno，Cno分别参照引用 Student表的主码和Course表的主码 ［例3］ 定义SC中的参照完整性 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT， PRIMARY KEY (Sno， Cno)， /*在表级定义实体完 整性*/ FOREIGN KEY (Sno) REFERENCES Student(Sno)， /*在表级定义参照完整性*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) /*在表级定义参照完整性*/ );
[ON{filegroup|DEFAULT}]
例：修改学生基本信息表STUDENT，为其添加主 键约束。 ALTER TABLE STUDENT ADD CONSTRAINT PK_ST PRIMARY KEY(SNO) GO
5.1 实体完整性
5.1.1 实体完整性定义
5.1.2 实体完整性检查和违约处理
5.1.2 实体完整性检查和违约处理
插入或对主码列进行更新操作时，RDBMS按照实 体完整性规则自动进行检查。包括： 1. 检查主码值是否唯一，如果不唯一则拒绝插入或修改
2. 检查主码的各个属性是否为空，只要有一个为空就拒 绝插入或修改
检查记录中主码值是否唯一的方法：
5.2.2 参照完整性检查和违约处理
5.2.1 参照完整性定义
关系模型的参照完整性定义 – 在CREATE TABLE中用FOREIGN KEY短语定义哪些 列为外码 – 用REFERENCES短语指明这些外码参照哪些表的主码
例如，关系SC中一个元组表示一个学生选修的某门课程的成 绩，（Sno，Cno）是主码。Sno，Cno分别参照引用 Student表的主码和Course表的主码 ［例3］ 定义SC中的参照完整性 CREATE TABLE SC (Sno CHAR(9) NOT NULL， Cno CHAR(4) NOT NULL， Grade SMALLINT， PRIMARY KEY (Sno， Cno)， /*在表级定义实体完 整性*/ FOREIGN KEY (Sno) REFERENCES Student(Sno)， /*在表级定义参照完整性*/ FOREIGN KEY (Cno) REFERENCES Course(Cno) /*在表级定义参照完整性*/ );
[ON{filegroup|DEFAULT}]
例：修改学生基本信息表STUDENT，为其添加主 键约束。 ALTER TABLE STUDENT ADD CONSTRAINT PK_ST PRIMARY KEY(SNO) GO
5.1 实体完整性
5.1.1 实体完整性定义
5.1.2 实体完整性检查和违约处理
5.1.2 实体完整性检查和违约处理
插入或对主码列进行更新操作时，RDBMS按照实 体完整性规则自动进行检查。包括： 1. 检查主码值是否唯一，如果不唯一则拒绝插入或修改
2. 检查主码的各个属性是否为空，只要有一个为空就拒 绝插入或修改
检查记录中主码值是否唯一的方法：
数据系统 -ch05
Database System Concepts - 6th Edition 5.9 ©Silberschatz, Korth and Sudarshan
Metadata Features
ResultSet metadata
E.g., after executing query to get a ResultSet rs:
Execute query and fetch and print results
ResultSet rset = stmt.executeQuery( "select dept_name, avg (salary) from instructor group by dept_name"); while (rset.next()) { System.out.println(rset.getString("dept_name") + " " + rset.getFloat(2)); }
relations present in the database and the names and types of relation attributes.
Model for communicating with the database:
Open a connection Create a “statement” object Execute queries using the Statement object to send queries and fetch results Exception mechanism to handle errors
Database System Concepts - 6th Edition
ch5 数据库系统概念(第6版)第五章高级SQL
函数/过程可以用SQL语言书写，也可以使用其他外部 程序语言. 函数对于特殊新的数据类型，例如图片和几何对象特 别有用. 例如: 用于检查多边形是否重叠，或者比较图片相 似性的函数. 一些数据库系统支持表值函数, 返回一个关系作为结果. 循环, if-then-else, 赋值
SQL:1999 还支持大量的命令式结构，例如
存储过程
存储过程的优点：
使用存储过程可以减少网络流量 增强代码的重用性和共享性 使用存储过程可以加快系统运行速度 使用存储过程保证安全性
存储过程的创建
写SQL语句 测试SQL语句 如得到所需结果，则创建结果 执行过程
触发器
触发器
触发器 是一条语句，当对数据库做修改时，它自动被系 统执行. 要设置触发器机制，必须满足: 指明什么条件下触发器被执行. 指明触发器执行的动作是什么. SQL-92 标准并不包括触发器，但是许多DB系统支持触发 器。 触发器于SQL:1999被引进到SQL标准 , 但是更早就通过非 标准语法被大部分数据库所支持.
SQL允许用if-then-else语句，for和while循环，等等 ，来定义过程.
存储过程
可以在数据库中存储过程 然后通过call语句来执行 允许外部应用程序对数据库进行操作，而无需了解内 部细节
面向对象方面将在22章介绍 (基于对象的数据库)*
函数和过程
SQL:1999 支持函数和过程
过程结构*
注意: 大部分数据库系统对下列标准语法实现了自 己的变种 复合语句: begin … end, While 和 repeat 语句:
end while
repeat
set n = n + 1
SQL:1999 还支持大量的命令式结构，例如
存储过程
存储过程的优点：
使用存储过程可以减少网络流量 增强代码的重用性和共享性 使用存储过程可以加快系统运行速度 使用存储过程保证安全性
存储过程的创建
写SQL语句 测试SQL语句 如得到所需结果，则创建结果 执行过程
触发器
触发器
触发器 是一条语句，当对数据库做修改时，它自动被系 统执行. 要设置触发器机制，必须满足: 指明什么条件下触发器被执行. 指明触发器执行的动作是什么. SQL-92 标准并不包括触发器，但是许多DB系统支持触发 器。 触发器于SQL:1999被引进到SQL标准 , 但是更早就通过非 标准语法被大部分数据库所支持.
SQL允许用if-then-else语句，for和while循环，等等 ，来定义过程.
存储过程
可以在数据库中存储过程 然后通过call语句来执行 允许外部应用程序对数据库进行操作，而无需了解内 部细节
面向对象方面将在22章介绍 (基于对象的数据库)*
函数和过程
SQL:1999 支持函数和过程
过程结构*
注意: 大部分数据库系统对下列标准语法实现了自 己的变种 复合语句: begin … end, While 和 repeat 语句:
end while
repeat
set n = n + 1
ch05-数据库原理及应用-SQL Server 2014-夏保芹-清华大学出版社
前 目录 后 第5章 数据库和表
• 2．事务日志文件 • 事务日志文件保存用于恢复数据库中的所有
事务所需的信息。每个数据库必须至少有一 个事务日志文件。事务日志文件的扩展名是 .ldf。
前 目录 后 第5章 数据库和表
• 3．文件组 • 文件组是数据库组织文件的一种管理机制，是文件的集合，用于
简化数据存放和管理。文件组分为主文件组和用户定义文件组。 • 1）主文件组 • 每个数据库有一个主文件组。主文件组包含主数据文件和未放入
• 创建数据库可以使用CREATE DATABASE语句，其基本语法格式 如下：
• CREATE DATABASE database_name • [ON [PRIMARY] <filespec>[,...n] [<filegroup>
<filespec>[,...n]][,...n] • [LOG ON <filespec>[,...n]]] • 其中各参数含义如下： • database_name：指定所创建的数据库的逻辑名称。 • ON子句：指定数据库的数据文件和文件组。 • LOG ON子句：指定数据库的事务日志文件的属性。 • <filespec>::={(NAME=logical_file_name, • FILENAME='os_file_name' • [,SIZE=size[KB|MB|GB|TB]] • [,MAXSIZE={max_size[KB|MB|GB|TB]|UNLIMITED}] • [,FILEGROWTH=growth_increment[KB|MB|GB|TB|%]])}
前 目录 后 第5章 数据库和表
• 5.1.2 数据文件和文件组 • 每个SQL Server 2014数据库至少具有两个
大学计算机教程第六版 第5章数据库技术应用
3．连接运算（Join简记为JN）
连接是从关系R与S的笛卡尔积中，选取R的第i 个属性值和S的第j个属性值之间满足一定条件表达 式，构成关系子集。
关系R与T作θ连接。如表5.13和表5.14所示。
5.6.1 数据库设计理论的应用 数据库数据相关性称为数据依赖。数据依赖主
要分为内在关系的函数依赖（简记FD）和多值依赖 （简记MVD）。
把关系看成一个集合，集合运算如并、交、差、 笛卡尔积等运算，均可用到关系运算中。
设有三个关系实例R、S和T，如表5.6所示。
关系R和S的并是由属于R或S或同时属于R和S的 元组组成的集合，记为R∪S，如表5.7所示。
2. 差运算（Difference）
关系R和S的差是由属于R而不属于S的所有元组 组成的集合，记为R-S，如表5.8所示。
3. 交运算（Intersection）
关系R和S交是由同时属于R和S的元组组成的集 合，记为R∩S，如表5.9所示。
4. 笛卡尔积
关系R和S的笛卡尔积R×S的结果如表5.10所 示。
1.选择运算(Selection 简记为SL)
选择条件用F表示,在关系R中挑选满足条件F的 所有元组，组成一个新的关系，这个关系是关系R 的一个子集，记为：
设D1，D2，…Dn为n个集合，称D1×D2×…×Dn={（d1， d2， …dn）∈Di，（i=1，2， …n）}为集合D1，D2，…Dn 的笛卡尔积。
其中，Di(i=1，2，…n）可能有相同的，称它们为域， 域是值的集合。
笛卡尔积可表示为一个二维表。如果给出三个域：
D1={王欣，刘伟平} D2={张德君，李波} D3={网络技术应用，数据库原理} 则D1，D2，D3的笛卡尔积为D1×D2×D3={ （王欣，张德君，网络技术应用），（王欣，张德君，数据库原理）， （王欣，李波，网络技术应用），（王欣，李波，数据库原理），（刘 伟平，张德君，网络技术应用），（刘伟平，张德君，数据库原理）， （刘伟平，李波，网络技术应用），（刘伟平，李波，数据库原理）}
数据库原理ch5
Chapter 5 数据库完整性
数据库的完整性是指数据的正确性和相容性
DBMS必须能够:
1.定义完整性
定义完整性时可给完整性条件取个名字
2.完整性检查
3.违约处理
拒绝，级连操作，设置空值
一．实体完整性
1.实体完整性定义
2.实体完整性检查和违约处理
二．参照完整性
1.参照完整性定义
2.参照完整性检查和违约处理
三．用户定义的完整性
1.属性上的约束条件的定义
①不允许取空值
②列值唯一
③用check指定列值应该满足的条
件
a)S表中属性ssex只允许取‘男’或
'女'
b)SC表中属性grade的值应在
0~100之间
c)S表中，计算机系(‘CS’)的学生年
龄应在16~25之间，其他系的
学生年龄应在16~35之间
2.约束条件检查和违约处理
拒绝执行
¾触发器
¾问题：
当用户输入一个修改表的语句给DBMS，DBMS做什么？
①检查语法
②检查用户权限
③检查此修改是否违反某个完整性
约束条件
④对相应数据项加锁
⑤执行修改操作
上机六
1.增加表SC对S、C的外键约束
2.将system.C中的值插入你的C
3.增加表C中cpno对cno的外键约束
4.在表SC中插入system.SC的相应记录
5.在表S中增加完整性约束:性别只允许
取‘男’或‘女’, 男学生的年龄在17~28之间，女学生的年龄在16~30之间6.在表S中将你的年龄改为16。
ch05_Visual FoxPro 6.0 程序设计教程_[共26页]
82第5章结构化查询语言SQL在Visual FoxPro 数据库管理系统中，除了具有Visual FoxPro 命令外，还支持结构化查询语言SQL 。
SQL 是关系数据库的标准语言。
查询是SQL 语言的重要组成部分，但不是全部，SQL 还包含数据定义、数据操纵和数据控制等功能。
本章将从数据查询、数据定义、数据修改这3个方面来介绍Visual FoxPro 支持的SQL 语言。
5.1 SQL 简介SQL 来源于20世纪70年代IBM 的一个被称为SEQUEL （Structured English Query Language ）的研究项目。
20世纪80年代，SQL 由美国国家标准局（简称ANSI ）进行了标准化。
1989年，国际标准化组织ISO 将SQL 定为国际标准，推荐它为标准关系数据库语言。
1990年，我国也颁布了《信息处理系统数据库语言SQL 》，将其定为中国国家标准。
现在，SQL 语言已广泛应用于各大、中、小数据库，如Oracle 、Access 、Sybase 、SQL Server 、FoxPro 等。
SQL 语言的主要特点如下。
（1）SQL 是一种一体化语言，它包括数据定义、数据查询、数据操纵和数据控制等方面的功能，可以完成数据库活动中的全部工作。
包括对表结构的定义、修改，记录的插入、更新、删除和查询以及安全性控制等一系列操作，为数据库应用系统的开发提供了良好的环境。
（2）SQL 是一种高度非过程化语言，它没有必要一步步告诉计算机“如何”去做，而只需要描述清楚用户要“做什么”，SQL 语言就可以将要求交给系统，系统自动完成全部工作。
（3）SQL 语言简洁，易学易用。
虽然SQL 语言功能很强，但它只有为数不多的几条命令，表5.1给出了分类的命令动词。
另外，SQL 的语法也非常简单，接近于自然语言（英语），容易学习与掌握。
（4）统一的语法格式，不同的工作方式。
不仅可以直接以命令的方式交互使用SQL ，也可以嵌入到程序设计语言中以程序方式使用SQL 。
计算机软件技术基础徐士良ch_05
5.3 数据库设计
5.3.1 数据库设计的基本概念 5.3.2 数据库设计的过程 5.3.3 数据字典
5.3.1 数据库设计的基本概念
可行性分析与研究阶段 系统设计阶段 设计实施与系统运行阶段
第5章 数据库技术
应用需求 (数据、处理)
转换规则、 DBMS 功 能 、 优化方法
应用要求、 DBMS 详 细 特征
例:找出关系R平均成绩（AVER）在85分以上的学生 姓名和学号。即
P＝πS#，SN(σAVER≥85(R))＝{(S1，MA)，(S3，FAN)， (S4，WANG)}
例:有关系T和P。要找出讲授课程G1的教师姓名、 所在系和他的职称。
π σ TP＝ TN，TD，T( TG＝G1(T|×|P))
1的联系
(2) 一对多（1：n）的联系
设有两个实体集E1和E2，如果E2中的每一个实体与E1中 的任意个实体（包括零个）有联系，而E1中的每一个实体 最多与E2中的一个实体有联系，则称这样的联系为“从E2 到E1的一对多的联系”，通常表示为“1：n的联系”。
例如，实体集学校与实体集教师之间的联系为一对多的联系。
或
σ(R)＝{ r|r∈R且g(r)为真 }
5. 选择运算(selection) R[g]＝{ r|r∈R且g(r)为真 }或
σ(R)＝{ r|r∈R且g(r)为真 } 公式中: ❖ R是关系名，g为一个逻辑表达式，取值为真或假。 ❖ G是表达式：由逻辑运算符与（∧或and）、或（∨
或or）、非（┐或not）联接各算术比较符组成；
学校教 学情况 的网状 模型
3. 关系模型 把数据看成一个二维表，每一个二维表称
为一个关系。
关系模型的优点。
数据库5版讲稿第四章高级SQL总结
–意义：如果关系R2的某个元组t2参照了关系R1的某 个元组t1，则t1必须存在 –例如关系S在Dno上的取值有两种可能
空值，表示该学生尚未分到任何系中；若非空值，则必须是DEPT关 系中某个元组的Dno值，表示该学生不可能分到一个不存在的系中
2019年2月8日星期五
数据库系统概念----SQL
7
完整性控制
2019年2月8日星期五
数据库系统概念----SQL
19
完整性控制
触发器：触发器是一条语句，当对数据库做修 改时，它自动被系统执行
– 触发器的定义
指明什么条件下触发器被执行 指明触发器执行的动作是什么
– 触发器的作用
示警 满足特定条件时自动执行某项任务
–触发器事件
Insert、
delete、update
数据库系统概念----SQL
2019年2月8日星期五
20
完整性控制
create trigger trigger-name
insert delete update begin after on table-name
[of column-name] old row as identifier referencing new row as identifier for each row when(search-condition) begin atomic triggered-SQL-statement end
4.2 完整性约束
完整性
– 数据的正确性和相容性
完整性检查
– DBMS必须提供一种机制来检查数据库中的数据是 否满足规定的条件，以保证数据库中数据是正确的， 避免非法的不合语义的错误数据的输入和输出，即 所谓的“垃圾进垃圾出”(Garbage In Garbage Out)所造成的无效操作和错误结果
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]
过程结构*
注意: 大部分数据库系统对下列标准语法实现了自 己的变种 复合语句: begin … end, While 和 repeat 语句:
end while
repeat
set n = n + 1
阅读系统手册，查看相关的系统语法
在begin和end之间可能包含多个SQL语句. 局部变量可以在一个复合语句中被声明
触发器--举例
假定不允许负的帐户余额，银行处理透支为： 设定帐户余额为0 在透支的数目上产生一个贷款 给这个贷款一个与透支的帐户号码相同的贷款 号码 执行触发器的条件是 account 关系的更新导致余 额为负值。
触发器--举例 (续)
define trigger overdraft on update of account T (if new T.balance < 0 then (insert into loan values (T.branch-name, T.account-number, –new T.balance) insert into borrower (select customer-name, account-number from depositor where T.account-number = depositor. account-number) update account S set S.balance = 0 where S.account-number = T.Account-number)) (SQL2000 define = create) 关键字new 表明T.balance的值在更新后应该被使用。如果被 省略，那么值在更新前就被使用。
函数/过程可以用SQL语言书写，也可以使用其他外部 程序语言. 函数对于特殊新的数据类型，例如图片和几何对象特 别有用. 例如: 用于检查多边形是否重叠，或者比较图片相 似性的函数. 一些数据库系统支持表值函数, 返回一个关系作为结果. 循环, if-then-else, 赋值
SQL:1999 还支持大量的命令式结构，例如
SQL允许用if-then-else语句，for和while循环，等等 ，来定义过程.
存储过程
可以在数据库中存储过程 然后通过call语句来执行 允许外部应用程序对数据库进行操作，而无需了解内 部细节
面向对象方面将在22章介绍 (基于对象的数据库)*
函数和过程
SQL:1999 支持函数和过程
用法:列出拥有超过12名教师的部门名字以及预算.
select dept_name, budget from department where dept_count (dept_name ) > 12
表函数
SQL:2003 增加了能够返回一个关系的函数 例如: 返回给定顾客所拥有的所有账户
create function instructors_of (dept_name char(20)
用法
select * from table (instructors_of (‘Music’))
SQL过程
dept_count 函数可以被写成如下过程:
create procedure dept_count_proc (in dept_name varchar(20), out d_count integer) begin select count(*) into d_count from instructor where instructor.dept_name = dept_count_proc.dept_name end
触发器--举例 (续)
CREATE TRIGGER reminder ON titles FOR INSERT AS EXEC master..xp_sendmail 'MaryM', 'New title, mention in the next report to distributors.'
returns table ( ID varchar(5), name varchar(20), dept_name varchar(20), salary numeric(8,2))
return table (select ID, name, dept_name, salary from instructor where instructor.dept_name = instructors_of.dept_name)
本章结束
附录
触发器实例×
例如，time_slot_id 不是timeslot的主码, 所以不能从section到 timeslot创建外码约束. 替代方案: 在 section 和 timeslot 使用触发器来执行完整性约束 create trigger timeslot_check1 after insert on section referencing new row as nrow for each row when (nrow.time_slot_id not in ( select time_slot_id from time_slot)) /* time_slot_id not present in time_slot */ begin rollback end;
可以使用 call语句从SQL过程中或者从嵌入式SQL中调用 过程.
过程和函数也可以从动态SQL中调用 SQL:1999 允许超过一个函数/过程拥有相同的名字 (称作 名字超载), 只要参数的数量是不同的, 或者至少参数的类 型是不同的.
declare d_count integer; call dept_count_proc( ‘Physics’, d_count);
触发器实例（续）×
create trigger timeslot_check2 after delete on timeslot referencing old row as orow for each row when (orow.time_slot_id not in ( select time_slot_id from time_slot) /* last tuple for time slot id deleted from time and orow.time_slot_id in ( select time_slot_id from section)) /* and time_slot_id still referenced from section*/ begin rollback end;
slot */
SQL触发事件和动作×
触发事件可能是 insert, delete 或者 update 更新操作的触发器可以被限制在特定的属性 例如, after update of takes on grade 属性更新前后的值都能被引用 referencing old row as : 用于删除和更新 referencing new row as : 用于插入和更新 触发器可以在事件之前被激活，作为额外限制. 例如，将空白的成绩 设为空值. create trigger setnull_trigger before update of takes referencing new row as nrow for each row when (nrow.grade = ‘ ‘) begin atomic set nrow.grade = null; end;
数据库系统
上海交通大学计算机系 张忠能
zhang-zn@
1
第5章：高级SQL
第5章：高级SQL
SQL数据类型和模式 函数和过程结构 触发器 高级聚集功能 OLAP 用编程语言接入SQL* 动态SQL JDBC 和 ODBC 嵌入式SQL
过程扩展和存储过程
SQL 提供模块语言
用触发器维护credits_earned值 ×
create trigger credits_earned after update of takes on (grade) referencing new row as nrow referencing old row as orow for each row when nrow.grade <> ’F’ and nrow.grade is not null and (orow.grade = ’F’ or orow.grade is null) begin atomic update student set tot_cred= tot_cred + (select credits from course where course.course_id= nrow.course_id) where student.id = nrow.id; end;
存储过程
存储过程的优点：
使用存储过程可以减少网络流量 增强代码的重用性和共享性 使用存储过程可以加快系统运行速度 使用存储过程保证安全性
存储过程的创建
写SQL语句 测试SQL语句 如得到所需结果，则创建结果 执行过程
触发器
触发器
触发器 是一条语句，当么条件下触发器被执行. 指明触发器执行的动作是什么. SQL-92 标准并不包括触发器，但是许多DB系统支持触发 器。 触发器于SQL:1999被引进到SQL标准 , 但是更早就通过非 标准语法被大部分数据库所支持.
过程结构*
注意: 大部分数据库系统对下列标准语法实现了自 己的变种 复合语句: begin … end, While 和 repeat 语句:
end while
repeat
set n = n + 1
阅读系统手册，查看相关的系统语法
在begin和end之间可能包含多个SQL语句. 局部变量可以在一个复合语句中被声明
触发器--举例
假定不允许负的帐户余额，银行处理透支为： 设定帐户余额为0 在透支的数目上产生一个贷款 给这个贷款一个与透支的帐户号码相同的贷款 号码 执行触发器的条件是 account 关系的更新导致余 额为负值。
触发器--举例 (续)
define trigger overdraft on update of account T (if new T.balance < 0 then (insert into loan values (T.branch-name, T.account-number, –new T.balance) insert into borrower (select customer-name, account-number from depositor where T.account-number = depositor. account-number) update account S set S.balance = 0 where S.account-number = T.Account-number)) (SQL2000 define = create) 关键字new 表明T.balance的值在更新后应该被使用。如果被 省略，那么值在更新前就被使用。
函数/过程可以用SQL语言书写，也可以使用其他外部 程序语言. 函数对于特殊新的数据类型，例如图片和几何对象特 别有用. 例如: 用于检查多边形是否重叠，或者比较图片相 似性的函数. 一些数据库系统支持表值函数, 返回一个关系作为结果. 循环, if-then-else, 赋值
SQL:1999 还支持大量的命令式结构，例如
SQL允许用if-then-else语句，for和while循环，等等 ，来定义过程.
存储过程
可以在数据库中存储过程 然后通过call语句来执行 允许外部应用程序对数据库进行操作，而无需了解内 部细节
面向对象方面将在22章介绍 (基于对象的数据库)*
函数和过程
SQL:1999 支持函数和过程
用法:列出拥有超过12名教师的部门名字以及预算.
select dept_name, budget from department where dept_count (dept_name ) > 12
表函数
SQL:2003 增加了能够返回一个关系的函数 例如: 返回给定顾客所拥有的所有账户
create function instructors_of (dept_name char(20)
用法
select * from table (instructors_of (‘Music’))
SQL过程
dept_count 函数可以被写成如下过程:
create procedure dept_count_proc (in dept_name varchar(20), out d_count integer) begin select count(*) into d_count from instructor where instructor.dept_name = dept_count_proc.dept_name end
触发器--举例 (续)
CREATE TRIGGER reminder ON titles FOR INSERT AS EXEC master..xp_sendmail 'MaryM', 'New title, mention in the next report to distributors.'
returns table ( ID varchar(5), name varchar(20), dept_name varchar(20), salary numeric(8,2))
return table (select ID, name, dept_name, salary from instructor where instructor.dept_name = instructors_of.dept_name)
本章结束
附录
触发器实例×
例如，time_slot_id 不是timeslot的主码, 所以不能从section到 timeslot创建外码约束. 替代方案: 在 section 和 timeslot 使用触发器来执行完整性约束 create trigger timeslot_check1 after insert on section referencing new row as nrow for each row when (nrow.time_slot_id not in ( select time_slot_id from time_slot)) /* time_slot_id not present in time_slot */ begin rollback end;
可以使用 call语句从SQL过程中或者从嵌入式SQL中调用 过程.
过程和函数也可以从动态SQL中调用 SQL:1999 允许超过一个函数/过程拥有相同的名字 (称作 名字超载), 只要参数的数量是不同的, 或者至少参数的类 型是不同的.
declare d_count integer; call dept_count_proc( ‘Physics’, d_count);
触发器实例（续）×
create trigger timeslot_check2 after delete on timeslot referencing old row as orow for each row when (orow.time_slot_id not in ( select time_slot_id from time_slot) /* last tuple for time slot id deleted from time and orow.time_slot_id in ( select time_slot_id from section)) /* and time_slot_id still referenced from section*/ begin rollback end;
slot */
SQL触发事件和动作×
触发事件可能是 insert, delete 或者 update 更新操作的触发器可以被限制在特定的属性 例如, after update of takes on grade 属性更新前后的值都能被引用 referencing old row as : 用于删除和更新 referencing new row as : 用于插入和更新 触发器可以在事件之前被激活，作为额外限制. 例如，将空白的成绩 设为空值. create trigger setnull_trigger before update of takes referencing new row as nrow for each row when (nrow.grade = ‘ ‘) begin atomic set nrow.grade = null; end;
数据库系统
上海交通大学计算机系 张忠能
zhang-zn@
1
第5章：高级SQL
第5章：高级SQL
SQL数据类型和模式 函数和过程结构 触发器 高级聚集功能 OLAP 用编程语言接入SQL* 动态SQL JDBC 和 ODBC 嵌入式SQL
过程扩展和存储过程
SQL 提供模块语言
用触发器维护credits_earned值 ×
create trigger credits_earned after update of takes on (grade) referencing new row as nrow referencing old row as orow for each row when nrow.grade <> ’F’ and nrow.grade is not null and (orow.grade = ’F’ or orow.grade is null) begin atomic update student set tot_cred= tot_cred + (select credits from course where course.course_id= nrow.course_id) where student.id = nrow.id; end;
存储过程
存储过程的优点：
使用存储过程可以减少网络流量 增强代码的重用性和共享性 使用存储过程可以加快系统运行速度 使用存储过程保证安全性
存储过程的创建
写SQL语句 测试SQL语句 如得到所需结果，则创建结果 执行过程
触发器
触发器
触发器 是一条语句，当么条件下触发器被执行. 指明触发器执行的动作是什么. SQL-92 标准并不包括触发器，但是许多DB系统支持触发 器。 触发器于SQL:1999被引进到SQL标准 , 但是更早就通过非 标准语法被大部分数据库所支持.