存盤程序

系統存盤程序
拓展存盤程序
- xp_cmdshell
用戶自定義存盤程序
- 無參存盤程序
- - 無參存盤程序定義
- 帶引數存盤程序
- - 創建帶引數的存盤程序語法
  - 執行存盤程序
  - 輸出引數
自定義錯誤資訊

預先儲存好的sql程式（資料庫操作陳述句，變數，邏輯控制陳述句等）
保存在SQL Server中
通過名稱和引數執行，可回傳結果，

存盤程序的優點：

執行速度快
允許模塊化程式設計
提高系統安全性
減少網路流通量

系統存盤程序

系統存盤程序的名稱一般以“sp_”開頭
由SQLServer創建、管理和使用
存放在Resource資料庫中

sp_databases 列舉服務器上所有資料庫
sp_tables 回傳當前環境下可查詢的物件串列
sp_helptext 把另外一個未加密的存盤程序打開查看
例子如下

execute sp_databases
exec sp_helptext sp_databases

拓展存盤程序

拓展存盤程序的名稱通常以“xp_”開頭
使用編輯語言（如C#）創建的外部存盤程序
以DLL形式單獨存在

xp_cmdshell

可以執行DOS命令下的一些操作
可以以為本行的方式回傳任何輸出

寫法如下：

EXEC xp_cmdshell DOS命令 [NO_OUTPUT]

例子如下：

USE master
GO
/*---若xp_cmdShell作為服務器安全配置的一部分而被關閉，請使用如下的陳述句啟用---*/
Exec sp_configure 'show advanced options ' , 1 --顯示高級配置資訊
GO
RECONFIGURE --重新配置
GO
exec sp_configure 'xp_cmdShell',1--打開xp_configure選項
GO
REconfigure --重新配置
/*---創建資料庫bankDB,要求保存在D:\bank---*/
Exec xp_cmdshell 'mkdir D:\bank', NO_OUTPUT --創建檔案夾D:\bank
--創建資料庫bankDB

用戶自定義存盤程序

無參存盤程序

無參存盤程序定義

CREATE PROC[ EDURE ] 存盤程序名
AS
SQL 陳述句
GO

使用存盤程序的語法：
EXECUTE 程序名
EXEC 程序名

如果執行存盤程序的陳述句是批處理陳述句中的第一個陳述句，則可以不指定
EXECUTE關鍵字，

例子如下：

 use MySchool
go
    create proc stuResult
    as
    
    ----查找本次Java OOP 考試的平均分以及沒有通過的人的資訊


    ---定義變數保存最近一次考試時間

declare @maxDate datetime 




select @maxDate = MAX(ExamDate) from Result
  where SubjectId =(select SubjectId from Subject 
                    where SubjectName = 'Java OOP')
 
 
---計算平均分
select AVG(StudentResult) from Result
	where ExamDate = @maxDate
	and SubjectId = (select SubjectId from Subject 
                    where SubjectName = 'Java OOP') 
--查詢未通過考試學生名單
select * from Student
		where StudentNo =(
	select StudentNo from Result
	where SubjectId =  (select SubjectId from Subject 
                    where SubjectName = 'Java OOP') 
    and ExamDate = @maxDate  
    and StudentResult < 80
    ) 
    go

呼叫無參存盤程序

Exec stuResult

帶引數存盤程序

創建帶引數的存盤程序語法

CREAT PROC[EDURE] 存盤程序名
@引數1，@引數2，…,@引數n
AS
SQL陳述句
GO

執行存盤程序語法

EXECUTE 程序名引數
EXEC 程序名引數

存盤程序引數：

輸入引數：向存盤程序傳入值
輸出引數：存盤程序輸出值，使用output關鍵字

執行存盤程序

EXEC 程序名 ’ 值 '，值

或

EXEC 存盤程序名引數1 = ’ ’ , 引數2 = ’ ’

兩種方式，一種是使用鍵值對的形式傳遞，一種是按照順序直接傳遞引數，

示例如下：

use MySchool
go

 
create proc proc_stuResultParam
	@subject varchar(10),
	@score int
    as
    
    ----查找本次Java OOP 考試的平均分以及沒有通過的人的資訊


    ---定義變數保存最近一次考試時間
declare @maxDate datetime 





select @maxDate = MAX(ExamDate) from Result
  where SubjectId =(select SubjectId from Subject 
                    where SubjectName = @subject)
 
 
---計算平均分
select AVG(StudentResult) from Result
	where ExamDate = @maxDate
	and SubjectId = (select SubjectId from Subject 
                    where SubjectName = @subject) 
--查詢未通過考試學生名單
select * from Student
		where StudentNo in(
	select StudentNo from Result
	where SubjectId =  (select SubjectId from Subject 
                    where SubjectName = @subject) 
    and ExamDate = @maxDate  
    and StudentResult < @score 
    ) 
    go  
    -- 呼叫有參存盤程序
    exec dbo.proc_stuResultParam 'Java OOP',94
    
    exec dbo.proc_stuResultParam @subject ='C# oop',@score =80

