[工学]实验九存储过程

1、.存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中。是 SQL 语句和可选控制流程语句的预编译集合，作为一个单元被DBMS处理。存储过程以一个名称存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量（指定参数)）有条件地执行。也可被应用程序嵌套调用。存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。优点： 1、增强了SQL语言的功能和灵活性。可以在单个存储过程中执行一系列 SQL 语句； 可以从自己的存储过程内引用其它存储过程。故即可以简化一系列复杂语句，同时完成复杂

2、的判断和较复杂的运算。 2、保证数据的安全性和完整性。通过存储过程可以使没有权限的用户在控制之下间接 地存取数据库，从而保证数据的安全；可以使相关的动作在一起发生，从而可以维护数据库的完整性。 3、存储过程在创建时即在服务器上进行编译，所以执行起来比单个 SQL 语句快。 SQL Server 2005提供了三种存储过程 1、系统存储过程：由系统提供，以sp_为前缀。可以作为命令执行各种操作。用来进行系统的各项设定，取得系统及用户信息，执行系统管理工作。系统存储过程定义在系统数据库master中。 如 sp_help就是取得指定对象的相关信息 。 2、扩展存储过程：在SQL Server环境之

3、外执行的动态链接库称为扩展存储过程，其前缀是sp_（或xp_ ,用来调用操作系统提供的功能 ）。使用时需要先加载到SQL Server系统中，并且按照使用存储过程的方法调用执行。 3、用户自定义的存储过程：也称本地存储过程，是指在用户数据库中创建的存储过程，这种存储过程完成用户指定的数据库操作，其名称不能以sp_为前缀。如果本地存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。 用户存储过程的创建与执行语法格式：CREATE PROC EDURE procedure_name ; number /* 定义存储过程名*/ parameter

4、data_type /*定义参数的类型*/ VARYING = default OUTPUT   /*定义参数的属性*/ ,.n WITH   RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION /*定义存储过程的处理方式*/ FOR REPLICATION AS sql_statements /*执行的SQL操作*/v 参数： 每个参数名前要有一个“”符号,每一个存储过程的参数仅为该程序内部使用,参数的类型除了IMAGE外，其他SQL Server所支持的数据类型都可使用。 v OUTPUT：用来指定该参数是既有输入又

5、有输出值的。在调用这个创建的存储过程时，如果所指定的参数值是用户需要输入的参数，同时也需要在结果中输出的，则该项必须为OUTPUT；而如果只是做输出参数用，可以用CURSOR，同时在使用该参数时，必须指定VARYING和OUTPUT这两个语句。 例如：declare var int execute proc_kk ;1, var output select tot_amt v RECOMPILE：表示每次执行此存储过程时都重新编译一次 v ENCRYPTION：所创建的存储过程的内容会被加密v FOR REPLICATION：为复制创建，不可在代理服务器上执行【例1】利用school数据库中的

6、school表、score表和course表，编写一个无参数存储过程，用于查询所有学生的选修课程成绩记录。USE schoolGOCREATE PROCEDURE aASSELECT student.sno,sname,o,cname,degree FROM student,score,courseWHERE student.sno=score.sno AND o=o执行：EXEC a GO存储过程的参数传递v 调用（执行）存储过程时，可由用户指定存储过程中定义的参数，用来传递输入和输出的参数值。v 执行存储过程时，参数传递通常有两种方式： 传递参数依次为存储过程中定义的参数列表的值。 调用形

7、式： EXEC procedure_name <实参列表> 执行存储过程时，为已定义参数指定值 调用形式： EXEC procedure_name 参数1=值1, 参数2=值2 , 【例1】 设计一个存储过程maxno，以学号为参数，输出指定学号学生的所有课程中最高分和对应的课程名。USE schoolGOCREATE PROCEDURE maxno(no int)ASSELECT s.sno,s.sname,ame,sc,degreeFROM student s,course c,score scWHERE s.sno=no AND s.sno=sc.sno AND o=o AN

8、D sc.degree=(SELECT MAX(degree) FROM score WHERE sno=no)GO【例2】 编写一存储过程，根据school数据库的student、score、course三个表查询指定学生的选修课程成绩情况。CREATE PROCEDURE b sno char(5)ASSELECT student.sno , sname , o , cname , degree FROM student,score,courseWHERE student.sno=score.sno AND o=o AND student.sno=sno调用：EXEC b '101

9、'带有通配符参数的存储过程【例3】利用shool数据库中student表、score、course表创建一存储过程c，查询指定课程名的选修情况。该存储过程在参数中使用了模糊查询，如果没有提供参数，则使用预设的默认值。CREATE PROCEDURE ccname varchar(16)='%计算机%'ASSELECT student.sno,sname,o,cname,degree FROM student,score,courseWHERE student.sno=score.sno AND o=o AND cname LIKE cname调用：EXEC c EXEC

10、 c '%计%'带有OUTPUT参数的存储过程【例4】编写存储过程，统计指定课程选修人数，存储过程中使用了输入和输出参数。CREATE PROCEDURE mcname char(16),num int outputASSELECT num=COUNT(sno) FROM score,courseWHERE o=o AND cname LIKE cname调用：DECLARE num intEXEC m '操作系统', num outputSELECT num AS 选修人数'带有OUTPUT游标参数的存储过程【例5 】在school数据库的studen

11、t表上声明并打开一个游标。CREATE PROCEDURE student_cursor student_cur CURSOR VARYING OUTPUTASSET student_cur=CURSOR FORWARD_ONLY STATIC FOR SELECT sno,sname,ssex,class FROM studentOPEN student_curDECLARE mycursor CURSOREXEC student_cursor student_cur=mycursor OUTPUTFETCH NEXT FROM mycursorWHILE FETCH_STATUS=0 FE

12、TCH NEXT FROM mycursorCLOSE mycursorDEALLOCATE mycursor使用WITH ENCRYPTION选项【例6 】创建一个加密过程。使用sp_helptext系统存储过程获取关于加密的存储过程的信息。CREATE PROCEDURE encryption_this WITH ENCRYPTIONASSELECT * FROM student调用：EXEC sp_helptext 'encryption_this'存储过程管理1、查看用户存储过程用户可以调用如下几个系统存储过程来查看用户创建的存储过程：sp_helpsp_helptex

13、tsp_helpdepends语法：EXEC sp_help procedure_name2、重命名用户存储过程用户可以调用系统存储过程sp_rename重命名用户存储过程。语法： EXEC sp_rename 原存储过程名，新存储过程名3、修改存储过程使用 ALTER PROCEDURE 命令可修改已存在的存储过程。语法格式：ALTER PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH   RECOMPILE | ENCRYPTION  | RECOMPILE , ENCRYPTION  FOR REPLICATION AS  sql_statements4、用户存储过程的删除如果确认一个数据库的某个存储过程与其它对象没有任何依赖关系，则可用 DROP PROCEDURE 语句永久地删除该存储过程。 语法格式： DROP PROCEDURE procedure ,.n 功能：从当前数据库中删除一个或多个存储过程或存储过