Sqlserver中存储过程，触发器，自定义函数(一)

Sqlserver中存储过程，触发器，自定义函数

1.存储过程有关内容
存储过程的定义；
存储过程的分类；
存储过程的创建，修改，执行；
存储过程中参数的传递，返回与接收；
存储过程的返回值；
存储过程使用游标。

1.1存储过程的定义：存放在服务器上预先编译好的sql语句，可以给存储过程传递参数，也可以
从存储过程返回值。

优点：提供了安全访问机制，比如可以将不同的存储过程的执行权限赋予权限不同的用户；
改进了执行性能，因为存储过程是预编译的；
减少了网络流量，因为在调用存储过程时，传递的字符串很短，没有很长的sql语句；
增强了代码的重用性。

1.2分类：系统存储过程，sp_开头；
扩展存储过程，xp_开头，允许其他高级语言编写，如c#，以dll形式存在；
用户自定义存储过程：
T_SQL存储过程；
临时存储过程；
局部：命名以 # 开头；
全局：命名以 ## 开头；
CLR存储过程。

1.3存储过程的创建，修改，执行：
首先确定三个组成部分：
输入参数和输出参数；
sql语句；
返回的状态值，指明执行状态。

简单语法：
eg1:查询指定数据库表orders中的记录个数

1. create proc CountOfOrders--指定存储过程名
2. as--指定存储过程的主体
3. begin
4. declare @CountOfOrders asint--声明一个作为int类型的存储过程的内部变量
5. select @CountOfOrders = Count(*) fromt orders--将sql语句的返回值赋给前面定义的变量
6. print convert(verchar(10),@CountOfOrders)--将变量转换为字符串型打印
7. end
8. go--确定一个执行计划
9. exec CountOfOrders--执行过程

create proc CountOfOrders--指定存储过程名
    as--指定存储过程的主体
     begin
      declare @CountOfOrders as int--声明一个作为int类型的存储过程的内部变量
      select @CountOfOrders = Count(*) fromt orders--将sql语句的返回值赋给前面定义的变量
      print convert(verchar(10),@CountOfOrders)--将变量转换为字符串型打印
     end
    go--确定一个执行计划
    exec CountOfOrders--执行过程

1. create proc CountOfTable
2. @TableName as Varchar(20)--定义一个普通的参数
3. as
4. begin
5. declare @Countas int
6. exec('select * into tmptable from ' + @TableName)--参数的使用方法,这里exec相当于调用一个新的存储过程
7. select @Count=Count(*)from tmptable--用临时表缓存原表的数据，对临时表操作完后，删除临时表
8. drop table tmptable
9. return @Count--存储过程的返回值，只能是整数值！！！
10. end
11. declare @Countas int--声明一个变量接收返回值
12. exec @Count=CountOfTable 仓库
13. print @Count

 create proc CountOfTable
    @TableName as Varchar(20)--定义一个普通的参数
    as
     begin
      declare @Count as int
      exec('select * into tmptable from ' + @TableName)--参数的使用方法,这里exec相当于调用一个新的存储过程
      select @Count=Count(*) from tmptable--用临时表缓存原表的数据，对临时表操作完后，删除临时表
      drop table tmptable
      return @Count--存储过程的返回值，只能是整数值！！！
     end
    declare @Count as int   --声明一个变量接收返回值
    exec @Count=CountOfTable 仓库  
    print @Count

1. <span style="font-family: Arial,Verdana,sans-serif; white-space: normal;"><span style="white-space: pre;"> </span>--或</span>

 --或

1. declare @Countas int--声明一个变量接收返回值
2. declare @Tableas varchar(20)
3. set @Table ='仓库'
4. exec @Count=CountOfTable @Table
5. print @Count

 declare @Count as int   --声明一个变量接收返回值
    declare @Table as varchar(20)
    set @Table = '仓库'
    exec @Count=CountOfTable @Table
    print @Count

