SQL Server——存储过程

我想从下面几个方面大概的讲述下存储过程,可能有些知识点是你没有注意的,也可能有些知识点我不知道,欢迎大家指点指点。如有不足,欢迎指教!

存储过程概念

存储过程优点

存储过程的接口

存储过程的解析、编译过程

存储过程安全性

查看存储过程

加密、解密存储过程

 

存储过程概念

 

存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(带参存储过程)来执行它。

存储过程优点

 

开发过程中使用存储过程的优点, 概括起来大体有下面一些:

1: 速度快、性能好。存储过程是一组已经编译过的SQL脚本,它已经进过解析、编译、优化器优化。调用存储过程可以 重复使用它的缓存执行计划。这样节省了解析、分析等所      需消耗的CPU资源。时间

2: 存储过程存储在数据库服务器,可以减少网络通信,减少网络阻塞。 调用存储过程只需存储过程名字和参数,从而避免了    把长串的SQL语句传送到SQL 服务器,可以大大减      轻网络负担。

3: 业务逻辑封装,可以把相当一部分业务逻辑封装到存储过程中,当业务逻辑变更的时候,只要接口不变,只需修改存储过程内部逻辑就OK了,避免了业务逻辑放在代码层,业务      逻辑变动改动大的痛苦。

4: 安全性  参数化的存储过程可以减少SQL Injiection攻击,而且可以通过检验参数、授予对象执行权限提高了安全性。

存储过程按类型分为:系统存储过程、扩展存储过程、用户存储过程(包括CLR存储过程)、临时存储过程(其中又分为全局临时存储过程、局部临时存储过程)。系统存储过程一般以SP做前缀开头。扩展存储过程一般以XP为前缀,自定义存储过程命名,我习惯以USP为前缀。

存储过程的接口

存储过程的参数可以是输入参数、输出参数。先看看下面两个存储过程,第一个存储过程:有两个输入参数@EmployeeID、@EmployeeName,其中@EmployeeID 的默认值是 -1, @EmployeeName 的默认值是 NULL。

Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->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

 

 

代码Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->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

 

 

你可以这样去调用、执行存储过程 ,也可以不用添加@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架构里面查找。

存储过程的解析、编译过程

创建存储过程时,首先分析检查语法的正确性。如果在过程定义中遇到语法错误,将会返回错误,创建存储过程失败。如果语法正确,存储过程的文本将会存储在SYS.SQL_MODULES 目录视图中。

SELECT * FROM SYS.SQL_MODULES WHERE object_id =OBJECT_ID(N'dbo.USP_GetEmployeById')

 

 

下面我们来看看一个有趣的小列子,你也可以试试。创建一个存储过程USP_GetTableTest,它里面引用了表Test,表Test根本不存在。

 

CREATE PROCEDURE USP_GetTableTest
AS
BEGIN
    SELECT * FROM TEST;
END

 

创建该存储过程时,不会出错,但是执行存储过程时,会爆出下面这样的错误


是因为在存储过程创建时,它先做语法检查,如果通过了语法检查,它会尝试解析它包含的对象名,如果存在也会解析该对象引用的对象是否存在。如果引用的对象
名不存在,解析会在存储过程首次执行时触发。即在首次执行存储过程时,查询处理器从 sys.sql_modules
目录视图中读取该存储过程的文本,并检查该过程所使用的对象名称是否存在。这一过程称为延迟名称解析,因为存储过程引用的表对象不需要在创建该存储过程时
就存在,而只需在执行该存储过程时存在。

注意:  

只有当引用的表对象不存在时才能使用延迟名称解析。所有其他对象在创建所存储的过程时必须存在。例如,引用所存储的过程中的一个现有表时,不能列出该表不存在的列。

 

看看下面的列子就知道了,我们先创建表TEST(col1); 然后在存储过程 USP_GetTableTest中查询它不存在的列col2

 

代码Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->CREATE TABLE TEST (col1 INT);
GO

