存储过程、事务、游标的使用

存储过程：

1. 语法结构

    create procedure pro_name
    -- 参数1  类型
    -- ...   ...
    -- 参数n  类型  output --返回参数
    as
    begin
      --过程代码
    end
    go

2. 传参，接收返回值方式

   create procedure colo2
   @iret int output -- 返回参数
   as
   begin
   	declare @var varchar(10)
   	set @var = 'A'
   	select @iret = 
   	case when @var='A' then 0   -- case的多重使用
   		 when @var='B' then 1
   		 when @var='C' then 2
   		 when @var='D' then 3
   		 else -1
   	end
   end
   go
   
   declare @k int
   exec colo2 @k output  -- 执行存储过程，并且用@k接受参数
   select @k -- 查看@k
   
   drop procedure colo2 -- 删除存储过程

3.  声明变量、变量赋值、while的使用

   -- 声明变量
   declare @变量1 类型
   -- 变量赋值
   set @变量1 = 值
   
   --while
   while 条件
   begin
      循环主主体
   end
   
   -- 如何接受存储过程中产生的表视图
   ---定义部分  存储过程中产生了一个结果集
   CREATE PROCEDURE p_3
   AS 
   SELECT 1 as id,'xiaoli' as name
   UNION
   SELECT 2 as id,'xiaowang' as name
   
   SELECT 3 id,'xiaozhang' name
   UNION
   SELECT 4 id,'xiaozhao' name
   --注意如果有两次查询,那么这两次查询返回的数据表的结构必需是一样的.
   drop procedure p_3
   
   --执行部分
   DECLARE @tab AS  TABLE(
   id int 
   ,name nvarchar(20)
   )
   INSERT INTO @tab(id,name)
   EXECUTE p_3
   
   SELECT * FROM @tab

    

事务：

常用方法

set NOCOUNT ON; --返回计数 影响行数 
set XACT_ABORT ON; --注意这里一定要写XACT_ABORT为ON 才能保持事务的特性

begintry
    begin tran
        --sql语句
    commit tran
endtry
begincatch
    if xact_state()=-1
        rollback tran
endcatch

游标：

--定义游标
declare 游标名 cursor for
<选择视图>

--使用游标
open 游标名
fetch next from 游标名 into <@接收变量> 
--注意，接收变量的个数要等于从游标中取出来的个数，并且变量类型同游标值类型一致
--循环取值
while @@fetch_status=0
begin
    --拿接收到的值进行需求操作
    fetch next from 游标名 into <@接收变量> 
end
CLOSE  游标名   
DEALLOCATE  游标名

-- while循环取值时 常用到的函数有
--1. 判断该游标数据id是否在目标表中存在？ NOT EXISTS()
if NOT EXISTS(select * from table_name where tabel_name.id = @游标中接收的id变量)
begin
    -- 进一步操作
end
--2. 查找满足条件的记录行数 COUNT(1)
declare @i int
set @i = 0
select @i=COUNT(1) from table_name
select @i