数据库编程存储过程的创建市公开课一等奖省赛课获奖课件

第八章数据库编程1数据库编程存储过程的创建第1页应用程序和数据库之间主要编程接口：SQL语言1.在客户端存放代码，并创建编写访问数据库服务器发送SQL语句（比如在C#、Java等客户端编程语言中嵌入访问数据库SQL语句）2.将sql语句存放在数据库服务器端，然后后应用程序调用执行这些SQL语句。2数据库编程存储过程的创建第2页第8章存放过程8.1存放过程概述8.2创建管理无参存放过程

8.3创建管理带参存放过程

3数据库编程存储过程的创建第3页8.1存放过程概述1.存放过程概念存放过程是为了实现某个特定任务，由一组预先编译好SQL语句组成，将其放在服务器上，由用户经过指定存放过程名字来执行一个数据库对象。2.存放过程类型

系统存放过程扩展存放过程用户自定义存放过程系统存放过程存放以SP_为前缀,是由SQLServer自己创建、管理和使用一个特殊存放过程，不能对其进行修改或删除。如Sp_helpdb、Sp_renamedb等。由用户自行创建存放过程，能够输入参数、向客户端返回表格或结果、消息等，也能够返回输出函数。4数据库编程存储过程的创建第4页8.2无参存放过程创建使用SQL语句创建存放过程1）语法格式以下：CREATEPROC[EDURE

]procedure_nameASsql_statement[...n]

2）语法注释：

procedure_name：新建存放过程名称，其名称必须符合标识符命名规则，且对于数据库及其全部者必须唯一。sql_statement：指存放过程中任意数目和类型Transact-SQL语句。5数据库编程存储过程的创建第5页8.2无参存放过程创建例1：返回30000041号学生成绩，不使用参数。代码以下：CREATEPROCEDUREstudent_infoASSELECT*FROM学生信息WHERE学号='30000041'6数据库编程存储过程的创建第6页创建存放过程注意事项只能在当前数据库中创建存放过程。数据库全部者能够创建存放过程，也能够授权其它用户创建存放过程。存放过程是数据库对象，其名称必须恪守标识符命名规则。不能将CREATEPROCEDURE语句与其它SQL语句组合到单个批处理中。创建存放过程时，应指定全部输入参数和向调用过程或批处理返回输出参数、执行数据库操作编程语句和返回至调用过程或批处理以表明成功或失败状态值。7数据库编程存储过程的创建第7页8.2无参存放过程执行对存放在服务器上存放过程，能够使用EXECUTE命令或其名称执行它，其语法格式以下：

