存储过程与触发器.ppt

1、数据库设计与应用,浙江工业大学计算机学院,存储过程 触发器 自定义函数,1 存储过程,1.1 创建存储过程 1.2 执行存储过程 1.3 查看和修改存储过程 1.4 重命名和删除存储过程,存储过程的概念,SQL提供了一种方法，它可以将一些固定的操作集中起来由数据库服务器来完成，以实现某个任务，这种方法就是存储过程。,存储过程的概念,存储过程是存放在服务器上的预先定义与编译好的SQL语句的命令集合，是一个独立的数据库对象 存储过程在第一次执行时进行语法检查和编译。编译好的版本存储在过程高速缓存中用于后续调用，执行速度快 存储过程可以由应用程序多次激活，提高重复任务的执行性能 存储过程可以接受输入

2、参数和返回值 SQL Server分为两类：系统提供的存储过程和用户自定义的存储过程。,创建过程,创建过程,语法检查,SQL,存入sysobjects和 syscomments表,执行过程,执行过程,从syscomments表读取存储过程 优化 编译,N,在内存中？,执行,Y,存储过程的优点,(1) 存储过程允许标准组件式编程，在服务器端运行，执行速度快。 (2) 存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。 (3) 确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对

3、数据库信息访问的权限。 (4) 自动完成需要预先执行的任务。存储过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。,1.1 创建存储过程,在SQL Server中，可以使用三种方法创建存储过程 ： 使用创建存储过程向导创建存储过程。 利用SQL Server 企业管理器创建存储过程。 使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。,创建存储过程时，需要确定存储过程的三个组成部分：,所有的输入参数以及传给调用者的输出参数。 被执行的针对数据库的操作语句，包括调用其它存储过程的语句。 返

4、回给调用者的状态值，以指明调用是成功还是失败。,用户存储过程的定义中不能使用下列对象创建语句： CREATE VIEW CREATE DEFAULT CREATE RULE CREATE PROCEDURE CREATE TRIGGER,使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程,创建存储过程前，应该考虑下列几个事项： 不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。 创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。 存储过程是数据库对象，其名称必须遵守标识符规则。 只能在当前数据库中创建存

5、储过程。,使用CREATE PROCEDURE创建存储过程的语法形式如下：,CREATE PROC EDURE owner. procedure_name parameter data_type VARYING =default OUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE , ENCRYPTION AS sql_statement .n ,各参数意义如下：,parameter：过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。 data_type：用于指定参数的数据类型。 VARYING：用于指定作为输出OUTP

6、UT参数支持的结果集。 Default：用于指定参数的默认值。 OUTPUT：表明该参数是一个返回参数。,RECOMPILE：表明 SQL Server 不会保存该存储过程的执行计划，在执行时重新编译。 ENCRYPTION ：表示 SQL Server 将加密包含 CREATE PROCEDURE 语句的存储过程文本。 AS：用于指定该存储过程要执行的操作。 sql_statement：是存储过程中要包含的任意数目和类型的 Transact-SQL 语句。,例1 通过SQL命令定义一个存储过程，查询XSCJ数据库中每个同学各门功课的成绩，然后调用该存储过程。步骤如下： -定义存储过程 USE

7、 XSCJ Go CREATE PROCEDURE student_grade AS SELECT XS.学号,XS.姓名,KC.课程名, XS_KC.成绩 FROM XS,XS_KC,KC WHERE XS.学号=XS_KC.学号 AND XS_KC.课程号=KC.课程号 Go -调用存储过程 EXEC student_grade GO,对于存储过程要注意下列几点： (1) 用户定义的存储过程只能在当前数据库中创建（临时过程除外，临时过程总是在 tempdb 中创建）。 (2) 成功执行 CREATE PROCEDURE 语句后，过程名称存储在sysobjects 系统表中，而 CREATE

8、 PROCEDURE 语句的文本存储在 syscomments 中。若使用加密属性，则该存储过程的定义文本在syscomments 中以密文形式显示。 (3) 权限。CREATE PROCEDURE的权限默认授予sysadmin固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员。,1.2 执行存储过程,直接执行存储过程可使用EXECUTE命令来执行，语法如下： EXECUTE return_status= -保存存储过程的返回状态 procedure_name;number|procedure_name_var parameter=value|variable

