Detailed explanation of database stored procedures

/*
存储过程可以看作是在数据库中的存储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 >=&#39;1996-07-01&#39; and shippeddate <=&#39;1997-07-01&#39;
and od.customerid = @custid
go
--调用、检验刚刚创建的存储过程
exec qry_showorders @custid = &#39;vinet&#39;
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 &#39;i&#39;&#39;m @in  &#39; + @in
set @out = @in
print &#39;i&#39;&#39;m @out  &#39;+@out

go
declare @i nvarchar(100),@o nvarchar(100)
set @i = &#39;让我们一起来测试&#39;
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 &#39;a&#39;+1
go
select @@error


select error, description from master.dbo.sysmessages
where error = 245

create proc testerror
as 
select &#39;a&#39;+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 &#39;有错误&#39;
   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 &#39;现在是master数据库&#39;
go
use northwind
go
create sp_test
as
select &#39;现在是northwind数据库&#39;
go
exec sp_test
exec master.dbo.sp_test
drop sp_test 

create proc sp1_test
as 
select &#39;这是master&#39;
go
use northwind
go
create proc sp1_test
as 
select &#39;这是northwind&#39;

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 &#39;groupedproc;1 &#39;
go
create proc groupedproc;2
as
select &#39;groupedproc;2 &#39;
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 &#39;a&#39;
go
create proc b
as
select &#39;b&#39;
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(&#39;autostart&#39;), &#39;execisstartup&#39;)
go
select * from northwind.dbo.start
--停止自动运行
execute sp_procoption
@procname = autostart,
@optionname = startup,
@optionvalue = false

execute sp_configure
@configname = &#39;scan for startup procs&#39;, @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 &#39;sys.xp_cmdshell&#39; of component &#39;xp_cmdshell&#39; because this component is turned off as part of the security configuration for this server. a system administrator can enable the use of &#39;xp_cmdshell&#39; by using sp_configure. for more information about enabling &#39;xp_cmdshell&#39;, 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=&#39;abcd&#39;,@cmpnm=&#39;abc company&#39;,@cntnm=&#39;anyone&#39;,@cntttl=&#39;mr.&#39;,@addr=&#39;anywhere&#39;,
                @city=&#39;shanghai&#39;,@rg=&#39;huangpu&#39;,@pscd=&#39;200000&#39;,@cntry=&#39;chian&#39;,@phone=&#39;021-88888888&#39;,@fax=&#39;021-66666666&#39;
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 &#39;a&#39;
go
-- -- -- 
set nocount on
select &#39;a&#39;

/*

动态语句的使用——动态条件

*/


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,&#39;1996-01-01&#39;,&#39;1997-01-01&#39;






alter proc qry_salesdetails @no int = -1,@start char(10),@end char(10)
as 
declare @sql varchar(4000)
set @sql = &#39;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  &#39;
if @no is not null
     set @sql = @sql + &#39; and  a.productid = &#39;+convert(varchar(10),@no)
if @start is not null  and  @end is not null
     set @sql = @sql    + &#39; and c.requireddate >=  &#39;&#39;&#39;+ @start+&#39;&#39;&#39;&#39;
                        + &#39; and c.requireddate <= &#39;&#39;&#39;+ @end+&#39;&#39;&#39;&#39;

--print @sql
exec(@sql)
print &#39;&#39;&#39;&#39;
go 
exec qry_salesdetails @end=null,@start=null
exec qry_salesdetails @no=35,@end=null,@start=null
exec qry_salesdetails @no=null,@end=&#39;1997-07-01&#39;,@start=&#39;1996-07-01&#39;
exec qry_salesdetails @no=38,@end=&#39;1997-07-01&#39;,@start=&#39;1996-07-01&#39;

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 &#39;月汇总&#39;,sum(金额),month(销售时间)
from #temp
group by year(销售时间),month(销售时间)
 

insert into #temp(商品编号,金额,排序)
select &#39;年汇总&#39;,sum(金额),12
from #temp
where 销售时间 is not null

select * from #temp
order by 排序 ,商品名称 desc

select * from #temp
drop table tempdb..#temp

For more technical articles related to SQL, please visit the SQL Tutorial column to learn!

The above is the detailed content of Detailed explanation of database stored procedures. For more information, please follow other related articles on the PHP Chinese website!