ASE存储过程和IQ存储过程的常见区别（附例子）

ASE存储过程和IQ存储过程的常见区别（附例子）

1 存储过程简介

    存储过程(Stored Procedure)是为了完成特定的功能而汇集成一组的SQL语句集，并为该组SQL语句命名、经编译后存储在SQL Server的数据库中。用户可以 根据需要决定是否在每次执行时让SQL Server进行重新编译。用户可以指定存储过程的名字和给出参数来执行它。 允许多个用户(有权)访问相同的代码。 提供一种集中且一致的实现数据完整性逻辑的方法。 存储过程用于实现：频繁使用的查询；业务规则；被其他过程使用的公共例行程序；例如错误处理例行程序等。
1.1 存储过程的优点
    使用存储过程可加快运行速度、可减少网络交通可重用、可共享性。存储过程也是一种安全机制，使用存储过程实现数据库完整性，提高数据与应用的独立性。
1.2 存储过程的分类
　　系统存储过程：系统提供存储过程，用于从系统表中获取信息、为系统管理员和有权用户提供更新系统表的途径。系统存储过程的名字都以"sp_"为前缀。 如：sp_help 。 用户定义的存储过用户定义存储过程是由用户为完成某一特定功能而编写的存储过程。

例如：在SQL Server安装时自动建立了一些以sp_为前缀的系统存储过程， 这些系统过程通常用来显示或修改系统表它们可为各用户所共享。

2 存储过程的创建、修改、删除
2.1 创建存储过程

create proc procedure_name 
as 
    begin 
        SQL_statements [return] 
    end    

2.1 创建存储过程
    存储过程被放在当前正在使用的数据库中。 在存储过程中可以引用在其他数据库中的对象。创建存储过程(create proc)语句不能与其他的SQL语句在同一个批中，即创建存储过程语句必须单独成为一个批。 在存储过程中可以包含SQL语句，但是不能包含：use, create view, create rule, create default, create proc, create trigger
2.2 执行存储过程

 exec procedure_name [参数];
 execute procedure_name [参数];

2.3 查看存储过程

--查看存储过程 查看创建存储过程的源代码，使用：
procedure_name sp_helptext;

--查看存储过程所依赖的表和视图信息，使用：
sp_depends procedure_name ;

--查看存储过程的一般信息，如创建日期等，使用：
sp_help procedure_name;

2.3 重命名存储过程

--重新命名存储过程 语法：
sp_rename old_name , new_name ;

--语法 例：将已创建的存储过程reports_1改名为reports_1b：
exec sp_rename reports_1, report_lb;

2.3 删除存储过程

--删除存储过程 语法：
drop proc procedure_name;

-- 语法 例： 删除已创建的存储过程reports：
drop proc reports;

2.4 存储过程中的注释

SQL Server提供了两种在T－SQL中的注释方法： 
1使用斜杠星号对/* 注示内容*/ 。
例如:
 /*
bind the rule 
to all columns 
with datatype
*/ 
exec sp_bindrule ul_tid,tid

2或使用双连字符 --
例如：
--bind the rule to all columns
--with datatype tid 
exec sp_bindrule ul_tid,tid

3 存储过程中的参数、返回值和变量
3.1 存储过程中的参数
    输入参数(Input Parameters) 是指由调用程序向存储过程提供的变量值。它们在创建存储过程语句中被定义，而在执行该存储过程语句中给出相应的变量值。使用输入参数的优点是使存储过程得更加灵活。

--语法：
create proc procedure_name (@parameter_name datatype [, @parameter_name datatype……]) 
as 
    begin 
        SQL_statements 
        return 
    end    

3.1.1 带参数存储过程

举例： 创建带参数的存储过程
create proc proc_author_addr (@lname varchar(40)) 
as 
    begin 
        select phone, address, city, state 
        from authors
        where au_lname = @lname 
        return
    end

-- 在调用程序的执行存储过程命令中，将相应的值传递给
-- 这个输入参数：用'Green'替换@lname 
exec proc_author_addr @lname = 'Green' ; --没见过
--或
exec proc_author_addr 'Green' end;  --没见过

exec proc_author_addr('Green'); --常用

3.1.2 带有返回参数的存储过程

创建向调用程序返回值的存储过程：
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

3.2 存储过程返回状态

    每个存储过程的执行，都将自动返回一个返回状态，用于告知调用程序执行该存储过程的状况。调用程序可根据返回状态作相应的处理。

语法
create proc procedure_name ( ……) 
as 
    begin 
        SQL_statements 
        return [ integer ] 
    end

　　其中：integer为一整数。如果不指定，系统将自动返回一个整数值。系统使用0表示该过程执行成功；-1至¨C14 表示该过程执行有错，-15至-99为系统保留值。用户一般使用大于0的整数，或小于-100的负整数。
3.3 存储过程中的变量：局部变量；全局变量。

3.3.1 局部变量
　　局部变量：用户自定义变量。使用declare语句定义。具有名和数据类型，通过用户赋值，说明时给变量赋值为空，局部变量可在存储过程，或触发器中定义。

