储存过程

我们现在需要了解储存过程

储存过程到底是什么？

他大概是一个函数吧！但是，仅仅是这样吗？

什么是储存过程

一段存储在数据库的“子程序”，下面对这两个部分进行分开的阐释。

 

1. 子程序（Subprogram）：

1. 一段可以被调用的子程序（subprogram）要么是一个过程（Procedrue）,要么是一个函数（function）.
2. 过程（Procerue）和函数（function）的区别之一是：函数总是只返回一个单个的值， 而过程并不是。（摘自Oracle Database concepts guide）
3. 数据库的存储过程通常是指一系列的SQL语句构成的”SQL代码块”， 和普通的SQL语句的不同点是, 代码块中包含了原生SQL所没有的元素， 例如cursor, variable，if, else, loop。

1. “存储在数据库”

1. SQL语句都是需要通过数据库相应组件编译后才能执行，最常见的例子是通过JDBC， 或ODBC创建数据区连接，发送SQL语句给数据库执行，并在程序中获得数据库返回的结果。
2. 存储过程则是把经常会被重复使用的SQL语句逻辑块封装起来，编译好，存储在数据库服务器端(这里的客户端是需要连接数据库的应用程序，但该应用程序本身也可能是一个Web服务器)。 这样，当存储过程再次被调用时，就无须编译了。 而调用的过程也无须发送SQL语句，只需要发送一个存储过程的标识， 数据库就可以找到相应的存储过程予以调用。

储存过程是一把双刃剑

储存过程的好处

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

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

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

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

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

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

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

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

 

储存过程的缺点

1. 不易调试。

1. 如果将 sql 逻辑通过应用程序控制， 可以很方便的加断点调试， 查看各个变量的值。 如果直接编写存储过程， 则很难调试， oracle 数据库的开发公爵 plsql developer 提供了一定的存储过程调试功能， 但是依旧不如应用程序的断点调试那么强大。

1. 不易迁移扩展。

1. 不同类型数据库的存储过程语法会有区别， 使用了存储过程会使得应用程序不具备迁移扩展能力。 不要问我为什么， 因为我就经历过一个在 sqlserver 有大量存储过程的应用程序迁移到 oracle 数据库的过程。

如何创建储存过程

系统的储存过程

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;

自定义的储存过程

带参数的储存过程

-- 创建一个储存过程

-- 为了获取当前地区的某种卡片已经产生了多少张

CREATE PROCEDURE test_proc_get_card_count

  @addressNumber VARCHAR(7) ,

  @type INT ,

  @count INT OUTPUT

  AS

BEGIN

  SET @count = (SELECT dot_count FROM tb_temp_dot_table WHERE dot_number = @addressNumber AND card_type = @type)

END

SELECT @count;

  

-- 调用储存过程

DECLARE @addressNumber VARCHAR(7)

DECLARE @type INT

DECLARE @count INT

SET @addressNumber = '152529'

SET @type = 5

EXEC test_proc_get_card_count @addressNumber, @type, @count;

不带参数的储存过程

-- 创建一个储存过程

-- 在银行网点的表中放入数据

CREATE PROCEDURE test_proc_save_date

  AS

BEGIN

  DECLARE @dot_number VARCHAR(7)

  DECLARE @i INT

  DECLARE @j INT

  SET @j = 1

  SET @i = 1

  WHILE @i < 3511

  BEGIN

    SET @dot_number = (SELECT address_number FROM tb_temp_address_table WHERE number = @i)

    WHILE @j <11

    BEGIN

      INSERT INTO tb_temp_dot_table(dot_number, card_type, dot_count) VALUES (@dot_number, @j, 0)

      SET @j = @j + 1

    end

    SET @j = 1

    SET @i =@i + 1

  end

end

-- 调用储存过程

exec test_proc_save_date;

有返回值的储存过程

-- 创建一个储存过程

-- 为了获取银行卡的类型

CREATE PROCEDURE test_proc_randBackCradTpeyNumber

  @bankCradTypeNumber INT OUTPUT

  AS

  BEGIN

    SET @bankCradTypeNumber = (SELECT TOP 1 bank_card_types_number FROM tb_list_of_bank_card_type ORDER BY NEWID())

  END

SELECT @bankCradTypeNumber;

-- 调用储存过程

DECLARE @number INT

EXEC test_proc_randBackCradTpeyNumber @number;

没有返回值的储存过程

-- 创建一个储存过程

-- 在银行网点的表中放入数据

CREATE PROCEDURE test_proc_save_date

  AS

BEGIN

  DECLARE @dot_number VARCHAR(7)

  DECLARE @i INT

  DECLARE @j INT

  SET @j = 1

  SET @i = 1

  WHILE @i < 3511

  BEGIN

    SET @dot_number = (SELECT address_number FROM tb_temp_address_table WHERE number = @i)

    WHILE @j <11

    BEGIN

      INSERT INTO tb_temp_dot_table(dot_number, card_type, dot_count) VALUES (@dot_number, @j, 0)

      SET @j = @j + 1

    end

    SET @j = 1

    SET @i =@i + 1

  end

end

-- 调用储存过程

exec test_proc_save_date;