T_SQL程序设计_游标_存储过程.ppt

1、第 7 章,第7章 Transact-SQL程序设计,数据库应用教程,本章主要内容,常用内置函数,变量与表达式,程序控制语句,游标及其使用,用户自定义函数,主要内容,存储过程及其使用,7.1 变量与表达式,7.1.1 变量,1全局变量,SQL Server使用全局变量来记录SQL Server服务器的活动状态，它由系统定义并保留在系统中，用户和程序不需声明就可随时读取，但不能改变它的值。全局变量的名称以两个字符开头。 SQL Server提供了30多个全局变量。,7.1 变量与表达式,7.1.1 变量,【例7-1】 用ERROR变量检查错误。在查询分析器中输入下列代码并执行。,USE Coll

2、egeMIS GO DECLARE ERRORNO INT /*定义一个局部变量ERRORNO*/ INSERT INTO Student(StuNo,StuName,StuID) VALUES(050301001,王高林,324382475454342321) SET ERRORNO=ERROR /*把错误号临时保存起来*/ IF ERRORNO0 /*如果插入语句出错，输出出错信息及错误号*/ PRINT 插入错误！+错误号+CONVERT(varchar,ERRORNO) /*CONVERT(varchar,ERRORNO)的作用是把ERRORNO 转换为varchar类型*/,7.1

3、变量与表达式,7.1.1 变量,（1）ERROR变量,在SQL Server中执行一条Transact-SQL语句之后，如果语句成功执行，那么ERROR变量的值为0，如果出现一个错误，则该变量的值是一个不为0的错误号，有关错误号的具体含义，读者可查阅sysmessages系统表。,（2）PRINT语句量,【格式】PRINT any ASCII text | local_variable | FUNCTION | string_expr,【功能】输出字符串、局部变量、全局变量或函数的返回值、字符串表达式的值。,7.1 变量与表达式,7.1.1 变量,2局部变量,（1）局部变量的声明,例如：,DE

4、CLARE StuNo char(9) /*定义一个名为StuNo的局部变量，数据类型是char(9) */ DECLARE Birthday datetime /*定义一个名为Birthday 的局部变量，数据类型是datetime */ DECLARE Height decimal(5,3)/*定义一个名为Height 的局部变量，数据类型是decimal(5,3)*/,【格式】DECLARE 局部变量名 数据类型,n,【功能】声明由“局部变量名”指定的局部变量。,7.1 变量与表达式,7.1.1 变量,2局部变量,（2）局部变量的赋值,【格式1】SET =,【功能1】把“表达式”的值赋给

5、“变量名”指定的局部变量。,【格式2】SET =,【功能2】把“表达式”的值赋给“变量名”指定的局部变量。,【格式3】SELECT ， = FROM WHERE ,【功能3】把从“表名”指定的表中，按照WHERE子句中“条件”选取记录，把计算出来的“表达式”的值赋给“变量名”指定的局部变量。如果SELECT语句返回多个数值，则局部变量取最后一个返回值 。,【例7-2】求出学号为“060301002”的学生的选课门数并输出。,USE CollegeMIS GO DECLARE XH AS char(9) /*存放学号*/ DECLARE NUMBER AS int /*存放课程门数*/ SET

6、XH=060301002 /*给变量赋值*/ /*通过SELECT语句得到学号为“060301002”的学生的选课门数并赋值给NUMBER*/ SELECT NUMBER=COUNT(*) FROM SelectCourse WHERE StuNo=XH PRINT 060301002号学生选了+CONVERT(char(2),NUMBER)+门课,7.1 变量与表达式,7.1.2 运算符与表达式,1算术运算符与算术表达式,7.1 变量与表达式,7.1.2 运算符与表达式,2位运算符与位表达式,位运算符用于对整型数进行按拉运算，有位与（ number parameter data_type V

7、ARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n ,【功能】创建由“procedure_name”作为过程名指定的存储过程。,【例7-18】为CollegeMIS数据库创建一个名称为P2_CJCX的存储过程，该存储过程返回所有计算机应用专业的学生的学习情况，包括学号、姓名、课程号、课程名和成绩信息。,USE CollegeMIS GO CREATE PROCEDURE P2_CJCX /*创建存储过程*/ AS