9、OUTPUT|DEFAULT,.n WITH RECOMPILE ,存储过程的执行要注意下列几点： (1) 参数可以通过 value 或 parameter_name = value 提供。 (2) 执行存储过程时，若语句是批处理中的第一个语句，则不一定要指定EXECUTE 关键字。 (3) 若定义为OUTPUT参数，则返回值保存在variable变量中。,使用带参数的存储过程 【例2】从三个表的联接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。 USE XSCJ IF EXISTS (SELECT name F

10、ROM sysobjects WHERE name = st_info AND type = P) DROP PROCEDURE st_info GO CREATE PROCEDURE st_info name varchar(30) = 刘% AS SELECT a.学号,a.姓名,c.课程名,b.成绩 FROM XS a INNER JOIN XS_KC b ON a.学号 =b.学号 INNER JOIN KC c ON c.课程号= b.课程号 WHERE 姓名 LIKE name GO,使用带OUTPUT参数的存储过程 【例3】用于计算指定学生的总学分，存储过程中使用了一个输入参数和

11、一个输出参数。 USE XSCJ GO IF EXISTS(SELECT name FROM sysobjects WHERE name = totalcredit AND type = P) DROP PROCEDURE totalcredit GO USE XSCJ GO CREATE PROCEDURE totalcredit name varchar(40), total int OUTPUT AS SELECT total= SUM(学分) FROM XS,XS_KC,KC WHERE 姓名=name AND XS.学号= XS_KC.学号 GROUP BY XS.学号 GO,例4：

12、创建一个带有output输出参数的存储过程。OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的某个值。 下面的示例创建一个存储过程 titles_sum，并使用一个可选的输入参数和一个输出参数。,首先创建titles_sum存储过程： CREATE PROCEDURE titles_sum TITLE varchar(40) = %, SUM money OUTPUT AS SELECT SUM = SUM(price) FROM titles WHERE title LIKE TITLE -查询符合输入参数条件的书籍的单价总和 GO,接下来，

13、将该 OUTPUT 参数用于控制流语言。 注意：OUTPUT 变量必须在创建存储过程和使用该变量时都进行定义。参数名和变量名不一定要匹配，但数据类型和参数位置必须匹配。 DECLARE TOTALCOST money EXECUTE titles_sum The%, TOTALCOST OUTPUT IF TOTALCOST 200 BEGIN PRINT PRINT All of these titles can be purchased for less than $200. END ELSE SELECT The total cost of these titles is $ + RTR

