SQL SERVER存储过程

目录

存储过程概念

存储过程的优点

存储过程的缺点

存储过程类型

创建自定义存储过程

 调用存储过程

修改存储过程

删除存储过程

启用|停用存储过程

---

存储过程概念

        存储过程实际上是一组预编译的TSQL语句，作为一种数据库对象被保存起来。它可以接受一个多输入参数，也能返回一个或多个输出参数亦或返回结果集。

存储过程的优点

1. 提高了处理复杂任务的能力。
2. 增强了代码的复用率和共享性。
3. 减少网络中的数据流量。
4. 可作为安全机制使用，数据库用户可以得到权限来执行存储过程，而不必授权用户直接访问数据库对象的权限。

存储过程的缺点

1. 如果需要对存储过程的输入参数进行更改，或更改返回的数据，则需要更改过程体中的代码，过程比较烦琐。
2. 可移植性差。
3. 很多存储过程不支持面向对象设计。
4. 代码可读性差，因此一般较难维护。

存储过程类型

1. 系统存储过程

        SQL SERVER系统预定义的存储过程，可作为命令执行各种操作。这里不做细述。

2. 扩展存储过程

        本篇博客不做涉及。

3.用户自定义存储过程

        是用户自己创建的一组T-SQL语句集合，可以接受和返回用户提供的参数，完成某些特定的功能。

创建自定义存储过程

CREATE PROCEDURE|PORC <存储过程名>[;n]
    [@形参名 数据类型][,...n]
    [@变参名 数据类型][OUTPUT][,...n]
    [WITH RECOMPILE|ENCRYPTION]
    [FOR REPLICATION]
AS
    <T-SQL语句组合>

- ;n：是可选整数，用于将相同名称的过程进行组合，使得它们可以使用一句DROP PROCEDURE语句删除。

- OUTPUT：表示该参数为返回参数

- RECOMPILE：指明存储过程不驻留在内存，每次执行时需要重新编译。

- ENCRYPTION：用于对存储创建存储过程的系统表syscomments进行加密。使其它用户无法查询到存储过程的创建语句。

- FOR REPLICATION：表示存储过程只能在复制过程中执行，和ENCRYPTION不能同时使用。

1. 不带参数的存储过程

CREATE PROCEDURE S3_Table
    AS
        SELECT *
        FROM SC
        WHERE SNO='S3'

2. 带参数的存储过程

3. 带输出参数的存储过程

注意：创建存储过程的SELECT子查询语句中赋值语句和目标列不能同时应用，譬如不能有SELECT SNAME,@S_AVG=AVG(GRADE)这样的语句。

存储过程可以嵌套即在一个存储过程中执行另一个存储过程。

 调用存储过程

EXEC|EXECUTE
    [<@状态变量>=]
    存储过程名
    [[<@过程参数>]=<参数值>|<@变量名> [OUTPUT]|DEFAULT]]
    [,...n]
    [WITH RECOMPILE]

- @状态变量：用于存储存储过程的返回状态，当返回值为0时，则代表存储过程正确执行，当返回值为1时则代表存储过程执行失败。

- WITH RECOMPILE：强制在执行存储过程时对其进行编译，并将其存储起来以后执行不再编译。

- DEFAULT：表示不提供实参，而是使用对应的默认值。

        可以通过sp_helptext系统存储过程查看创建存储过程的内容。

EXEC sp_helptext proc_name

修改存储过程

ALTER PROCEDURE|PORC <存储过程名>[;n]
    [@形参名 数据类型][,...n]
    [@变参名 数据类型][OUTPUT][,...n]
    [FOR REPLICATION]
AS
    <T-SQL语句组合>

删除存储过程

DROP PROCEDURE Procedure_name[,...n]