8、SELECT S.StuNo AS 学号,S.StuName AS 姓名, C.CourseNo AS 课程号,C.CourseName AS 课程名, SC.Score AS 成绩 FROM Student S,Course C,SelectCourse SC WHERE S.StuNo=SC.StuNo AND S.Major=计算机应用 AND C.CourseNo=SC.CourseNo,7.6 存储过程及其使用,7.6.2 存储过程的创建与管理,2执行存储过程,【格式】 EXECUTE return_status = procedure_name ;number | procedur

9、e_name_var parameter = value | variable OUTPUT | DEFAULT ,.n WITH RECOMPILE ,【功能】执行由“procedure_name”作为过程名指定的存储过程或由局部变量“pprocedure_name_var”引用的存储过程。,例如：EXECUTE P2_CJCX,（1）通过对象资源管理器执行存储过程,（2）使用T-SQL语句执行存储过程,7.6 存储过程及其使用,7.6.2 存储过程的创建与管理,3查看存储过程,（1）通过对象资源管理器查看存储过程信息,【格式】EXEC sp_help ,例如：EXEC sp_help P1

10、_XSCX,（2）使用系统存储过程查看存储过程信息,【功能】查看“存储过程名”指定的存储过程的概要信息。,【格式】EXEC sp_helptext ,例如：EXEC sp_helptext P1_XSCX,【功能】查看“存储过程名”指定的存储过程的定义文本信息 。,7.6 存储过程及其使用,7.6.2 存储过程的创建与管理,4存储过程的修改,（2）使用TransactSQL语句修改存储过程,【格式】 ALTER PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH REC

11、OMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n ,【功能】修改由procedure_name作为存储过程名指定的存储过程。,（1）通过对象资源管理器修改存储过程信息,【例】要想P2_CJCX存储过程不显示课程号，则可使用如下语句对该存储过程进行修改。,ALTER PROCEDURE P2_CJCX /*修改存储过程*/ AS SELECT S.StuNo AS 学号,S.StuName AS 姓名, C.CourseName AS 课程名,SC.Score AS 成绩 FROM S

12、tudent S,Course C,SelectCourse SC WHERE S.StuNo=SC.StuNo AND S.Major=计算机应用 AND C.CourseNo=SC.CourseNo,7.6 存储过程及其使用,7.6.2 存储过程的创建与管理,5删除存储过程,（1）通过对象资源管理器删除存储过程,【格式】DROP PROCEDURE procedure_name ,.n ,例如：要删除P1_XSCX存储过程，只需执行如下语句： DROP PROCEDURE P1_XSCX,（2）使用TransactSQL语句删除存储过程,【功能】删除由procedure_name作为存储过

13、程名指定的存储过程。可同时删除多个存储过程，存储过程名之间用“,”隔开。,7.6 存储过程及其使用,7.6.3 带参数的存储过程,使用输入参数，可以把用户或调用程序的数据传给存储过程，使用输出参数可以把存储过程处理的结果返回给用户或调用程序，从而扩展存储过程的功能。,【例7-19】编写一个带有输入参数存储过程：存储过程的名称为P3_XHCCJ，存储过程的功能是给定一个学生的学号，查询出该学生的所有选课信息，包括姓名、课程名和成绩。然后调用该存储过程进行验证。,USE CollegeMIS GO -如果存储过程已经存在则先删除它- IF EXISTS(SELECT name FROM sysob

14、jects WHERE name=P3_XHCCJ AND type=P) DROP PROCEDURE P3_XHCCJ GO,-创建存储过程 CREATE PROCEDURE P3_XHCCJ XH CHAR(9) AS SELECT S.StuName AS 姓名,C.CourseName AS 课程名,SC.Score AS 成绩 FROM Student S,Course C,SelectCourse SC WHERE S.StuNo=SC.StuNo AND S.StuNo=XH AND C.CourseNo=SC.CourseNo GO -调用存储过程- EXECUTE P3_X

15、HCCJ 060301002 GO,【例7-20】编写一个带有输入参数和输出参数的存储过程：存储过程的名称为P4_XHCCJ，存储过程的功能是给定一个学生的学号，查询出该学生的平均成绩及选课门数 。,USE CollegeMIS GO -如果存储过程已经存在则先删除它- IF EXISTS(SELECT name FROM sysobjects WHERE name=P4_XHCCJ AND type=P) DROP PROCEDURE P4_XHCCJ GO,-创建带有输入参数和输出参数的存储过程- CREATE PROCEDURE P4_XHCCJ XH char(9),PJCJ deci

