数据库存储过程详解
/*
存储过程可以看作是在数据库中的存储t-sql脚本
为什么使用存储过程
1、增加性能 本地存储发送的内容少、调用快、预编译、高速缓存
一般语句的执行：检查权限、检查语法，建立执行计划处理语句的要求
存储过程：创建时已经检查了语法；第一次执行的时候执行计划被创建，被编译；
再次执行时不需要重检查语法、不需要重编译、根据已经缓存的计划来决定是否需要重创建执行计划
2、增强安全 加密、分离（权限设置，用户只需要有执行存储过程的权限，不需要有访问存储过程所使用的对象的权限）
3、在transact-sql中使用非数据库技术 dll
4、编程模式——使用外部编程语言调用
1）input
2）output
3）feedback 状态代码或描述性的文本
4）模块化、可重用、可调用其他存储过程
5）隐藏程序逻辑，便于编程
6）可以调用动态连接库（外接的程序）
基本原则：越简单越好 单一任务
*/
/*
分类
1、系统存储过程
存在于master数据库，一般以sp_开头
提供对系统表格数据调用、数据库管理功能、安全管理功能的支持
--表格授权
use pubs
go
execute sp_table_privileges stores
--显示kylin\administrator的所有进程
execute sp_who @loginame='W2K3SERVER\Administrator'
--报告有关孤立的 microsoft windows nt 用户和组的信息，这些用户和组已不在 windows nt 环境中，但仍在 microsoft sql server系统表中拥有项。
execute sp_validatelogins
2、本地存储过程 用户创建的解决特定问题的
3、临时存储过程 存储于tempdb
创建、调用时的数据库 使用范围 生存周期
#local 不限数据库 创建时的连接有效 从创建时开始，当创建的连接中断时消失
##global 不限数据库 所有连接 从创建时开始，当创建的连接中断时消失
直接创建在tempdb的存储过程 tempdb 所有连接 从创建时开始，当数据库服务器服务停止时消失
create proc #local
as
select '#local'
go
exec #local
go
create proc ##global
as
select '##global'
go
exec ##global
go
use tempdb
go
create procedure directtemp
as
select * from [pubs].[dbo].[authors]
go
use northwind
go
exec tempdb.dbo.directtemp
4、扩展存储过程 c++ xp
xp_sendmail既是系统存储过程，也是扩展存储过程
使用objectproperty来判断是否是扩展存储过程
use master
--扩展存储过程
select objectproperty(object_id('sp_prepare'), 'isextendedproc')
--非扩展存储过程
select objectproperty(object_id('xp_logininfo'), 'isextendedproc')
5、远程存储过程
目前版本中只是为了向后兼容，已被分布式查询替代
*/
/*
存储过程在数据库中如何存储
名字 sysobjects
文本 syscomments
*/
/*
练习1：通过查询分析器中的对象查看器查看存储过程
*/
/*
练习2：查看存储过程的内容
图形
语句
*/
select * from sysobjects
select * from syscomments
go
select * from syscomments
where id = object_id('custorderhist')
go
select name,text
from sysobjects inner join syscomments
on sysobjects.id = syscomments.id
where sysobjects.name = 'custorderhist'
go
sp_helptext sp_helptext
go
use northwind
go
exec sp_help custorderhist
exec sp_helptext custorderhist
exec sp_depends custorderhist
exec sp_stored_procedures 'custorderhist'
/*
系统存储过程
以使用为主
*/
/*
本地存储过程的创建、修改、删除
1、t-sql语句
create procedure
alter procedure
drop procedure
create procedure 存储过程名字
as
存储过程文本
go
alter procedure 存储过程名字
as
存储过程文本
go
drop procedure 存储过程名字
2、企业管理器 右键
向导
*/
/*
简单
*/
-- -- -- select top 1 * from products
-- -- -- select top 1 * from orders
-- -- -- select top 1 * from [order details]
/*1、和视图比较*/
alter proc sp_qry_salesdetails
as
select a.productid as 商品编号,a.productname as 商品名称,b.unitprice as 数量,b.quantity as 价格,
b.unitprice*b.quantity as 金额,c.requireddate as 销售时间
from [order details] as b join products as a
on b.productid=a.productid
join orders as c
on b.orderid=c.orderid
go
print '测试'
execute sp_qry_salesdetails
--递归算法
--视图 存储过程 函数
alter view v_qry_salesdetails
as
select a.productid as 商品编号,a.productname as 商品名称,b.unitprice as 数量,b.quantity as 价格,
b.unitprice*b.quantity as 金额,c.requireddate as 销售时间
from [order details] as b join products as a
on b.productid=a.productid
join orders as c
on b.orderid=c.orderid
print '测试'
select * from v_qry_salesdetails
/*
默认情况下第一次执行时的执行计划被保存，以后执行时都是用这个执行计划，直到服务器重启或存储过程使用的表格变化时
当存储过程变化时，如：参数变化，需要重新编译、制定新的执行计划
当每次调用存储过程时强制重新编译的方法：
1、创建时指定 with recompile
2、sp_recompile
*/
create procedure sp1
as
select * from customers
exec sp1
alter procedure sp1
as
select * from customers
alter procedure sp1
with recompile
as
select * from customers
sp_recompile sp1
--加密存储过程 with encryption
select objectproperty(object_id('sp_qry_salesdetails'), 'isencrypted')
/*
删除存储过程
drop proc
*/
use northwind
go
create proc dbo.sp_dropproc
as
select 'northwind.dbo.sp_dropproc'
go
exec northwind.dbo.sp_dropproc
go
use master
go
create proc dbo.sp_dropproc
as
select 'master.dbo.sp_dropproc'
go
exec master.dbo.sp_dropproc
go
use northwind
go
drop proc sp_dropproc
go
exec sp_dropproc
exec master.dbo.sp_dropproc
/*
提供输入参数 input
*/
create proc qry_salesdetails @y int,@m int --varchar(10)
as
select a.productid as 商品编号,a.productname as 商品名称,b.unitprice as 数量,b.quantity as 价格,b.unitprice*b.quantity as 金额,c.requireddate as 销售时间
from [order details] as b join products as a
on b.productid=a.productid
join orders as c
on b.orderid=c.orderid
--where convert(varchar(2),month(c.requireddate)) = @m
where year(c.requireddate) = @y and month(c.requireddate) = @m
go
exec qry_salesdetails 1996,9
exec qry_salesdetails 9,1996
exec qry_salesdetails @m=9,@y=1996
exec qry_salesdetails @y=1996,@m=9
go
/*
northwind 数据库
orders order details 表格 *
根据指定用户ID显示此用户在1996-07-01到1997-07-01之间的订货记录
要求存储过程文本加密
*/
use northwind
go
--创建存储过程
-- drop proc qry_showorders
create proc qry_showorders @custid nchar(5)
with encryption --加密
as
if @custid is null
-- begin
-- print '提供了不正确的参数'
-- return
-- end
select *
from orders od inner join [order details] oddt
on od.orderid = oddt.orderid
where shippeddate >='1996-07-01' and shippeddate <='1997-07-01'
and od.customerid = @custid
go
--调用、检验刚刚创建的存储过程
exec qry_showorders @custid = 'vinet'
exec qry_showorders null
go
--检查是否已经被加密
exec sp_helptext qry_showorders
/*
返回值 output ,一个返回值变量一次只能有一个返回的值
*/
create proc testoutput @a varchar(10) output
as
select @a = 100
go
declare @b varchar(10)
--exec testoutput @b output
exec testoutput @a=@b output
select @b
--error
create proc sum_money @count money, @unitprice money
as
select @count*@unitprice
go
declare @sum_temp money ,@sum_temp2 money
set @sum_temp2 = exec sum_money @count= 1.1,@unitprice = 2.2
create proc sum_money @count money, @unitprice money ,@sum money output
as
set @sum = @count*@unitprice
go
declare @sum_temp money ,@sum_temp2 money
exec sum_money @count= 1.1,@unitprice = 2.2,@sum = @sum_temp output
set @sum_temp2= @sum_temp*100
select @sum_temp2
create proc test_output @in nvarchar(100),@out nvarchar(100) output
as
print 'i''m @in ' + @in
set @out = @in
print 'i''m @out '+@out
go
declare @i nvarchar(100),@o nvarchar(100)
set @i = '让我们一起来测试'
exec test_output @in = @i,@out = @o output
select @o
/*
return 语句和错误处理
*/
--return 主要用来进行错误处理
create proc testreturn @a int
as
if @a<0
begin
return(-1)
end
else if @a = 0
begin
return(0)
end
else
begin
return(1)
end
go
declare @rtn int
exec @rtn = testreturn @a=-100
select @rtn
go
/*
@@error
*/
select @@error
go
select 'a'+1
go
select @@error
select error, description from master.dbo.sysmessages
where error = 245
create proc testerror
as
select 'a'+1
go
exec testerror
go
create proc testerror
as
declare @e int,@a int ,@b int
set @e = 0
set @a = 1
set @b = 0
select @a/@b
if @@error<>0
begin
print '有错误'
set @e = @@error
end
return @e
go
declare @er int
exec @er = testerror
select @er
/*
@@rowcount
*/
select @@rowcount
select * from customers
select @@rowcount
/*
null 值
*/
create proc testreturn @a int
as
if @a is null
begin
return(100)
end
else if @a<0
begin
return(-1)
end
else if @a = 0
begin
return(0)
end
else
begin
return(1)
end
/***************************************************************************************************************************
特殊问题
***************************************************************************************************************************/
/*
关于sp_的命名
*/
use master
go
create sp_test
as
select '现在是master数据库'
go
use northwind
go
create sp_test
as
select '现在是northwind数据库'
go
exec sp_test
exec master.dbo.sp_test
drop sp_test
create proc sp1_test
as
select '这是master'
go
use northwind
go
create proc sp1_test
as
select '这是northwind'
exec sp1_test
drop proc sp1_test
/*
命名延迟解决方案：
创建存储过程时，应用的对象可以不存在，建议存储过程及引用的对象所有者都设置为dbo
*/
--按契约编程
use northwind
go
create proc testdelay
as
select * from tbldelay
go
exec testdelay
/*
在创建存储过程时可以逻辑上形成组，以便作为同一个管理单元并在一个程序中使用
*/
create proc groupedproc;1
as
select 'groupedproc;1 '
go
create proc groupedproc;2
as
select 'groupedproc;2 '
go
sp_helptext groupedproc
go
exec groupedproc;1
go
exec groupedproc;2
go
exec groupedproc
go
drop proc groupedproc
/*
存储过程嵌套，最多32层
*/
create proc a
as
select 'a'
go
create proc b
as
select 'b'
exec a
go
exec b
/*
使用默认值
*/
-- -- drop proc testdefault
create proc testdefault @a int,@b int=2
as
select @a,@b
go
exec testdefault 1
go
exec testdefault @a=1
exec testdefault 1,100
/*
在服务器启动时自动运行的存储过程
要求：所有者是dbo，在master数据库中
*/
use northwind
go
create table start
(
dt datetime
)
go
use master
go
create proc autostart
as
insert into northwind.dbo.start
values(getdate())
go
--设置为自动运行
execute sp_procoption
@procname = autostart,
@optionname = startup,
@optionvalue = true
go
use master
--判断是否自动运行
select objectproperty(object_id('autostart'), 'execisstartup')
go
select * from northwind.dbo.start
--停止自动运行
execute sp_procoption
@procname = autostart,
@optionname = startup,
@optionvalue = false
execute sp_configure
@configname = 'scan for startup procs', @configvalue = 0
reconfigure
go
/*
扩展存储过程
使用sp_addextendedproc 注册
或使用企业管理器 在master 扩展存储过程
*/
-- -- --
-- exec xp_dirtree "D:\"
-- -- --
-- -- -- ------msg 15281, level 16, state 1, procedure xp_cmdshell, line 1
-- -- -- ------sql server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. a system administrator can enable the use of 'xp_cmdshell' by using sp_configure. for more information about enabling 'xp_cmdshell', see "surface area configuration" in sql server books online.
---exec xp_cmdshell "dir *.exe"
-- -- --
-- -- -- exec xp_cmdshell tree
-- -- --
/*
练习：向northwind数据库中的customers 表格插入记录的存储过程
名字insertcust
*/
select
insert
update
delete
create proc insertcust @custid nchar(5),
@cmpnm nvarchar(40),
@cntnm nvarchar(30),
@cntttl nvarchar(30),
@addr nvarchar(60),
@city nvarchar(15),
@rg nvarchar(15),
@pscd nvarchar(10),
@cntry nvarchar(15),
@phone nvarchar(24),
@fax nvarchar(24)
as
--业务逻辑
insert into customers(customerid,companyname,contactname,contacttitle,
address,city,region,postalcode,country,phone,fax)
values(@custid,@cmpnm,@cntnm,@cntttl,
@addr,@city,@rg,@pscd,@cntry,@phone,@fax)
go
exec insertcust @custid='abcd',@cmpnm='abc company',@cntnm='anyone',@cntttl='mr.',@addr='anywhere',
@city='shanghai',@rg='huangpu',@pscd='200000',@cntry='chian',@phone='021-88888888',@fax='021-66666666'
go
--简单实现
create proc createcustid @id nchar(5) output
as
--自动产生客户ID
create proc insertcust
@cmpnm nvarchar(40),
@cntnm nvarchar(30),
@cntttl nvarchar(30),
@addr nvarchar(60),
@city nvarchar(15),
@rg nvarchar(15),
@pscd nvarchar(10),
@cntry nvarchar(15),
@phone nvarchar(24),
@fax nvarchar(24)
as
declare @id nchar(t5)
exec createcustid @id output
insert into customers(customerid,companyname,contactname,contacttitle,address,city,region,postalcode,country,phone,fax)
values(@id,@cmpnm,@cntnm,@cntttl,@addr,@city,@rg,@pscd,@cntry,@phone,@fax)
go
/*
其他要考虑的因素：
customerid 自动生成
如果重复怎么处理？ 生成新id？
电话号码格式不正确如何处理？ return
*/
------------------------------------------------------------------------------------------------------------------------
set nocount off
select 'a'
go
-- -- --
set nocount on
select 'a'
/*
动态语句的使用——动态条件
*/
create proc qry_salesdetails @no int = -1,@start char(10),@end char(10)
as
select a.productid as 商品编号,a.productname as 商品名称,b.unitprice as 数量,b.quantity as 价格,b.unitprice*b.quantity as 金额,c.requireddate as 销售时间
from [order details] as b join products as a
on b.productid=a.productid
join orders as c
on b.orderid=c.orderid
where a.productid= @no and c.requireddate<=@end
and c.requireddate>=@start
go
exec qry_salesdetails 6,'1996-01-01','1997-01-01'
alter proc qry_salesdetails @no int = -1,@start char(10),@end char(10)
as
declare @sql varchar(4000)
set @sql = 'select a.productid as 商品编号,a.productname as 商品名称,
b.unitprice as 数量,b.quantity as 价格,b.unitprice*b.quantity as 金额,
c.requireddate as 销售时间
from [order details] as b join products as a
on b.productid=a.productid
join orders as c
on b.orderid=c.orderid where 1=1 '
if @no is not null
set @sql = @sql + ' and a.productid = '+convert(varchar(10),@no)
if @start is not null and @end is not null
set @sql = @sql + ' and c.requireddate >= '''+ @start+''''
+ ' and c.requireddate <= '''+ @end+''''
--print @sql
exec(@sql)
print ''''
go
exec qry_salesdetails @end=null,@start=null
exec qry_salesdetails @no=35,@end=null,@start=null
exec qry_salesdetails @no=null,@end='1997-07-01',@start='1996-07-01'
exec qry_salesdetails @no=38,@end='1997-07-01',@start='1996-07-01'
sp_stored_procedures qry_salesdetails
/*
临时表的使用
年度销售汇总表
月汇总
年汇总
*/
drop table tempdb..#temp
go
create table #temp
(
商品编号 varchar(100),
商品名称 varchar(100),
金额 money,
销售时间 datetime,
排序 int
)
insert into #temp
select a.productid as 商品编号,a.productname as 商品名称,
b.unitprice*b.quantity as 金额,c.requireddate as 销售时间,
month(c.requireddate)
from [order details] as b join products as a
on b.productid=a.productid
join orders as c
on b.orderid=c.orderid
where year(c.requireddate) = 1996
insert into #temp(商品编号,金额,排序)
select '月汇总',sum(金额),month(销售时间)
from #temp
group by year(销售时间),month(销售时间)
insert into #temp(商品编号,金额,排序)
select '年汇总',sum(金额),12
from #temp
where 销售时间 is not null
select * from #temp
order by 排序 ,商品名称 desc
select * from #temp
drop table tempdb..#temp登录后复制更多SQL的相关技术文章，请访问SQL教程栏目进行学习！
【感谢龙石为本站提供数据交换平台,http://www.longshidata.com/pages/exchange.html】