IF OBJECT_ID(N'USP_GetTableTest') IS NOT NULL
BEGIN
    DROP PROC USP_GetTableTest;
END
GO

CREATE PROCEDURE USP_GetTableTest
AS
BEGIN
    SELECT col2 FROM TEST;
END

 

 

创建存储过程时,它会报如下错误提示。

 


解析阶段,Microsoft SQL Server 2005
还执行其他验证活动(例如,检查列数据类型与变量的兼容性)。如果执行存储过程时存储过程所引用的对象丢失,则存储过程在到达引用丢失对象的语句时将停止
执行。在这种情况下,或在解析阶段发现其他错误时,将返回错误信息。

如果执行过程时成功通过解析阶段,则 Microsoft SQL Server 查询优化器将分析存储过程中的 Transact-SQL 语句并创建一个执行计划。执行计划描述执行存储过程的最快方法,所依据的信息包括: 

表中的数据量。

表的索引的存在及特征,以及数据在索引列中的分布。

WHERE 子句条件所使用的比较运算符和比较值。

是否存在联接以及 UNION、GROUP BY 和 ORDER BY 关键字。

查询优化器在分析完存储过程中的这些因素后,将执行计划置于内存中。分析存储过程和创建执行计划的过程称为编译。优化的内存中的执行计划将用来执行该查询。执行计划将驻留在内存中,直到重新启动 SQL Server 或其他对象需要存储空间时为止。

如果随后执行了存储过程,而现有执行计划仍留在内存中,则 SQL Server 将重用现有执行计划。如果执行计划不再位于内存中,则创建新的执行计划。 

 

 存储过程安全性

在sa账号下面,执行了下面这条语句

DENY INSERT ON dbo.Employee TO Kerry;
GRANT EXECUTE ON dbo.USP_AddEmploye TO Kerry;

然后我们用Kerry这个账号登陆,往表Employee离插入一条数据

 

而你调用存储过程,往表Employee离插入一条数据,则如图所示

 

 

这种安全模式能让你很灵活的控制用户允许进行的活动.

 

查看存储过程

查看存储过程的方式很多,你可以如图所示,你可以在MSMS工具中选择“修改存储过程”或是“编写存储过程为”来查看存储过程,也可以通过查询视图,或是系统存储过程来查看你想要看的存储过程

代码Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->SP_HELP 'dbo.USP_GetEmployeById'  --查看存储过程的基本信息:例如参数等

SP_HELPTEXT 'dbo.USP_GetEmployeById'  --查看具体的存储过程

SELECT * FROM SYS.SQL_MODULES WHERE object_id =OBJECT_ID(N'dbo.USP_GetEmployeById')  --查看具体的存储过程

 加密、解密存储过程


储过程加密一般是为了安全需要、或是保护源代码需要而加密存储过程,它一般通过关键字ENCRYPTION 来实现。SQL Server 将
CREATE PROCEDURE 语句的原始文本转换为模糊格式。模糊代码的输出在 SQL Server 2005
的任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。但是,可通过 DAC
端口访问系统表的特权用户或直接访问数据库文件的特权用户可使用此文本。此外,能够向服务器进程附加调试器的用户可在运行时从内存中检索已解密的过程。

我们先把刚才那个存储过程加密吧

代码Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->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
WITH ENCRYPTION
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

 

执行以后,你可以看见加密过后的存储过程,它的图标多了个小锁,而且你再也不能通过上面查看存储过程的方式来查看存储过程了。使用MSMS查看则会弹出下面错误提示

 

而用SP_HELPTEXT 则提示:对象 'dbo.USP_GetEmployeById' 的文本已加密。