16、mal(4,1) OUTPUT,SKSL int OUTPUT AS SELECT PJCJ=AVG(SC.Score),SKSL=COUNT(SC.CourseNo) FROM Student S,SelectCourse SC WHERE S.StuNo=SC.StuNo AND S.StuNo=XH GO -声明调用存储过程所需要的参数- DECLARE AvgScore decimal(4,1),CourseNum int,StuNo char(9) SET StuNo=060302112 -调用存储过程，注意参数输出参数的写法- EXECUTE P4_XHCCJ StuNo,AvgS

17、core OUTPUT,CourseNum OUTPUT SELECT StuNo AS 学号,AvgScore AS 平均成绩,CourseNum AS 选课门数 GO,7.6 存储过程及其使用,7.6.4 系统存储过程,除了用户自定义的存储过程外，SQL Server还提供了两百多个系统存储过程，用来帮助用户方便地管理SQL Server数据库。所有的系统存储过程名称都是以sp_开头，在安装SQL Server数据库系统时，由系统安装在master数据库中，并且初始状态只有系统管理员拥有使用权。,虽然系统存储过程存储在master数据库中，但是绝大部分系统存储过程可以在任何数据库中执行，而

18、且在使用时不用在名称前加数据库名。,下面是一些常用的系统存储过程举例。 【例9.9】利用sp_addgroup命令在当前数据库中建立一个名为user_group的角色。 代码如下： USE master GO EXEC sp_addgroup user_group 【例9.10】利用sp_addlogin命令建立一个名为user01的登录用户。 代码如下： USE master GO EXEC sp_addlogin user01 运行后提示创建。需要注意的是，在没有指定用户密码和默认数据库的时候，创建的用户默认数据库是master，默认的密码是NULL。,【例9.11】利用sp_addtyp

19、e命令创建新的用户自定义数据库类型user_date，该类型为datetime数据类型。 代码如下： EXEC sp_addtype user_date,datetime 运行结果为类型已添加。 【例9.12】使用sp_monitor显示CPU、I/O的使用信息。 代码如下： USE master GO EXEC sp_monitor GO 执行后返回如图9-5所示的 结果集，该结果报告了当 时有关SQL Server繁忙程 度的信息。,图9-5 执行sp_monitor的结果,扩展存储过程：是允许用户使用一种编程语言（如C语言）创建的应用程序，程序中使用SQL Server开放数据服务的AP

20、I函数，直接可以在SQL Server地址空间中运行。用户可以像使用普通的存储过程一样使用它，同样也可以将参数传给它并返回结果和状态值。 扩展存储过程编写好后，可以由系统管理员在SQL Server中注册登记，然后将其执行权限授予其他用户。扩展存储过程只能存储在master数据库中。下面通过几个例子，介绍扩展存储过程的创建和应用实例。 【例9.13】使用sp_addextendproc存储过程将一个编写好的扩展存储过程xp_userprint.dll注册到SQL Server中。 代码如下： EXEC sp_addextendedproc xp_userprint,xp_userprint.d

21、ll 其中： sp_addextendproc为系统存储过程。 xp_userprint为扩展存储过程在SQL Server中的注册名。 xp_userprint.dll为用某种语言编写的扩展存储过程动态连接库。,扩展存储过程,【例9.14】使用存储过程xp_dirtree返回本地操作系统的系统目录“C:winnt”目录树。代码如下： EXEC xp_dirtreeC:winnt 执行结果返回目录树。 【例9.15】利用扩展存储过程xp_cmdshell为一个操作系统外壳执行指定命令串，并作为文本返回任何输出。 代码如下： EXEC masterxp_cmdshell dir *.exe GO

22、 执行结果返回系统目录下的文件内容文本信息。,【例9.16】利用扩展存储过程实现远程备份数据库。假设Windows 2000 Server服务器计算机名为jkx，本地域名为“Domain域”，系统管理员账号为sa，密码为123，需要备份的数据库为student。代码如下： EXEC xp_cmdshell net share baktest=e:baktest GO EXEC masterxp_cmdshell net usejkxbaktest 123 /use:domainsa GO BACKUP database student to disk=jkxbakteststudent.bak GO EXEC xp_cmdshell net share baktest/delete GO,创建存储过程时，需要注意下列事项： 只能在当前数据库中创建存储过程。 数据库的所有者可以创建存储过程，也可以授权其他用户创建存储过程。 存储过程是数据库对象，其名称必须遵守标识符命名规则。 不能将CREATE PROCEDURE语句与其他SQL语句组合到单个批处理中。 创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。,存储过程第一次执行后，其被编译的代码将驻留在高速