数据库存储过程 — Sql Server

Mysql、Oracle等主流关系型数据库基本都支持存储过程，这里使用Sql Server为例进行说明。

存储过程的概念：

Sql Server存储过程

SQL Server 中的存储过程是由一个或多个 Transact-SQL 语句或对 Microsoft .NET Framework 公共语言运行时 (CLR) 方法的引用构成的一个组。 简单的说，非常类似与java中的方法，实质就是部署在数据库端的一组定义代码以及SQL。

 

存储过程的作用（优点）：

1.代码的重复使用

    任何重复的数据库操作的代码都非常适合于在过程中进行封装。 这消除了不必要地重复编写相同的代码、降低了代码不一致性，并且允许拥有所需权限的任何用户或应用程序访问和执行代码。

2.更强的安全性

    多个用户和客户端程序可以通过过程对基础数据库对象执行操作，即使用户和程序对这些基础对象没有直接权限。 过程控制执行哪些进程和活动，并且保护基础数据库对象。 这消除在了单独的对象级别授予权限的要求，并且简化了安全层。

3.更容易维护

    在客户端应用程序调用过程并且将数据库操作保持在数据层中时，对于基础数据库中的任何更改，只有过程是必须更新的。 应用程序层保持独立，并且不必知道对数据库布局、关系或进程的任何更改的情况。

4.改进的性能

    默认情况下，在首次执行过程时将编译过程，并且创建一个执行计划，供以后的执行重复使用。 因为查询处理器不必创建新计划，所以，它通常用更少的时间来处理过程。

5.减少了服务器/客户端网络流量

    过程中的命令作为代码的单个批处理执行。 这可以显著减少服务器和客户端之间的网络流量，因为只有对执行过程的调用才会跨网络发送。 如果没有过程提供的代码封装，每个单独的代码行都不得不跨网络发送。

可以类比成java中的基础类库。总之就是高性能、安全、复用、易维护。

存储过程的类型：

1.用户定义

    用户定义的过程可在用户定义的数据库中创建，或者在除了 Resource 数据库之外的所有系统数据库中创建。

2.临时

    临时过程是用户定义过程的一种形式。 临时过程与永久过程相似，只是临时过程存储于 tempdb 中。

3.系统

    系统过程是 SQL Server 随附的。 它们物理上存储在内部隐藏的 Resource 数据库中，但逻辑上出现在每个系统定义数据库和用户定义数据库的 sys 架构中。

4.扩展的用户定义过程（Deprecated，将被CLR存储过程替代，以后再专门开贴）

    通过扩展的过程，可以使用 Java 之类的编程语言创建外部例程。 这些过程是 SQL Server 实例可以动态加载和运行的 DLL。

创建表(Sql Server 2005)

1. 打开Microsoft SQL Server Management Studio
2. 建表表Sql语句：

CREATE TABLE [user](userId int,userName NVARCHAR[20],password NVARCHAR[20]);

　　3.  再随便填入一些数据。

　　4.  打开查询分析器，创建存储过程，如图。

 

 

使用存储过程：

先来看一个简单的存储过程定义（我个人认为先看实例，再看语法是最好的学习方式，因为一开始上来就看语法，会让人摸不着北）：

1.不带参数

存储过程Name = select_del，获取user表中的全部记录。

CREATE PROCEDURE proc_select_user
AS
    BEGIN
        SELECT * FROM [user]
    END
GO

执行：

EXECUTE proc_select_user;

2.带输入参数

存储过程Name = proc_find_by_id_range，获取user表中Id在给定范围内的所有记录。

CREATE PROCEDURE proc_find_by_id_range(@startId INT, @endId INT)
AS
    BEGIN
        SELECT * FROM [user] WHERE userId BETWEEN @startId AND @endId
    END
GO 

执行：

EXECUTE proc_find_by_id_range @startId = 10000,@endId = 10100;

3.带输入输出参数

存储过程Name = proc_find_userName_by_id,通过id获取userName。

CREATE PROCEDURE proc_find_userName_by_id
   @userId INT,
   @userName NVARCHAR(20) OUTPUT
AS
    BEGIN
        SELECT @userName = userName
        FROM [user]
        WHERE userId = @userId
    END
GO

执行：

DECLARE @userName NVARCHAR(20);
EXECUTE proc_find_userName_by_id 10004,@userName OUTPUT;（这边如果用@变量 = OUTPUT会报错，所以换一种写法）
SELECT @userName as 'result';

4.带返回状态

存储过程Name = proc_check_password_isnull,查询某Id的用户是否设了密码。

CREATE PROCEDURE proc_check_password_isnull
@userId int
AS
BEGIN
IF(select password FROM [user]
     WHERE userId = @userId) = null
     RETURN 0
ELSE
     RETURN 1
END
GO

执行：

DECLARE @status int;
EXECUTE @status = proc_check_password_isnull @userId = 10000;
SELECT @status as 'result';

通过上面几个例子基本上也能明白存储过程的用法了，下面再给出存储过程语法的定义：

--SQL Server Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]

    [ { @parameter [ type_schema_name. ] data_type }
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
    ] [ ,...n ]

[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]

AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

[;]

<procedure_option> ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

其中大部分关键字都在上面的四个例子中出现过，也比较好理解，下面再解释一些没展示过的关键字：

[ WITH <procedure_option> [ ,...n ] ]
<procedure_option> ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

这个是可以为存储过程定义一些特性，ENCRYPTION是可以为存储过程加密，RECOMPILE是不缓存，每次调用存储过程需要重新编译，EXECUTE AS Clause是否作为语句块执行。

[ FOR REPLICATION ]

指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。

ELSE

其实每个数据库都有很多系统定义的存储过程，如user表中的存储过程：

如果有兴趣可以去研究下系统的存储过程，既可以熟练存储过程的使用，也能对数据库里面的机制理解的更深。

其实存储过程中还有很多用法没有介绍，如通配符、CLR、以及游标。以后如有时间再深入研究。有不对的地方，请大家指正：）