存储过程优缺点-附加简单例子

1、什么是存储过程？

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

2、存储过程的优点：

A、存储过程允许标准组件式编程

存储过程创建后可以在程序中被多次调用执行，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，但对应用程序源代码却毫无影响，从而极大的提高了程序的可移植性。

B、存储过程能够实现较快的执行速度

如果某一操作包含大量的T-SQL语句代码，分别被多次执行，那么存储过程要比批处理的执行速度快得多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划。而批处理的T-SQL语句每次运行都需要预编译和优化，所以速度就要慢一些。

C、存储过程减轻网络流量

对于同一个针对数据库对象的操作，如果这一操作所涉及到的T-SQL语句被组织成一存储过程，那么当在客户机上调用该存储过程时，网络中传递的只是该调用语句，否则将会是多条SQL语句。从而减轻了网络流量，降低了网络负载。

D、存储过程可被作为一种安全机制来充分利用

系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。

　　 E、可扩展性：应用程序和数据库操作分开，独立进行，而不是相互在一起。方便以后的扩展和DBA维护优化。

　　总述：执行快、减少网络传输、可以维护性强、加强安全性、可扩展性强

3、存储过程的缺点：

　　 A. SQL本身是一种结构化查询语言，但不是面向对象的的，本质上还是过程化的语言，面对复杂的业务逻辑，过程化的处理会很吃力。同时SQL擅长的是数据查询而非业务逻辑的处理，如果如果把业务逻辑全放在存储过程里面，违背了这一原则。

　　B. 如果需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新调用，等等，这时候估计会比较繁琐了。

　　C. 开发调试复杂，由于IDE的问题，存储过程的开发调试要比一般程序困难。

　　D. 没办法应用缓存。虽然有全局临时表之类的方法可以做缓存，但同样加重了数据库的负担。如果缓存并发严重，经常要加锁，那效率实在堪忧。

　　 E. 不支持群集，数据库服务器无法水平扩展，或者数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。

　　总述：编写复杂度高、逻辑性强、更改不便等

4、优缺点总述：

　　1、我们要适当的使用存储过程来提高我们查询的质量，如复杂的连接查询等

　　2、避免滥用，因为开发存储过程需要时间，避免复杂的逻辑而去使用存储过程，减少数据库的压力

5、数据库中的存储过程有二部分：

　　1、系统存储过程：系统存储过程是系统创建的存储过程以“sp”下划线开头的存储过程一般存放在master数据库中，其目的是为了帮助我们调用、更新和管理系统相关的信息。

exec sp_databases; --查看数据库
exec sp_tables;        --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename student, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;

　　2、用户自定义的存储过程：

　　基本语法：

create proc | procedure pro_name
    [{@参数数据类型} [=默认值] [output],
     {@参数数据类型} [=默认值] [output],
     ....
    ]
as
    SQL_statements

　　不带参数的存储过程语法

if (exists (select * from sys.objects where name = 'proc_name'))
    drop proc proc_name
go
create proc proc_name
as
    select * from student;

--调用、执行存储过程
exec proc_name;

　　掌握了基本语法之后，试着去写几个复杂的存储过程，在实际应用中还是有很多好处的，毕竟对于后端开发工程师来说：这是一个必经之路，当然对于DBA（数据库管理员）来说，这个都是非常EASY的事情

　　最后作者在奉献一个我在开发中写的一个存储过程：非常简单希望各位大神不要笑(*^_^*)

　　这是一个红酒检验检疫查询

展示存储过程

CREATE PROC [dbo].[Proc_Check_Checkout] 
    @pageIndex AS INT,
    @pageSize AS INT,
    @ForecastBegin AS NVARCHAR(MAX),--预检
    @ForecastEnd AS NVARCHAR(MAX),
    @InspectionBegin AS NVARCHAR(MAX),--报检
    @InspectionEnd AS NVARCHAR(MAX), 
    @CheckoutID AS NVARCHAR(MAX),--预检单号
    @InspectionID AS NVARCHAR(MAX),--报检单号
    @recordCount  AS INT OUTPUT
