数据库系统原理教案.docx

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

2、要讲述以下内容介绍如下： 第一次：嵌入式SQL。 第二次：存储过程。 第三次：触发器。 第四次：ODBC编程。 教学难点 教学过程 作业与要求 P183第6、7、8、9、10、11、13、16、17。 备注 本提交文档内容与次序与实际讲课内容与次序有不一致的地方。 1 第五章 数据库编程 在数据库系统的应用开发中常常使用数据库编程方法对数据库进行操纵，主要包括：嵌入式SQL，SQL的过程化扩展PL/SQL和存储过程，触发器，使用ODBC设计与开发数据库应用系统的方法。 51嵌入式SQL SQL语言有两种不同的使用方式： *在终端交互方式下使用 *嵌入到某种高级语言中使用 *主语言：嵌入SQL的

3、高级语言 例：建立我班同学的档案信息 学生表student(sno,sname,sage,ssex,sdept) EXEC SQL INCLUDE SQLCA； 定义SQL通信区 EXEC SQL BEGIN DECLARE SECTION； 说明主变量 CHAR sno(8); CHAR sname(20); CHAR ssex(1); CHAR sdept(10); INTsage; EXEC SQL END DECLARE SECTION； Main() gets(sdept); while(1) 结束控制； 从键盘读入一个学生的数据到主变量中； EXEC SQL INSERT INTO

4、 student(sno,sname,sage,ssex,sdept) VALUES(:sno,:sname,:sage,:ssex,:sdept) IF (SUCCESS)Break; 说明： 1、 区分SQL语言与主语言语句， 所有SQL语句前都必须加前缀EXEC SQL，结束标志随主语言的不同而不同，有些语言用；有写用END-EXEC 2、 嵌入式SQL分两类：说明性语句；可执行语句。 2 *数据定义、数据控制只要在SQL语句前加EXEC SQL，其他方面基本上同交互方式下使用的SQL。 *数据操纵，与交互式比因使用主变量有所略有不同。 3、 SQL通信区：是一个数据结构，其中主要包括描

5、述系统当前的工作状态和运行环境的各种数据。如PB的SQLCA称为事物对象，有16个属性，其中10个提供数据库管理系统所需的连接信息，6个用于返回每条SQL语句运行成功或失败信息。其中属性表示当前SQL操作成功或失败 4、 主变量：在SQL语句中使用的主语言程序变量。 主变量的主要作用。 *可以指定向数据库中插入的数据。通过输入主变量。如上例。 *可以指定WHERE子句或HAVING子句中的条件。 *可以得到SQL语句的结果数据和状态。通过输出主变量。 Givensno=2001; EXEC SQL SELECT sno,sname,sage,ssex,sdept INTO :sno,:snam

6、e,:sage,:ssex,:sdept FROM student WHERE sno=:givensno; 查询结果为单记录的SELECT语句说明： *明确结果为单记录 *增加INTO *将数据库中的数据修改为指定的值。 EXEC SQL UPDATE StudentSET Ssge=sage+:xx 5、 嵌入式SQL语句与主程序间的通信小节 1） 向主语言传递SQL语句的执行状态信息，使主语言能据此控制程序流程。通过SQL通信区。 2） 主语言向SQL语句提供参数。通过主变量 3） 将SQL语句查询数据库的结果交主语言进一步处理。通过主变量。 6、 使用游标的SQL 因一组主变量一次只能

7、存放一条记录，仅使用主变量不能完全满足SQL语句向应用程序输出数据的要求。 例：查询某个系的学生信息。要查询的系名有用户在程序运行过程中指定，放在主变量deptname. EXEC SQL INCLUDE SQLCA；定义SQL通信区 EXEC SQL BEGIN DECLARE SECTION； 说明主变量 CHAR sno(8); CHAR sname(20); CHAR ssex(1); CHAR sdept(10); 3 INTsage; EXEC SQL END DECLARE SECTION； Main() gets(deptname); / 为主变量赋值 EXEC SQL DEC

8、LARE SX CURSOR FORSELECT sno, sname, sage, ssex FROM student WHERE sdept=:deptname; /说明游标 EXEC SQL open SX ;/打开游标：执行SELECT 语句，把所有满足条件的记录取到缓冲区中，游标处于活动状态，指针指向查询结果集中的第一条记录。 WHILE EXEC SQL FETCH SX INTO :sno , :sname , :sage , :ssex； /推进游标，将缓冲区中的当前记录取出来送至主变量供主语言进一步处理。 IF (SUCCESS) Break; /若所有查询结果处理完或出现S