1. create proc ParamsTransfer
2. @类别名称 varchar(15),
3. @单价 money=$10,
4. @库存量 smallint,
5. @订购量 smallint = 5--带默认值，假如没有给它传值，则使用默认值
6. as
7. begin
8. select * from 产品
9. join 类别 on 产品.id = 类别.id
10. where
11. 类别.类别名称=@类别名称 and
12. 产品.单价 > @单价 and
13. 产品.库存量 > @库存量 and
14. 产品.订购量 > @订购量
15. end
16. exec ParamsTransfer 饮料,1,10,20--顺序传值
17. exec ParamsTransfer @单价=1,@订购量=20,@库存量=10,@类别名称='饮料'--不按顺序传值
18. exec ParamsTransfer 饮料,default,default--使用默认值
19. exec ParamsTransfer 饮料,10--不指定default也是使用默认值
20. exec ParamsTransfer @类别名称='饮料',@库存量=10--不按顺序并且使用默认值的传值

create proc ParamsTransfer
      @类别名称 varchar(15),@单价 money=$10,@库存量 smallint,@订购量 smallint = 5--带默认值，假如没有给它传值，则使用默认值
     as
      begin
       select * from 产品
        join 类别 on 产品.id = 类别.id
        where
         类别.类别名称=@类别名称 and 
         产品.单价 > @单价 and
         产品.库存量 > @库存量 and
         产品.订购量 > @订购量
       end
      exec ParamsTransfer 饮料,20--顺序传值
      exec ParamsTransfer @单价=1,@类别名称='饮料'--不按顺序传值
      exec ParamsTransfer 饮料,default,default--使用默认值
      exec ParamsTransfer 饮料,10--不指定default也是使用默认值
      exec ParamsTransfer @类别名称='饮料',@库存量=10--不按顺序并且使用默认值的传值

1. create proc ReturnValue
2. @返回值1 varchar(20) output
3. as
4. begin
5. declare @返回值2 int
6. declare @总仓库数 int
7. select @总仓库数=Count(distinct 仓库号)from 仓库
8. set @返回值1 = '' +cast(@总仓库数 asvarchar(10))
9. select @返回值2=Count(distinct 仓库号)from 仓库
10. return @返回值2
11. end
12. go
13. declare @接收值1 varchar(20)
14. declare @接收值2 int
15. exec @接收值2=ReturnValue @接收值1output
16. print @接收值1
17. print @接收值2

   create proc ReturnValue
      @返回值1 varchar(20) output
     as
      begin 
       declare @返回值2 int
       declare @总仓库数 int
       select @总仓库数=Count(distinct 仓库号) from 仓库
       set @返回值1 = '' + cast(@总仓库数 as varchar(10))
       select @返回值2=Count(distinct 仓库号) from 仓库
       return @返回值2
    end
     go
     declare @接收值1 varchar(20)  
     declare @接收值2 int
     exec @接收值2=ReturnValue @接收值1 output
     print @接收值1
     print @接收值2

1. create proc UseCursor
2. @cursor cursorVarying output
3. as
4. begin
5. set @cursor=Cursor forward_onlystatic for
6. select top 10 *from 订单
7. open @cursor
8. end
9. declare @my_cursor cursor
10. declare @订单号 varchar(20)
11. declare @供应商号 varchar(20)
12. declare @职工号 varchar(20)
13. declare @订单日期 varchar(30)
14. exec UseCursor @my_cursor output
15. fetch nextfrom @my_cursor
16. into @职工号,@订单号,@供应商号,@订单日期
17. while @@fetch_status=0
18. begin
19. print @订单号 + ' -- ' + @订单日期
20. fetch nextfrom @my_cursor
21. into @职工号,@订单日期
22. end

 create proc UseCursor
     @cursor cursor Varying output
    as
     begin
      set @cursor=Cursor forward_only static for
      select top 10 * from 订单
      open @cursor
     end
    declare @my_cursor cursor 
    declare @订单号 varchar(20)
    declare @供应商号 varchar(20)
    declare @职工号 varchar(20)
    declare @订单日期 varchar(30)
    exec UseCursor @my_cursor output
    fetch next from @my_cursor 
     into @职工号,@订单日期
     while @@fetch_status=0
      begin 
       print @订单号 + ' -- ' + @订单日期
       fetch next from @my_cursor 
        into @职工号,@订单日期
      end
     

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!