數(shù)據(jù)庫(kù)存儲(chǔ)過程的示例分析

這篇文章給大家分享的是有關(guān)數(shù)據(jù)庫(kù)存儲(chǔ)過程的示例分析的內(nèi)容。小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過來看看吧。

/*
存儲(chǔ)過程可以看作是在數(shù)據(jù)庫(kù)中的存儲(chǔ)t-sql腳本

為什么使用存儲(chǔ)過程
1、增加性能   本地存儲(chǔ)發(fā)送的內(nèi)容少、調(diào)用快、預(yù)編譯、高速緩存
       一般語句的執(zhí)行：檢查權(quán)限、檢查語法，建立執(zhí)行計(jì)劃處理語句的要求
       存儲(chǔ)過程：創(chuàng)建時(shí)已經(jīng)檢查了語法；第一次執(zhí)行的時(shí)候執(zhí)行計(jì)劃被創(chuàng)建，被編譯；
              再次執(zhí)行時(shí)不需要重檢查語法、不需要重編譯、根據(jù)已經(jīng)緩存的計(jì)劃來決定是否需要重創(chuàng)建執(zhí)行計(jì)劃
2、增強(qiáng)安全   加密、分離（權(quán)限設(shè)置，用戶只需要有執(zhí)行存儲(chǔ)過程的權(quán)限，不需要有訪問存儲(chǔ)過程所使用的對(duì)象的權(quán)限）
   
3、在transact-sql中使用非數(shù)據(jù)庫(kù)技術(shù)  dll
4、編程模式——使用外部編程語言調(diào)用
   1）input
   2）output
   3）feedback 狀態(tài)代碼或描述性的文本
   4）模塊化、可重用、可調(diào)用其他存儲(chǔ)過程
   5）隱藏程序邏輯，便于編程
   6）可以調(diào)用動(dòng)態(tài)連接庫(kù)（外接的程序）
基本原則：越簡(jiǎn)單越好 單一任務(wù)
*/

/*
分類
1、系統(tǒng)存儲(chǔ)過程 
   存在于master數(shù)據(jù)庫(kù)，一般以sp_開頭
   提供對(duì)系統(tǒng)表格數(shù)據(jù)調(diào)用、數(shù)據(jù)庫(kù)管理功能、安全管理功能的支持
  --表格授權(quán)
  use pubs
  go
  execute sp_table_privileges stores
  --顯示kylin\administrator的所有進(jìn)程
  execute sp_who @loginame='W2K3SERVER\Administrator'
  --報(bào)告有關(guān)孤立的 microsoft windows nt 用戶和組的信息，這些用戶和組已不在 windows nt 環(huán)境中，但仍在 microsoft sql server系統(tǒng)表中擁有項(xiàng)。
  execute sp_validatelogins
2、本地存儲(chǔ)過程   用戶創(chuàng)建的解決特定問題的
3、臨時(shí)存儲(chǔ)過程   存儲(chǔ)于tempdb
                    創(chuàng)建、調(diào)用時(shí)的數(shù)據(jù)庫(kù)    使用范圍           生存周期 
   #local                     不限數(shù)據(jù)庫(kù)        創(chuàng)建時(shí)的連接有效    從創(chuàng)建時(shí)開始，當(dāng)創(chuàng)建的連接中斷時(shí)消失
   ##global                   不限數(shù)據(jù)庫(kù)        所有連接            從創(chuàng)建時(shí)開始，當(dāng)創(chuàng)建的連接中斷時(shí)消失
   直接創(chuàng)建在tempdb的存儲(chǔ)過程  tempdb            所有連接            從創(chuàng)建時(shí)開始，當(dāng)數(shù)據(jù)庫(kù)服務(wù)器服務(wù)停止時(shí)消失
   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、擴(kuò)展存儲(chǔ)過程  c++ xp
   xp_sendmail既是系統(tǒng)存儲(chǔ)過程，也是擴(kuò)展存儲(chǔ)過程
   使用objectproperty來判斷是否是擴(kuò)展存儲(chǔ)過程
    use master
    --擴(kuò)展存儲(chǔ)過程
    select objectproperty(object_id('sp_prepare'), 'isextendedproc')
    --非擴(kuò)展存儲(chǔ)過程
    select objectproperty(object_id('xp_logininfo'), 'isextendedproc')
5、遠(yuǎn)程存儲(chǔ)過程
   目前版本中只是為了向后兼容，已被分布式查詢替代
*/


