n一、存储过程简介
n二、存储过程的创建、修改、删除
n三、存储过程中的参数、返回值和变量
n四、存储过程中的流程控制语言
n五、存储过程中的事务、游标
n六、ASE存储过程和IQ存储过程的常见区别（附例子）
1 存储过程简介
na.存储过程(Stored Procedure)是为了完成特定的功能而汇集成一组的SQL语句集，并为该组SQL语句命名、经编译后 存储在SQL Server的数据库中。用户可以根据需要决定是 否在每次执行时让SQL Server进行重新编译。用户可以指 定存储过程的名字和给出参数来执行它。
nb.允许多个用户(有权)访问相同的代码。
nc.提供一种集中且一致的实现数据完整性逻辑的方法。
nd.存储过程用于实现：
n -- 频繁使用的查询
n -- 业务规则
n -- 被其他过程使用的公共例行程序，例如错误处理例行程序等
1.1 存储过程的优点
n--使用存储过程可加快运行速度
n--可减少网络交通
n--可重用、可共享性
n--存储过程也是一种安全机制
n--使用存储过程实现数据库完整性
n--提高数据与应用的独立性
1.2 存储过程的分类
n--系统提供存储过程 系统存储过程主要用于从系统表中获取信息、为系统管理员和有权用户提供更新系统表的途径。系统存储 过程的名字都以"sp_"为前缀。 如：sp_help 。n--用户定义存储过程 户定义的存储过程是由用户为完成某一特定功能 而编写的存储过程。
1.2.1 系统存储过程
在SQL Server安装时自动建立了一些以sp_为前缀的系统存储过程，这些系统过程通常用来显示或修改系统表它们可为各用户所供享：
2 存储过程的创建、修改、删除
n--创建存储过程
n--执行存储过程
n--查看、修改、删除存储过程
n--存储过程中的注释
2.1 创建存储过程
ncreate proc procedure_name
as
begin
SQL_statements
[return]
end
n--存储过程被放在当前正在使用的数据库中。
n--在存储过程中可以引用在其他数据库中的对象
n--创建存储过程(create proc)语句不能与其他的SQL语句在同一个批中，即创建存储过程语句必须单独成为一个批。n--在存储过程中可以包含SQL语句，但是不能包含：use, create view, create rule, create default, create proc, create trigger
2.2 执行存储过程
n语法:[exec[ute]] procedure_name [参数]
2.3 查看、修改和删除存储过程
n查看存储过程--查看创建存储过程的源代码，使用： sp_helptext procedure_name--查看存储过程所依赖的表和视图信息，使用： sp_depends procedure_name--查看存储过程的一般信息，如创建日期等，使用： sp_help procedure_name
n重新命名存储过程语法：sp_rename old_name , new_name例：将已创建的存储过程reports_1改名为reports_1b：exec sp_rename reports_1, report_lb
n删除存储过程语法：drop proc procedure_name例： 删除已创建的存储过程reports：drop proc reports
2.4 存储过程中的注释
nSQL Server提供了两种在T－SQL中的注释方法：n— 使用斜杠星号对/* 注示内容*/例如:/*bind the rule to allcolumns with datatype*/exec sp_bindrule ul_tid,tid
n— 使用双连字符“--”例如：--bind the rule to all columns--with datatype tidexec sp_bindrule ul_tid,tid
3 存储过程中的参数、返回值和变量
n存储过程中的参数n存储过程的返回状态n存储过程中的变量
3.1 存储过程中的参数
输入参数(Input Parameters) 是指由调用程序向存储过程 提供的变量值。它们在创建存储过程语句中被定义，而 在执行该存储过程语句中给出相应的变量值。使用输入 参数的优点是使存储过程得更加灵活。语法： create proc procedure_name(@parameter_name datatype[, @parameter_name datatype...])asbeginSQL_statementsreturnend
3.1.1 带参数存储过程
举例： 创建带参数的存储过程create proc proc_author_addr (@lname varchar(40))asbeginselect phone, address, city, statefrom authorswhere au_lname = @lnamereturn-- 在调用程序的执行存储过程命令中，将相应的值传递给-- 这个输入参数：用'Green'替换@lnameexec proc_author_addr @lname = 'Green'或exec proc_author_addr 'Green‘end
3.1.2 带有返回参数的存储过程
举例：创建向调用程序返回值的存储过程：create proc proc_num_sales(@book_id char(6) = null, /* 输入参数 */@tot_sales int output /* 输出参数 */asbegin/* 过程将返回对于给定书号的书的总销售量 */select @tot_sales = sum(qty) from salesdetailwhere title_id = @book_idreturnend
3.2 存储过程返回状态
na.每个存储过程的执行，都将自动返回一个返回状态，用于告知调用程序执行该存储过程的状况。调用程序可根据返回状态作相应的处理。n语法:create proc procedure_name ( ...... )asbeginSQL_statementsreturn [ integer ]endb.其中：integer为一整数。如果不指定，系统将自动返回一个整数值。系统使用0表示该过程执行成功；-1至–14 表示该过程执行有错，-15至 -99为系统保留值。用户一般使用大于0的整数，或小于 -100的负整数。
3.3 存储过程中的变量
n 
n局部变量n全局变量
3.3.1.1 局部变量的定义与声明
na.局部变量是－用户自定义变量－使用declare语句定义－具有名和数据类型－通过用户赋值－说明时给变量赋值为空－局部变量可在存储过程，或触发器中定义nb.语法DECLARE @var_name data_type[, @var_name data_type]。。。nc.举例declare @msg varchar(40)declare @myqty int, @myid char(4)
3.3.1.2 为局部变量赋值
na.局部变量被声明时，它的初值为NULL ，使用SELECT语句将指定值赋给局部变量。n语法select @var = expression[,@var = expression ][from…[where…]…nb.举例declare @var1 intselect @var1=99nc.注意—在一个赋值给局部变量的select语句中，可以使用常数、从表中取值、或使用表达式给局部变量赋值。
—不能使用同一SELECT语句既给局部变量赋值，又检索数据返回给客户。
—一个赋值给局部变量的SELECT语句，不向用户显示任何值。
3.3.1.3 对局部变量的限制
n局部变量必须先用DECLARE定义，再用SELECT语句赋值后才能使用。n局部变量只能使用在T－SQL语句中使用常量的地方。n局部变量不能使用在表名、列名、其它数据库对象名、保留字使用的地方。n局部变量是标量，它们拥有一个确切的值。n赋值给局部变量的SELECT语句应该返回单个值。如果赋值的SELECT语句没有返回值，则该局部变量的值保持不变；如果赋值的SELECT语句返回多个值，则该局部变量取最后一个返回的值。
3.3.1.4 使用局部变量时通常发生的错误
n在程序中，使用局部变量通常容易发生的错误是数据类型不匹配。即使用DECLARE语句定义局部变量的数据类型与赋值给局部变量的值的数据类型不匹配。如果发生这种情况，SQL Server总是试图隐式转换为局部变量的数据类型。
3.3.2 全局变量
全局变量(Gloabal Variable )是SQL Server系统提供并赋值的变量。用户不能建立全局变量，也不能使用SELECT语句去修改全局变量的值。全局变量的名字用@@开始。大多数全局变量的值报告本次SQL Server启动后发生的系统活动，可以使用系统存储过程sp_monitor显示全局变量的当前值。通常全局变量的值赋给在同一批中的局部变量，以便保存和作进一步处理
3.3.2.2 全局变量举例
n举例select @@versiondeclare @book_price moneyselect @book_price = price from titleswhere title_id = 'BU1032'if @@rowcount = 0print 'no such title_id'elsebeginprint 'title_id exists with'select 'price of' = @book_priceend
4 存储过程中的流程控制语言
n流程控制SQL语句的执行顺序，这在存储过程、触发器、批中非常有用。流控制关键字（命令）包括：na.IF ELSEnb.IF EXISTS 和 IF NOT EXISTSnc.BEGIN…ENDnd.RETURNne.WHILEnf.BREAK和CONTINUEng.WAITFORnh.PRINT
4.1 IF ELSE
n部分语法(ASE)if boolean_expressionstatement[else [if boolean_expression1]statement1 ]n部分语法(IQ)if boolean_expression thenstatement[else [if boolean_expression1]statement1 ]End if
4.2 IF EXISTS 和 IF NOT EXISTS
n功能当你关心数据是否存在时，在IF语句中使用[NOT] EXISTS是很有用的。n语法(ASE)if [not] exists (select statement)statement block
4.3 BEGIN…END
n功能当需要将一个以上的SQL语句作为一组语句对待时，可以使用BEGIN和END将它们括起来形成一个SQL语句块。从语法上看，一个SQL语句块相当于一个SQL语句。在流控制语言中，允许用一个SQL语句块替代单个SQL语句出现的地方。n语法BEGINstatement blockEND这里：statement block通常为一个以上的SQL语句。当然也可是一个SQL语句。
4.4 RETURN
n功能RETURN命令无条件退出它所在的批、存储过程或触发器。退出时，能选择提供返回状态。RETURN语句之后的任何语句不被执行。n语法RETURN [integer_expression]n举例if not exists ( select * from titles where title_id = @t_id)beginprint 'here is no title by this title_id'return --无条件退出批，其后语句不被执行。insert salesdetail values(@s_id, @o_num, @t_id,@qty_sold, @disc)endgo
4.5 WHILE
n语法(ASE)while boolean exprissionstatement blockn语法(IQ)while boolean exprission loopstatement blockend loopn举例while (select avg(price) from titles) < $40beginselect title_id, price from titleswhere price > $20update titles set price = price + $2endselect title_id, price from titlesprint "Too much for the market to bear"
4.6 BREAK和CONTINUE
n功能BREAK和CONTINU关键字控制在WHILE循环中语句块中语句的执行。— BREAK关键字将退出它所在的循环，继续执行后面的语句(即跳过从关键字BREAK到它所在循环结束END之间的所有语句)。— CONTINU关键字使循环重新开始，即跳过任何在该循环内但在CONTINU关键字之后的语句。n语法(ASE)WHILE boolean expressionBEGINstatement1statement2BREAKCONTINUENDstatementn举例(ASE)while (select avg(price) from titles) >= $20beginupdate titles set price = price / 2if (select max(price) from titles) < $40breakelseif (select avg(price) from titles) < $20continuprint "Average price still over $20"endprint "Not too expensive.or Average price under $20"
4.7 WAITFOR
n功能WAITFOR关键字将挂起当前的执行, 直到指定的事件发生。它常常被系统用来实现有规律的系统维护、出错处理、事件处理和统计记录等。n部分语法waitfor {delay time | time time |...}n举例这个例子是一个无终止循环，它每隔半小时记录一次锁的个数。while 2>1 /* 这一表达式总是返回'TRUE' */waitfor delay '0:30:00' /* 每隔30分钟 */insetrt into num_procsselect getdate(), count(*) from master, syslocks
4.8 PRINT
n功能PRINT关键字用来在屏幕上显示用户定义的信息，局部变量的值或char/varchar类型的全局变量的值。n语法print {"any ascii characters or string"|local_variable | global_variable }[, arg_list ]n举例(1) print "hello"(2) print @msg /* @msg 是一个局部变量。 */(3) declare @table_name varchar(30) , @user_name varchar(30)select @table_name = "titles", @user_name = "ezekiel"print "The table%1! is not owned by the user %2!",@table_name ,@user_name
5 存储过程中的事务、游标
n存储过程中的事务n存储过程中的游标
5.1 嵌套事务n嵌套事务 是指在存储过程中的事务的间接嵌套， 即嵌套事务的形成是因为调用含有事务的过程。@@trancount 记录了事务嵌套级次。@@trancount在第一个begin tran语句后值为1，以后每遇到一个 begin tran 语句，不论是否在嵌套过程中，@@trancount的值增加1；每遇到 一个commit，@@trancount的值就减少1。若@@trancount的 值 等于 零，表示当前没有事务；若@@trancount的值不等于零，其值 假定为i，表明当前处于第 i 级嵌套事务中。对于嵌套事务，直 到使用@@trancount 的值为零的那个 commit语句被执行,整个 事务才被提交。select @@trancount举例：在存储过程中的事务嵌套
5.1.1 与事务相关的语句
nRollback根据SQL Server的缺省规定，一个不带事务名或保存 点名的 rollback tran 语句，不论它是否在嵌套事务中， 总是退到最外面的 begin tran语句，即回退包括所有 嵌套事务在内的整个事务ncommit根据SQL Server的缺省规定，即使是在嵌套事务中 执行commit 语句，@@trancount计数值也只减少1。
5.2 存储过程中的游标
n语法create proc procedure_nameasSQL_statements containing cursor processing其中：SQL_statements containing cursor processing是指包含游标处理的SQL语句。n举例create proc proc_fetch_bookAsbegindeclare @book_title char(30), @book_id char(6)declare biz_book cursor forselect title, title_id from titleswhere type = "business"open biz_bookfetch biz_book into @book_title, @book_id。。。 -- 在这里做某些处理close biz_bookdeallocate cursor biz_bookreturnendn游标的作用域 如果存储过程是嵌套的话，那么也包括它的所有子域。这就是说，如果嵌套的存储过程构成一棵调用树(Call Tree),那么在这棵 树的某个结点上定义的游标，其作用域就是它位于的树叉，即自定义游标的那个结点的存储过程及它所包含的所有子域。但是如果在它所包含的子域中，定义了与它同名的游标，那么它将在定义同名游标的子域内及该子域所包含的其他子域内失效。
6 ASE存储过程和IQ存储过程常见区别、举例
n1.COMMIT – 在IQ存储过程中，每一个增、删、改、查的上sql后都要加上一个commit以保证语句成功执行（ASE不用）n2.BEGIN END – ASE的存储过程的每个程序分支要放在BEGIN END中（每个条件判断，每个循环等），而IQ不用，只要在程序最外边有个BEGIN END 就可以了n3.ASE存储过程要在存储过程名之后，BEGIN之前加上AS，IQ不用n变量定义 – ASE DECLARE @ date_begin CHAR(8);IQ DECLARE date_begin CHAR(8);n4.变量赋值 - ASE存储过程中：select @sql_str = ‘1’IQ存储过程中： select‘1' into sql_strn5.变量引用 - ASE存储过程中：@+变量名IQ存储过程中：变量名
