存储过程 是存储在数据库中
并按名字来调用执行的一组SQL语 句。
proc_mБайду номын сангаасne
insert .... if @@error!=0 rollback raiserror ....
isql
execute proc_mine
当前 数据库
存储过程的优点
• • • • • • 使用存储过程可加快运行速度 可减少网络交通 可重用、可共享性 存储过程也是一种安全机制 使用存储过程实现数据库完整性 提高数据与应用的独立性
流控制语言
流控制语句控制SQL语句的执行顺序，这在存储 过程、触发器、批中非常有用。流控制关键字（命令 ）包括： • IF ELSE 条件执行命令 • BEGIN END 将一组SQL语句作为一个语句块 • WHILE 循环执行相同的命令 • RETURN 无条件退出 • WAITFOR 依赖于某一事件的发生而执行 • GOTO N 转向已定义的语句标号处，也是 流控制命令。但不提倡使用GOTO命 令，以便使代码结构化。
执行存储过程
• 语法: [exec[ute]] procedure_name . . .
查看、改名和删除存储过程
• 查看存储过程 查看创建存储过程的源代码，使用： sp_helptext procedure_name 查看存储过程所依赖的表和视图信息，使用： sp_depends procedure_name
下面列出部分常用的全局变量信息:
全局变量 @@error @@rowcount（常用） @@version 赋给的值 由最近一个语句产生的错误号 被最近一个语句影响的行数 SQL Server的版本号
@@max_connections 最大用户个数 @@Servername
允许与该SQL Server连接的
创建存储过程
• create proc procedure_name as begin SQL_statements [return] end • 存储过程被放在当前正在使用的数据库中。 • 在存储过程中可以引用在其他数据库中的对象 • 创建存储过程(create proc)语句不能与其他的SQL 语句在同一个批中，即创建存储过程语句必须单独 成为一个批。 • 在存储过程中可以包含SQL语句，但是不能包含： use, create view, create rule, create default, create proc, create trigger
• 删除存储过程 语法：drop proc procedure_name 例： 删除已创建的存储过程reports： drop proc reports
存储过程的输入参数和参数的传递
输入参数(Input Parameters) 是指由调用程序向 存储过程 提供的变量值。它们在创建存储过程语 句中被定义，而 在执行该存储过程语句中给出相 应的变量值。使用输入 参数的优点是使存储过程 变得更加灵活。 语法： create proc procedure_name (@parameter_name datatype [, @parameter_name datatype...]) as begin SQL_statements return end
为 Server 方
@@version @@servername @@max_connections 为每个进程
@@rowcount,_@@error @@rowcount,_@@error
• 全局变量 (Gloabal Variable )是SQL Server系统 提供并赋值的变量。用户不能建立全局变量，也不能 使用SELECT语句去修改全局变量的值。全局变量的名 字用@@开始。大多数全局变量的值报告本次SQL Server启动后发生的系统活动，可以使用系统存储过 程sp_monitor显示全局变量的当前值。通常全局变量 的值赋给在同一批中的局部变量，以便保存和作进一 步处理
结果 SQL Server /11.0/P/Sun4/OS4.1.x/1/OPT/Fri Nov 3 14:33:54 PST 1995 ( 1 row affected ) ( 1 row affected ) title_id exists with price of -------------19.99 ( 1 row affected ) 2010 年7月30号看到此
举例： 创建带参数的存储过程 create proc proc_author_addr (@lname varchar(40)) as begin select au_id, au_fname, au_lname, phone, address, city, state, postalcode from authors where au_lname = @lname return -- 在调用程序的执行存储过程命令中，将相应的值 传递给 -- 这个输入参数：用'Green'替换@lname exec proc_author_addr @lname = 'Green' 或 exec proc_author_addr 'Green‘ end
全局变量的集合
SQL Server提供的全局变量集分为如下两类： • 与每次处理或与每次同SQL Server的连接有关的全 局变量。 例如@@rowcount, @@spid, @@error等 • 与内部管理所要求的关于系统内部信息有关的全局 变量。 例如，@@max_connections, @@version, @@Servername等。
该SQL Server的名字
举例 select @@version declare @book_price money select @book_price = price from titles where title_id = 'BU1032' if @@rowcount = 0 print 'no such title_id' else begin print 'title_id exists with' select 'price of' = @book_price End
使用局部变量时通常发生的错误
在程序中，使用局部变量通常容易发生的错误是数 据类型不匹配。即使用DECLARE语句定义局部变量的 数据类型与赋值给局部变量的值的数据类型不匹配。 如果发生这种情况，SQL Server总是试图隐式转换为 局部变量的数据类型。
全局变量
• 全局变量是由 SQL Server提供的变量，由系统为 其提供取值
存储过程的返回状态
每个存储过程的执行，都将自动返回一个返回 状态，用于告知调用程序执行该存储过程的状况。 调用程序可根据返回状态作相应的处理。 语法 create proc procedure_name ( ...... ) as begin SQL_statements return [ integer ]
•语法 DECLARE @var_name data_type [, @var_name data_type]。。。 举例 declare @msg varchar(40) declare @myqty int, @myid char(4) declare @myname type_name /* type_name是用户定义数据类型*/
查看存储过程的一般信息，如创建日期等，使 用： sp_help procedure_name
• 重新命名存储过程 语法：sp_rename old_name , new_name 例：将已创建的存储过程reports_1改名为 reports_1b： exec sp_rename reports_1, report_lb
存储过程
•
存储过程(Stored Procedure)是为了完成特定的功能而汇集成一组的 SQL语句集，并为该组SQL语句命名、经编译后 存储在SQL Server的数 据库中。用户可以根据需要决定是 否在每次执行时让SQL Server进行 重新编译。用户可以指 定存储过程的名字和给出参数来执行它。 • 允许多个用户(有权)访问相同的代码。 • 提供一种集中且一致的实现数据完整性逻辑的方法。 • 存储过程用于实现： -- 频繁使用的查询 -- 业务规则 -- 被其他过程使用的公共例行程序，例如错误处理例行程序等
举例： 创建向调用程序返回值的存储过程： create proc proc_num_sales (@book_id char(6) = null, /* 输入参数 */ @tot_sales int output /* 输出参数 */ as begin /* 过程将返回对于给定书号的书的总销售量 */ select @tot_sales = sum(qty) from salesdetail where title_id = @book_id return end
• 注意： — 在一个赋值给局部变量的select语句中，可以使 用常数、从表中取值、或使用表达式给局部变量赋值 。 — 不能使用同一SELECT语句既给局部变量赋值，又 检索数据返回给客户。 — 一个赋值给局部变量的SELECT语句，不向用户显 示任何值。
对局部变量的限制
局部变量必须先用DECLARE定义，再用SELECT语句赋 值后才能使用。 局部变量只能使用在T－SQL语句中使用常量的地方。 局部变量不能使用在表名、列名、其它数据库对象 名、保留字使用的地方。 局部变量是标量，它们拥有一个确切的值。 赋值给局部变量的SELECT语句应该返回单个值。如 果赋值的SELECT语句没有返回值，则该局部变量的值 保持不变；如果赋值的SELECT语句返回多个值，则该 局部变量取最后一个返回的值。
end
其中：integer为一整数。如果不指定，系统将自动返回一 个整数值。系统使用0表示该过程执行成功；-1至–14 表 示该过程执行有错，-15至 -99为系统保留值。用户一般 使用大于0的整数，或小于 -100的负整数。