/*
存儲(chǔ)過程在數(shù)據(jù)庫(kù)中如何存儲(chǔ)
名字 sysobjects
文本 syscomments 
*/

/*
練習(xí)1：通過查詢分析器中的對(duì)象查看器查看存儲(chǔ)過程
*/

/*
練習(xí)2：查看存儲(chǔ)過程的內(nèi)容
       圖形
       語句
*/
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' 


/*
系統(tǒng)存儲(chǔ)過程
以使用為主
*/

/*
本地存儲(chǔ)過程的創(chuàng)建、修改、刪除
1、t-sql語句
create procedure
alter procedure
drop procedure

create procedure 存儲(chǔ)過程名字
as
存儲(chǔ)過程文本
go 

alter procedure 存儲(chǔ)過程名字
as
存儲(chǔ)過程文本
go 
 
drop procedure 存儲(chǔ)過程名字
2、企業(yè)管理器  右鍵
               向?qū)?
*/

/*
簡(jiǎn)單 
*/
-- -- -- 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 商品編號(hào),a.productname as 商品名稱,b.unitprice as 數(shù)量,b.quantity as 價(jià)格,
b.unitprice*b.quantity as 金額,c.requireddate as 銷售時(shí)間
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 '測(cè)試'

execute sp_qry_salesdetails

--遞歸算法
--視圖  存儲(chǔ)過程  函數(shù)
alter view v_qry_salesdetails
as
select a.productid as 商品編號(hào),a.productname as 商品名稱,b.unitprice as 數(shù)量,b.quantity as 價(jià)格,
b.unitprice*b.quantity as 金額,c.requireddate as 銷售時(shí)間
from [order details] as b join products as a
on b.productid=a.productid
join orders as c
on b.orderid=c.orderid
print '測(cè)試'

select * from v_qry_salesdetails 
/*
默認(rèn)情況下第一次執(zhí)行時(shí)的執(zhí)行計(jì)劃被保存，以后執(zhí)行時(shí)都是用這個(gè)執(zhí)行計(jì)劃，直到服務(wù)器重啟或存儲(chǔ)過程使用的表格變化時(shí)
當(dāng)存儲(chǔ)過程變化時(shí)，如：參數(shù)變化，需要重新編譯、制定新的執(zhí)行計(jì)劃
當(dāng)每次調(diào)用存儲(chǔ)過程時(shí)強(qiáng)制重新編譯的方法：
1、創(chuàng)建時(shí)指定 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

--加密存儲(chǔ)過程 with encryption 
select objectproperty(object_id('sp_qry_salesdetails'), 'isencrypted')

/*
刪除存儲(chǔ)過程
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

/*
提供輸入?yún)?shù) input
*/
create proc qry_salesdetails @y int,@m int --varchar(10)
as
select a.productid as 商品編號(hào),a.productname as 商品名稱,b.unitprice as 數(shù)量,b.quantity as 價(jià)格,b.unitprice*b.quantity as 金額,c.requireddate as 銷售時(shí)間
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 數(shù)據(jù)庫(kù)
orders order details 表格 *
根據(jù)指定用戶ID顯示此用戶在1996-07-01到1997-07-01之間的訂貨記錄 
要求存儲(chǔ)過程文本加密 
*/


use northwind
go
--創(chuàng)建存儲(chǔ)過程
-- drop proc qry_showorders 
create proc qry_showorders @custid nchar(5)
with encryption   --加密
as
if @custid is  null
-- begin
--   print '提供了不正確的參數(shù)'
--   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
--調(diào)用、檢驗(yàn)剛剛創(chuàng)建的存儲(chǔ)過程
exec qry_showorders @custid = 'vinet'
exec qry_showorders null
go
--檢查是否已經(jīng)被加密
exec sp_helptext qry_showorders

/*
返回值 output ,一個(gè)返回值變量一次只能有一個(gè)返回的值
*/
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 = '讓我們一起來測(cè)試'
exec test_output @in = @i,@out = @o output
select @o

/*
return 語句和錯(cuò)誤處理
*/
--return 主要用來進(jìn)行錯(cuò)誤處理
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 '有錯(cuò)誤'
   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 




