第7章存储过程和触发器 第7章存储过程和触发器 本章要点 口存储过程类型 口存储过程的创建、执行、修改和删除 口触发器概述 口触发器的创建、修改和删除 2023/7/16 《SQLSever程序设计》
第7章 存储过程和触发器 2023/7/16 《SQL Sever 程序设计》 1 第7章 存储过程和触发器 本章要点 存储过程类型 存储过程的创建、执行、修改和删除 触发器概述 触发器的创建、修改和删除
第7章存储过程和触发器 7.1存储过程 存储过程是一种数据库对象，将执行计划存储在数据库的服务器中。 (运行的速度比独立运行同样的程序快)可以了解数据库对象和数据 库信息。 7.1.1存储过程类型 1、系统提供的存储过程 ·存储在master)库中，以sp_为前缀. ·从系统表中获取 ·调用时前面不用加数据库名。 创建数据库时，一些存储过程会被自动创建。 2、用户自定义的存储过程 用户创建来完成某一特定功能，如查询用户所需的数据信息。 2023/7/16 《SQLSever程序设计》
第7章 存储过程和触发器 2023/7/16 《SQL Sever 程序设计》 2 7.1 存储过程 存储过程是一种数据库对象,将执行计划存储在数据库的服务器中. (运行的速度比独立运行同样的程序快)可以了解数据库对象和数据 库信息。 7.1.1 存储过程类型 1、系统提供的存储过程 • 存储在master库中,以sp_为前缀. • 从系统表中获取. • 调用时前面不用加数据库名。 • 创建数据库时，一些存储过程会被自动创建。 2、用户自定义的存储过程 用户创建来完成某一特定功能，如查询用户所需的数据信息
第7章存储过程和触发器 7.1.2创建存储过程 创建存储过程时，需要确定存储过程的三个组成部分： 口所有的输入参数以及传给调用者的输出参数： 口被执行的针对数据库的操作的操作语句，包括调用其它存储过程的语 句； 口返回给调用者的状态值，以指明调用是否成功。 1、用企业管理器创建存储过程。 数据库/存储过程/新建存储过程/输入存储过程下文/检查语法/确定 在右窗格中/右键/所有任务/管理权限 2023/7/16 《SQLSever程序设计》 3
第7章 存储过程和触发器 2023/7/16 《SQL Sever 程序设计》 3 7.1.２ 创建存储过程 创建存储过程时，需要确定存储过程的三个组成部分： 所有的输入参数以及传给调用者的输出参数； 被执行的针对数据库的操作的操作语句，包括调用其它存储过程的语 句； 返回给调用者的状态值，以指明调用是否成功。 1、用企业管理器创建存储过程。 数据库／存储过程／新建存储过程／输入存储过程下文／检查语法／确定 在右窗格中／右键／所有任务／管理权限
第7章存储过程和触发器 2、用T一SQL命令的create procedure创建存储过程。 创建存储过程前要考虑到四点： 口在一个批处理中，create procedure语句不能与其它SQL 语句合并在一起. 口数据库的所有者具有默认的创建存储过程的权限，它可将该权 限传递给其它的用户. 口存储过程作为数据库对象其命名规则必须符合命名规则， 口只能在当前数据库中创建以属于当前数据库的存储过程， 2023/7/16 《SQL Sever程序设计》
第7章 存储过程和触发器 2023/7/16 《SQL Sever 程序设计》 4 2、用Ｔ－ＳＱＬ命令的create procedure创建存储过程。 创建存储过程前要考虑到四点： 在一个批处理中，create procedure 语句不能与其它ＳＱＬ 语句合并在一起． 数据库的所有者具有默认的创建存储过程的权限，它可将该权 限传递给其它的用户． 存储过程作为数据库对象其命名规则必须符合命名规则． 只能在当前数据库中创建以属于当前数据库的存储过程．
第7章存储过程和触发器 语法规则如下： Create procedure procedurename [number] [{@parameter data_type} [VARYING][=default][OUTPUT]][...n] [WITH (RECOMPILE ENCRYPTION RECOMPILE,ENCRYPTION)] [FOR REPLICATION]/**用于复制/ AS sql_statement [...n] 2023/7/16 《SQLSever程序设计》
第7章 存储过程和触发器 2023/7/16 《SQL Sever 程序设计》 5 语法规则如下： Create procedure procedure＿name ［；number］ ［｛＠parameter data＿type｝ ［VARYING］[=default][OUTPUT]] [,…n] [WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}] [FOR REPLICATION] /**用于复制/ AS sql_statement […n]
procedure name: 要创建的过程名字。[：，mm中er是一个馨孩存储过程和触发器 用来区别同一组同名的存储过程。同一数据库中名字必须惟一。 @parameter:存储过程的参数，可以有一个或多个参数。当调存储 过程时用户必须给出所有的参数。若参数以@parameter=value 出现，参数的次序可以不同；其它参数也必须以该形式出现。 data_type:参数的数据类型。可以用所有的数据类型。当定义 cursor数据类型时，必须对VARYING、和OUTPUT关键字进行定义。 VARYING:指定由OUTPUT参数支持的结果集，仅用于游标型参数。 Default:指定默认值。 OUTPUT:表明该参数是一个返回参数。Text不能用于OUTPUT中. RECOMPILE:指明SQL Sever并不存储过程的执行计划。每执行一次， 就要重新编译。 ENCRYPTION:表明SQL Severi加密了syscomments?表，该表的text字段 是包含有create procedurei语句的存储过程文本。如果使用 encryption字段，查看syscomments表无法看到存储过程内容。 AS:指明该存储过程将要执行的动作。 sql_statement:包括任何数量和类型的包含在存储过程中的SQL语句。 2023/7/16 《SQLSever程序设计》 6
第7章 存储过程和触发器 2023/7/16 《SQL Sever 程序设计》 6 procedure＿name：要创建的过程名字。［；number］是一个整数， 用来区别同一组同名的存储过程。同一数据库中名字必须惟一。 ＠parameter：存储过程的参数，可以有一个或多个参数。当调存储 过程时用户必须给出所有的参数。若参数以@ parameter=value 出现，参数的次序可以不同；其它参数也必须以该形式出现。 data＿type：参数的数据类型。可以用所有的数据类型。当定义 cursor数据类型时，必须对VARYING、和OUTPUT关键字进行定义。 VARYING：指定由OUTPUT 参数支持的结果集，仅用于游标型参数。 Default：指定默认值。 OUTPUT：表明该参数是一个返回参数。Text不能用于OUTPUT中. RECOMPILE：指明SQL Sever并不存储过程的执行计划。每执行一次， 就要重新编译。 ENCRYPTION：表明SQL Sever加密了syscomments表，该表的text字段 是包含有create procedure语句的存储过程文本。如果使用 encryption字段，查看syscomments表无法看到存储过程内容。 AS：指明该存储过程将要执行的动作。 sql_statement：包括任何数量和类型的包含在存储过程中的SQL语句
第7章存储过程和触发器 例7.1：创建存储过程返回所有学生及其学分： Use XSCJ IF exists (select name from sysobjects Where name='student_infor'and type=p') Drop procedure student infor Go CREATE PROCEDURE student infor AS Select学号，姓名，总学分 From XSQK go 2023/7/16 《SQLSever程序设计》
第7章 存储过程和触发器 2023/7/16 《SQL Sever 程序设计》 7 例7.1：创建存储过程返回所有学生及其学分： Use XSCJ IF exists (select name from sysobjects Where name=‘student_infor’ and type=‘p’ ) Drop procedure student_infor Go CREATE PROCEDURE student_infor AS Select 学号，姓名 ，总学分 From XSQK go
第7章存储过程和触发器 例7.2：创建存储过程返回所有学生及其学分，并在该存储过程中使用参数： Use XSCJK IF exists (select name from sysobjects Where name='student_infor'and type='p') Drop procedure student_infor Go Use XSCJK CREATE PROCEDURE student infor @学号char(8) AS Select学号，姓名，总学分From XSQK Where学号=@学号 go 2023/7/16 《SQLSever程序设计》
第7章 存储过程和触发器 2023/7/16 《SQL Sever 程序设计》 8 例7.2：创建存储过程返回所有学生及其学分，并在该存储过程中使用参数： Use XSCJK IF exists (select name from sysobjects Where name=‘student_infor’ and type=‘p’ ) Drop procedure student_infor Go Use XSCJK CREATE PROCEDURE student_infor @学号 char（8） AS Select 学号，姓名 ，总学分 From XSQK Where 学号=@学号 go
第7章存储过程和触发器 7.1.3执行存储过程EXECUTE 语法规则如下： [EXECUTE] {[@return_status=] {procedure_name [number]@procedure_name_var} [[@parameter=](value|@variable[OUTPUT]|[DEFAULT][,...n]} [WITH RECOMPILE] } @return status,是可选一整型变量，用来存储存储过程向调用者返回 的值. @procedure_name_var是一变量名，用来代表存储过程的名字. 2023/7/16 《SQLSever程序设计》 9
第7章 存储过程和触发器 2023/7/16 《SQL Sever 程序设计》 9 7.1.3 执行存储过程 EXECUTE 语法规则如下： [EXECUTE] {[@return_status=] {procedure_name [;number]|@procedure_name_var} [[@parameter=]{value|@variable[OUTPUT]|[DEFAULT][,…n]} [WITH RECOMPILE] } • @return_status是可选一整型变量,用来存储存储过程向调用者返回 的值. • @procedure_name_var是一变量名,用来代表存储过程的名字
第7章存储过程和触发器 例7.3：该存储过程被用来将两个字符串连接成一个字符串，并将结果返回： CREATE PROCEDURE strconnect @str1 varchar(20),@str2 varchar(20), @connect varchar(40)output AS Select @connect=@str1+@str2 /*select用于赋值，此时运行看不到 结果*/ Declare @result varchar(40) Execute strconnect 'I am','John','string' Select 'The result'=@result 运行结果： The result NULL/米无OUTPUT*/ 2023/7/16 《SQLSever程序设计》
第7章 存储过程和触发器 2023/7/16 《SQL Sever 程序设计》 10 例7.3：该存储过程被用来将两个字符串连接成一个字符串,并将结果返回: CREATE PROCEDURE strconnect @str1 varchar(20),@str2 varchar(20), @connect varchar(40) output AS Select @connect=@str1+@str2 /* select用于赋值,此时运行看不到 结果*/ Declare @result varchar(40) Execute strconnect ‘I am’ , ’John’ , ’string’ Select ‘The result’=@result 运行结果: The result NULL /*无OUTPUT*/