数据库原理与应用：第14章 存储过程

数据库原理与应用第14章存储过程

存储过程是SQL语句和可选控制流程语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。

存储过程具有以下优点：

1、可以在单个存储过程中执行一系列SQL语句。

2、可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

3、存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快。

14.1存储过程的分类(1)系统存储过程系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库master中，其前缀是sp_。(2)本地存储过程本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成用户指定的数据库操作，其名称不能以sp_为前缀。

(3)临时存储过程临时存储过程属于本地存储过程。如果本地存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。

(4)远程存储过程远程存储过程指从远程服务器上调用的存储过程。(5)扩展存储过程在SQLServer环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。使用时需要先加载到SQLServer系统中，并且按照使用存储过程的方法执行。14.2用户存储过程的创建与执行1.通过SQL命令创建和执行存储过程步骤如下：(1)定义如下存储过程USEschoolGOCREATEPROCEDUREaASSELECTstudent.sno,sname,course.cno,cname,degreeFROMstudent,score,courseWHEREstudent.sno=score.snoANDscore.cno=course.cnoGO(2)调用存储过程EXECaGO14.2用户存储过程的创建与执行1)创建存储过程语法格式：CREATEPROC[EDURE]procedure_name[;number]/*定义存储过程名*/[{@parameterdata_type} /*定义参数的类型*/[VARYING][=default][OUTPUT]

] /*定义参数的属性*/[,...n] [WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] /*定义存储过程的处理方式*/[FORREPLICATION]ASsql_statements /*执行的操作*/14.2用户存储过程的创建与执行2)存储过程的执行通过EXEC命令可以执行一个已定义的存储过程。语法格式：[EXEC[UTE]]

[@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,...n]一、设计简单的存储过程例14.1利用school数据库中的school表、score表和course表，编写一无参存储过程用于查询所有学生的选修课程成绩记录。CREATEPROCEDUREaASSELECTstudent.sno,sname,course.cno,cname,degreeFROMstudent,score,courseWHEREstudent.sno=score.snoANDscore.cno=course.cnoEXECa二、使用带有参数的存储过程例14.2编写一存储过程，根据school数据库的student、score、course三个表查询指定学生的选修课程成绩情况。CREATEPROCEDUREb@snochar(5)ASSELECTstudent.sno,sname,course.cno,cname,degreeFROMstudent,score,courseWHEREstudent.sno=score.snoANDscore.cno=course.cnoANDstudent.sno=@snoEXECb'101'三、带有通配符参数的存储过程例14.3利用shool数据库中student表、score、course表创建一存储过程c，查询指定课程名的选修情况。该存储过程在参数中使用了模糊查询，如果没有提供参数，则使用预设的默认值。CREATEPROCEDUREc@cnamevarchar(16)='%计算机%'ASSELECTstudent.sno,sname,course.cno,cname,degreeFROMstudent,score,courseWHEREstudent.sno=score.snoANDscore.cno=course.cnoANDcnameLIKE@cnameEXECcEXECc'操作%'EXECc'%计%'四、带有OUTPUT参数的存储过程例14.4编写一存储过程，统计指定课程选修人数，存储过程中使用了输入和输出参数。CREATEPROCEDUREm@cnamechar(16),@numintoutputASSELECT@num=COUNT(sno)FROMscore,courseWHEREscore.cno=course.cnoANDcnameLIKE@cnameDECLARE@numintEXECm'操作系统',@numoutputSELECT@numAS'选修次数'五、带有OUTPUT游标参数的存储过程例14.5在school数据库的student表上声明并打开一个游标。CREATEPROCEDUREstudent_cursor@student_curCURSORVARYINGOUTPUTASSET@student_cur=CURSORFORWARD_ONLYSTATICFORSELECTsno,sname,ssex,classFROMstudentOPEN@student_curDECLARE@mycursorCURSOREXECstudent_cursor@student_cur=@mycursorOUTPUTFETCHNEXTFROM@mycursorWHILE@@FETCH_STATUS=0FETCHNEXTFROM@mycursorCLOSE@mycursorDEALLOCATE@mycursor六、使用WITHENCRYPTION选项例14.6创建加密过程（使用sp_helptext系统存储过程获取关于加密的存储过程的信息）。CREATEPROCEDUREencryption_thisWITHENCRYPTIONASSELECT*FROMstudentEXECsp_helptext'encryption_this'七、创建用户定义的系统存储过程例14.7创建存储过程sp_showtable,显示以student开头的所有表名及其对应的索引名。如果没有指定参数，该存储过程将返回以course开头的所有表名及对应的索引名。CREATEPROCEDUREsp_showtable@tablevarchar(20)='course%'ASSELECTsysobjects.name,sysindexes.nameFROMsysobjects,sysindexesWHEREsysobjects.id=sysindexes.idANDsysobjects.nameLIKE@tableANDindid<>0ANDindid<>255EXECsp_showtableEXECsp_showtable'student%'14.2用户存储过程的创建与执行2.利用企业管理器创建用户存储过程(1)在SQLServer

企业管理器的目录树中，选中school数据库文件夹下的存储过程图标右击，出现一快捷菜单，选择菜单项“新建存储过程”，进入新建存储过程窗口，如图所示；(2)在新建存储过程窗口输入定义的存储过程，然后选择“确定”按钮。14.3用户存储过程的编辑修改使用ALTERPROCEDURE命令可修改已存在的存储过程。语法格式：ALTERPROC[EDURE]procedure_name[;number] [{@parameterdata_type} [VARYING][=default][OUTPUT]][,...n][WITH

{RECOMPILE|ENCRYPTION

|RECOMPILE,ENCRYPTION

}][FORREPLICATION]AS

sql_statements14.4用户存储过程的删除

如果确认一个数据库的某个