sqlserver数据库存储过程

存储过程的分类
(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 SubjectNo=@subjectNo
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 ExamDate=@date AND subjectNo=@subjectNo AND StudentResult<@Pass
SELECT @TotalNum = COUNT(*) FROM Result WHERE ExamDate=@date AND subjectNo=@subjectNo
GO
调用带输出参数的存储过程 ：
DECLARE @UnPassNum int
DECLARE @TotalNum int
EXEC usp_query_num @UnPassNum OUTPUT, @TotalNum OUTPUT,'Java Logic'
--调用时必须带OUTPUT关键字 ，返回结果将分别存放在变量@unPassNum和@TotalNum中