数据库系统原理教案.docx

1、数据库系统原理教案数据库系统原理教案教学内容第五章 数据库编程教材章节第五章（第5节）第八章教学周次 教学课时8授课对象计算机科学与技术信息管理与信息系统教学环境多媒体教室教学目标本章介绍使用编程方法对数据库进行操纵的编程技术。教学重点1、 嵌入式SQL中游标的概念和用法。2、 存储过程的基本概念、结构和语法。 3、 存储过程的基本概念、类型以及各种触发器的创建。4、 ODBC的工作流程、使用ODBC开发应用程序。教学难点系统开发中存储过程和触发器的使用。使用ODBC进行数据库应用程序的设计，实现异构数据库之间的访问。教学过程本章分4次讲述，每次2学时，每次主要讲述以下内容介绍如下：第一次：嵌

2、入式SQL。第二次：存储过程。第三次：触发器。第四次：ODBC编程。作业与要求P183第6、7、8、9、10、11、13、16、17。备注本提交文档内容与次序与实际讲课内容与次序有不一致的地方。第五章 数据库编程在数据库系统的应用开发中常常使用数据库编程方法对数据库进行操纵，主要包括：嵌入式SQL，SQL的过程化扩展PL/SQL和存储过程，触发器，使用ODBC设计与开发数据库应用系统的方法。51嵌入式SQLSQL语言有两种不同的使用方式：*在终端交互方式下使用（作为独立语言在交互环境下使用的SQL语言）*嵌入到某种高级语言中使用（因为对查询结果不能做进一步处理，利用高级语言的过程性结构来弥补S

3、QL语言在实现复杂应用方面的不足）*主语言（宿主语言）：嵌入SQL的高级语言例：建立我班同学的档案信息学生表student(sno,sname,sage,ssex,sdept)EXEC SQL INCLUDE SQLCA； （1）定义SQL通信区EXEC SQL BEGIN DECLARE SECTION； （2）说明主变量CHAR sno(8); CHAR sname(20);CHAR ssex(1);CHAR sdept(10);INT sage;EXEC SQL END DECLARE SECTION；Main() gets(sdept); while(1) 结束控制；从键盘读入一个学生

4、的数据到主变量中；EXEC SQL INSERT INTO student(sno,sname,sage,ssex,sdept) VALUES(:sno,:sname,:sage,:ssex,:sdept)IF (sqlca.sqlcodeSUCCESS) Break; 说明：1、 区分SQL语言与主语言语句，所有SQL语句前都必须加前缀EXEC SQL，结束标志随主语言的不同而不同，有些语言用；有写用END-EXEC2、 嵌入式SQL分两类：说明性语句（主要有定义SQL通信区、说明主变量语句）；可执行语句（数据定义、数据控制、数据操纵）。*数据定义、数据控制只要在SQL语句前加EXEC SQ

5、L，其他方面基本上同交互方式下使用的SQL。*数据操纵，与交互式比因使用主变量有所略有不同。3、 SQL通信区：是一个数据结构，其中主要包括描述系统当前的工作状态（每条SQL语句运行成功或失败信息，使主语言能据此控制程序流程）和运行环境的各种数据。如PB的SQLCA称为事物对象，有16个属性，其中10个提供数据库管理系统（DBMS）所需的连接信息，6个用于返回每条SQL语句运行成功或失败信息。其中属性sqlca.sqlcode表示当前SQL操作成功或失败（0成功，100表示SELECT语句找不到符合条件的数据，-1表示SQL操作出错）4、 主变量：在SQL语句中使用的主语言程序变量。主变量的主

6、要作用。*可以指定向数据库中插入的数据。通过输入主变量（由应用程序对其赋值，SQL语句引用）。如上例。*可以指定WHERE子句或HAVING子句中的条件。*可以得到SQL语句的结果数据和状态。通过输出主变量（由SQL语句对其赋值，返回给应用程序）。 Givensno=2001;EXEC SQL SELECT sno,sname,sage,ssex,sdept INTO :sno,:sname,:sage,:ssex,:sdeptFROM studentWHERE sno=:givensno;查询结果为单记录的SELECT语句说明（P117）：*明确结果为单记录（如结果为多记录出错）*增加INT

