正文
Sqlserver中存储过程，触发器，自定义函数（一）
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
Sqlserver中存储过程，触发器，自定义函数
1.存储过程有关内容
存储过程的定义；
存储过程的分类；
存储过程的创建，修改，执行；
存储过程中参数的传递，返回与接收；
存储过程的返回值；
存储过程使用游标。
1.1存储过程的定义：存放在服务器上预先编译好的sql语句，可以给存储过程传递参数，也可以
从存储过程返回值。
优点：提供了安全访问机制，比如可以将不同的存储过程的执行权限赋予权限不同的用户；
改进了执行性能，因为存储过程是预编译的；
减少了网络流量，因为在调用存储过程时，传递的字符串很短，没有很长的sql语句；
增强了代码的重用性。
1.2分类：系统存储过程，sp_开头；
扩展存储过程，xp_开头，允许其他高级语言编写，如c#，以dll形式存在；
用户自定义存储过程：
T_SQL存储过程；
临时存储过程；
局部：命名以 # 开头；
全局：命名以 ## 开头；
CLR存储过程。
1.3存储过程的创建，修改，执行：
首先确定三个组成部分：
输入参数和输出参数；
sql语句；
返回的状态值，指明执行状态。
简单语法：
eg1:查询指定数据库表orders中的记录个数
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--执行过程
以stuinfo表为例子：
create proc countofinfoq
as
begin
declare @CountOfOrders as int--声明一个作为int类型的存储过程的内部变量
select @CountOfOrders = Count(*) from stuDB.dbo.stuInfo--将sql语句的返回值赋给前面定义的变量
--print convert(varchar(10),@CountOfOrders)--将变量转换为字符串型打印
print @CountOfOrders
end
exec countofinfoq
eg2:查询任意数据库表的记录个数，这里需要指定参数，要注意参数的定义和执行的时的参数传递
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
以stuinfo表为例子：
select * from stuinfo
drop table stuinfobak1
select * into stuDB.dbo.stuinfobak1 from stuinfo
go
create proc CountOfTable1
@TableName as Varchar(20)--定义一个普通的参数
as
begin
declare @Count as int
exec('select * into stuDB.dbo.stuinfobak2 from ' + @TableName)--参数的使用方法,这里exec相当于调用一个新的存储过程
select @Count=Count(*) from stuDB.dbo.stuinfobak2--用临时表缓存原表的数据，对临时表操作完后，删除临时表
drop table stuinfobak2
return @Count--存储过程的返回值，只能是整数值！！！
end
go
declare @Count as int --声明一个变量接收返回值
exec @Count=CountOfTable1 stuinfo
print @Count
调用：
declare @Count as int --声明一个变量接收返回值
declare @Table as varchar(20)
set @Table = '仓库'
exec @Count=CountOfTable @Table
print @Count
eg3:参数传递方式：
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 饮料,1,10,20--顺序传值
exec ParamsTransfer @单价=1,@订购量=20,@库存量=10,@类别名称='饮料'--不按顺序传值
exec ParamsTransfer 饮料,default,10,default--使用默认值
exec ParamsTransfer 饮料,default,10--不指定default也是使用默认值
exec ParamsTransfer @类别名称='饮料',@库存量=10--不按顺序并且使用默认值的传值
--eg4:存储过程的返回值： return一个整数值；使用output参数；返回结构集。
select * from stuinfo
select * from stuinfobak1
drop table stuinfobak1
select * into stuDB.dbo.stuinfobak1 from stuinfo
go
create proc ReturnValue
@返回值1 varchar(20) output
as
begin
declare @返回值2 int
declare @stuID库数 int
select @stuID库数=Count(distinct stuID) from stuinfo set @返回值1 = '' + cast(@stuID库数 as varchar(10))
select @返回值2=Count(distinct stuID) from stuinfo
return @返回值2
end
go
declare @接收值1 varchar(20)
declare @接收值2 int
exec @接收值2=ReturnValue @接收值1 output
print @接收值1
print @接收值2 go
alter proc ReturnValue
@返回值1 varchar(20) output
as
begin
declare @返回值2 int
declare @stuID库数 int
select @stuID库数=Count(distinct stuID) from stuinfo set @返回值1 = '----' + cast(@stuID库数 as varchar(10))
select @返回值2=Count(distinct stuID) from stuinfo
return @返回值2
end
go
declare @接收值1 varchar(20)
declare @接收值2 int
exec @接收值2=ReturnValue @接收值1 output
print @接收值1
print @接收值2
--eg5:调用存储过程返回一个打开的游标，这个是存储过程跟游标的小结合 ----------------------还是看不懂。。
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
