第9章 存储过程的创建和使用

• 例 9-2-2 ：创建一个带有参数的存储过程 Stu_Info，该存 储过程根据传入的学生编号，在T_STUDENT中查询此学 生的信息。 • USE STUDENT
• IF EXISTS (SELECT name FROM sysobjects • • • GO WHERE name = 'Stu_Info' AND type = 'P') DROP PROCEDURE Stu_Info
• • • • • • • • • • • • • •
--创建存储过程 CREATE PROCEDURE StuScoreInfo AS Select 班级=SUBSTRING(T_STUDENT.S_NUMBER,1, LEN(T_STUDENT.S_NUMBER)-2), 学号=SUBSTRING(T_STUDENT.S_NUMBER, LEN(T_STUDENT.S_NUMBER)-1,2), S_NAME AS 姓名, SEX AS 性别, T_COURSE.C_NAME AS 课程名称, t_SCORE.SCORE AS 考试分数 FROM T_STUDENT,T_COURSE,t_SCORE WHERE T_STUDENT.S_NUMBER=t_SCORE.S_NUMBER AND T_COURSE.C_NUMBER=t_SCORE.C_NUMBER • GO
sql_statement
END
9.2.2 使用Transact-SQL语句创 建存储过程
• • • • • 其中，各参数的意义如下： schema_name：过程所属架构的名称。 procedure_name：新存储过程的名称。 @ parameter：过程中的参数。 [ type_schema_name. ] data_type：参数 以及所属架构的数据类型。 • VARYING：指定作为输出参数支持的结果 集。仅适用于cursor参数。
9.2 创建存储过程
• 在SQL Server中，可以使用两种方法创建存储过 程： • 当创建存储过程时，需要确定存储过程的三个组 成部分： • 所有的输入参数以及传给调用者的输出参数。 • 被执行的针对数据库的操作语句，包括调用其他 存储过程的语句。 • 返回给调用者的状态值，以指明调用是成功还是 失败。
• ENCRYPTION ：表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 语句文本的条目。
• < sql_statement>： 要 包 含 在 过 程 中 的 一 个 或 多 个 Transact-SQL语句。
• 创建存储过程
• 1、创建普通的存储过程 例 ： 在 student 数 据 库 中 ， 创 建 一 个 名 称 为 myproc 的存储过程，该存储过程的功能是从 数据表 student_info 中查询所有男同学的信 息。
• 例 9 - 2 - 1 ： 创 建 一 个 名 称 为 “ StuInfo” 的 存 储 过 程 ， 要 求 完 成 以 下 功 能 ： 在 T_STUDENT 表中查询 05541班学生的学号、姓名、性别、出生日期和政治面貌五个 字段的内容。 在创建存储过程的窗口中，单击“查询”菜单，选择“指定模板参数的值”，会弹出 “指定模板参数的值”对话框。 在“指定模板参数的值”对话框中将“Procedure_Name”参数对应的名称修改为 “StuInfo”，单击“确定”按钮，关闭此对话框。在创建存储过程的窗口中将对应的 SELECT语句修改为以下程序代码： Select S_NUMBER AS 学号, S_NAME AS 姓名, SEX AS 性别, BIRTHDAY AS 出生日期, POLITY AS 政治面貌 FROM T_STUDENT WHERE LEFT(S_NUMBER,5)='05541' 输入完毕后，单击窗口工具栏上的“执行”按钮执行以上程序段，就会创建一个新的 存储过程“StuInfo”。
• 4百度文库创建能够返回值的存储过程 例：在 student 数据库中，创建一个名称为 Query_Study的存储过程，该存储过程的功 能是从数据表stud_info中根据学号查询某 一同学的姓名和系别。
USE student GO CREATE PROCEDURE Query_Study ( @sno char(6), @sn char(20) OUTPUT, @dept char(10) OUTPUT ) AS SELECT @sn=sn,@dept=dept FROM stud_info WHERE sno=@sno GO
• 例9-2-2：创建一个存储过程 StuScoreInfo，完成的功能是在表 T_STUDENT、表 T_COURSE 和表 t_SCORE中查询以下字段： 班级、学号、姓名、性别、课程名称、考试分数。 • 程序清单如下：
• --打开STUDENT数据库 • USE STUDENT • --查询是否已存在此存储过程，如果存在，就删除它 • IF EXISTS (SELECT name FROM sysobjects • • • GO WHERE name = 'StuScoreInfo' AND type = 'P') DROP PROCEDURE StuScoreInfo
• •
• • • • • • • •
9.2.2 使用Transact-SQL语句创 建存储过程
• 可以使用CREATE PROCEDURE命令创建 存储过程，考虑下列几个事项： • CREATE PROCEDURE 语 句 不 能 与 其 他 SQL语句在单个批处理中组合使用。 • 必须具有数据库的CREATE PROCEDURE 权限。 • 只能在当前数据库中创建存储过程。 • 不要创建任何使用sp_作为前缀的存储过程。
9.1.3 存储过程的分类
• 在 SQL Server 2005 中，用户自定义存储 过程有两种类型： Transact-SQL存储过程 和CLR存储过程。 • Transact-SQL 存 储 过 程 ： 是 指 保 存 的 Transact-SQL语句集合。 • CLR 存 储 过 程 ： 是 指 对 Microsoft .NET Framework 公共语言运行时 (CLR) 方法的 引用，可以接受和返回用户提供的参数。
9.1.1 存储过程的概念
• 存储过程有以下特点： • 存储过程中可以包含一条或多条 TransactSQL语句。 • 存储过程可以接受输入参数并可以返回输 出值。 • 在一个存储过程中可以调用另一个存储过 程。 • 存储过程可以返回执行情况的状态代码给 调用它的程序。
9.1.2 存储过程的优点
• --创建存储过程
• USE STUDENT • GO
CREATE PROCEDURE Stu_Info @S_NUMBER varchar(10)
AS Select 班级=SUBSTRING(T_STUDENT.S_NUMBER,1,
LEN(T_STUDENT.S_NUMBER)-2),
学号=SUBSTRING(T_STUDENT.S_NUMBER, LEN(T_STUDENT.S_NUMBER)-1,2), S_NAME AS 姓名, SEX AS 性别, BIRTHDAY AS 出生日期, POLITY AS 政治面貌 FROM T_STUDENT
9.2.2 使用Transact-SQL语句创建 存储过程
• 用CREATE PROCEDURE创建存储过程的语法如下： CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ OUT | OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] AS BEGIN
USE student GO CREATE PROCEDURE myproc AS SELECT * FROM student_info WHERE sex = ' 男' GO
• 2、创建带有参数的存储过程 例：在 student 数据库中，创建一个名称为 InsertRecord 的存储过程，该存储过程的 功能是向数据表 stud_info 中插入一条记 录，新记录的值由参数提供。
• 3、创建具有参数默认值的存储过程 例 ： 在 student 数 据 库 中 ， 创 建 一 个 名 称 为 InsertRecordDef的存储过程，该存储过程的 功能是向数据表student中插入一条记录，新 记录的值由参数提供，如果未提供系别 dept 的值时，由参数的默认值代替。
USE student GO CREATE PROCEDURE InsertRecordDef ( @sno char(6), @sn char(20), @age numeric(5), @sex char(2), @dept char(10)='无' ) AS INSERT INTO stud_info VALUES(@sno,@sn, @sex , @age, @dept) GO
第9章 存储过程 的创建和使用
本章学习目标
• • • • • • • 了解存储过程的概念 了解使用存储过程的优点 了解系统存储过程的特点及用途 掌握创建存储过程的方法 掌握执行存储过程的方法 掌握查看和修改存储过程的方法 掌握删除存储过程的方法
9.1 概述
9.1.1 存储过程的概念
• 在使用 Transact-SQL语言编程的过程中， 可以将某些需要多次调用的实现某个特定 任务的代码段编写成一个过程，将其保存 在数据库中，并由SQL Server服务器通过 过程名来调用它们，这些过程就叫做存储 过程。 • 存储过程在创建时就被编译和优化，调用 一次以后，相关信息就保存在内存中，下 次调用时可以直接执行。
9.2.2 使用Transact-SQL语句创 建存储过程
• default：参数的默认值。 • OUTPUT：指示参数是输出参数。 • {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} • RECOMPILE ：表明 SQL Server 不会缓存该过程的计 划，该过程将在运行时重新编译。
• 使用存储过程有很多优点，具体如下： • 实现了模块化编程，一个存储过程可以被 多个用户共享和重用。 • 存储过程具有对数据库立即访问的功能。 • 使用存储过程可以加快程序的运行速度。 • 使用存储过程可以减少网络流量。。 • 使用存储过程可以提高数据库的安全性。
9.1.3 存储过程的分类
• 在SQL Server中的存储过程分为两类：即系统提 供的存储过程和用户自定义的存储过程。 • 系统存储过程：由系统自动创建，系统存储过程 出现在每个系统定义数据库和用户定义数据库的 sys 构 架 中 。 在 SQL Server 2005 中 ， 可 将 GRANT、DENY和REVOKE权限应用于系统存储 过程。 • 用户自定义存储过程：是指封装了可重用代码的 模块或例程，由用户创建，能完成某一特定的功 能。可以接受输入参数，返回输出参数。
USE student GO CREATE PROCEDURE InsertRecord ( @sno char(6), @sn char(20), @age numeric(5), @sex char(2), @dept char(10) ) AS INSERT INTO stud_info VALUES(@sno,@sn,@sex,@age,@dept) GO
9.2.1 使用SQL Server管理控制 台创建存储过程
• 在SQL Server管理控制台中，选择指定的 服务器和数据库，展开数据库中的“可编 程性”文件夹，右击其中的“存储过程”， 在弹出的快捷菜单中选择“新建存储过 程…”选项。
9.2.1 使用SQL Server管理控制台创建存储过程