sql server 存储过程

• SQL Server的存储过程类似于其他编程语言 里的过程（procedure），具体体现在：
（1）存储过程可以接收参数，并以输出参数的形式 返回多个参数给调用存储过程的过程或批处理。 （2）存储过程也可以容纳存储过程，可以在对数据 库进行查询、修改的编程语句中调用其他的存储 过程。 （3）可以返回执行存储过程的状态值以反映存储过 程的执行情况
• • •
(3) /*带输入参数默认值存储过程，某个学生的选课成绩*/ use student if exists(select name from sysobjects where name=‘pro_mark1' and type='p') • drop proc pro_mark1 • go • create proc pro_mark1 • @name char(8)='%张' • as • select tbl_stu.sno,sname, c_name,mark • from tbl_course,tbl_stu,tbl_mark • where tbl_stu.sno=tbl_mark.sno and tbl_course.c_id=tbl_mark.c_id and sname like @name • go
• (4)/*带输出参数*/ • use student • if exists(select name from sysobjects where name=‘pro_sum' and type='p') • drop proc pro_sum • go • create proc pro_sum • @cname as varchar(50), • @sum as int output • as • select sum(mark) from tbl_mark,tbl_course • where tbl_course.c_id=tbl_mark.c_id and c_name=@cname • go
• exec sp_helptext 'pro_mark1'
4.相关的系统存储过程
• sp_help:报告有关数据库对象、用户定义数据类型或SQL Server所提供的数据类型的信息 (语法格式:sp_help[[ @objname =]name ])。 • sp_helptext:用于显示规则、默认值、未加密的存储过程、 用户定义函数、触发器或视图的文本. (语法格式:sp_helptext[ @objname =]name ) • sp_depends:用于显示有关数据库对象相关性的信息 (语法格式:sp_depends [ @objname = ]’object’)。 • sp_stored_procedures:用于返回当前环境中的存储过程列 表。
• • • • • • • • • • • • •
(2) /*带输入参数默认值存储过程，某个学生的选课成绩*/ use student if exists(select name from sysobjects where name=‘pro_mark1' and type='p') drop proc pro_mark1 go create proc pro_mark1 @name char(8) --定义输入参数 as select tbl_stu.sno,sname, c_name,mark from tbl_course,tbl_stu,tbl_mark where tbl_stu.sno=tbl_mark.sno and tbl_course.c_id=tbl_mark.c_id and sname=@name go exec stu_mark1 '王三'
2.优点
(1) 可以在单个存储过程中执行一系列 SQL 语句。 (2) 可以从自己的存储过程内引用其它存储过程， 这可以简化一系列复杂语句。 (3) 存储过程在创建时即在服务器上进行编译，所 以执行起来比单个SQL语句快。
3.三种类型
(1) 系统存储过程 （附有前缀sp_） (2) 扩展存储过程 （附有前缀xp_ ） (3) 用户自定义存储过程 （用户自己建立）
主要内容
• 存储过程基本概念 • 创建存储过程
1.创建存储过程
• 过程名 • 参数的说明 • 过程的主体(其中包含执行过程操作的T-SQL 语句)
2.使用sql语句创建存储过程
CREATE PROC[EDURE] 存储过程名[参数说明] [WITH RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION] AS SQL语句 其中： RECOMPILE：在执行完存储过程以后不在高速缓存里保留存储过 程的备份，所以每次执行存储过程时都需要对存储过程进行重新 编译和优化。 ENCRYPTION：存储过程作为数据库对象将在syscomments 表中留下完整的代码等信息。使用了ENCRYPTION参数以后， 将对访问这些数据的入口进行加密。
• declare @total int • exec pro_sum '计算机网络技术', @total output • Select @total
• (5)/*带加密*/ • use student • if exists(select name from sysobjects where name='stu_mark1' and type='p') • drop proc pro_mark1 • • • • • • • • • go create proc pro_mark1 @name char(8)='%张' with encryption /*加密*/ as select tbl_stu.sno,sname, c_name,mark from tbl_course,tbl_stu,tbl_mark where tbl_stu.sno=tbl_mark.sno and tbl_course.c_id=tbl_mark.c_id and sname like @name go
存储过程
概念 创建存储过程
复习
• 全局变量和局部变量的区别 • 何定义局部变量 • 如何给变量赋值
主要内容
• 存储过程基本概念 • 创建存储过程
1.什么是存储过程
• 存储过程（stored procedure）是一组事先编译 好的Transact-SQL代码。存储过程作为一个独立 的数据库对象，可以作为一个单元被用户的应用 程序调用。由于存储过程是已经编译好的代码， 所以执行的时候不必再次进行编译，从而提高了 程序的运行效率。
• 创建存储过程时,需要确定存储过程的三个 组成部分： 1 所有输入参数以及传给调用者的输出参 数。 2 被执行的针对数据库的操作语句,包括调 用其它存储过程的语句。 3 返回给调用者的状态值,以指明调用是成 功还是失败
3.实例
• • • • • • • • • • • • • (1) /*无参数存储过程*/ use student if exists(select name from sysobjects where name=‘proc_mark' and type='p') drop proc pro_mark go create proc pro_mark as select tbl_stu.sno,sname, c_name,mark from tbl_course,tbl_stu,tbl_mark where tbl_stu.sno=tbl_mark.sno and tbl_course.c_id=tbl_mark.c_id go /*调用*/ exec pro_mark