--局部变量的定义与声明
DECLARE @var_name data_type [, @var_name data_type] …… 

declare @msg varchar(40) declare @myqty int, @myid char(4)

--为局部变量赋值。局部变量被声明时， 它的初值为NULL ， 使用SELECT语句将指定值赋给局部变量。
select @var = expression [,@var = expression ] [from…[where…]… 

declare @var1 int select @var1=99

注意 ： 在一个赋值给局部变量的select 语句中， 可以使用常数、 从表中取值、或使用表达式给局部变量赋值。 不能使用同一SELECT 语句既给局部变量赋值，又检索数据返回给客户。一个赋值给局部变量的SELECT 语句，不向用户显示任何值。

对局部变量的限制
    局部变量必须先用DECLARE定义，再用SELECT语句赋值后才能使用。 局部变量只能使用在T－SQL语句中使用常量的地方。 局部变量不能使用在表名、列名、其它数据库对象名、保留字使用的地方。 局部变量是标量，它们拥有一个确切的值。 赋值给局部变量的SELECT语句应该返回单个值。如果赋值的SELECT语句没有返 回值，则该局部变量的值保持不变；如果赋值的SELECT语句返回多个值，则该局部变量取最后一个返回的值。
使用局部变量时通常发生的错误
    在程序中， 使用局部变量通常容易发生的错误是数据类型不匹配。 即使用DECLARE 语句定义局部变量的数据类型与赋值给局部变量的值的数据类型不匹配。 如果发生这种情况，SQL Server 总是试图隐式转换为局部变量的数据类型。
3.3.2 全局变量
    全局变量( Gloabal Variable )是SQL Server系统提供并赋值的变量。 用户不能建立全局变量，也不能使用SELECT语句去修改全局变量的值。全局变量的名字用@@开始。大多数全局变量的值报告本次SQL Server启动后发生的系统活动，可以使用系统存储过程sp_monitor显示全局变量的当前值。通常全局变量的值赋给在同一批中的局部变 量，以便保存和作进一步处理

--常用的全局变量
@@error   --由最近一个语句产生的错误号；
@@rowcount    --被最近一个语句影响的行数；
@@version   --SQL Server的版本号；
@@max_connections  --允许与该SQL Server连接的最大用户个数；
@@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

4 存储过程中的流程控制语言
    流程控制SQL语句的执行顺序，这在存储过程、触发器、批中非常有用。流控制关键字（命令）包括：IF ELSE;IF EXISTS 和IF NOT EXISTS BEGIN…END RETURN WHILE BREAK和CONTINUE WAITFOR PRINT

4.1 IF ELSE / else if / End if

--IF ELSE
--部分语法(ASE) 
if boolean_expression statement 
[else [if boolean_expression1] statement1 ] 

--部分语法(IQ) 
if boolean_expression then statement 
[else [if boolean_expression1] statement1 ] 
End if

4.2 IF EXISTS 和IF NOT EXISTS

--当你关心数据是否存在时，在IF 语句中使用[NOT] EXISTS 是很有用的。 
--语法(ASE) 语法
if [not] exists (select statement) 
　　statement block

--举例(ASE) 举例/* 是否存在姓“Smith”的作者*/ 
declare @lname varchar(40) 
select @lname = 'Smith' 
if exists ( select * from authors where au_lname = @lname)
    select 'here is a ' + @lname 
else 
    select 'here is no author called'+@lname

 4.3 BEGIN…END

　　功能：当需要将一个以上的SQL 语句作为一组语句对待时， 可以使用BEGIN 和END 将它们括起来形成一个SQL 语句块。从 语法上看，一个SQL 语句块相当于一个SQL 语句。在流控制语言中， 允许用一个SQL语句块替代单个SQL语句出现的地方。
　　语法BEGIN statement block END 这里：statement block 通常为一个以上的SQL 语句。当然也可是 一个SQL语句。
4.4 RETURN
　　功能RETURN：命令无条件退出它所在的批、 存储过程或触发器。 退出时，能选择提供返回状态。RETURN 语句之后的任何语句不被执行。

　　语法RETURN [integer_expression]

if not exists ( select * from titles where title_id = @t_id) 
begin 
    print 'here is no title by this title_id' 
    return --无条件退出批，其后语句不被执行。
    insert salesdetail values(@s_id, @o_num, @t_id,@qty_sold, @disc) 
end 
go

4.5 WHILE

　　功能：WHILE关键字为要重复执行的某一语句或语句块设置条件， 当指定的条件为真（TRUE ）时，执行这一语句或语句块， 直到条件为假( FALSE ) 或执行BREAK 语句。语句块是由BEGIN 和END 括起来的两个或两个以上的语句构成。

--语法(ASE) 语
while boolean 
    exprission 
    statement block 

--语法(IQ) 语法
while boolean 
    exprission 
    loop 
        statement block 
    end loop

--举例
while (select avg(price) from titles) < $40
    begin 
        select title_id, price 
        from titles 
        where price > $20 
        update titles 
        set price = price + $2 
    end 
select title_id, price 
　　from titles 
print "Too much for the market to bear"

4.6 BREAK和CONTINUE
　　功能：BREAK和CONTINU关键字控制在WHILE循环中语句块中语句的执行。BREAK关键字将退出它所在的循环，继续执行后面的语句(即跳过从关键字BREAK到它所在循环结束END之间的所有语句)；CONTINU关键字使循环重新开始，即跳过任何在该循环内但在CONTINU关键字之后的语句。

--语法(ASE) 语法
WHILE boolean 
    expression 
    BEGIN 
        statement1 
        statement2 
        BREAK 
        CONTINU 
    END 
statement

--举例(ASE) 举例
while (select avg(price) from titles) >= $20 
    begin 
        update titles 
            set price = price / 2 
        if (select max(price) from titles) < $40 
            break 
        else if (select avg(price) from titles) < $20 
            continu 
        print "Average price still over $20" 
    end 
print "Not too expensive.or Average price under $20"

 4.7 WAITFOR

　　功能：WAITFOR 关键字将挂起当前的执行, 直到指定的事件发生。它常常被系统用来实现有规律的系统维护、出错处理、 事件处理和统计记录等。

　　部分语法waitfor {delay time | time time |……}

--举例 这个例子是一个无终止循环，它每隔半小时记录一次锁的个数。
while 2>1 /* 这一表达式总是返回'TRUE' */
    waitfor delay '0:30:00' /* 每隔30分钟*/ 
        insert into num_procs 
            select getdate(), count(*) 
                from master, syslocks            

4.8 PRINT
　　功能：PRINT关键字用来在屏幕上显示用户定义的信息，局部变量的值或char/varchar类型的全局变量的值。

--语法
print {"any ascii characters or string" |local_variable | global_variable } [, arg_list ]

--举例(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_name    

5 存储过程中的事务、游标
5.1 嵌套事务
    嵌套事务是指在存储过程中的事务的间接嵌套， 即嵌套事务的形成是因为调用含有事务的过程。

　　@@trancount 记录了事务嵌套级次。@@trancount在第一个begin tran语句后值为1，以后每遇到一个begin tran 语句，不论是否在嵌套 过程中，@@trancount的值增加1；每遇到 一个commit，@@trancount的值就减少1。若@@trancount的 值 等于 零，表示当前没有事务；若@@trancount的值不等于零，其值假定为i，表明当前处于第i 级嵌套事务中。对于嵌套事务，直到 使用@@trancount 的值为零的那个commit语句被执行,整个事务才被提交。select @@trancount
5.1.1 与事务相关的语句
    Rollback 根据SQL Server的缺省规定，一个不带事务名或保存 点名的rollback tran 语句，不论它是否在嵌套事务中， 总是退到最外 面的begin tran语句，即回退包括所有 嵌套事务在内的整个事务commit 根据SQL Server的缺省规定，即使是在嵌套事务中 执行commit 语 句，@@trancount计数值也只减少1。
5.2 存储过程中的游标

create proc procedure_name 
as 
SQL_statements 
containing 
cursor 
processing 

--其中：SQL_statements containing cursor processing 是指包含游标处理的SQL语句。

--举例
create proc proc_fetch_book 
As 
    begin declare @book_title char(30), 
        　　　　　　@book_id char(6) 
        declare biz_book cursor for select title, title_id from titles where type = "business" 
        open biz_book 
            fetch biz_book into @book_title, @book_id ……-- 在这里做某些处理
        close biz_book 
        deallocate cursor biz_book 
        return 
    end

 5.2 存储过程中的游标

　　游标的作用域：如果存储过程是嵌套的话，那么也包括它的所有子域。这就是说，如果嵌套的存储过程构成一棵调用树(Call Tree),那么在 这棵树的某个结点上定义的游标，其作用域就是它位于的树叉，即自定义游标的那个结点的存储过程及它所包含的所有子域。但是如果 在它所包含的子域中，定义了与它同名的游标，那么它将在定义同名游标的子域内及该子域所包含的其他子域内失效。

6 ASE存储过程和IQ存储过程常见区别、举例

COMMIT 
在IQ存储过程中， 每一个增、 删、 改、 查的上sql 后都要加上一个commit以保证语句成功执行（ASE不用）；

BEGIN END 
ASE的存储过程的每个程序分支要放在BEGIN END中（每个条件判断，每个循环等）；
IQ不用，只要在程序最外边有个BEGIN END 就可以了；

ASE存储过程要在存储过程名之后，BEGIN之前加上@，IQ不用变量定义
ASE：DECLARE @ date_begin CHAR(8);
IQ：DECLARE date_begin CHAR(8);

变量赋值
ASE存储过程中：select @sql_str = '123'
IQ存储过程中：select '123' into sql_str

变量引用
ASE存储过程中：@+变量名
IQ存储过程中：变量名