存儲過程

一、存儲過程：經過編譯的SQL語句

1、計算機語言：

A、機器語言

B、彙編語言

C、進階語言（BASIC、forbase、forpro、ASP.PHP、JSP）

說明：将語言轉變為機器語言稱為編譯

特點：A、效率高

B、有利于子產品化程式設計

C、可以減少網絡流量

2、存儲過程的分類

A、系統存儲過程：為了友善管理工作，系統自帶的存儲過程以sp_開頭

B、使用者存儲過程

3、常用的存儲過程

A、顯示資料庫伺服器中的資料庫資訊

sp_databases

B、顯示某一個資料庫的詳細資訊

sp_helpDB

C、顯示dufei資料庫的詳細資訊

sp_helpDB dufei

D、顯示目前資料庫的檔案資訊

sp_helpfile

E、檔案組資訊

sp_helpfilegroup

F、顯示目前資料庫中的表對象（視圖）

sp_tables －－@table_type="table"

G、資料庫改名

sp_renameDB

H、資料庫中對象改名

sp_rename 表 列 視圖 觸發器 ...

I、顯示表的字段資訊

sp_columns 表名

二、特殊存儲過程：擴充存儲過程

1、xp_cmdshell

作用：可以執行系統的任何指令

xp_cmdshell 'dir'

SQL Server 2005 預設 xp_cmdshell是關閉的

開啟方法：

方法一：sp_configure 'show advanced options',1

reconfigure

sp_configure 'xp_cmdshell',1

reconfigure

檢視C槽：master..xp_cmdshell 'dir c:\'

增加一個檔案夾：master..xp_cmdshell 'md c:\muma'

删除一個檔案夾：xp_cmdshell 'RD c:\muma'

關閉：sp_configure 'xp_cmdshell',0

删除 xp_cmdshell

* sp_dropextendedproc 'xp_cmdshell'

恢複：sp_addextendedproc 'xp_cmdshell'

說明：xp_開頭的擴充存儲過程，一般是和.dll檔案結合使用，.dll：動态連結檔案

方法二：開始－－程式－－Microsoft SQL Server 2005－－配置工具－－SQL Server 2005外圍應用配置器－－功能的外圍應用配置器－－xp_cmdshell

2、建立使用者：xp_cmdshell 'net user dufei dufei /add'

xp_cmdshell 'net user dufei'

3、進階使用者：xp_cmdshell 'net localgroup administrators dufei /add'

4、格式化：xp_cmdshell 'format'

5、共享：xp_cmdshell 'net share'

三、 使用者自定義存儲過程

1、文法：create procedure 存儲過程名[參數]

as

SQL 語句

說明：存儲過程－－可程式設計性

執行個體：編寫一個存儲過程，實作顯示四行星

create procedure xing

declare @i int, @j int,@s varchar(100)

set @i=1

while @i<=4

begin

set @j=1

set @s=''

while @j<=2*@i-1

begin

set @s=@s+'*'

set @j=@j+1

end

print space(50-@i)+@s

@i=@i+1

調用：exec 存儲過程名

exec xing

執行個體：編寫一個存儲過程，用于顯示yuanGong表中技術部的員工

create proc proc01 --proc是簡寫

select * from yuanGong where 部門＝'技術部'

exec proc01

2、[參數]

兩種：形式參數：形參，在建立的時候所給定的參數

實際參數：實參，在調用的時候所給定的參數

執行個體：存儲過程，顯示指定行的星

create procedure xing @h int --修改時create可改為alter

declare @i int,@j int,@s varchar(100)

while @i<=@h

set @i=@i+1

exec xing 30 --數量對應@h

執行個體：編寫一個存儲過程，用于删除某個員工記錄

create proc deluser @username varchar(10)

delete from yuanGong where 姓名=@username

exec deluser '張三' --實參

執行個體：通過一個存儲過程來向表中插入一條記錄

create proc procinsert @spname varchar(10),@pri int,@num int

insert into sp values (@spname,@pri,@num)

exec procinsert '包子',3.5,2

select * from sp

exec procinsert '鳥巢',10,2

四、編寫一個存儲過程：自動關閉資料庫伺服器

1、給客戶機發消息：30分鐘後關機

create proc shut

exec xp_cmdshell 'net send 192.168.3.39 各位使用者請注意三十分鐘後，系統将關機維護，請儲存資料！'

說明：什麼時候帶exec,什麼時候不帶exec

print '目前伺服器中'

cmd --cd 回車－－net pause mssqlserver （pause為暫停服務）

2、先暫停（不允許新使用者登入了）

exec xp_cmdshell 'net pause mssqlserver'

3、等待10分鐘

waitfor delay '00:10:00'

4、再發消息：20分鐘後關機

exec xp_cmdshell 'net send 192.168.3.39 各位使用者請注意二十分鐘後，系統将關機維護，請儲存資料！'

5、等待20分鐘

waitfor delay '00:20:00'

6、關機

exec xp_cmdshell 'net stop mssqlserver'

exec shut

說明：發送資訊：

services.msc --messenger 啟動

cmd -- net send * 各位使用者請注意三十分鐘後，系統将關機維護，請儲存資料！（*代表所有使用者，也可用固定使用者如：192.168.2.42）