7、O*将数据库中的数据修改为指定的值。EXEC SQL UPDATE Student SET Ssge=sage+:xx5、 嵌入式SQL语句与主程序间的通信小节1） 向主语言传递SQL语句的执行状态信息，使主语言能据此控制程序流程。通过SQL通信区。2） 主语言向SQL语句提供参数。通过主变量3） 将SQL语句查询数据库的结果交主语言进一步处理。通过主变量。6、 使用游标的SQL 因一组主变量一次只能存放一条记录，仅使用主变量不能完全满足SQL语句向应用程序输出数据的要求。例：查询某个系的学生信息。要查询的系名有用户在程序运行过程中指定，放在主变量deptname.EXEC SQL INCLU

8、DE SQLCA； （1）定义SQL通信区EXEC SQL BEGIN DECLARE SECTION； （2）说明主变量CHAR sno(8); CHAR sname(20);CHAR ssex(1);CHAR sdept(10);INT sage;EXEC SQL END DECLARE SECTION；Main()gets(deptname); /为主变量赋值EXEC SQL DECLARE SX CURSOR FOR SELECT sno, sname, sage, ssexFROM studentWHERE sdept=:deptname; /说明游标EXEC SQL open SX

9、 ; /打开游标：执行SELECT 语句，把所有满足条件的记录取到缓冲区中，游标处于活动状态，指针指向查询结果集中的第一条记录。WHILE （1） EXEC SQL FETCH SX INTO :sno , :sname , :sage , :ssex； /推进游标，将缓冲区中的当前记录取出来送至主变量供主语言进一步处理。IF (sqlca.sqlcodeSUCCESS) Break; /若所有查询结果处理完或出现SQL语句错误，则推出循环。/*由主语言作进一步处理*/EXEC SQL CLOSE SX ; /关闭游标：释放结果集占用的缓冲区及其它资源。游标的作用：通过游标把对集合的操作转换为

10、对单记录的处理。52 PL/SQLPL/SQL是对 SQL的扩展，使其增加了过程化语句功能。PL/SQL程序的基本结构是块，每个块完成一个逻辑操作1、常量变量的定义2、常用语句： 赋值语句 条件控制语句 循环控制语句53 存储过程1、存储过程的定义SQL Server的存储过程类似于编程语言中的“过程”。在使用Transact-SQL语言编程的过程中，将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执

11、行。 存储过程是存储在服务器上的一组预编译的T-SQL 语句，是一种封装重复任务操作的方法，具有强大的编程功能。2、存储过程的优点 实现了模块化编程。 存储过程具有对数据库立即访问的功能。 使用存储过程可以加快程序的运行速度。 使用存储过程可以减少客户机和服务器之间的通信量。 方便实施企业规则。3、存储过程的创建使用Transact-SQL语句创建存储过程的语法格式：CREATE PROCEDURE 过程名(参数列表)AS例如，CREATE PROCEDURE dbo.xselect ASinsert into student (sno,sname,ssex,sage,sdept) value

12、s (11,zhanglu,男,23,IS)insert into student (sno,sname, ssex,sage,sdept) values (12,yangjing,女,22,CS)select * from student where sname 4、存储过程的执行存储过程创建成功后，保存在数据库中。在SQL Server中可用EXECUTE命令来直接执行存储过程： execute 存储过程名称例如：执行前面创建的存储过程xselect， execute xselect例1，创建一个无参存储过程StuScoreInfo，查询以下信息：班级、学号、姓名、性别、课程名称、考试成绩

13、。Use StudentDBIf exists (select name from sysobjects where name=StuScoreInfo and type =P) Drop procedure StuScoreInfo -删除已存在的存储过程GoCreate procedure StuScoreInfo as Select 班级= substring (student.sno, 1, 6), student.sno as 学号, sname as 姓名,ssex as 性别, ame as 课程名称, sc.grade as 考试成绩 from student, course,

14、 sc where student.sno=sc.sno and o=ogo执行存储过程：exec StuScoreInfo;例2，创建一个带参数的存储过程stu_info，该存储过程根据传入的学生编号在student表中查询此学生的信息。Use StudentDBIf exists (select name from sysobjects where name=stu_info and type =P) Drop procedure stu_info -删除已存在的存储过程GoCreate procedure stu_info sno char(5) as Select 班级= substr

