共計 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 問一下細節
正文完