EXEC[UTE]procedure_name对上例存放过程pr_searchorddate执行语句以下：EXECstudent_info8数据库编程存储过程的创建第8页8.3创建带参存放过程语法1）语法格式以下：CREATEPROC[DURE]procedure_name[{@parameterdata_type}[=default][OUTPUT][,…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASSql_statement9数据库编程存储过程的创建第9页8.3创建带参存放过程语法2）语法注释：parameter：存放过程中输入和输出参数。

data_type：参数数据类型。DEFAULT：表示参数默认值。假如定义了默认值，则在调用存放过程时，能够省略该参数值。

OUTPUT：表明参数是返回参数。该选项值能够返回给EXEC[UTE]。10数据库编程存储过程的创建第10页带参数存放过程例子：查询某个指定系学生考试情况，列出学生姓名、所在系、课程名和考试成绩。createprocedurep_StudentGrade1@deptchar(20)asselectsname,sdept,cname,gradefromstudentsinnerjoinscons.sno=o=owheresdept=@dept11数据库编程存储过程的创建第11页带多个输入参数并有默认值存放过程例子1：查询某个学生某门课程考试成绩，课程默认值为“VB”Createprocp_StudentGrade2

@snamechar(10),@cnamechar(20)=‘VB’Asselectsname,cname,gradefromstudentsinnerjoinscons.sno=o=o

wheresname=@snameandcname=@cname12数据库编程存储过程的创建第12页例2：CREATEPROCdeletestu@学号char(4)ASDELETE学生信息WHERE学号=@学号存放过程执行以下：Execdeletestu‘30000041’13数据库编程存储过程的创建第13页8.3.2创建/执行带输入参数存放过程执行带输入参数存放过程按位置传递参数值在执行存放过程语句中，直接给出参数值。当有多个参数时，给出参数次序与创建执行存放过程语句中参数次序一致，即参数传递次序就是参数定义次序。例：execp_StudentGrade2‘吴宾’，‘高等数学’经过参数名传递参数值在执行存放过程语句中，使用【参数名=参数值】形式给出参数值。其优点是参数能够以任意次序给出。例：例：execp_StudentGrade2@cname=‘高等数学’，@sname=‘吴宾’

14数据库编程存储过程的创建第14页8.3.2创建/执行带输入参数存放过程在输入参数中使用默认值在执行存放过程时，假如没有指定参数，则系统运行就会犯错；此时假如希望在执行时不给出参数也能正确运行，则在创建存放过程时给输入参数指定默认值。例：execp_StudentGrade2‘吴宾’相当于执行：execp_StudentGrade2‘吴宾’，‘VB’

15数据库编程存储过程的创建第15页8.3.3存放过程返回值存放过程在执行后都会返回一个整型值。假如执行成功，则返回0；不然返回-1到-99之间随机数，也能够使用RETURN语句来指定一个存放过程返回值。16数据库编程存储过程的创建第16页8.3.3存放过程返回值例：在northwind数据库创建一个存放过程，返回产品表中全部产品库存量。createprocpr_lierasbegindeclare@fanhuizhiintselect@fanhuizhi=sum(unitsinstock)fromproductsreturn@fanhuizhiend17数据库编程存储过程的创建第17页8.3.3存放过程返回值接收这个返回值必须要用变量来接收

declare@jieshouzhiintexec@jieshouzhi=pr_lierprint@jieshouzhi18数据库编程存储过程的创建第18页8.3.3存放过程返回值上例中返回值也能够用output参数来返回，能够将上例存放过程改写为：createprocpr_lier_2@fanhuizhiintoutputasbeginselect@fanhuizhi=sum(unitsinstock)fromproductsend19数据库编程存储过程的创建第19页8.3.3存放过程返回值接收output返回值也必须要用变量，如：declare@jieshouzhiintexecpr_lier_2@jieshouzhioutputprint@jieshouzhi注意：1)return返回是整数，output能够返回任何数据。2)假如让return返回非整数值，在创建存放过程时不会犯错，不过运行存放过程时将会犯错。20数据库编程存储过程的创建第20页例：统计全体学生人数，并将统计结果用输出参数返回Createprocp_count@totalintoutputAsselect@total=count(*)fromstudent执行此存放过程:Declare@resintExecp_count@resoutputPrint@res其中:declare为变量申明语句语法格式：declare@局部变量名数据类型@res:变量名。print：输出语句，表示将后边变量值显示在屏幕上语法格式：print‘ASCII文本字符串’|@局部变量名|字符串表示式

21数据库编程存储过程的创建第21页8.4查看存放过程

1.使用对象资源管理器查看存放过程2.使用系统存放过程查看存放过程信息在SQLServer中，能够使用sp_helptext、sp_depends、sp_help等系统存放过程来查看存放过程不一样信息。1）使用sp_helptext查看存放过程文本信息.其语法格式为：sp_helptext存放过程名2）使用sp_depends查看存放过程相关性.其语法格式为：sp_depends存放过程名3）使用sp_help查看存放过程普通信息.其语法格式为：

sp_help存放过程名22数据库编程存储过程的创建第22页8.5修改存放过程

修改存放过程语法格式为：ALTERPROC[DURE]procedure_name[{@parameterdata_type}[=default][OUTPUT][,…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASSql_statement23数据库编程存储过程的创建第23页8.5修改存放过程

例：修改存放过程student_info,查询学生信息表中某个指定学生信息。其程序清单以下：ALTER

PROC

student_info@学号varchar(14)AsSelect*From学生信息Where学号=@学号GO24数据库编程存储过程的创建第24页8.6编译存放过程在我们使用了一次存放过程后，可能会因为一些原因，必须向表中新增加数据列或者为表新添加索引，从而改变了数据库逻辑结构。这时，需要对存放过程进行重新编译，SQLServer提供三种重新编译存放过程方法：1、在建立存放过程时设定重新编译语法格式：CREATEPROCEDUREprocedure_name

WITHRECOMPILE

ASsql_statement2、在执行存放过程时设定重编译

语法格式：EXECUTE

procedure_nameWITH

RECOMPILE3、经过使用系统存放过程设定重编译

语法格式为：EXEC

sp_recompileOBJECT（procedure_name）25数据库编程存储过程的创建第25页8.7删除存放过程

1.使用SSMS对象资源管理器删除存放过程2.使用DROPPROCEDURE语句删