15、ing (student.sno, 1, 6), student.sno as 学号, sname as 姓名, ssex as 性别, sage as 年龄, sdept as 所在系 from student where sno=snogo执行存储过程：exec stu_info 20060701;5、删除存储过程删除存储过程的语句格式是：DROP PROCEDURE 过程名(); 例如，DROP PROCEDURE stu_info ();6、SQL Server中常用的系统存储过程(1) sp_addlogin 创建新的 SQL Server 登录，该登录允许用户使用 SQL Serv

16、er 身份验证连接到 SQL Server 实例。 sp_addlogin loginame = login , passwd = password , defdb = database , deflanguage = language , sid = sid , encryptopt= encryption_option 例如，为用户 Victoria 创建 SQL Server 登录，密码为 B1r12-36，并且不指定默认数据库。 EXEC sp_addlogin Victoria, B1r12-36; 例如，为用户 Albert 创建 SQL Server 登录，密码为 B5432-3

17、M6，默认数据库为StudentDBEXEC sp_addlogin Albert, B5432-3M6, StudentDB; (2) sp_droplogin 删除 Microsoft SQL Server 登录，禁止以该登录名访问 SQL Server 实例。 sp_droplogin loginame= login sp_droplogin 调用 DROP LOGIN。例如，从 SQL Server 实例中删除 Victoria 登录。 sp_droplogin Victoria；(3) sp_adduser 向当前数据库中添加新的用户： sp_adduser loginame = l

18、ogin , name_in_db = user , grpname = role 例如，使用现有的 SQL Server 登录名 Vidur，将数据库用户 Vidur 添加到当前数据库中的现有 Recruiting 角色。 EXEC sp_adduser Vidur, Vidur, Recruiting ；例如，将用户 Arvind 添加到 SQL Server 登录名 Arvind 的当前数据库。该用户属于默认的 public 角色。 EXEC sp_adduser Arvind ；(4) sp_dropuser 从当前数据库中删除数据库用户。 sp_dropuser sp_dropuse

19、r user sp_dropuser 执行 sp_revokedbaccess 以从当前数据库中删除用户。 使用 sp_helpuser 将显示一个可从当前数据库中删除的用户名的列表。例如，从当前数据库中删除用户 Albert。 EXEC sp_dropuser Albert; (5) sp_addrole 在当前数据库中创建新的数据库角色。 sp_addrole rolename = role , ownername = owner 例如，向当前数据库中添加名为 Managers 的新角色。 EXEC sp_addrole Managers ；(6) sp_droprole 从当前数据库中删

20、除数据库角色：sp_droprole rolename= role 使用 sp_droprole 只能删除数据库角色。 不能删除带有现有成员的数据库角色。必须删除数据库角色的所有成员，然后才能删除该数据库角色。若要从角色中删除用户，请使用 sp_droprolemember。 不能删除固定角色及 public 角色。 例如，删除应用程序角色 Sales。 EXEC sp_droprole Sales; (7) sp_grantdbaccess 将数据库用户添加到当前数据库的语句格式： sp_grantdbaccess loginame = login , name_in_db = name_i

21、n_db OUTPUT 例如，将 Windows 登录名 EdmondsLolanSo 的数据库用户添加到当前数据库。新用户名为 Lolan。 EXEC sp_grantdbaccess Lolan FOR LOGIN EdmondsLolanSo; (8) sp_revokedbaccess 从当前数据库中删除数据库用户的语句格式： sp_revokedbaccess name_in_db = name 例如，从当前数据库中删除映射到 EdmondsLolanSo 的数据库用户。 EXEC sp_revokedbaccess EdmondsLolanSo; (9) sp_rename 在当前

22、数据库中更改用户创建对象的名称，此对象可以是表、索引、列、别名、数据类型。其语句格式：sp_rename objname = object_name , newname = new_name , objtype = object_type 例如，将 SalesTerritory 表重命名为 SalesTerr。 EXEC sp_rename Sales.SalesTerritory, SalesTerr; 例如，将 SalesTerritory 表中的 TerritoryID 列重命名为 TerrID。 EXEC sp_rename Sales.SalesTerritory.Territory

23、ID, TerrID, COLUMN; (10)sp_renamedb 更改数据库的名称的格式： sp_renamedb dbname = old_name , newname = new_name 例如，创建 Accounting 数据库，然后将该数据库的名称更改为 Financial。然后，查询 sys.databases 目录视图以确认数据库的新名称。 CREATE DATABASE Accounting;EXEC sp_renamedb Accounting, Financial; SELECT name, database_id FROM sys.databases WHERE na