14、IM(CAST(TOTALCOST AS varchar(20),使用OUTPUT游标参数的存储过程 OUTPUT 游标参数用于返回存储过程的局部游标。 【例5】在 XSCJ数据库的XS表上声明并打开一个游标。 USE XSCJ IF EXISTS (SELECT name FROM sysobjects WHERE name = st_cursor and type = P) DROP PROCEDURE st_cursor GO CREATE PROCEDURE st_cursor st_cursor CURSOR VARYING OUTPUT AS SET st_cursor = CUR

15、SOR FORWARD_ONLY STATIC FOR SELECT * FROM XS OPEN st_cursor GO,从存储过程中返回数据,4种返回数据的方式： 输出参数，既可以返回数据（整型值或字符值等），也可以返回游标变量（游标是可以逐行检索的结果集） 返回代码，始终是整型值 SELECT 语句的结果集，这些语句包含在该存储过程内或该存储过程所调用的任何其它存储过程内 可从存储过程外引用的全局游标,使用 WITH ENCRYPTION 选项 WITH ENCRYPTION 子句对用户隐藏存储过程的文本。 【例6】创建加密过程，使用 sp_helptext 系统存储过程获取关于加密过

16、程的信息，然后尝试直接从 syscomments 表中获取关于该过程的信息。 IF EXISTS (SELECT name FROM sysobjects WHERE name = encrypt_this AND type = P) DROP PROCEDURE encrypt_this GO USE XSCJ GO CREATE PROCEDURE encrypt_this WITH ENCRYPTION AS SELECT * FROM XS GO,例7：利用产品销售数据库CPXS，创建求客户名为XXX的客户所销售的产品名称、价格、库存量、销售数量及销售额的存储过程“客户销售记录”。 注

17、：销售数据库包含三张表，说明如下： 产品（产品编号，产品名称，价格，库存量）； 销售商（客户编号，客户名称，地区，负责人，电话）； 产品销售（产品编号，客户编号，销售日期，数量，销售额）,USE CPXS If exists (select name from sysobjects where name=客户销售记录 and type =p) drop procedure 客户销售记录 Go Create procedure 客户销售记录 v_客户名称 char(30) As Select 产品名称,价格,库存量, 数量,销售额 From 产品 a inner join 产品销售 b On a

18、.产品编号=b.产品编号 inner join 销售商 c on c.客户编号=b.客户编号 Where 客户名称 like v_客户名称,使用 EXECUTE 命令传递单个参数，执行”客户销售记录”存储过程，以 杭州% 为参数值。 其程序清单如下： use CPXS GO EXEC 客户销售记录 杭州% 当然，在执行过程中变量可以显式命名，如下： EXEC 客户销售记录 v_客户名称= 杭州%,例8：利用产品销售数据库CPXS，创建插入客户信息的存储过程（INS_Customer_proc），可以插入客户编号、客户名称，地区，负责人及电话，若不输入电话，则默认为1234567。,USE CP

19、XS If exists (select name from sysobjects where name= INS_Customer_proc and type =p) drop procedure INS_Customer_proc Go Create procedure INS_Customer_proc cno char(10), cname char(30), zone varchar(50), manager char(20), phoneno= 1234567 As INSERT INTO 销售商 values(cno, cname, zone, manager, phoneno)

20、 GO EXEC INS_Customer_proc 00110,杭州一厂,杭州,王明,例9：利用产品销售数据库CPXS，创建一个存储过程（SUMSALE_PROC）求客户XXX在指定日期的销售总量，并将最后求得的值输出进行判断。若销售量小于1000则显示“销售量不足”；若销售量介于10005000之间则显示“销售量适中”；若销售量大于5000，则显示“销售量超额”。,USE CPXS Create procedure SUMSALE_PROC cname char(20), vdate datetime, sumsale money output As select sumsale=sum(

21、销售额) from 销售商 a inner join 产品销售 b On a.客户编号=b.产品编号 Where 客户名称 like cname and 销售日期= vdate GO,DECLARE SUMTOTAL money EXEC SUMSALE_PROC 杭州%，2005-01-01， SUMTOTAL OUTPUT IF SUMTOTAL 5000 PRINT 销售量超额 ELSE PRINT 销售量适中,例10：创建一个存储过程（sum_proc），求给定参数的和。 （如给定起始值2，最大值100，递增值为2，则相当于求2到100的偶数和）,Create procedure su

22、m_proc ivalue int=0, fvalue int, increase int=1 As declare sum int set sum=0 while (ivalue =fvalue ) begin sum=sum+ ivalue ivalue= ivalue +increase end select sum GO EXEC sum_proc 2, 100, 2,1.3 查看和修改存储过程,1. 查看存储过程 存储过程被创建后，其名字存储在系统表sysobjects中，源代码存放在系统表syscomments中。 可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。,使

23、用系统存储过程来查看用户创建的存储过程,可供使用的系统存储过程及其语法形式如下： sp_help：用于显示存储过程的参数及其数据类型 sp_help objname= name -name为要查看的存储过程的名称。 sp_helptext：用于显示存储过程的源代码 sp_helptext objname= name sp_depends：用于显示和存储过程相关的数据库对象 sp_depends objname=object -object为要查看依赖关系的存储过程的名称。 sp_stored_procedures：用于返回当前数据库中的存储过程列表,2. 修改存储过程,使用ALTER PROC

24、EDURE语句可以更改已创建的存储过程，但不会更改权限，也不影响相关的存储过程或触发器。其语法形式如下： ALTER PROCEDURE procedure_name parameterdata_type VARYING=defaultOUTPUT,.n WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONASsql_statement .n ,例11：修改上述创建的“客户销售记录”存储过程，使其可以查询指定销售日期的客户销售信息。销售日期默认值为2002-3-18,USE CPXS Go alter procedure 客户销售记录 v_客户名称 char(30), v_销售日期 datetime=2002-3-18 As Select 产品名称,价格,库存量, 数量,销售额 From 产品 a inner join 产品销售 b On a.产品编号=b.产品编号 inner join 销售商 c on c.客户编号=b.客户编号 Where 客户名称 like v_客户名称 and 销售日期= v_销售日期,1.4 重命名