/***************************************************************************************************************************
特殊問題
***************************************************************************************************************************/
/*
關(guān)于sp_的命名
*/
use master
go
create sp_test
as
select '現(xiàn)在是master數(shù)據(jù)庫(kù)'
go
use northwind
go
create sp_test
as
select '現(xiàn)在是northwind數(shù)據(jù)庫(kù)'
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
/*
命名延遲解決方案：
創(chuàng)建存儲(chǔ)過程時(shí)，應(yīng)用的對(duì)象可以不存在，建議存儲(chǔ)過程及引用的對(duì)象所有者都設(shè)置為dbo
*/
--按契約編程
use northwind
go
create proc testdelay
as
select * from tbldelay
go
exec testdelay
/*
在創(chuàng)建存儲(chǔ)過程時(shí)可以邏輯上形成組，以便作為同一個(gè)管理單元并在一個(gè)程序中使用
*/
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
/*
存儲(chǔ)過程嵌套，最多32層
*/ 
create proc a 
as
select 'a'
go
create proc b
as
select 'b'
exec a 
go
exec b

/*
使用默認(rèn)值
*/
-- -- 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

/*
在服務(wù)器啟動(dòng)時(shí)自動(dòng)運(yùn)行的存儲(chǔ)過程
要求：所有者是dbo，在master數(shù)據(jù)庫(kù)中
*/
use northwind
go
create table start
(
dt datetime
)
go
use master
go
create proc autostart
as
insert into northwind.dbo.start
values(getdate())
go
--設(shè)置為自動(dòng)運(yùn)行
execute sp_procoption
@procname = autostart,
@optionname = startup,
@optionvalue = true
go
use master
--判斷是否自動(dòng)運(yùn)行
select objectproperty(object_id('autostart'), 'execisstartup')
go
select * from northwind.dbo.start
--停止自動(dòng)運(yùn)行
execute sp_procoption
@procname = autostart,
@optionname = startup,
@optionvalue = false

execute sp_configure
@configname = 'scan for startup procs', @configvalue = 0
reconfigure
go



/*
擴(kuò)展存儲(chǔ)過程
使用sp_addextendedproc 注冊(cè)
或使用企業(yè)管理器 在master 擴(kuò)展存儲(chǔ)過程
*/

-- -- -- 
-- 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
-- -- -- 


/*
  練習(xí)：向northwind數(shù)據(jù)庫(kù)中的customers 表格插入記錄的存儲(chǔ)過程
  名字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 
--業(yè)務(wù)邏輯
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

--簡(jiǎn)單實(shí)現(xiàn)
create proc createcustid  @id nchar(5) output
as 
 --自動(dòng)產(chǎn)生客戶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 自動(dòng)生成
如果重復(fù)怎么處理？ 生成新id？
電話號(hào)碼格式不正確如何處理？  return
*/
------------------------------------------------------------------------------------------------------------------------
set nocount off
select 'a'
go
-- -- -- 
set nocount on
select 'a'

/*

動(dòng)態(tài)語句的使用——?jiǎng)討B(tài)條件

*/


create proc qry_salesdetails @no int = -1,@start char(10),@end char(10)
as
select a.productid as 商品編號(hào),a.productname as 商品名稱,b.unitprice as 數(shù)量,b.quantity as 價(jià)格,b.unitprice*b.quantity as 金額,c.requireddate as 銷售時(shí)間 
        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 商品編號(hào),a.productname as 商品名稱,
b.unitprice as 數(shù)量,b.quantity as 價(jià)格,b.unitprice*b.quantity as 金額,
c.requireddate as 銷售時(shí)間 
        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


/*
臨時(shí)表的使用

年度銷售匯總表
月匯總
年匯總
*/
drop table tempdb..#temp
go
create table #temp 
(
商品編號(hào) varchar(100),
商品名稱  varchar(100),
金額 money,
銷售時(shí)間 datetime,
排序 int
)

insert into #temp
select a.productid as 商品編號(hào),a.productname as 商品名稱,
       b.unitprice*b.quantity as 金額,c.requireddate as 銷售時(shí)間,
       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(商品編號(hào),金額,排序)
select '月匯總',sum(金額),month(銷售時(shí)間)
from #temp
group by year(銷售時(shí)間),month(銷售時(shí)間)
 

insert into #temp(商品編號(hào),金額,排序)
select '年匯總',sum(金額),12
from #temp
where 銷售時(shí)間 is not null

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

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

感謝各位的閱讀！關(guān)于“數(shù)據(jù)庫(kù)存儲(chǔ)過程的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！