第8章存储过程 ●8.1存储过程的概念 ●8.2存储过程的类型 83创建存陆过程 ●8.4执行存储过程 8.5查看、修改存储过程 ●8.6删除存储过程
第8章 存储过程 8.1 存储过程的概念 8.2 存储过程的类型 8.3 创建存储过程 8.4 执行存储过程 8.5 查看、修改存储过程 8.6 删除存储过程
8.1存储过程的概念 SQL Server的存储过程类似于其它编程语 言中的函数。在使用T-SQL语言编程的过程中 第 8可以将某些需要多次调用的实现某个特定任务 的代码段编写成一个过程,将其保存在数据库 中,并由 SQL Server服务器通过过程名来调用 它们,这些过程就叫做存储过程 存储过程在创建时就被编译和优化,调用 次以后,相关信息就保存在内存中,下次调 用时可以直接执行
8.1 存储过程的概念 SQL Server的存储过程类似于其它编程语 言中的函数。在使用T-SQL语言编程的过程中， 可以将某些需要多次调用的实现某个特定任务 的代码段编写成一个过程，将其保存在数据库 中，并由SQL Server服务器通过过程名来调用 它们，这些过程就叫做存储过程。 存储过程在创建时就被编译和优化，调用 一次以后，相关信息就保存在内存中，下次调 用时可以直接执行。 第 8 章 存 储 过 程
8.1存储过程的概念 存储过程相对于本地SQL语句,具有以下优点 单个存储过程中可以执行一组相关的SQL语句 存储过程能够实现较快的执行速度。只需创建过 奇程一次并将其存储在数据库中,以后即可在程序中调 有用该过程任意次。因为存储过程是预编译的,在首次 储运行一个存储过程时,查询优化器对其进行分析、优 化,并给出最终被存在系统表中的执行计划 存储过程独立于程序源代码,便于单独修改 存储过程能够减少网络流量。存储过程由一条执 行过程代码的单独语句就可实现调用,网络中传送的 只是该调用语句,而不需要在网络中发送所有相关的 源代码,所以其执行时要比直接使用SQL语句快
8.1 存储过程的概念 存储过程相对于本地SQL语句，具有以下优点： l 单个存储过程中可以执行一组相关的SQL语句； l 存储过程能够实现较快的执行速度。只需创建过 程一次并将其存储在数据库中，以后即可在程序中调 用该过程任意次。因为存储过程是预编译的，在首次 运行一个存储过程时，查询优化器对其进行分析、优 化，并给出最终被存在系统表中的执行计划； l 存储过程独立于程序源代码，便于单独修改； l 存储过程能够减少网络流量。存储过程由一条执 行过程代码的单独语句就可实现调用，网络中传送的 只是该调用语句，而不需要在网络中发送所有相关的 源代码，所以其执行时要比直接使用SQL语句快。 第 8 章 存 储 过 程
8.2存储过程的类型 第82.1系统存储过程 系统存储过程是指由 SQL Server提供的存储过程,用以 存管理 SQL Server和显示有关数据库和用户的信息。 过8.2.2临时存储过程 SQL Server支持两种临时过程:局部临时过程和全局临 时过程。其中在过程名的前面带有#符号的表示是局部临时过 程;而带有#符号的表示是全局临时过程。 8.2.3远程存储过程 仅限于在远程 SQL Server上执行存储过程。 8.2.4扩展存储过程 扩展存储过程是 SQL Server可以动态装载并执行的动态链 接库DL
8.2 存储过程的类型 8.2.1 系统存储过程 系统存储过程是指由SQL Server提供的存储过程，用以 管理SQL Server和显示有关数据库和用户的信息。 8.2.2 临时存储过程 SQL Server支持两种临时过程：局部临时过程和全局临 时过程。其中在过程名的前面带有#符号的表示是局部临时过 程；而带有##符号的表示是全局临时过程。 8.2.3 远程存储过程 仅限于在远程SQL Server上执行存储过程。 8.2.4 扩展存储过程 扩展存储过程是SQL Server可以动态装载并执行的动态链 接库（DLL）。 第 8 章 存 储 过 程
8.3创建存储过程 创建存储过程既可以使用T-S哑L语句实现,也可以 在企业管理器中完成。存储过程创建后,它的名称 存储在系统表 sysob jects中;它的源代码存放在系 奇统表 syscomments中 创建存储过程时,需要确定存储过程的三个组成 过部分: 所有的输入参数以及传给调用者的输出参数; 被执行的针对数据库的操作语句,包括调用其 它存储过程的语句; 返回给调用者的状态值,以指明调用是成功还 是失败
8.3 创建存储过程 创建存储过程既可以使用T-SQL语句实现，也可以 在企业管理器中完成。存储过程创建后，它的名称 存储在系统表sysobjects中；它的源代码存放在系 统表syscomments中。 创建存储过程时，需要确定存储过程的三个组成 部分： l 所有的输入参数以及传给调用者的输出参数； l 被执行的针对数据库的操作语句，包括调用其 它存储过程的语句； l 返回给调用者的状态值，以指明调用是成功还 是失败。 第 8 章 存 储 过 程
8.3.1使用T-S语句创建存储过程 创建存储过程的TSL语句的语法为: CREATE PROCIEDUREI 第8章存储过 8 procedure_name[: number i@parameter data typeVARYING] [default] OUTPUTI][…n] WITH RECOMPILE ENCRYPTION RECOMPILE, ENCRYPTION I FOR REPLICATION I AS sql statement[…n]
8.3.1 使用T-SQL语句创建存储过程 创建存储过程的T-SQL语句的语法为： CREATE PROC[EDURE] procedure_name[;number] [ { @parameter data_type}[VARYING] [=default] [OUTPUT] ] [,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ] 第 8 章 存 储 过 程
8.3.1使用S语句创建存储过程 【例8-1】在 Educational数据库中创建一个存储过程 唤 spStuGrade Class_term,要求返回某班某一学期所有学生 8 章的课程成绩。 存 CREATE PROC spStuGrade Class term/*存储过程名* @Para ClassID char(8) *班级名,输入参数* @Para Term tinyint=l /*学期,输入参数,默认值为1*/ WITH ENCRYPTION /*使用加密存放* AS SELECT S Student Id, s StudentName. C. CourseName, G Grade FROM Grade g INNEr Join Student S/*按 Studentid联接 Student* ON G StudentID= S Student ID INNER JOIN Course C /*fiCourseIDEE 接 Course*/ ON G CourseID= C Course Id INNer join Speciality Course Sc ON G Courseid= sc CourseId /*按 Coursed联接 Speciality Course */ WHERE S Class id=@Para Classid and SC Term=@Para Term /*查询 的班级及学期* ORDER BY S StudentID ○
8.3.1 使用T-SQL语句创建存储过程 【例8-1】 在Educational数据库中创建一个存储过程 spStuGrade_Class_term，要求返回某班某－学期所有学生 的课程成绩。 CREATE PROC spStuGrade_Class_term /* 存储过程名 */ @Para_ClassID char(8), /* 班级名，输入参数 */ @Para_Term tinyint=1 /* 学期，输入参数，默认值为1 */ WITH ENCRYPTION /* 使用加密存放 */ AS SELECT S.StudentID, S.StudentName, C.CourseName, G.Grade FROM Grade G INNER JOIN Student S /* 按StudentID联接Student */ ON G.StudentID = S.StudentID INNER JOIN Course C /* 按CourseID联 接Course */ ON G.CourseID = C.CourseID INNER JOIN Speciality_Course SC ON G.CourseID = SC.CourseID /* 按 CourseID 联 接 Speciality_Course */ WHERE S.ClassID=@Para_ClassID And SC.Term=@Para_Term /* 查询 的班级及学期 */ ORDER BY S.StudentID 第 8 章 存 储 过 程
8.3.2使用企业管理器创建存储过程 第 通过企业管理器,可以方便地创建存储过程,具体步骤如下 8 1)启动企业管理器,展开相应的服务器组和所要操作的服务器节点。 2)展开“数据库”文件夹,再展开要在其中创建存储过程的数据库 存(这里为 EDucational)节点 3)右击“存储过程”项,在弹出的快捷菜单中执行“新建存储过程” 过菜单项,出现如图8-2所示的“新建存储过程”对话框。 4)在“文本”文本框中显示了 CREATE PROCEDURE语句的框架。在此 修改存储过程的名称、参数以及对应的SQL语句,将例8-1中的存储过程输 入,创建一个名为 spStuGrade Class term的存储过程 5)单击“检査语法”按钮,检查存储过程中语句的合法性,直到 语法检査成功”。 6)若要更改权限,则单击“权限”按钮,在出现的“对象属性”对话 框中设置该存储过程的权限。 7)单击“确定”按钮,就可完成存储过程的创建工作。此时,检查 “存储过程”项,会发现系统中多了一个名为 spStuGrade_ Class term的 存储过程
8.3.2 使用企业管理器创建存储过程 通过企业管理器，可以方便地创建存储过程，具体步骤如下： 1） 启动企业管理器，展开相应的服务器组和所要操作的服务器节点。 2） 展开“数据库”文件夹，再展开要在其中创建存储过程的数据库 （这里为Educational）节点。 3） 右击“存储过程”项，在弹出的快捷菜单中执行“新建存储过程” 菜单项，出现如图8-2所示的“新建存储过程”对话框。 4） 在“文本”文本框中显示了CREATE PROCEDURE语句的框架。在此 修改存储过程的名称、参数以及对应的SQL语句，将例8-1中的存储过程输 入，创建一个名为spStuGrade_Class_term的存储过程。 5） 单击“检查语法”按钮，检查存储过程中语句的合法性，直到 “语法检查成功”。 6）若要更改权限，则单击“权限”按钮，在出现的“对象属性”对话 框中设置该存储过程的权限。 7） 单击“确定”按钮，就可完成存储过程的创建工作。此时，检查 “存储过程”项，会发现系统中多了一个名为spStuGrade_Class_term的 存储过程。 第 8 章 存 储 过 程
8.4执行存储过程 第 使用T-S哑L的 EXECUTE语句可以执行存储过程,如果存储过 8程是批处理中的第一条语句,那么省略 EXECUTE关键字也可以 章执行存储过程。如果需要在每次 SQL Server启动时,由系统 存自动执行相关的存储过程。 8.4.1使用 EXECUTE语句执行存储过程 使用 EXECUTE语句可以执行存储过程。ENCU语句的语法 格式如下: L EXECLUTE I LOreturn status= procedure name[; number procedure name var I [Parameter Name=]( value @variable [OUTPUt DEFAULTIL n L WITH RECOMPILE I
8.4 执行存储过程 使用T-SQL的EXECUTE语句可以执行存储过程，如果存储过 程是批处理中的第一条语句，那么省略EXECUTE关键字也可以 执行存储过程。如果需要在每次SQL Server启动时，由系统 自动执行相关的存储过程。 8.4.1 使用EXECUTE语句执行存储过程 使用EXECUTE语句可以执行存储过程。EXECUTE语句的语法 格式如下： [ EXEC[UTE] ] { [@return_status= ]{ procedure_name[;number] | @procedure_name_var } [ [@parameterName= ] { value | @variable [OUTPUT] | [DEFAULT] }[ ,...n ] ] [ WITH RECOMPILE ] 第 8 章 存 储 过 程
8.4执行存储过程 1.顺序格式 顺序格式是传递参数的最简单方式,它以存储过程定义时 第参数说明的先后次序,以alue或 variable的方式传递。 8 例8-3】调用例8-1中创建的存储过程 spStuGrade_ Class term,返回030501班第5学期所有学生的 有课程成绩 储 uSE Educational GO EXECUTE spStuGrade Class term 030501 /*030501班第5学期米
8.4 执行存储过程 1．顺序格式 顺序格式是传递参数的最简单方式，它以存储过程定义时 参数说明的先后次序，以value或@variable的方式传递。 【 例 8-3】 调 用 例 8-1 中 创 建 的 存 储 过 程 spStuGrade_Class_term，返回030501班第5学期所有学生的 课程成绩。 USE Educational GO EXECUTE spStuGrade_Class_term '030501', 5 /* 030501班第5学期 */ 第 8 章 存 储 过 程