9、QL语句错误，则推出循环。 /*主语言作进一步处理*/ EXEC SQL CLOSE SX ; /关闭游标：释放结果集占用的缓冲区及其它资源。 游标的作用：通过游标把对集合的操作转换为对单记录的处理。 52 PL/SQL PL/SQL是对 SQL的扩展，使其增加了过程化语句功能。PL/SQL程序的基本结构是块，每个块完成一个逻辑操作 1、常量变量的定义 2、常用语句： ? 赋值语句 ? 条件控制语句 ? 循环控制语句 53 存储过程 1、存储过程的定义 SQL Server的存储过程类似于编程语言中的“过程”。在使用Transact-SQL语言编程的过程中，将某些需要多次调用的实现某个特定任务

10、的代码段编写成一个过程，将其保存在数据库中，并SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。 存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。 存储过程是存储在服务器上的一组预编译的T-SQL 语句，是一种封装重复任务操作的方法，具有强大的编程功能。 2、存储过程的优点 ? 实现了模块化编程。 4 ? 存储过程具有对数据库立即访问的功能。 ? 使用存储过程可以加快程序的运行速度。 ? 使用存储过程可以减少客户机和服务器之间的通信量。 ? 方便实施企业规则。 3、存储过程的创建 使用Transact-SQL语句创建存储过程的

