SYBASE存储过程.doc

SYBASE存储过程n 一、存储过程简介n 二、存储过程的创建、修改、删除n 三、存储过程中的参数、返回值和变量n 四、存储过程中的流程控制语言n 五、存储过程中的事务、游标n 六、ASE存储过程和IQ存储过程的常见区别（附例子）1 存储过程简介n 存储过程(Stored Procedure)是为了完成特定的功能而汇集成一组的SQL语句集，并为该组SQL语句命名、经编译后 存储在SQL Server的数据库中。用户可以根据需要决定是 否在每次执行时让SQL Server进行重新编译。用户可以指 定存储过程的名字和给出参数来执行它。n 允许多个用户(有权)访问相同的代码。 n 提供一种集中且一致的实现数据完整性逻辑的方法。n 存储过程用于实现：n - 频繁使用的查询 n - 业务规则 n - 被其他过程使用的公共例行程序，例如错误处理例行程序等1.1 存储过程的优点n 使用存储过程可加快运行速度 n 可减少网络交通n 可重用、可共享性n 存储过程也是一种安全机制n 使用存储过程实现数据库完整性n 提高数据与应用的独立性1.2 存储过程的分类n 系统提供存储过程 系统存储过程主要用于从系统表中获取信息、为系统管理员和有权用户提供更新系统表的途径。系统存储 过程的名字都以sp_为前缀。 如：sp_help 。n 用户定义存储过程 用户定义的存储过程是由用户为完成某一特定功能 而编写的存储过程。1.2.1 系统存储过程n 在SQL Server安装时自动建立了一些以sp_为前缀的系统存储过程，这些系统过程通常用来显示或修改系统表它们可为各用户所供享：n 例如2 存储过程的创建、修改、删除n 创建存储过程n 执行存储过程n 查看、修改、删除存储过程n 存储过程中的注释2.1 创建存储过程n create proc procedure_name as begin SQL_statements return end2.1 创建存储过程n 存储过程被放在当前正在使用的数据库中。n 在存储过程中可以引用在其他数据库中的对象n 创建存储过程(create proc)语句不能与其他的SQL语句在同一个批中，即创建存储过程语句必须单独成为一个批。n 在存储过程中可以包含SQL语句，但是不能包含：use, create view, create rule, create default, create proc, create trigger2.2 执行存储过程n 语法: execute procedure_name 参数 2.3 查看、修改和删除存储过程n 查看存储过程查看创建存储过程的源代码，使用： sp_helptext procedure_name查看存储过程所依赖的表和视图信息，使用： sp_depends procedure_name查看存储过程的一般信息，如创建日期等，使用： sp_help procedure_name2.3 查看、修改和删除存储过程n 重新命名存储过程 语法：sp_rename old_name , new_name 例：将已创建的存储过程reports_1改名为reports_1b： exec sp_rename reports_1, report_lb2.3 查看、修改和删除存储过程n 删除存储过程 语法：drop proc procedure_name 例： 删除已创建的存储过程reports： drop proc reports2.4 存储过程中的注释n SQL Server提供了两种在TSQL中的注释方法：n 使用斜杠星号对/* 注示内容*/例如:/*bind the rule to all columns with datatype*/exec sp_bindrule ul_tid,tid2.4 存储过程中的注释n 使用双连字符-例如：-bind the rule to all columns-with datatype tid exec sp_bindrule ul_tid,tid3 存储过程中的参数、返回值和变量n 存储过程中的参数n 存储过程的返回状态n 存储过程中的变量3.1 存储过程中的参数n 输入参数(Input Parameters) 是指由调用程序向存储过程 提供的变量值。它们在创建存储过程语句中被定义，而 在执行该存储过程语句中给出相应的变量值。使用输入 参数的优点是使存储过程得更加灵活。3.1 存储过程中的参数语法： create proc procedure_name (parameter_name datatype , parameter_name datatype.) as begin SQL_statements return end3.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 Greenend3.1.2 带有返回参数的存储过程举例： 创建向调用程序返回值的存储过程：create proc proc_num_sales(book_id char(6) = null, /* 输入参数 */ tot_sales int output /* 输出参数 */asbegin/* 过程将返回对于给定书号的书的总销售量 */select tot_sales = sum(qty) from salesdetail where title_id = book_idreturnend3.2 存储过程返回状态n 每个存储过程的执行，都将自动返回一个返回状态，用于告知调用程序执行该存储过程的状况。调用程序可根据返回状态作相应的处理。n 语法create proc procedure_name ( . ) as begin SQL_statements return integer end3.2 存储过程返回状态n 其中：integer为一整数。如果不指定，系统将自动返回一个整数值。系统使用0表示该过程执行成功；-1至C14 表示该过程执行有错，-15至 -99为系统保留值。用户一般使用大于0的整数，或小于 -100的负整数。3.3 存储过程中的变量n 局部变量n 全局变量3.3.1.1 局部变量的定义与声明n 局部变量是用户自定义变量使用declare语句定义具有名和数据类型通过用户赋值说明时给变量赋值为空局部变量可在存储过程，或触发器中定义3.3.1.1 局部变量的定义与声明n 语法DECLARE var_name data_type, var_name data_type 。n 举例declare msg varchar(40)declare myqty int, myid char(4) 3.3.1.2 为局部变量赋值n 局部变量被声明时，它的初值为NULL ，使用SELECT语句将指定值赋给局部变量。n 语法 select var = expression ,var = expression fromwheren 举例declare var1 intselect var1=993.3.1.2 为局部变量赋值n 注意 在一个赋值给局部变量的select 语句中，可以使用常数、从表中取值、或使用表达式给局部变量赋值。 不能使用同一SELECT 语句既给局部变量赋值，又检索数据返回给客户。 一个赋值给局部变量的SELECT 语句，不向用户显示任何值。3.3.1.3 对局部变量的限制n 局部变量必须先用DECLARE定义，再用SELECT语句赋值后才能使用。n 局部变量只能使用在TSQL语句中使用常量的地方。n 局部变量不能使用在表名、列名、其它数据库对象名、保留字使用的地方。n 局部变量是标量，它们拥有一个确切的值。n 赋值给局部变量的SELECT语句应该返回单个值。如果赋值的SELECT语句没有返回值，则该局部变量的值保持不变；如果赋值的SELECT语句返回多个值，则该局部变量取最后一个返回的值。3.3.1.4 使用局部变量时通常发生的错误n 在程序中，使用局部变量通常容易发生的错误是数据类型不匹配。即使用DECLARE 语句定义局部变量的数据类型与赋值给局部变量的值的数据类型不匹配。如果发生这种情况，SQL Server 总是试图隐式转换为局部变量的数据类型。3.3.2 全局变量n 全局变量( Gloabal Variable )是SQL Server系统提供并赋值的变量。用户不能建立全局变量，也不能使用SELECT语句去修改全局变量的值。全局变量的名字用开始。大多数全局变量的值报告本次SQL Server启动后发生的系统活动，可以使用系统存储过程sp_monitor显示全局变量的当前值。通常全局变量的值赋给在同一批中的局部变量，以便保存和作进一步处理 3.3.2.1 常用的全局变量n 全局变量 赋给的值 n error由最近一个语句产生的错误号n rowcount被最近一个语句影响的行数n versionSQL Server的版本号 n max_connections允许与该SQL Server连接的最大用户个数n Servername该SQL Server的名字、3.3.2.2 全局变量举例n 举例select versiondeclare book_price moneyselect book_price = price from titleswhere title_id = BU1032if rowcount = 0print no such title_idelsebeginprint title_id exists withselect price of = book_priceend4 存储过程中的流程控制语言n 流程控制SQL语句的执行顺序，这在存储过程、触发器、批中非常有用。流控制关键字（命令）包括：n IF ELSEn IF EXISTS 和 IF NOT EXISTSn BEGINENDn RETURNn WHILEn BREAK和CONTINUEn WAITFORn PRINT4.1 IF ELSEn 部分语法(ASE)if boolean_expressionstatementelse if boolean_expression1statement1 n 部分语法(IQ) if boolean_expression thenstatementelse if boolean_expression1statement1 End if 4.2 IF EXISTS 和 IF NOT EXISTSn 功能 当你关心数据是否存在时，在IF 语句中使用NOT EXISTS 是很有用的。 n 语法(ASE)if not exists (select statement)statement block4.2 IF EXISTS 和 IF NOT EXISTSn 举例(ASE)/* 是否存在姓“Smith”的作者 */declare lname varchar(40)select lname = Smithif exists ( select * from authors where au_lname = lname) select here is a + lnameelse select here is no author called+lname4.3 BEGINENDn 功能 当需要将一个以上的SQL 语句作为一组语句对待时，可以使用BEGIN 和END 将它们括起来形成一个SQL 语句块。从语法上看，一个SQL 语句块相当于一个SQL 语句。在流控制语言中，允许用一个SQL 语句块替代单个SQL 语句出现的地方。4.3 BEGINENDn 语法BEGIN statement blockEND 这里： statement block 通常为一个以上的SQL 语句。当然也可是一个SQL 语句。4.4 RETURNn 功能 RETURN 命令无条件退出它所在的批、存储过程或触发器。退出时，能选择提供返回状态。RETURN 语句之后的任何语句不被执行。 n 语法RETURN integer_expression4.4 RETURNn 举例if not exists ( select * from titles where title_id = t_id)beginprint here is no title by this title_idreturn -无条件退出批，其后语句不被执行。 insert salesdetail values(s_id, o_num, t_id,qty_sold, disc)endgo4.5 WHILEn 功能WHILE 关键字为要重复执行的某一语句或语句块设置条件，当指定的条件为真（TRUE ）时，执行这一语句或语句块，直到条件为假( FALSE ) 或执行BREAK 语句。语句块是由BEGIN 和END 括起来的两个或两个以上的语句构成4.5 WHILEn 语法(ASE) while boolean exprission statement blockn 语法(IQ) while boolean exprission loop statement block end loop4.5 WHILEn 举例 while (select avg(price) from titles) $20 update titles set price = price + $2 end select title_id, price from titles print Too much for the market to bear4.6 BREAK和CONTINUEn 功能 BREAK和CONTINU关键字控制在WHILE循环中语句块中语句的执行。 BREAK关键字将退出它所在的循环，继续执行后面的语句(即跳过从关键字BREAK到它所在循环结束END之间的所有语句)。 CONTINU关键字使循环重新开始，即跳过任何在该循环内但在CONTINU关键字之后的语句。4.6 BREAK和CONTINUEn 语法(ASE) WHILE boolean expression BEGIN statement1 statement2 BREAK CONTINU END statement4.6 BREAK和CONTINUEn 举例(ASE)while (select avg(price) from titles) = $20begin update titles set price = price / 2 if (select max(price) from titles) $40 break else if (select avg(price) from titles) 1 /* 这一表达式总是返回TRUE */ waitfor delay 0:30:00 /* 每隔30分钟 */ insetrt into num_procs select getdate(), count(*) from master, syslocks4.8 PRINTn 功能 PRINT关键字用来在屏幕上显示用户定义的信息，局部变量的值或char/varchar类型的全局变量的值。n 语法 print any ascii characters or string |local_variable | global_variable , arg_list 4.8 PRINTn 举例(1) print hello(2) print msg /* msg 是一个局部变量。 */(3) declare table_name varchor(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_name5 存储过程中的事务、游标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 与事务相关的语句n Rollback 根据SQL Server的缺省规定，一个不带事务名或保存 点名的 rollback tran 语句，不论它是否在嵌套事务中， 总是退到最外面的 begin tran语句，即回退包括所有 嵌套事务在内的整个事务n commit 根据SQL Server的缺省规定，即使是在嵌套事务中 执行commit 语句，trancount计数值也只减少1。5.2 存储过程中的游标n 语法create proc procedure_name asSQL_statements containing cursor processing其中：SQL_statements containing cursor processing是指包含游标处理的SQL语句。5.2 存储过程中的游标n 举例create proc proc_fetch_bookAsbegin declare book_title char(30), book_id char(6) declare biz_book cursor for select