SQLServer2005
里使用WITH
ENCRYPTION选项创建的存储过程仍然和sqlserver2000里一样,都是使用XOR进行了的加密。和2000不一样的是,在2005的系统
表syscomments里已经查不到加密过的密文了。要查密文必须使用DAC(专用管理员连接)连接到数据库后,如果你接手了数据库管理,里面有些存储
过程加密了,你又没有创建加密存储过程的那些脚本,你是否干着急啊。别急,呵呵,网上有位叫王成辉翻译整理了国外大牛写的解密加密存储过程的一个存储过程
usp_decrypt。呵呵,有兴趣的可以找来看看,我在SQL SERVER 05下面实验过了,确实能解密已经被加密的存储过程。

时间: 2024-09-12 03:08:22

SQL Server——存储过程的相关文章

SQL Server 存储过程的分页方案比拼

server|存储过程|分页 SQL Server 存储过程的分页,这个问题已经讨论过几年了,很多朋友在问我,所以在此发表一下我的观点建立表: CREATE TABLE [TestTable] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL , [LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_

SQL Server 存储过程的分页

server|存储过程|分页 SQL Server 存储过程的分页,这个问题已经讨论过几年了,很多朋友在问我,所以在此发表一下我的观点建立表: CREATE TABLE [TestTable] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL , [LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_

SQL SERVER存储过程语法详解

SQL SERVER存储过程语法: Create PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]

JSP中调用SQL Server存储过程实例讲解

下面介绍一个JSP调用SQL Server存储过程的实例: 创建表: CREATE TABLE [BookUser] (     [UserID] [int] IDENTITY (1, 1) NOT NULL ,     [UserName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,     [Title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,     [Guid]

SQL Server 存储过程名称查询

  SQL Server存储过程名称查询 Select OBJECT_NAME(id) FROM syscomments Where [text] LIKE '%table_name%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id) 所有使用表名

sql-java 执行SQL Server 存储过程

问题描述 java 执行SQL Server 存储过程 存储过程 有 2个输入参数 为 int nvarchar 类型 1个输出参数 nvarchar类型 -- String orderNo=""""; // 返回值 con=BaseDao.getConn(); //获取连接 CallableStatement cstmt=null; try { cstmt=con.prepareCall(""{?=call proc_createOrder(?

sqlserver 存储过程-sql server 存储过程 树节点 每次加载一个节点

问题描述 sql server 存储过程 树节点 每次加载一个节点 有一张数据表A,表字段:treeid,treeparentid,treename三个字段,现在要用存储过程建一个树目录结构的菜单,初始化时,只加载根节点和所有的一级子节点,一级子节点下面的子节点初始化时不加载,初始化完成之后,我们点击一级子节点菜单,它才会加载选中的节点菜单下面的子节点,每次加载一级节点,无限次加载.非常急,在线等,本人第一次在CSDN上提问,不知道要不要分,好像有几十分,都给,写存储过程的时候,请把存储过程的几

c#-C#程序调用SQL SERVER存储过程的output参数始终为空,求助?

问题描述 C#程序调用SQL SERVER存储过程的output参数始终为空,求助? C#中的如下两条语句调用存储过程output参数始终为空(dataGridView1能正确调用到想要的select数据,但是存储过程的两个输出参数值不能传递给两个texBox.Text),不知为何?textBox1.Text = parameters[2].Value.ToString();textBox2.Text = nowpage + ""/"" + parameters[3

存储过程 带参数-SQL SERVER 存储过程问题

问题描述 SQL SERVER 存储过程问题 目的: 创建一个存储过程,入口参数是 表名称 ,功能是 若该表不存在就创建之 . 我的做法: CREATE PROCEDURE [dbo].[CCT] @TblName varchar(32) AS begin Declare @vproc As varchar(8000) set @vproc=' if object_id(['+@TblName+']) is null begin Create Table '+@TblName+' not nul

sybase跨数据库调用sql server存储过程

问题描述 sybase跨数据库调用sql server存储过程 sql server能调用sybase存储过程,sybase如何调用sql server的存储过程呢? 解决方案 Sql Server数据库的存储过程sql server 数据库存储过程SQL Server 2000数据库存储过程的调用(VB) 解决方案二: http://blog.sina.com.cn/s/blog_b5b346bb0102vbg8.html