11、语法格式： CREATE PROCEDURE 过程名(参数列表) AS 例如，CREATE PROCEDURE dbo.xselect AS insert into student (sno,sname,ssex,sage,sdept) values (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中可

12、用EXECUTE命令来直接执行存储过程： execute 存储过程名称 例如：执行前面创建的存储过程xselect，execute xselect 例1，创建一个无参存储过程StuScoreInfo，查询以下信息：班级、学号、姓名、性别、课程名称、考试成绩。 Use StudentDB If exists (select name from sysobjects where name=StuScoreInfo and type =P)Drop procedure StuScoreInfo -删除已存在的存储过程 Go Create procedure StuScoreInfo as Selec

13、t 班级= substring (, 1, 6), as 学号, sname as 姓名,ssex as 性别, as 课程名称, as 考试成绩 from student, course, sc where = and = go 执行存储过程： exec StuScoreInfo; 例2，创建一个带参数的存储过程stu_info，该存储过程根据传入的学生编号在student表中查询此学生的信息。 Use StudentDB If exists (select name from sysobjects where name=stu_info and type =P)Drop procedure

14、 stu_info-删除已存在的存储过程 Go Create procedure stu_info sno char(5) as Select 班级= substring (, 1, 6), as 学号, sname as 姓名, ssex as 性别, sage as 年龄, sdept as 所在系from student where sno=sno 5 go 执行存储过程： exec stu_info ?20060701?; 5、删除存储过程 删除存储过程的语句格式是： DROP PROCEDURE 过程名(); 例如， DROP PROCEDURE stu_info (); 6、SQL

15、 Server中常用的系统存储过程 (1) sp_addlogin 创建新的 SQL Server 登录，该登录允许用户使用 SQL Server 身份验证连接到 SQL Server 实例。 sp_addlogin loginame = login , passwd = password , defdb = database , deflanguage = language , sid = sid , encryptopt= encryption_option 例如，为用户 Victoria 创建 SQL Server 登录，密码为 B1r12-36，并且不指定默认数据库。 EXEC sp_

16、addlogin Victoria, B1r12-36; 例如，为用户 Albert 创建 SQL Server 登录，密码为 B5432-3M6，默认数据库为StudentDB EXEC 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

17、_droplogin ?Victoria?； (3) sp_adduser 向当前数据库中添加新的用户： sp_adduser loginame = login , name_in_db = user , grpname = role 例如，使用现有的 SQL Server 登录名 Vidur，将数据库用户 Vidur 添加到当前数据库中的现有 Recruiting 角色。 EXEC sp_adduser ?Vidur?, ?Vidur?, ?Recruiting? ； 例如，将用户 Arvind 添加到 SQL Server 登录名 Arvind 的当前数据库。该用户属于默认的 public

18、 角色。 EXEC sp_adduser ?Arvind? ； (4) sp_dropuser 从当前数据库中删除数据库用户。 sp_dropuser sp_dropuser user ? sp_dropuser 执行 sp_revokedbaccess 以从当前数据库中删除用户。 ? 使用 sp_helpuser 将显示一个可从当前数据库中删除的用户名的列表。 例如，从当前数据库中删除用户 Albert。 EXEC sp_dropuser Albert; 6 (5) sp_addrole 在当前数据库中创建新的数据库角色。 sp_addrole rolename = role? , owne

19、rname = owner 例如，向当前数据库中添加名为 Managers 的新角色。 EXEC sp_addrole ?Managers? ； (6) sp_droprole 从当前数据库中删除数据库角色： sp_droprole rolename= role ? 使用 sp_droprole 只能删除数据库角色。 ? 不能删除带有现有成员的数据库角色。必须删除数据库角色的所有成员，然后才能删除该数据库角色。若要从角色中删除用户，请使用 sp_droprolemember。 ? 不能删除固定角色及 public 角色。 例如，删除应用程序角色 Sales。 EXEC sp_droprole

20、Sales; (7) sp_grantdbaccess 将数据库用户添加到当前数据库的语句格式： sp_grantdbaccess loginame = login , name_in_db = name_in_db OUTPUT 例如，将 Windows 登录名 EdmondsLolanSo 的数据库用户添加到当前数据库。新用户名为 Lolan。 EXEC sp_grantdbaccess Lolan FOR LOGIN EdmondsLolanSo; (8) sp_revokedbaccess 从当前数据库中删除数据库用户的语句格式： sp_revokedbaccess name_in_d

21、b = name 例如，从当前数据库中删除映射到 EdmondsLolanSo 的数据库用户。 EXEC sp_revokedbaccess EdmondsLolanSo; (9) sp_rename 在当前数据库中更改用户创建对象的名称，此对象可以是表、索引、列、别名、数据类型。其语句格式： sp_rename objname = object_name , newname = new_name , objtype = object_type 例如，将 SalesTerritory 表重命名为 SalesTerr。 EXEC sp_rename , SalesTerr; 例如，将 Sales

22、Territory 表中的 TerritoryID 列重命名为 TerrID。 EXEC sp_rename , TerrID, COLUMN; (10)sp_renamedb 更改数据库的名称的格式： sp_renamedb dbname = old_name , newname = new_name 例如，创建 Accounting 数据库，然后将该数据库的名称更改为 Financial。然后，查询 目录视图以确认数据库的新名称。 CREATE DATABASE Accounting; EXEC sp_renamedb Accounting, Financial; SELECT name,

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

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

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

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

27、”值对应记录；对DELETE操作来说，不存在“新”值对应记录。 触发器按照触发动作的间隔尺寸可以分为行级触发器和语句级触发器，行级触发器表示每次插入、删除、更新一行就调用触8 发器；而语句级触发器表示每个数据修改语句执行后调用一次，而不论影响到多少行。有的DBMS省缺此子句，表示定义的是语句级触发器。 例如，假设在节SPJ数据库的零件表P上创建一个AFTER UPDATE触发器，若表中有1000条记录，执行如下SQL语句： UPDATE P SET COLOR=?RED?; 如果该触发器为语句级触发器，那么执行完该语句后触发动作只发生一次，如果是行级触发器，则触发动作将执行1000次。 WHE

28、N指明触发动作的条件，即触发事件发生后，应满足什么条件才执行该动作。 触发动作体确定触发器所应完成的对相关数据库表的操作，这些操作就是在定义触发器时要求实现的功能。触发动作体既可以是一个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 AS sql_statem

29、ent .n SQL Server支持两种类型的触发器： ? AFTER触发器：指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发，只可以建立在表上。 ? INSTEAD OF触发器：代替了相应的触发事件而被执行，既可以建立在表上也可以建立在视图上。当为表或视图定义了针对某一操作(INSERT、DELETE、UPDATE)的INSTEAD OF 类型触发器且执行了相应的操作时，尽管触发器被触发，但相应的操作并不被执行，而运行的仅是触发器SQL 语句本身。INSTEAD-OF触发器在数据库引擎中可以取代数据操作语句而执行。 例如，在SPJ数据库的SPJ表上创建一个AFTER触

30、发器，完成的功能是：在SPJ表上创建一个插入、更新类型的触发器SPJCheck，当在SPJ表的QTY字段中插入或修改供应数量后，触发该触发器，检查供应数量是否在100-1000之间。 Use SPJ Go IF EXISTS(SELECT name FROM sysobjects WHERE name=SPJCheck and type =TR) DROP TRIGGER SPJCheck Go CREATE TRIGGER SPJCheck ON SPJ FOR INSERT, UPDATE AS IF UPDATE (QTY) PRINT AFTER触发器开始执行 BEGIN Declare QTYValue real SELECT QTYValue=( SELECT QTY FROM INSERTED) 9 If QTYValue 1000 or QTYValue 用下面的语句测试创建的触发器： Use SPJ Go Print