《数据库编程》PPT课件.ppt

1、1,数据库编程,7.1 Transact-SQL语言 7.2 存储过程 7.3 触发器 7.4 嵌入式SQL 7.5 ODBC,2,7.1 Transact-SQL语言-概述,标准的SQL不支持流程控制，仅仅是一些简单的语句，有时使用起来不方便。大型的关系型数据库系统都在标准SQL基础上，结合自身的特点，推出了可以编程的、结构化的SQL编程语言。例如，SQL Server的Transact-SQL、Oracle的PL/SQL等。 在Transact-SQL中，标准的SQL语句可畅通无阻。Transact-SQL最主要的用途是设计服务器端的能够在后台执行的程序块，如存储过程和触发器等。,3,7.

2、1 Transact-SQL语言-Transact-SQL程序的结构1,通过该程序可以看出，一个Transact-SQL程序与一般的高级语言的语法要素是一致的，主要含有注释、变量与常量、各种运算符、函数与表达式、流程控制语句等。 下面通过一个实例来说明Transact-SQL程序的基本结构。 例7-1 打开学生选修课程管理数据库sc，并从数据表Student中计算management学生数，如果该院人数超过800，则程序输出“这个学院的人数超过800”，否则输出“这个学院的人数没有超过800”。,4,USE SC /*将学生选修课程管理数据库SC置为当前数据库*/ GO DECLARE Stu

3、Num INT SET StuNum =800 IF (SELECT COUNT(*) FROM Table_Student WHERE School=management) StuNum -输出结果 PRINT 这个学院的人数超过+CONVERT(VARCHAR(10), StuNum) ELSE -输出结果 PRINT 这个学院的人数没有超过+CONVERT(VARCHAR(10), StuNum) -执行批处理 GO,7.1 Transact-SQL语言-Transact-SQL程序的结构2,5,7.1 Transact-SQL语言- Transact-SQL程序的批处理,Transac

4、t-SQL程序内两个“GO”标记符之间的代码称为一个“批”。SQL Server对Transact-SQL程序的编译和执行是按照“批”为单位来进行的，称为批处理。 GO语句本身并不是Transact-SQL语句的组成部分，它只是一个用于表示批处理结束的前端指令。 “批”中的语句要么编译成功都执行，要么编译不成功都不执行。 一个Transact-SQL程序中可以包含多个“批”。,6,7.1 Transact-SQL语言-变量1,在Transact-SQL中，变量要声明后才可使用。ECLARE命令用于声明一个或多个变量。 DECLARE variable data_type ，n 在用DECLAR

5、E命令声明之后，所有的变量都被赋予初值NULL。需要使用SELECT 或SET命令来给变量赋值。 SELECT 局部变量=变量值 或 SET 局部变量=变量值 如果变量为字符型，那么，在data_type表达式中应指明其最大长度，否则系统认为其长度为1。,7,7.1 Transact-SQL语言-变量2,例7-2 DECLARE命令的用法。 DECLARE x CHAR , y CHAR(10) SELECT x=123 , y=data_type PRINT x PRINT y 则运行结果为： 1 data_type,8,7.1 Transact-SQL语言-流程控制命令1,Transact

6、-SQL使用的流程控制命令与常见的程序设计语言类似，主要有以下几种控制命令。 1）BEGIN END BEGIN END的语法格式如下： BEGIN END BEGIN END用来设定一个程序块，将在BEGIN END内的所有程序视为一个单元执行。,9,2）IF ELSE IF ELSE的语法格式如下： IF ELSE 其中，可以是各种表达式的组合，但表达式的值必须是逻辑值True或False。ELSE子句是可选的，最简单的IF语句没有ELSE子句部分。IF ELSE用来判断当条件成立时执行IF后的一段程序，条件不成立时执行ELSE后的一段程序。,7.1 Transact-SQL语言-流程控制

7、命令2,10,3）IF NOT EXISTS IF NOT EXISTS的语法格式如下： IF NOT EXISTS（SELECT 子查询） ELSE IF EXISTS语句用于检测数据是否存在，如果EXISTS后面的“SELECT子查询”的结果不为空，即检测到有数据记录存在时，就执行其后面的程序块，否则执行ELSE后面的程序块。 当采用NOT关键字时，则与上面的功能正好相反。,7.1 Transact-SQL语言-流程控制命令3,11,例7-3 从Student查询学号为“s001”的同学的数据记录，如果存在，则输出“存在学号为s001的学生”；否则输出“不存在学号为s001的学生”。 US