也可以在宣告形式引數的時候指明默認值默認值會被新引數覆寫，
在宣告引數時要把帶默認值的引數放在后面宣告，否則要在傳入引數時在引數前要加入default關鍵字，

輸出引數

使用時必須在屬性后加入output關鍵字來表示引數是輸出引數
在接收傳出的結果時要從新宣告一個引數用于接收

例子如下：

use MySchool
go

 
create proc proc_stuResultParam
	@subject varchar(10), --輸入引數
	@score int = 60,
	@count int output --輸出引數
    as
    
    ----查找本次Java OOP 考試的平均分以及沒有通過的人的資訊


    ---定義變數保存最近一次考試時間
declare @maxDate datetime 





select @maxDate = MAX(ExamDate) from Result
  where SubjectId =(select SubjectId from Subject 
                    where SubjectName = @subject)
 
 
---計算平均分
select AVG(StudentResult) from Result
	where ExamDate = @maxDate
	and SubjectId = (select SubjectId from Subject 
                    where SubjectName = @subject) 
--查詢未通過考試學生名單
select * from Student
		where StudentNo in(
	select StudentNo from Result
	where SubjectId =  (select SubjectId from Subject 
                    where SubjectName = @subject) 
    and ExamDate = @maxDate  
    and StudentResult < @score 
    ) 
--統計不及格的人數

select @count = count(StudentNo) from Result
	where SubjectId  =  (select SubjectId from Subject 
                    where SubjectName = @subject) 
    and ExamDate = @maxDate  
    and StudentResult < @score 
    go  
    declare @c int 
    -- 呼叫有參存盤程序
   --exec dbo.proc_stuResultParam 'Java OOP',80, @c output
   --select @c
    
   exec dbo.proc_stuResultParam @subject ='C# oop',@count = @c output

也可以向方法一樣用return ，不過需要在接收傳出來的資料之前，要從新宣告一個引數去接收return出來的引數，這樣接收的引數只是return傳出來的引數，
例子如下：

use MySchool
go

 
create proc proc_stuResultParam
	@subject varchar(10), --輸入引數
	@score int = 60,
	@count int output --輸出引數
    as
    
    ----查找本次Java OOP 考試的平均分以及沒有通過的人的資訊


    ---定義變數保存最近一次考試時間
declare @maxDate datetime 





select @maxDate = MAX(ExamDate) from Result
  where SubjectId =(select SubjectId from Subject 
                    where SubjectName = @subject)
 
 
---計算平均分
select AVG(StudentResult) from Result
	where ExamDate = @maxDate
	and SubjectId = (select SubjectId from Subject 
                    where SubjectName = @subject) 
--查詢未通過考試學生名單
select * from Student
		where StudentNo in(
	select StudentNo from Result
	where SubjectId =  (select SubjectId from Subject 
                    where SubjectName = @subject) 
    and ExamDate = @maxDate  
    and StudentResult < @score 
    ) 
--統計不及格的人數

select @count = count(StudentNo) from Result
	where SubjectId  =  (select SubjectId from Subject 
                    where SubjectName = @subject) 
    and ExamDate = @maxDate  
    and StudentResult < @score 
   --存盤程序回傳最高分
   declare @max int = 0
   select @max = max(StudentResult) from Result
	where SubjectId  =  (select SubjectId from Subject 
                    where SubjectName = @subject) 
    and ExamDate = @maxDate  
    return @max
    go  
    declare @c int
   
    -- 呼叫有參存盤程序
   --exec dbo.proc_stuResultParam 'Java OOP',80, @c output
   --select @c
    declare @MaxReturnParam int --通過接收return方式回傳的值
   exec @MaxReturnParam = dbo.proc_stuResultParam @subject ='C# oop',@count = @c output
   select @MaxReturnParam

自定義錯誤資訊

使用RAISERROR來自定義錯誤資訊：
RAISERROR（msg_id| msg_str,severity, state WITH option[,…n]）
msg_id : 在sysmessages系統表中指定用戶定義錯誤資訊
msg_str: 用戶定義的特定資訊，最長255個字符
severity: 定義嚴重性級別，用戶可以使用級別為0~18級

0~10：不會跳到catch
11-19：進入catch
20-：終止資料庫連接

state：表示錯誤的狀態，1到127之間的值
option：指示是否將錯誤記錄到服務器錯誤日中
示例如下：`

declare @age int 
set @age =1000



if(@age>=0 and @age<=100)
print @age
else
--文本，錯誤級別（0~18之間），狀態（1~127）
raiserror('年齡應輸入0~100之間',16,1)

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/226161.html

標籤：其他

上一篇：Concurrent 包結構介紹

下一篇：歷代iPhone主要引數對比，更新于2020年10月

SQLSEVER中的存盤程序

存盤程序

系統存盤程序

拓展存盤程序

xp_cmdshell

用戶自定義存盤程序

無參存盤程序

無參存盤程序定義

帶引數存盤程序

創建帶引數的存盤程序語法

執行存盤程序

輸出引數

自定義錯誤資訊