24、me = Financial;54 触发器数据库触发器是存放在数据库中的代码，由应用所产生的事件触发。触发器程序运行于数据库服务器上，由于不存在客户端与数据库服务器端的数据传输，因而有较好的执行性能。当用户对有数据库触发器的表执行某种操作时，就会触发对应的触发器工作，完成规定的任务。触发器实际上就是具有特殊功能的能够自动执行的存储过程。SQL的触发器有3种类型：插入（INSERT）、更新（UPDATE）、删除（DELETE）。触发器定义规定了触发器的特征和被调用时采取的行动。这些动作被规定在一个或多个SQL语句中（称作被触发SQL语句），可以包括如：更新表、删除数据、调用过程或执行在SQL语句

25、中实现的更多任务。任何对这些语句的限制通常也就是SQL实现方式的限制。就触发器的执行环境而言，是SQL的执行环境之一。这个执行环境创建在计算机内存中、在语句执行过程中保存语句进程的空间。 每当调用触发器时，就创建了触发器的执行环境。如果调用多个触发器，就会分别为每个触发器创建执行环境。但是，在任何时候，一个会话只有惟一的一个执行环境是活动的。一个触发器执行环境包含了触发器正确执行所必需的信息，这些信息包括有关触发器本身的细节和触发器所定义的表，即目标表。此外，执行环境还包括一个或两个迁移表，迁移表是虚表，它保存对目标表插入、更新、删除的数据信息。如果更新数据，则创建两个迁移表，一个用于旧数据，

26、一个用于新数据。如果插入数据，则创建一个迁移表来保存新数据。如果删除数据，则创建一个迁移表来保存旧数据。迁移表和触发器环境的信息是实现触发动作的SQL语句执行的依据。触发器的功能主要表现在： 审核修改：可以检测和拒绝数据库中不允许的特定更新操作。 级联操作：可以检测通过对参照完整性的定义完成更新、删除操作中的级联处理。 强制互联：可以强制执行比参照完整性更复杂的、通过定义的数据互联关系。 日志管理：可以存储对数据库的增、删、改操作内容，建立数据库日志。一、触发器的创建创建触发器的一般语句格式是：CREATE TRIGGER BEFORE | AFTERINSERT | DELETE | UPD

27、ATE OF ON REFERENCING OLD AS old| NEW AS new FOR EACH ROW | FOR EACH STATEMENT WHEN（触发条件）说明： BEFORE 与 AFTER 确定触发器是在目标表中数据修改语句前（选择BEFORE）调用还是修改语句后（选择AFTER）调用。 触发事件：插入（INSERT）、删除（DELETE）、更新（UPDATE），对于更新，还可以通过选择触发器应用到哪些列。 ON 是定义触发器的表（目标表）。触发事件会导致记录数据的改变，REFERENCING子句是对引用数据的来源与数据迁移的描述，一般用NEW代表新值状态对应的记录，

28、OLD代表旧值状态对应的记录，其中AS可以省略。注意：对INSERT操作来说，不存在“旧”值对应记录；对DELETE操作来说，不存在“新”值对应记录。 触发器按照触发动作的间隔尺寸可以分为行级触发器（FOR EACH ROW）和语句级触发器（FOR EACH STATEMENT），行级触发器表示每次插入、删除、更新一行就调用触发器；而语句级触发器表示每个数据修改语句执行后调用一次，而不论影响到多少行。有的DBMS省缺此子句，表示定义的是语句级触发器。例如，假设在2.1节SPJ数据库的零件表P上创建一个AFTER UPDATE触发器，若表中有1000条记录，执行如下SQL语句： UPDATE P

29、 SET COLOR=RED;如果该触发器为语句级触发器，那么执行完该语句后触发动作只发生一次，如果是行级触发器，则触发动作将执行1000次。 WHEN指明触发动作的条件，即触发事件发生后，应满足什么条件才执行该动作。 触发动作体确定触发器所应完成的对相关数据库表的操作，这些操作就是在定义触发器时要求实现的功能。触发动作体既可以是一个PL/SQL程序块，也可以是对已创建存储过程的调用。MS SQL Server创建触发器的语句格式：CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , DELETE , UPDATE WITH APPEND NOT FOR REPLICATION ASsql_statement .n SQL Server支持两种类型的触发器： AFTER触发器：指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发，只可以建立在表上