8、E SC /*将学生选修课程数据库置为当前数据库*/ GO DECLARE message VARCHAR(255) /*定义变量message*/ IF EXISTS (SELECT * FROM Table_Student WHERE StudentID=s001) SET message=存在学号为s001的学生 ELSE SET message=不存在学号为s001 PRINT message GO,7.1 Transact-SQL语言-流程控制命令4,12,4）CASE CASE命令的语法格式如下： CASE WHEN THEN WHEN THEN ELSE END 该语句的执行过程

9、是：将CASE后面表达式的值与各WHEN子句中的表达式的值进行比较，如果二者相等，则返回THEN后的表达式的值，然后跳出CASE语句，否则返回ELSE 子句中的表达式的值。,7.1 Transact-SQL语言-流程控制命令5,13,例7-4 统计各个院的人数，如果人数超过1200人，则输出“大院”；800人到1200人输出“中等院系”；少于800人则输出“小院”。 SELECT School, Type=CASE WHEN COUNT(*) 1200 THEN 大院 WHEN COUNT(*) BETWEEN 800 AND 1200 THEN 中等院系 WHEN COUNT(*) 800

10、THEN 小院 END FROM Table_Student GROUP BY Sdep,7.1 Transact-SQL语言-流程控制命令6,14,5）WHILE CONTINUEBREAK WHILE CONTINUEBREAK的语法格式如下： WHILE BEGIN BREAK CONTINUE 命令行或程序块 END WHILE命令在设定的条件成立时会重复执行命令行或程序块。CONTINUE命令可以让程序跳过CONTINUE命令之后的语句，回到WHILE循环的第一行，继续进行下一次循环。BREAK命令则让程序完全跳出循环，结束WHILE命令的执行。,7.1 Transact-SQL语言

11、-流程控制命令7,15,例7-5 以下程序是计算1100之间所有能被3整除的数的个数及总和。,DECLARE s INT ,i INT ,nums INT SET s=0 SET i=1 SET nums=0 WHILE (i=100) BEGIN IF ( i%3=0) BEGIN SET s=s+i SET nums=nums+1 END SET i=i+1 END PRINT s PRINT nums,7.1 Transact-SQL语言-流程控制命令8,16,7.2 存储过程-概述1,存储过程基本概念 存储过程是一种把重复的任务操作封装起来的方法，是一组SQL语句和可选控制流语句的预编

12、译集合，它们只需编译一次，以后即可多次执行。 因为 SQL 语句不需要重新编译，所以执行存储过程可以提高性能。存储过程可以接受参数，并可以将结果集、返回代码和输出参数返回给调用应用程序。,17,7.2 存储过程-概述2,存储过程的类型 系统存储过程 本地存储过程 临时存储过程 远程存储过程 扩展存储过程,18,7.2 存储过程-创建存储过程1,CREATE PROCEDURE的语法形式如下： CREATE PROC EDURE =默认值 OUTPUT ,.n AS SQL语句 .n 存储过程包括过程首部和过程体。各个选项的含义如下： ：是要创建的存储过程的名字。存储过程的命名必须符合命名规则，

13、在一个数据库中或对其所有者而言，存储过程的名字必须惟一。,19,：是存储过程的参数。在CREATE PROCEDURE语句中，可以声明一个或多个参数。当调用该存储过程时，用户必须给出所有的参数值，除非定义了参数的缺省值。 ：是参数的数据类型。 默认值：是指参数的缺省值。如果定义了缺省值，那么即使不给出参数值，则该存储过程仍能被调用。缺省值必须是常数，或者是空值。 OUTPUT：表明该参数是一个返回参数。用OUTPUT 参数可以向调用者返回信息。 AS：指明该存储过程将要执行的动作。 SQL语句：是任何数量和类型的包含在存储过程中的SQL 语句。,7.2 存储过程-创建存储过程2,20,7.2

14、存储过程-执行存储过程,用EXECUTE语句直接执行存储过程。EXECUTE语句的语法形式如下： EXECUTE =value | 变量 OUTPUT|DEFAULT | ，n 各参数的含义和在CREATE PROCEDURE中含义相同。 注意：在执行有输出参数的存储过程时，执行语句中的变量名的后边要加上OUTPUT修饰符。在调用有输出参数的存储过程时，与输出参数对应的是一个变量，要先声明此变量，用来保存输出参数返回的结果。,21,7.2 存储过程-修改和删除存储过程,修改存储过程的语句与定义存储过程的语句基本一样，只要将CREATE PROCEDURE 改为ALTER PROCEDURE 即

15、可。 用DROP PROCEDURE语句删除存储过程。 删除存储过程的语法形式如下： DROP PROCEDURE ，n,22,7.2 存储过程-举例1,CREATE PROCEDURE usp_Getallstudents AS SELECT * FROM Student； EXECUTE usp_Getallstudents；,23,CREATE PROCEDURE usp_Getstudent number varchar(40) AS SELECT * FROM Student WHERE Sid=number； EXECUTE usp_Getstudent S001；,7.2 存储过

