数据库存储过程的示例分析

共计 11601 个字符，预计需要花费 30 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章给大家分享的是有关数据库存储过程的示例分析的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。

/*
存储过程可以看作是在数据库中的存储 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、远程存储过程
  目前版本中只是为了向后兼容，已被分布式查询替代

select * from syscomments 
where id = object_id(custorderhist)
select name,text
from sysobjects inner join syscomments 
on sysobjects.id = syscomments.id
where sysobjects.name =  custorderhist 
sp_helptext sp_helptext
use northwind
exec sp_help custorderhist
exec sp_helptext custorderhist
exec sp_depends custorderhist
exec sp_stored_procedures  custorderhist  

-- -- -- select top 1 * from products
-- -- -- select top 1 * from orders
-- -- -- select top 1 * from [order details]
/*1、和视图比较 */
alter proc sp_qry_salesdetails
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  测试 
execute sp_qry_salesdetails
-- 递归算法
-- 视图   存储过程   函数
alter view v_qry_salesdetails
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
create proc dbo.sp_dropproc
select  northwind.dbo.sp_dropproc 
exec northwind.dbo.sp_dropproc
use master
create proc dbo.sp_dropproc
select  master.dbo.sp_dropproc 
exec master.dbo.sp_dropproc
use northwind
drop proc sp_dropproc
exec sp_dropproc
exec master.dbo.sp_dropproc
提供输入参数  input
create proc qry_salesdetails @y int,@m int --varchar(10)
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
northwind  数据库
orders order details  表格  *
根据指定用户 ID 显示此用户在 1996-07-01 到 1997-07-01 之间的订货记录  
要求存储过程文本加密  

-- drop proc qry_showorders 
create proc qry_showorders @custid nchar(5)
with encryption -- 加密
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
-- 调用、检验刚刚创建的存储过程
exec qry_showorders @custid =  vinet 
exec qry_showorders null
-- 检查是否已经被加密
exec sp_helptext qry_showorders
返回值  output , 一个返回值变量一次只能有一个返回的值
create proc testoutput @a varchar(10) output
select @a = 100
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
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
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
print  i m @in   + @in
set @out = @in
print  i m @out  +@out
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 
declare @rtn int
exec @rtn = testreturn @a=-100
select @rtn
 @@error
select @@error
select  a +1
select @@error

select error, description from master.dbo.sysmessages
where error = 245
create proc testerror
as 
select  a +1
exec testerror
create proc testerror
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
 return @e
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)
else if @a 0
begin
 return(-1)
end 
else if @a = 0
begin
 return(0)
end 
else 
begin
 return(1)
end 

/***************************************************************************************************************************
***************************************************************************************************************************/
关于 sp_的命名
use master
create sp_test
select  现在是 master 数据库 
use northwind
create sp_test
select  现在是 northwind 数据库 
exec sp_test
exec master.dbo.sp_test
drop sp_test 
create proc sp1_test
as 
select  这是 master 
use northwind
create proc sp1_test
as 
select  这是 northwind 
exec sp1_test
drop proc sp1_test
命名延迟解决方案：创建存储过程时，应用的对象可以不存在，建议存储过程及引用的对象所有者都设置为 dbo
-- 按契约编程
use northwind
create proc testdelay
select * from tbldelay
exec testdelay
在创建存储过程时可以逻辑上形成组，以便作为同一个管理单元并在一个程序中使用
create proc groupedproc;1 
select  groupedproc;1  
create proc groupedproc;2
select  groupedproc;2  
sp_helptext groupedproc
exec groupedproc;1
exec groupedproc;2
exec groupedproc
drop proc groupedproc
存储过程嵌套，最多 32 层
*/ 
create proc a 
select  a 
create proc b
select  b 
exec a 
exec b
使用默认值
-- -- drop proc testdefault
create proc testdefault @a int,@b int=2
select @a,@b
exec testdefault 1
exec testdefault @a=1
exec testdefault 1,100
在服务器启动时自动运行的存储过程
要求：所有者是 dbo，在 master 数据库中
use northwind
create table start
dt datetime
use master
create proc autostart
insert into northwind.dbo.start
values(getdate())
-- 设置为自动运行
execute sp_procoption
@procname = autostart,
@optionname = startup,
@optionvalue = true
use master
-- 判断是否自动运行
select objectproperty(object_id( autostart),  execisstartup )
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

-- -- -- 
-- -- -- ------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)
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 
-- 简单实现
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)
其他要考虑的因素：customerid  自动生成
如果重复怎么处理？  生成新 id？电话号码格式不正确如何处理？ return
------------------------------------------------------------------------------------------------------------------------
set nocount off
select  a 
-- -- -- 
set nocount on
select  a 
动态语句的使用——动态条件

create proc qry_salesdetails @no int = -1,@start char(10),@end char(10)
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
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

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

感谢各位的阅读！关于“数据库存储过程的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！

向 AI 问一下细节