SQL存储过程示例

以SQL Server为例来讲述存储过程。

什么是存储过程

存储过程就是数据库为了实现特定任务而将一些需要多次调用的固定操作语句编写成程序段，这些程序段存储在服务器上，由数据库服务器通过子程序来调用。

存储过程的优点

1、存储过程加快系统运行速度，存储过程只是在创建时编译，以后每次执行都不需要重新编译。

2、存储过程可以封装复杂的数据库操作，简化操作流程，例如对多个表的更新删除等。

3、可实现模块化的程序设计，存储过程可以多次调用，提供统一的数据库访问接口，改进应用程序的可维护性。

4、存储过程可增强代码的安全性，sqlserver中可以设定用户对指定存储过程的执行权限，使用户不能直接操作存储过程中的对象。

5、存储过程可以降低网络流量，存储过程代码直接存储在数据库中，在客户端与服务器的通信过程中，不会产生大量的t-sql代码流量。

存储过程的缺点

1、数据库移植不方便，存储过程依赖于数据库管理系统，例如SQLServer中存储过程封装的操作代码不能直接移植到其他的数据库管理系统中。

2、不支持面向对象的设计，无法采用面向对象的方式将逻辑业务进行封装，甚至形成通用的可支持服务的业务逻辑框架。

3、代码可读性差，不易维护

4、不支持集群

存储过程的分类

1、系统存储过程

系统存储过程位于数据库服务器中，并且以sp_开头，在调用时不必在存储过程前加数据库限定名，有些存储过程只能由系统管理员来使用，有些过程通过授权也可以被其他用户使用。

2、自定义存储过程

这种过程也是最常用的过程，用户使用t-sql语句编写、为了实现某一特定业务需求，在用户数据库中编写的T-SQL语句集合。存储过程前加#表示创建临时的存储过程，存储过程前加##表示创建了全局的临时存储过程。

用户定义存储过程可以分为两类

1、T-SQL语句存储过程。是T-SQL语句的集合，可以接受和返回用户提供的参数。

2、CLR存储过程。是指的应用.net Framework公共语言方法的存储过程。，可以接受和返回用户提供的参数，他们在.net Framework中作为类的静态方法实现。

3、扩展存储过程

扩展存储过程是以SQLSERVER环境外执行动态链接库（DLL文件）来实现的。扩展存储过程可以使用SQLServer2012扩展存储过程API完成编程，扩展存储过程以前缀xp_来标识，对于用户来说，扩展存储过程和普通存储过程一样，可以使用相同的方式来执行。

创建存储过程

创建一张表

CREATE TABLE [dbo].[Stu](
	[ID] [int] NOT NULL,
	[Name] [varchar](50) NULL,
 CONSTRAINT [PK_Stu] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

创建不带参数的存储过程

USE TEST;
GO
CREATE PROCEDURE GetStu
AS
SELECT ID,NAME FROM Stu;
GO

存储过程最大容量是128M

 

创建带参数的存储过程

 

USE TEST;
GO
CREATE PROCEDURE AddStu
@Id int,
@NAME varchar(50)
AS
INSERT INTO Stu([ID],[Name]) VALUES(@Id,@NAME)
GO

存储过程最大参数数目是2100

 

创建带返回值的存储过程

USE TEST;
GO
CREATE PROCEDURE GetStuName
@Id int,
@Name varchar(50) output
AS
SELECT @Name=Name FROM Stu WHERE ID=@Id;
GO

调用（执行）存储过程

USE TEST;
GO
--执行插入存储过程带参数的
EXEC [dbo].[AddStu] 1,'张三'
--执行查询 不带参
EXEC [dbo].[GetStu]
--执行带返回值参数的存储过程
declare @name varchar(50)
EXEC GetStuName 1,@Name output
print @Name

修改存储过程

USE TEST;
GO
ALTER PROCEDURE GetStuName
@Id int,
@Name varchar(50) output
AS
SELECT @Name=Name FROM Stu WHERE ID>@Id;
GO

关键字ALTER PROCEDURE +过程名

删除存储过程

USE TEST;
GO
DROP PROCEDURE dbo.GetStuName;

关键字DROP PROCEDURE +过程名

 

备注：PROCEDURE 可以缩写成PROC