16、程-举例2,24,CREATE PROCEDURE usp_GetstudentByName name varchar(40)=% AS SELECT * FROM Student WHERE Sname LIKE name； EXECUTE usp_GetstudentByName ； EXECUTE usp_GetstudentByName A%; EXECUTE usp_GetstudentByName Alice；,7.2 存储过程-举例3,25,CREATE PROCEDURE usp_GetAvgGradeByCid cid varchar(40), aveage int OUT

17、AS SET aveage= (SELECT AVG(Grade) FROM Choose WHERE Cid=cid )； DECLARE aveAgeOfCM int EXECUTE usp_GetAveAgeByDept C001, aveAgeOfCM OUT PRINT The average grade of C001 is +str(aveAgeOfCM),7.2 存储过程-举例4,26,ALTER PROCEDURE,usp_GetstudentByName name varchar(40) AS SELECT * FROM Student WHERE Sname LIKE n

18、ame；,DROP PROCEDURE usp_GetstudentByName;,7.2 存储过程-举例5,27,7.3 触发器-概述,触发器基本概念 触发器是一种特殊类型的存储过程。触发器和表紧密相连，用户的操作影响到触发器保护的数据时，触发器就会自动发生。 触发器有三种类型 INSERT触发器 UPDATET触发器 DELETE触发器,28,7.3 触发器-创建触发器1,使用CREATE TRIGGER语句创建触发器，其语法形式如下： CREATE TRIGGER ON FOR | AFTER|INSTEAD OF INSERT | UPDATE | DELETE AS SQL语句 其中

19、，各个选项的含义如下： ：触发器名称，必须符合SQL Server命名规则。,29,7.3 触发器-创建触发器2,FOR:指定触发器只有在引发的SQL语句中指定的操作都已经成功执行，并且所有的约束检查也成功完成后，才执行此触发器。这种触发器称为后触发型触发器。 AFTER：作用和FOR一样。 INSTEAD OF：指定执行触发器而不是执行引发触发器执行的SQL语句，从而替代触发语句的操作。这种触发器称为前触发型触发器。 INSERT，UPDATE，DELETE：引发触发器执行的操作，可以同时指定多个操作，各个操作之间用逗号隔开。,30,7.3 触发器-创建后触型触发器,例7-6 更改了学生的学

20、号, 希望他的选课对应的学号修改。 CREATE TRIGGER TriStudent On Student -在Student表中创建触发器 FOR UPDATE -为什么事件触发 AS -事件触发后所要做的事情 if UPDATE(Sid) BEGIN UPDATE choose SET Sid=i.Sid FROM choose sc , Deleted d ,Inserted i -Deleted和Inserted临时表 WHERE sc.Sid=d.Sid END,31,7.3 触发器-创建触发器3,理解触发器里面的两个临时的表：Inserted, Deleted ,。注意Inser

21、ted 与Deleted分别表示触发事件的表“新的一条记录”和“旧的一条记录” 。,32,7.3 触发器-修改和删除触发器,在触发器创建之后，可以根据需要修改触发器定义。 修改触发器的语句与定义触发器的语句基本一样，只要将CREATE TRIGGER 改为ALTER TRIGGER就可以了。 删除触发器的语法格式为： DROP TRIGGER ,33,7.4 嵌入式SQL-概述,为什么使用嵌入式SQL？ 有些操作对于交互式SQL是不可能的任务。 SQL的表达能力相比高级语言有一定的限制，有些数据访问要求单纯使用SQL无法完成。一方面，SQL在逐渐增强自己的表达能力，另一方面，太多的扩展会导致优

22、化能力及执行效率的降低。 非声明性动作 实际的应用系统是非常复杂的，数据库访问只是其中一个部件。有些动作如与用户交互、图形化显示数据等只能用高级语言实现。,34,7.4 嵌入式SQL-嵌入式SQL的处理过程,SQL函数定义库,35,7.4 嵌入式SQL-嵌入式SQL与宿主语言的接口1,嵌入式SQL和宿主语言程序之间是通过主变量来传递信息。 SQL语句引用主变量时，主变量前面都要加上冒号，但在宿主语言的语句中变量前没有冒号。 宿主语言中使用SQL语句时，在SQL语句之前要加上关键字EXEC SQL。 所有的主变量的说明必须放在以下两个嵌入式SQL语句之间。 EXEC SQL BEGIN DECL