AS
    SET NOCOUNT ON
    DECLARE @tempPageSize INT;
    SET @tempPageSize = (@pageIndex - 1) * @pageSize;

 SELECT 
 --第一二条查询
 t1.ID,t2.CheckoutID,t1.QuarantineID,t1.PurchaseContract,t1.Origin,t1.Consignee,t1.GoodsName,t1.Bottle,t1.Numbers,t1.RiseNumber,t1.Mount,CONVERT(NVARCHAR(16),t1.ForecastDate,20) AS ForecastDate,
 t2.InspectionID,t2.KBottle,t2.KNumbers,t2.KRiseNumber,
 --三四条查询
 t2.Franchisee,t2.Brand,t2.BottleInspection,t2.NewspaperNumber,t2.NumberInspection,t2.CheckMount,CONVERT(NVARCHAR(16),t2.HisDate,20) AS HisDate,t2.InspectionDate
 INTO #TempData1
 FROM dbo.TBPreview_Table AS t1 

 LEFT JOIN dbo.TBCheckout AS t2 ON t2.CheckoutID = t1.ID
 ORDER BY t1.Date DESC ;


 SELECT * INTO #TempData2 FROM #TempData1 WHERE 1 = 2;

 --时间检索 
 IF @ForecastBegin IS NOT NULL AND LEN(@ForecastBegin) > 0
 BEGIN
        TRUNCATE TABLE #TempData2;
        INSERT INTO #TempData2 
        SELECT *FROM #TempData1 AS t WHERE t.ForecastDate >= @ForecastBegin;
        TRUNCATE TABLE #TempData1;
        INSERT INTO #TempData1 SELECT * FROM #TempData2;
 END
 
 --
 IF @ForecastEnd IS NOT NULL AND LEN(@ForecastEnd) > 0
 BEGIN
        TRUNCATE TABLE #TempData2;
        INSERT INTO #TempData2 
        SELECT *FROM #TempData1 AS t WHERE t.ForecastDate < DATEADD(DAY,1,@ForecastEnd) ;
        TRUNCATE TABLE #TempData1;
        INSERT INTO #TempData1 SELECT * FROM #TempData2;
 END
 
 --时间检索 报检时间
 IF @InspectionBegin IS NOT NULL AND LEN(@InspectionBegin) > 0
 BEGIN
        TRUNCATE TABLE #TempData2;
        INSERT INTO #TempData2 
        SELECT *FROM #TempData1 AS t WHERE t.InspectionDate >= @InspectionBegin;
        TRUNCATE TABLE #TempData1;
        INSERT INTO #TempData1 SELECT * FROM #TempData2;
 END
 
 --
 IF @InspectionEnd IS NOT NULL AND LEN(@InspectionEnd) > 0
 BEGIN
        TRUNCATE TABLE #TempData2;
        INSERT INTO #TempData2 
        SELECT *FROM #TempData1 AS t WHERE t.InspectionDate < DATEADD(DAY,1,@InspectionEnd) ;
        TRUNCATE TABLE #TempData1;
        INSERT INTO #TempData1 SELECT * FROM #TempData2;
 END
 
 /* 预检单号 */
  IF @CheckoutID IS NOT NULL AND LEN(@CheckoutID) > 0
 BEGIN
        TRUNCATE TABLE #TempData2;
        INSERT INTO #TempData2 
        SELECT *FROM #TempData1 AS t WHERE t.QuarantineID LIKE '%'+@CheckoutID+'%' ;
        TRUNCATE TABLE #TempData1;
        INSERT INTO #TempData1 SELECT * FROM #TempData2;
 END

 /* 报检单号*/
   IF @InspectionID IS NOT NULL AND LEN(@InspectionID) > 0
 BEGIN
        TRUNCATE TABLE #TempData2;
        INSERT INTO #TempData2 
        SELECT *FROM #TempData1 AS t WHERE t.InspectionID LIKE '%'+@InspectionID+'%' ;
        TRUNCATE TABLE #TempData1;
        INSERT INTO #TempData1 SELECT * FROM #TempData2;
 END

SELECT @recordCount = COUNT(1) FROM #TempData1;
    DROP TABLE #TempData2;
    SELECT * INTO #TempData3 FROM #TempData1 WHERE 1 = 2;
    INSERT INTO #TempData3
    SELECT TOP (@pageSize) t1.* FROM #TempData1 AS t1 , 
    (SELECT * , ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber FROM #TempData1) AS t2
    WHERE t1.ID = t2.ID AND RowNumber > @tempPageSize 
    ORDER BY t2.RowNumber;
    DROP TABLE #TempData1;
    SELECT * FROM #TempData3;
GO

最后作者希望各位指出作者的不足，毕竟在程序这条路才刚刚起步！