SQL之存储过程详细介绍及语法（转）
1：定義
存儲過程(stored procedure)是一組為了完成特定功能的SQL語句集合，經編譯后存儲在服務器端的數據庫中，利用存儲過程可以加速SQL語句的執行。
存儲過程分為系統存儲過程和自定義存儲過程。
*系統存儲過程在master數據庫中，但是在其他的數據庫中可以直接調用，并且在調用時不必在存儲過程前加上數據庫名，因為在創建一個新數據庫時，系統存儲過程
在新的數據庫中會自動創建
*自定義存儲過程，由用戶創建并能完成某一特定功能的存儲過程，存儲過程既可以有參數又有返回值，但是它與函數不同，存儲過程的返回值只是指明執行是否成功，
并不能像函數那樣被直接調用，只能利用execute來執行存儲過程。
2：存儲過程的優點
*提高應用程序的通用性和可移植性：存儲過程創建后，可以在程序中被多次調用，而不必重新編寫該存儲過程的SQL語句。并且數據庫專業人員可以隨時對存儲過程進行
修改，且對程序源代碼沒有影響，這樣就極大的提高了程序的可移植性。
*可以更有效的管理用戶操作數據庫的權限：在Sql Server數據庫中，系統管理員可以通過對執行某一存儲過程的權限進行限制，從而實現對相應的數據訪問進行控制，
避免非授權用戶對數據庫的訪問，保證數據的安全。
*可以提高SQL的速度，存儲過程是編譯過的，如果某一個操作包含大量的SQL代碼或分別被執行多次，那么使用存儲過程比直接使用單條SQL語句執行速度快的多。
*減輕服務器的負擔：當用戶的操作是針對數據庫對象的操作時，如果使用單條調用的方式，那么網絡上還必須傳輸大量的SQL語句，如果使用存儲過程，
則直接發送過程的調用命令即可，降低了網絡的負擔。
3：創建存儲過程
SQL Server創建存儲過程：
create procedure 過程名
@parameter 參數類型
@parameter 參數類型
。。。
as
begin
end
執行存儲過程：execute 過程名
Oracle創建存儲過程：
create procedure 過程名
parameter in|out|in out 參數類型
.......
parameter in|out|in out 參數類型
........
as
begin
命令行或者命令塊
exception
命令行或者命令塊
end
4：不帶參數的存儲過程
1 create procedure proc_sql1
2 as
3 begin
4 declare @i int
5 set @i=0
6 while @i<26
7 begin
8 print char(ascii('a') + @i) + '的ASCII碼是: ' + cast(ascii('a') + @i as varchar(5))
9 set @i = @i + 1
10 end
11 end
1 exec proc_sql1
1 a的ASCII碼是: 97 2 b的ASCII碼是: 98 3 c的ASCII碼是: 99 4 d的ASCII碼是: 100 5 e的ASCII碼是: 101 6 f的ASCII碼是: 102 7 g的ASCII碼是: 103 8 h的ASCII碼是: 104 9 i的ASCII碼是: 105 10 j的ASCII碼是: 106 11 k的ASCII碼是: 107 12 l的ASCII碼是: 108 13 m的ASCII碼是: 109 14 n的ASCII碼是: 110 15 o的ASCII碼是: 111 16 p的ASCII碼是: 112 17 q的ASCII碼是: 113 18 r的ASCII碼是: 114 19 s的ASCII碼是: 115 20 t的ASCII碼是: 116 21 u的ASCII碼是: 117 22 v的ASCII碼是: 118 23 w的ASCII碼是: 119 24 x的ASCII碼是: 120 25 y的ASCII碼是: 121 26 z的ASCII碼是: 122
5：數據查詢功能的不帶參數的存儲過程
1 create procedure proc_sql2 2 as 3 begin 4 select * from 職工 where 工資>2000 5 end
execute proc_sql2
在存儲過程中可以包含多個select語句，顯示姓名中含有”張“字職工信息及其所在的倉庫信息，
1 create procedure pro_sql5 2 as 3 begin 4 select * from 職工 where 姓名 like '%張%' 5 select * from 倉庫 where 倉庫號 in(select 倉庫號 from 職工 where 姓名 like '%張%') 6 end 7 8 go 9 execute pro_sql5
6：帶有輸入參數的存儲過程
找出三個數字中的最大數：
1 create proc proc_sql6 2 @num1 int, 3 @num2 int, 4 @num3 int 5 as 6 begin 7 declare @max int 8 if @num1>@num2 9 set @max = @num1 10 else set @max = @num2 11 12 if @num3 > @max 13 set @max = @num3 14 15 print '3個數中最大的數字是:' + cast(@max as varchar(20)) 16 end
execute proc_sql6 15, 25, 35
3個數中最大的數字是:35
7：求階乘之和 如6! + 5! + 4! + 3! + 2! + 1
1 alter proc proc_sql7 2 @dataSource int 3 as 4 begin 5 declare @sum int, @temp int, @tempSum int 6 set @sum = 0 7 set @temp = 1 8 set @tempSum = 1 9 while @temp <= @dataSource 10 begin 11 set @tempSum = @tempSum * @temp 12 set @sum = @sum + @tempSum 13 set @temp = @temp + 1 14 end 15 print cast(@dataSource as varchar(50)) + '的階乘之和為:' + cast(@sum as varchar(50)) 16 end
execute proc_sql7 6
6的階乘之和為：873
8：帶有輸入參數的數據查詢功能的存儲過程
1 create proc proc_sql8 2 @mingz int, 3 @maxgz int 4 as 5 begin 6 select * from 職工 where 工資>@mingz and 工資<@maxgz 7 end
execute proc_sql8 2000,5000
9：帶輸入和輸出參數的存儲過程：顯示指定倉庫號的職工信息和該倉庫號的最大工資和最小工資
1 create proc proc_sql9 2 @cangkuhao varchar(50), 3 @maxgz int output, 4 @mingz int output 5 as 6 begin 7 select * from 職工 where 倉庫號=@cangkuhao 8 select @maxgz=MAX(工資) from 職工 where 倉庫號=@cangkuhao 9 select @mingz=MIN(工資) from 職工 where 倉庫號=@cangkuhao 10 end
1 declare @maxgz int, @mingz int 2 execute proc_sql9 'wh1', @maxgz output, @mingz output 3 select @maxgz as 職工最大工資, @mingz as 職工最小工資
10：帶有登錄判斷功能的存儲過程
[sql]view plaincopy
createprocproc_sql10
@hyuervarchar(50),
@hypwdvarchar(50)
as
begin
if@hyuer='hystu1'
begin
if@hypwd='1111'
print'用戶名和密碼輸入正確'
else
print'密碼輸入錯誤'
end
elseif@hyuer='hystu2'
begin
if@hypwd='2222'
print'用戶名和密碼輸入正確'
else
print'密碼輸入錯誤'
end
elseif@hyuer='hystu3'
begin
if@hypwd='3333'
print'用戶名和密碼輸入正確'
else
print'密碼輸入錯誤'
end
else
print'您輸入的用戶名不正確，請重新輸入'
end
[sql]view plaincopy
executeproc_sql10'hystu1','11'
密碼輸入錯誤
11：帶有判斷條件的插入功能的存儲過程
[sql]view plaincopy
createprocproc_sq111
@zghaovarchar(30),
@ckhaovarchar(30),
@snamevarchar(50),
@sexvarchar(10),
@gzint
as
begin
ifExists(select*from職工where職工號=@zghao)
print'該職工已經存在，請重新輸入'
else
begin
ifExists(select*from倉庫where倉庫號=@ckhao)
begin
insertinto職工(職工號,倉庫號,姓名,性別,工資)
values(@zghao,@ckhao,@sname,@sex,@gz)
end
else
print'您輸入的倉庫號不存在，請重新輸入'
end
end
[sql]view plaincopy
executeproc_sq111'zg42','wh1','張平','女',1350
12： 創建加密存儲過程
[sql]view plaincopy
createprocproc_enerypt
withencryption
as
begin
select*from倉庫
end
所謂加密存儲過程，就是將create proc 語句的原始文本轉換為模糊格式，模糊代碼的輸出在SQL Server的任何目錄視圖中都能直接顯示
13： 查看存儲過程和功能代碼信息
[sql]view plaincopy
selectname,crdatefromsysobjectswheretype='p'
查看指定存儲過程的屬性信息：
[sql]view plaincopy
executesp_helpproc_sql1
查看存儲過程所使用的數據對象的信息
[sql]view plaincopy
executesp_dependsproc_sql2
查看存儲過程的功能代碼
[sql]view plaincopy
executesp_helptextproc_sql9
14：重命名存儲過程名
execute sp_rename 原存儲過程名, 新存儲過程名
15：刪除存儲過程
drop 過程名
帶有判斷條件的刪除存儲過程
[sql]view plaincopy
ifExists(select*fromdbo.sysobjectswherename='proc_sql6'andxtype='p')
begin
print'要刪除的存儲過程存在'
dropprocproc_sq16
print'成功刪除存儲過程proc_sql6'
end
else
print'要刪除的存儲過程不存在'
16：存儲過程的自動執行
使用sp_procoption系統存儲過程即可自動執行一個或者多個存儲過程，其語法格式如下：
sp_procoption [@procName=] 'procedure', [@optionName=] 'option', [@optionValue=] 'value'
各個參數含義如下：
[@procName=] 'procedure'： 即自動執行的存儲過程
[@optionName=] 'option'：其值是startup，即自動執行存儲過程
[@optionValue=] 'value'：表示自動執行是開(true)或是關(false)
[sql]view plaincopy
sp_procoption@procName='masterproc',@optionName='startup',@optionValue='true'
利用sp_procoption系統函數設置存儲過程masterproc為自動執行
17：監控存儲過程
可以使用sp_monitor可以查看SQL Server服務器的各項運行參數，其語法格式如下：
sp_monitor
該存儲過程的返回值是布爾值，如果是0，表示成功，如果是1，表示失敗。該存儲過程的返回集的各項參數的含義如下：
*last_run: 上次運行時間
*current_run：本次運行的時間
*seconds: 自動執行存儲過程后所經過的時間
*cpu_busy:計算機CPU處理該存儲過程所使用的時間
*io_busy:在輸入和輸出操作上花費的時間
*idle：SQL Server已經空閑的時間
*packets_received：SQL Server讀取的輸入數據包數
*packets_sent：SQL Server寫入的輸出數據包數
*packets_error:SQL Server在寫入和讀取數據包時遇到的錯誤數
*total_read: SQL Server讀取的次數
*total_write: SQLServer寫入的次數
*total_errors: SQL Server在寫入和讀取時遇到的錯誤數
*connections:登錄或嘗試登錄SQL Server的次數
總結
以上是生活随笔為你收集整理的SQL之存储过程详细介绍及语法（转）的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Bug的生命周期
- 下一篇： BigDecimal.setScale