23、ARE SECTION； . EXEC SQL END DECLARE SECTION；,36,例7-7 下列语句会出现在更新关系学生Student（学号，姓名，院系）的C+函数中。 EXEC SQL BEGIN DECLARE SECTION; char Sid 4 , Sname 10 ,Sdep10; EXEC SQL END DECLARE SECTION；,7.4 嵌入式SQL-嵌入式SQL与宿主语言的接口1,37,7.4 嵌入式SQL-嵌入式SQL的使用,1）非查询语句及主变量的使用 任何非查询语句（即不返回结果的SQL语句）都可以用主变量作为接口嵌入到宿主语言中 2）查询语句 （

24、1）只产生一个元组的查询语句 （2）产生多个元组的查询语句及游标的使用,38,7.4 嵌入式SQL-非查询语句及主变量的使用举例,例7- 8 使用主变量插入新的学生信息。 void getStudent（） EXEC SQL BEGIN DECLARE SECTION； char Sid 4 , Sname 10 ,Sdep10; EXEC SQL END DECLARE SECTION ; coutSid; cinSname; cinSdep EXEC SQL INSERT INTO Student( SID,Sname,Sdep) VALUES ( :Sid, :Sname, :Sdep)

25、;,39,7.5 ODBC体系结构 概述,（1）ODBC应用程序（Application） （2）ODBC 应用程序接口（ODBC API） （3）ODBC管理器（Administrator） （4）ODBC驱动程序管理器（DriverManager） （5）ODBC 驱动程序（Driver） （6）数据源（data source name，DSN） 系统DSN面向系统全部用户的数据源， 系统中的所有用户都可以使用。 用户DSN仅面向某些特定用户的数据源， 只有通过身份验证才能连接。 文件DSN用于从文本文件中获取数据，提供多用户访问。,40,7.5 ODBC体系结构 建立ODBC数据源1,用

26、ODBC管理器注册一个数据源，建立起ODBC与具体数据库的联系。这样，只要应用程序将数据源提供给ODBC，ODBC就能建立起与相应数据库的连接。 可以通过Windows的控制面板建立ODBC数据源。,41,7.5 ODBC体系结构 建立ODBC数据源2,建立ODBC数据源的步骤 （1）打开控制面板 Windows系统提供了“ODBC数据源管理器”来管理数据源的设置，在Windows2000/NT中，该工具可以在“控制面板”“管理工具”中找到； 在WindowsXP中它位于“控制面板”“性能和维护”“管理工具”中，双击数据源图标，启动ODBC数据源管理器（如图7-1 所示）。 其主要内容是要求用

27、户创建一个数据源，应用程序就能够通过ODBC管理器的数据源直接操作数据库。,42,图7-1 ODBC数据源管理器,43,（2）假设我们要建立一个系统ODBC数据源，选择“系统DSN”选项卡，然后单击“添加”按钮，弹出如图7-2所示的窗口。,图7-2 创建数据源窗口,44,（3）在图7-2所示的窗口中选择要连接的数据库管理系统的驱动程序。这里选择的是“SQL Server”，单击“完成”后会弹出如图7-3所示的窗口。,图7-3 建立新数据源,45,（4）在图7-3所示的窗口中，为数据源命名，并指定要连接到的数据库服务器的名字。在“名称”文本框中输入数据源的名字，在“说明”文本框中输入此数据源的说

28、明信息，在“服务器”下拉列表中指定要连接的数据库服务器的名字，如果使用本地服务器时可输入（local）。单击“下一步”按钮 ，弹出如图7-4所示的窗口。,图7-4选择验证登录ID方式,46,（5）在图7-4所示的窗口中，选择用户登录到数据库服务器的身份验证方式和用户登录标识，然后单击“下一步”，弹出如图7-5 所示的窗口。,图7-5 建立SQL Server新数据源,47,（6）在图7-5 所示的窗口中，根据需要更改默认的数据库名称或添加附加数据库文件名后，单击“下一步”按钮，弹出如图7-6所示的窗口。,图7-6 设置使用的数据库服务器选项,48,（7）在图7-6所示的窗口中，可指定用于SQL

29、 Server消息的语言、字符数据转换等，单击“完成”按钮，弹出如图7-7所示的窗口。,图7-7 新建ODBC数据源的描述,49,（8）在图7-8 所示的窗口中，单击“测试数据源”按钮，如果数据源创建成功，则测试结果对话框现实测试成功信息，如图7-8所示。,图7-8 测试数据源,50,本 章 小 结(1),存储过程是存储在服务器上的一组预编译的SQL语句。它是一种封装重复任务操作的方法，支持用户提供的变量，具有强大的编程功能。 创建存储过程可以使用CREATE PROCEDURE语句，修改存储过程可以使用ALTER PROCEDURE语句，删除存储过程可以使用DROP PROCEDURE语句。存储过程可以使用输入