sqlserver資料庫預存程序

標籤：exists   span   管理   objects   sql語句   --   sel   自訂   定義   

預存程序的分類
(1)系統預存程序
系統預存程序的名稱一般以“sp_”開頭
由SQLServer建立、管理和使用
存放在Resource資料庫中
類似java語言類庫中的方法
(2)擴充預存程序
擴充預存程序的名稱通常以“xp_”開頭
以DLL形式單獨存在
(3)使用者自訂預存程序
由使用者在自己的資料庫中建立的預存程序
類似java語言中使用者自訂的方法

//調用預存程序文法：
EXECUTE 過程名 [參數]
或
EXEC 過程名 [參數]

//定義預存程序的文法：
CREATE PROC[EDURE] 預存程序名
@參數1 資料類型 = 預設值 OUTPUT,
… … ,
@參數n 資料類型 = 預設值 OUTPUT
AS
SQL語句
GO

樣本1：
IF EXISTS (SELECT * FROM sysobjects WHERE name = ‘usp_grade_subject‘ )
DROP PROCEDURE usp_grade_subject
GO
/*---建立預存程序----*/
CREATE PROCEDURE usp_grade_subject
AS
SELECT GradeName,SubjectName,ClassHour FROM Grade
INNER JOIN Subject ON Grade.GradeId=Subject.GradeId
ORDER BY Subject.GradeId,SubjectNo
GO
/*---調用執行預存程序---*/
EXEC usp_grade_subject

 

 

樣本2 帶輸入參數的預存程序：
CREATE PROCEDURE usp_unpass
@subName varchar(50),
@score int
AS
DECLARE @date datetime --最近考試時間
DECLARE @subjectNo int --課程編號
SELECT @subjectNo=SubjectNo FROM Subject Where SubjectName = @subName
… …
SELECT StudentName,Student.StudentNo,StudentResult FROM Student
INNER JOIN Result ON Student.StudentNo = Result.StudentNo
WHERE StudentResult < @score AND ExamDate = @date AND [email protected]
GO

 

樣本3 建立帶參數預設值的預存程序：

CREATE PROCEDURE usp_unpass
@subName varchar(50),
@score int = 60
AS
… …
GO
調用帶參數預設值的預存程序：
EXEC usp_unpass ‘C# OOP‘--使用default代表預設值

 

樣本4 建立帶輸出參數的預存程序：

CREATE PROCEDURE usp_query_num
@UnPassNum INT OUTPUT,
@TotalNum INT OUTPUT,
@SubjectName NCHAR(10),
@Pass INT = 60 --推薦將預設參數放在最後
AS
DECLARE @date datetime
DECLARE @subjectNo int
… …
SELECT @UnPassNum = COUNT(*) FROM Result WHERE [email protected] AND [email protected] AND StudentResult<@Pass
SELECT @TotalNum = COUNT(*) FROM Result WHERE [email protected] AND [email protected]
GO
調用帶輸出參數的預存程序：
DECLARE @UnPassNum int
DECLARE @TotalNum int
EXEC usp_query_num @UnPassNum OUTPUT, @TotalNum OUTPUT,‘Java Logic‘
--調用時必須帶OUTPUT關鍵字 ，返回結果將分別存放在變數@unPassNum和@TotalNum中

sqlserver資料庫預存程序