SQL Server教程:SQL Server—存储过程详解
来源:学生作业帮助网 编辑:作业帮 时间:2024/05/10 07:22:03 SQLServer
SQL Server教程:SQL Server—存储过程详解SQLServer
【无忧考网-SQL Server教程:SQL Server—存储过程详解】:
SET @Result = '';
EXEC dbo.USP_AddEmploye
@EmployeeName ='张飞',
@Sex =1 ,
@Department = '市场部',
@Success = @Result OUTPUT
SELECT @Result;
GO
SQLServer
我想从下面几个方面大概的讲述下存储过程,可能有些知识点是你没有注意的,也可能有些知识点我不知道,欢迎大家指点指点。如有不足,欢迎指教!
存储过程概念
存储过程优点
存储过程的接口
存储过程的解析、编译过程
存储过程安全性
查看存储过程
加密、解密存储过程
存储过程概念
存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(带参存储过程)来执行它。
存储过程优点
开发过程中使用存储过程的优点, 概括起来大体有下面一些:
1: 速度快、性能好。存储过程是一组已经编译过的SQL脚本,它已经进过解析、编译、优化器优化。调用存储过程可以 重复使用它的缓存执行计划。这样节省了解析、分析等所 需消耗的CPU资源。时间
2: 存储过程存储在数据库服务器,可以减少网络通信,减少网络阻塞。 调用存储过程只需存储过程名字和参数,从而避免了 把长串的SQL语句传送到SQL 服务器,可以大大减 轻网络负担。
3: 业务逻辑封装,可以把相当一部分业务逻辑封装到存储过程中,当业务逻辑变更的时候,只要接口不变,只需修改存储过程内部逻辑就OK了,避免了业务逻辑放在代码层,业务 逻辑变动改动大的痛苦。
4: 安全性 参数化的存储过程可以减少SQL Injiection攻击,而且可以通过检验参数、授予对象执行权限提高了安全性。
存储过程按类型分为:系统存储过程、扩展存储过程、用户存储过程(包括CLR存储过程)、临时存储过程(其中又分为全局临时存储过程、局部临时存储过程)。系统存储过程一般以SP做前缀开头。扩展存储过程一般以XP为前缀,自定义存储过程命名,我习惯以USP为前缀。
存储过程的接口
存储过程的参数可以是输入参数、输出参数。先看看下面两个存储过程,第一个存储过程:有两个输入参数@EmployeeID、@EmployeeName,其中@EmployeeID 的默认值是 -1, @EmployeeName 的默认值是 NULL。
USE MyAssistant;
GO
IF OBJECT_ID(N'dbo.USP_GetEmployeById') IS NOT NULL
BEGIN
DROP PROC dbo.USP_GetEmployeById;
END
GO
--====================================================================================================
-- Function : 按员工号获取员工信息
-- Author : Kerry
-- Create Date : 2010-08-10
-- Description :
------------------------------------------------------------------------------------------------------
-- 2010-08-13 : 修改.......增加.....
-- 2010-08-14 : 修改.......增加.....
--====================================================================================================
CREATE PROCEDURE dbo.USP_GetEmployeById
@EmployeeID INT = -1,
@EmployeeName NVARCHAR(30) = NULL
AS
SET NOCOUNT ON;
BEGIN
IF (@EmployeeID = -1 AND @EmployeeName IS NULL)
BEGIN
PRINT '请输入员工ID号或是用户名字';
END
IF @EmployeeID = -1
SELECT * FROM dbo.Employee WHERE EmployeeName = @EmployeeName;
ELSE
SELECT * FROM dbo.Employee WHERE EmployeeID = @EmployeeID;
END
GO
GO
IF OBJECT_ID(N'dbo.USP_GetEmployeById') IS NOT NULL
BEGIN
DROP PROC dbo.USP_GetEmployeById;
END
GO
--====================================================================================================
-- Function : 按员工号获取员工信息
-- Author : Kerry
-- Create Date : 2010-08-10
-- Description :
------------------------------------------------------------------------------------------------------
-- 2010-08-13 : 修改.......增加.....
-- 2010-08-14 : 修改.......增加.....
--====================================================================================================
CREATE PROCEDURE dbo.USP_GetEmployeById
@EmployeeID INT = -1,
@EmployeeName NVARCHAR(30) = NULL
AS
SET NOCOUNT ON;
BEGIN
IF (@EmployeeID = -1 AND @EmployeeName IS NULL)
BEGIN
PRINT '请输入员工ID号或是用户名字';
END
IF @EmployeeID = -1
SELECT * FROM dbo.Employee WHERE EmployeeName = @EmployeeName;
ELSE
SELECT * FROM dbo.Employee WHERE EmployeeID = @EmployeeID;
END
GO
代码
USE MyAssistant;
GO
IF OBJECT_ID(N'dbo.USP_AddEmploye') IS NOT NULL
BEGIN
DROP PROC dbo.USP_AddEmploye;
END
GO
--==========================================================================================================
-- Function : 新增一条员工记录。
-- Author : Kerry
-- Create Date : 2010-08-10
-- Description :
-------------------------------------------------------------------------------------------------------------
-- 2010-08-13 : 修改.......增加.....
-- 2010-08-14 : 修改.......增加.....
--==========================================================================================================
CREATE PROCEDURE dbo.USP_AddEmploye
@EmployeeName NVARCHAR(30),
@Sex BIT = 1,
@Department NVARCHAR(20),
@Success NVARCHAR(4) OUTPUT
AS
SET NOCOUNT ON;
BEGIN TRY
IF (@EmployeeName IS NULL OR LEN(@EmployeeName) =0)
BEGIN
PRINT ('员工姓名不能为空');
SET @Success ='失败';
RETURN;
END
INSERT INTO Employee
VALUES(@EmployeeName, @Sex, @Department)
IF @@error = 0
SET @Success ='成功';
END TRY
BEGIN CATCH
SET @Success ='失败';
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSevertiy
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_MESSAGE() AS ErrorMessage
END CATCH
GO
GO
IF OBJECT_ID(N'dbo.USP_AddEmploye') IS NOT NULL
BEGIN
DROP PROC dbo.USP_AddEmploye;
END
GO
--==========================================================================================================
-- Function : 新增一条员工记录。
-- Author : Kerry
-- Create Date : 2010-08-10
-- Description :
-------------------------------------------------------------------------------------------------------------
-- 2010-08-13 : 修改.......增加.....
-- 2010-08-14 : 修改.......增加.....
--==========================================================================================================
CREATE PROCEDURE dbo.USP_AddEmploye
@EmployeeName NVARCHAR(30),
@Sex BIT = 1,
@Department NVARCHAR(20),
@Success NVARCHAR(4) OUTPUT
AS
SET NOCOUNT ON;
BEGIN TRY
IF (@EmployeeName IS NULL OR LEN(@EmployeeName) =0)
BEGIN
PRINT ('员工姓名不能为空');
SET @Success ='失败';
RETURN;
END
INSERT INTO Employee
VALUES(@EmployeeName, @Sex, @Department)
IF @@error = 0
SET @Success ='成功';
END TRY
BEGIN CATCH
SET @Success ='失败';
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSevertiy
,ERROR_STATE() AS ErrorState
,ERROR_LINE() AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_MESSAGE() AS ErrorMessage
END CATCH
GO
你可以这样去调用、执行存储过程 ,也可以不用添加@EmployeeName这样的参数。
DECLARE @Result NVARCHAR(4);
SET @Result = '';
EXEC dbo.USP_AddEmploye
@EmployeeName ='张飞',
@Sex =1 ,
@Department = '市场部',
@Success = @Result OUTPUT
SELECT @Result;
GO
一般在执行存储过程是,最好加上架构名称,例如:dbo.USP_AddEmploye 这样可以可以减少不必要的系统开销,提高性能。 因为如果在存储过程名称前面没有加上架构名称、SQL SERVER 首先会从当前数据库sys schema开始查找,如果没有找到,则会去其它schema查找,最后在dbo架构里面查找。
SQLServer