数据库系统原理教案.docx
《数据库系统原理教案.docx》由会员分享，可在线阅读，更多相关《数据库系统原理教案.docx（20页珍藏版）》请在冰豆网上搜索。
数据库系统原理教案
《数据库系统原理》教案
教学内容
第五章数据库编程
教材章节
第五章（第5节）
第八章
教学周次
教学课时
8
授课对象
计算机科学与技术
信息管理与信息系统
教学环境
多媒体教室
教学目标
本章介绍使用编程方法对数据库进行操纵的编程技术。
教学重点
1、嵌入式SQL中游标的概念和用法。
2、存储过程的基本概念、结构和语法。
3、存储过程的基本概念、类型以及各种触发器的创建。
4、ODBC的工作流程、使用ODBC开发应用程序。
教学难点
系统开发中存储过程和触发器的使用。
使用ODBC进行数据库应用程序的设计，实现异构数据库之间的访问。
教学过程
本章分4次讲述，每次2学时，每次主要讲述以下内容介绍如下：
第一次：
嵌入式SQL。
第二次：
存储过程。
第三次：
触发器。
第四次：
ODBC编程。
作业与要求
P183第6、7、8、9、10、11、13、16、17。
备注
本提交文档内容与次序与实际讲课内容与次序有不一致的地方。
第五章数据库编程
在数据库系统的应用开发中常常使用数据库编程方法对数据库进行操纵，主要包括：
嵌入式SQL，SQL的过程化扩展PL/SQL和存储过程，触发器，使用ODBC设计与开发数据库应用系统的方法。
5．1嵌入式SQL
SQL语言有两种不同的使用方式：
*在终端交互方式下使用（作为独立语言在交互环境下使用的SQL语言）
*嵌入到某种高级语言中使用（因为对查询结果不能做进一步处理，利用高级语言的过程性结构来弥补SQL语言在实现复杂应用方面的不足）
**主语言（宿主语言）：
嵌入SQL的高级语言
例：
建立我班同学的档案信息
学生表student（sno,sname,sage,ssex,sdept）
EXECSQLINCLUDESQLCA；
（1）定义SQL通信区
EXECSQLBEGINDECLARESECTION；
（2）说明主变量
CHARsno（8）;
CHARsname（20）;
CHARssex
（1）;
CHARsdept（10）;
INTsage;
EXECSQLENDDECLARESECTION；
Main（）
{
gets（sdept）;
while
（1）
{
结束控制；
从键盘读入一个学生的数据到主变量中；
EXECSQLINSERT
INTOstudent（sno,sname,sage,ssex,sdept）
VALUES（:
sno,:
sname,:
sage,:
ssex,:
sdept）
IF（sqlca.sqlcode<>SUCCESS）
Break;
}
}
说明：
1、区分SQL语言与主语言语句，
所有SQL语句前都必须加前缀EXECSQL，结束标志随主语言的不同而不同，有些语言用；有写用END-EXEC
2、嵌入式SQL分两类：
说明性语句（主要有定义SQL通信区、说明主变量语句）；可执行语句（数据定义、数据控制、数据操纵）。
*数据定义、数据控制只要在SQL语句前加EXECSQL，其他方面基本上同交互方式下使用的SQL。
*数据操纵，与交互式比因使用主变量有所略有不同。
3、SQL通信区：
是一个数据结构，其中主要包括描述系统当前的工作状态（每条SQL语句运行成功或失败信息，使主语言能据此控制程序流程）和运行环境的各种数据。
如PB的SQLCA称为事物对象，有16个属性，其中10个提供数据库管理系统（DBMS）所需的连接信息，6个用于返回每条SQL语句运行成功或失败信息。
其中属性sqlca.sqlcode表示当前SQL操作成功或失败（0成功，100表示SELECT语句找不到符合条件的数据，-1表示SQL操作出错）
4、主变量：
在SQL语句中使用的主语言程序变量。
主变量的主要作用。
*可以指定向数据库中插入的数据。
通过输入主变量（由应用程序对其赋值，SQL语句引用）。
如上例。
*可以指定WHERE子句或HAVING子句中的条件。
*可以得到SQL语句的结果数据和状态。
通过输出主变量（由SQL语句对其赋值，返回给应用程序）。
Givensno=2001;
EXECSQLSELECTsno,sname,sage,ssex,sdept
INTO:
sno,:
sname,:
sage,:
ssex,:
sdept
FROMstudent
WHEREsno=:
givensno;
查询结果为单记录的SELECT语句说明（P117）：
*明确结果为单记录（如结果为多记录出错）
*增加INTO
*将数据库中的数据修改为指定的值。
EXECSQLUPDATEStudent
SETSsge=sage+:
xx
5、嵌入式SQL语句与主程序间的通信小节
1）向主语言传递SQL语句的执行状态信息，使主语言能据此控制程序流程。
通过SQL通信区。
2）主语言向SQL语句提供参数。
通过主变量
3）将SQL语句查询数据库的结果交主语言进一步处理。
通过主变量。
6、使用游标的SQL
因一组主变量一次只能存放一条记录，仅使用主变量不能完全满足SQL语句向应用程序输出数据的要求。
例：
查询某个系的学生信息。
要查询的系名有用户在程序运行过程中指定，放在主变量deptname.
EXECSQLINCLUDESQLCA；
（1）定义SQL通信区
EXECSQLBEGINDECLARESECTION；
（2）说明主变量
CHARsno（8）;
CHARsname（20）;
CHARssex
（1）;
CHARsdept（10）;
INTsage;
EXECSQLENDDECLARESECTION；
Main（）
{
gets（deptname）;// 为主变量赋值
EXECSQLDECLARESXCURSORFOR
SELECTsno,sname,sage,ssex
FROMstudent
WHEREsdept=:
deptname;//说明游标
EXECSQLopenSX;//打开游标：
执行SELECT语句，把所有满足条件的记录取到缓冲区中，游标处于活动状态，指针指向查询结果集中的第一条记录。
WHILE
（1）
{
EXECSQLFETCHSXINTO:
sno,:
sname,:
sage,:
ssex；
//推进游标，将缓冲区中的当前记录取出来送至主变量供主语言进一步处理。
IF（sqlca.sqlcode<>SUCCESS）
Break;//若所有查询结果处理完或出现SQL语句错误，则推出循环。
/*由主语言作进一步处理*/
}
EXECSQLCLOSESX;//关闭游标：
释放结果集占用的缓冲区及其它资源。
}
游标的作用：
通过游标把对集合的操作转换为对单记录的处理。
5．2PL/SQL
PL/SQL是对SQL的扩展，使其增加了过程化语句功能。
PL/SQL程序的基本结构是块，每个块完成一个逻辑操作
1、常量变量的定义
2、常用语句：
⏹赋值语句
⏹条件控制语句
⏹循环控制语句
5．3存储过程
1、存储过程的定义
SQLServer的存储过程类似于编程语言中的“过程”。
在使用Transact-SQL语言编程的过程中，将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQLServer服务器通过过程名来调用它们，这些过程就叫做存储过程。
存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。
存储过程是存储在服务器上的一组预编译的T-SQL语句，是一种封装重复任务操作的方法，具有强大的编程功能。
2、存储过程的优点
⏹实现了模块化编程。
⏹存储过程具有对数据库立即访问的功能。
⏹使用存储过程可以加快程序的运行速度。
⏹使用存储过程可以减少客户机和服务器之间的通信量。
⏹方便实施企业规则。
3、存储过程的创建
使用Transact-SQL语句创建存储过程的语法格式：
CREATEPROCEDURE过程名（[参数列表]）
AS
<过程体>
例如，CREATEPROCEDURE[dbo].[xselect]AS
insertintostudent（sno,sname,ssex,sage,sdept）
values（'11','zhanglu','男',23,'IS'）
insertintostudent（sno,sname,ssex,sage,sdept）
values（'12','yangjing','女',22,'CS'）
select*fromstudentwheresname<>''
4、存储过程的执行
存储过程创建成功后，保存在数据库中。
在SQLServer中可用EXECUTE命令来直接执行存储过程：
execute存储过程名称
例如：
执行前面创建的存储过程xselect，
executexselect
例1，创建一个无参存储过程StuScoreInfo，查询以下信息：
班级、学号、姓名、性别、课程名称、考试成绩。
UseStudentDB
Ifexists（selectnamefromsysobjectswherename='StuScoreInfo'andtype='P'）
DropprocedureStuScoreInfo--删除已存在的存储过程
Go
CreateprocedureStuScoreInfoas
Select班级=substring（student.sno,1,6）,student.snoas学号,snameas姓名,ssexas性别,ameas课程名称,sc.gradeas考试成绩fromstudent,course,sc
wherestudent.sno=sc.snoando=o
go
执行存储过程：
execStuScoreInfo;
例2，创建一个带参数的存储过程stu_info，该存储过程根据传入的学生编号在student表中查询此学生的信息。
UseStudentDB
Ifexists（selectnamefromsysobjectswherename='stu_info'andtype='P'）
Dropprocedurestu_info--删除已存在的存储过程
Go
Createprocedurestu_info@snochar（5）as
Select班级=substring（student.sno,1,6）,student.snoas学号,snameas姓名,ssexas性别,sageas年龄,sdeptas所在系
fromstudentwheresno=@sno
go
执行存储过程：
execstu_info‘20060701’;
5、删除存储过程
删除存储过程的语句格式是：
DROPPROCEDURE过程名（）;
例如，
DROPPROCEDUREstu_info（）;
6、SQLServer中常用的系统存储过程
（1）sp_addlogin
创建新的SQLServer登录，该登录允许用户使用SQLServer身份验证连接到SQLServer实例。
sp_addlogin[@loginame=]'login' [,[@passwd=]'password']
[,[@defdb=]'database'] [,[@deflanguage=]'language']
[,[@sid=]sid] [,[@encryptopt=]'encryption_option']
例如，为用户Victoria创建SQLServer登录，密码为B1r12-36，并且不指定默认数据库。
EXECsp_addlogin'Victoria','B1r12-36';
例如，为用户Albert创建SQLServer登录，密码为B5432-3M6，默认数据库为StudentDB
EXECsp_addlogin'Albert','''B5432-3M6',StudentDB';
（2）sp_droplogin
删除MicrosoftSQLServer登录，禁止以该登录名访问SQLServer实例。
sp_droplogin[@loginame=]'login'
sp_droplogin调用DROPLOGIN。
例如，从SQLServer实例中删除Victoria登录。
sp_droplogin‘Victoria’；
（3）sp_adduser
向当前数据库中添加新的用户：
sp_adduser[@loginame=]'login'
[,[@name_in_db=]'user'][,[@grpname=]'role']
例如，使用现有的SQLServer登录名Vidur，将数据库用户Vidur添加到当前数据库中的现有Recruiting角色。
EXECsp_adduser‘Vidur’,‘Vidur’,‘Recruiting’；
例如，将用户Arvind添加到SQLServer登录名Arvind的当前数据库。
该用户属于默认的public角色。
EXECsp_adduser‘Arvind’；
（4）sp_dropuser
从当前数据库中删除数据库用户。
sp_dropuser[sp_dropuser]'user'
⏹sp_dropuser执行sp_revokedbaccess以从当前数据库中删除用户。
⏹使用sp_helpuser将显示一个可从当前数据库中删除的用户名的列表。
例如，从当前数据库中删除用户Albert。
EXECsp_dropuser'Albert';
（5）sp_addrole
在当前数据库中创建新的数据库角色。
sp_addrole[@rolename=]'role‘
[,[@ownername=]'owner']
例如，向当前数据库中添加名为Managers的新角色。
EXECsp_addrole‘Managers’；
（6）sp_droprole
从当前数据库中删除数据库角色：
sp_droprole[@rolename=]'role'
⏹使用sp_droprole只能删除数据库角色。
⏹不能删除带有现有成员的数据库角色。
必须删除数据库角色的所有成员，然后才能删除该数据库角色。
若要从角色中删除用户，请使用sp_droprolemember。
⏹不能删除固定角色及public角色。
例如，删除应用程序角色Sales。
EXECsp_droprole'Sales';
（7）sp_grantdbaccess
将数据库用户添加到当前数据库的语句格式：
sp_grantdbaccess[@loginame=]'login'
[,[@name_in_db=]'name_in_db'[OUTPUT]]
例如，将Windows登录名Edmonds\LolanSo的数据库用户添加到当前数据库。
新用户名为Lolan。
EXECsp_grantdbaccessLolanFORLOGIN[Edmonds\LolanSo];
（8）sp_revokedbaccess
从当前数据库中删除数据库用户的语句格式：
sp_revokedbaccess[@name_in_db=]'name'
例如，从当前数据库中删除映射到Edmonds\LolanSo的数据库用户。
EXECsp_revokedbaccess'Edmonds\LolanSo';
（9）sp_rename
在当前数据库中更改用户创建对象的名称，此对象可以是表、索引、列、别名、数据类型。
其语句格式：
sp_rename[@objname=]'object_name',[@newname=]'new_name'
[,[@objtype=]'object_type']
例如，将SalesTerritory表重命名为SalesTerr。
EXECsp_rename'Sales.SalesTerritory','SalesTerr';
例如，将SalesTerritory表中的TerritoryID列重命名为TerrID。
EXECsp_rename'Sales.SalesTerritory.TerritoryID','TerrID','COLUMN';
（10）sp_renamedb
更改数据库的名称的格式：
sp_renamedb[@dbname=]'old_name',[@newname=]'new_name'
例如，创建Accounting数据库，然后将该数据库的名称更改为Financial。
然后，查询sys.databases目录视图以确认数据库的新名称。
CREATEDATABASEAccounting;
EXECsp_renamedb'Accounting','Financial';
SELECTname,database_idFROMsys.databasesWHEREname='Financial';
5．4触发器
数据库触发器是存放在数据库中的代码，由应用所产生的事件触发。
触发器程序运行于数据库服务器上，由于不存在客户端与数据库服务器端的数据传输，因而有较好的执行性能。
当用户对有数据库触发器的表执行某种操作时，就会触发对应的触发器工作，完成规定的任务。
触发器实际上就是具有特殊功能的能够自动执行的存储过程。
SQL的触发器有3种类型：
插入（INSERT）、更新（UPDATE）、删除（DELETE）。
触发器定义规定了触发器的特征和被调用时采取的行动。
这些动作被规定在一个或多个SQL语句中（称作被触发SQL语句），可以包括如：
更新表、删除数据、调用过程或执行在SQL语句中实现的更多任务。
任何对这些语句的限制通常也就是SQL实现方式的限制。
就触发器的执行环境而言，是SQL的执行环境之一。
这个执行环境创建在计算机内存中、在语句执行过程中保存语句进程的空间。
每当调用触发器时，就创建了触发器的执行环境。
如果调用多个触发器，就会分别为每个触发器创建执行环境。
但是，在任何时候，一个会话只有惟一的一个执行环境是活动的。
一个触发器执行环境包含了触发器正确执行所必需的信息，这些信息包括有关触发器本身的细节和触发器所定义的表，即目标表。
此外，执行环境还包括一个或两个迁移表，迁移表是虚表，它保存对目标表插入、更新、删除的数据信息。
如果更新数据，则创建两个迁移表，一个用于旧数据，一个用于新数据。
如果插入数据，则创建一个迁移表来保存新数据。
如果删除数据，则创建一个迁移表来保存旧数据。
迁移表和触发器环境的信息是实现触发动作的SQL语句执行的依据。
触发器的功能主要表现在：
●审核修改：
可以检测和拒绝数据库中不允许的特定更新操作。
●级联操作：
可以检测通过对参照完整性的定义完成更新、删除操作中的级联处理。
●强制互联：
可以强制执行比参照完整性更复杂的、通过定义的数据互联关系。
●日志管理：
可以存储对数据库的增、删、改操作内容，建立数据库日志。
一、触发器的创建
创建触发器的一般语句格式是：
CREATETRIGGER<触发器名>BEFORE|AFTER
INSERT|DELETE|UPDATE[OF<列名表>]
ON<表名>[REFERENCING{OLD[AS]old|NEW[AS]new}]
[FOREACHROW|FOREACHSTATEMENT]
[WHEN（触发条件）]
<触发动作体>
说明：
①BEFORE与AFTER确定触发器是在目标表中数据修改语句前（选择BEFORE）调用还是修改语句后（选择AFTER）调用。
②触发事件：
插入（INSERT）、删除（DELETE）、更新（UPDATE），对于更新，还可以通过<列名表>选择触发器应用到哪些列。
③ON<表名>是定义触发器的表（目标表）。
触发事件会导致记录数据的改变，REFERENCING子句是对引用数据的来源与数据迁移的描述，一般用NEW代表新值状态对应的记录，OLD代表旧值状态对应的记录，其中AS可以省略。
注意：
对INSERT操作来说，不存在“旧”值对应记录；对DELETE操作来说，不存在“新”值对应记录。
④触发器按照触发动作的间隔尺寸可以分为行级触发器（FOREACHROW）和语句级触发器（FOREACHSTATEMENT），行级触发器表示每次插入、删除、更新一行就调用触发器；而语句级触发器表示每个数据修改语句执行后调用一次，而不论影响到多少行。
有的DBMS省缺此子句，表示定义的是语句级触发器。
例如，假设在2.1节SPJ数据库的零件表P上创建一个AFTERUPDATE触发器，若表中有1000条记录，执行如下SQL语句：
UPDATEPSETCOLOR=’RED’;
如果该触发器为语句级触发器，那么执行完该语句后触发动作只发生一次，如果是行级触发器，则触发动作将执行1000次。
⑤WHEN指明触发动作的条件，即触发事件发生后，应满足什么条件才执行该动作。
⑥触发动作体确定触发器所应完成的对相关数据库表的操作，这些操作就是在定义触发器时要求实现的功能。
触发动作体既可以是一个PL/SQL程序块，也可以是对已创建存储过程的调用。
MSSQLServer创建触发器的语句格式：
CREATETRIGGERtrigger_nameON{table|view}
[WITHENCRYPTION]
{
{{FOR|AFTER|INSTEADOF}
{[INSERT][,DELETE][,UPDATE]}
[WITHAPPEND][NOTFORREPLICATION]
AS
sql_statement[...n]
}
}
SQLServer支持两种类型的触发器：
⏹AFTER触发器：
指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发，只可以建立在表上
升级会员 