1. Oracle Database concepts guide（11g2） By Thomas Kyte
2. Stored Procedure Wiki

• @H_502_1@先修知识：
  • 数据库的基本概念
  • sql
• @H_502_1@什么是存储过程（Stored Procedure）：
  • 一段存储在数据库的“子程序”，下面对这两个部分进行分开的阐释。
  • 子程序（Subprogram）：
    
    • 一段可以被调用的子程序（subprogram）要么是一个过程（Procedrue）,要么是一个函数（function）.
    • 过程（Procerue）和函数（function）的区别之一是：函数总是只返回一个单个的值， 而过程并不是。（摘自Oracle Database concepts guide）
    • 数据库的存储过程通常是指一系列的sql语句构成的”sql代码块”， 和普通的sql语句的不同点是,代码块中包含了原生sql所没有的元素， 例如cursor,variable，逻辑控制语句。
  • “存储在数据库”
    
    • sql语句都是需要通过数据库相应组件编译后才能执行，最常见的例子是通过JDBC， 或ODBC创建数据区连接，发送sql语句给数据库执行，并在程序中获得数据库返回的结果。
    • 存储过程则是把经常会被重复使用的sql语句逻辑块封装起来，编译好，存储在数据库服务器端(这里的客户端是需要连接数据库的应用程序，但该应用程序本身也可能是一个Web服务器)。 这样，当存储过程再次被调用时，就无须编译了。 而调用的过程也无须发送sql语句，只需要发送一个存储过程的标识， 数据库就可以找到相应的存储过程予以调用。
  • PL/sql与存储过程的概念区分：
    
    • PL/sql是 Oracle对sql的扩展，经常会与存储过程的相关文章一同出现， 容易给初学者造成存储过程即为PL/sql的误解，但其实很多数据库都支持存储过程，如MysqL,sqlserver(Transact-sql). 存储过程的实现语法上也存在细微的差异。 但存储过程的概念在不同的数据库中是通用的。
• @H_502_1@存储过程的好处：
  • 性能提升
    
    • 如之前所提到的， 原本由通过代码发送sql实现的业务逻辑，编写封装成存储过程后， 经过编译， 存放在数据库段， 这样应用程序在调用该业务逻辑时， 就无须发送sql语句， 也无需经历编译的步骤，从而带来性能上的提升
    • 存储过程可以利用数据库的内存共享能力， 如果一个存储过程可以被多个用户间共享，就能够节省内存的空间。
  • 开发维护的效率提升
    
    • 如果多个应用程序都具有通用的数据库操作逻辑，那么就可以利用数据库实现存储过程的复用，避免为每一个应用编写一套代码。 同时，如果需要修改特定业务的数据库操作逻辑， 也无须改动应用程序的代码。 另外， 存储过程可以被任意语言编写的应用程序所调用，最大限度的实现了“代码复用”。
  • 数据完整性与一致性
    
    • 由于存储过程可以被重复调用， 所以只要经过严格测试，并保证正确运行的存储过程，在新的代码中被使用， 也无需再次经过测试。
  • 安全性
    
    • 存储过程可以以定义者的权限执行，而不是使用者的权限执行。为普通用户执行敏感操作提供了一种有效的方法。
      
      • 举例： 假如某个应用程序要向用户提供修改密码的功能，而不同用户的用户名和密码都存储在一张User表中。
      • 如果，如果不使用存储过程， 该应用程序在访问数据库时， 必须已具有操作“User”表权限的用户身份去连接数据库。此时如果程序员编写的代码出错，或者程序员想恶意修改别人的password,则数据库User表的安全无法保证。
      • 如果使用存储过程，则可以定义一个具有修改User表权限的存储过程changePassword(username,oldPassword,newPassword),该存储过程实现了严格的校验逻辑，即首先检查useranme,oldpassword是否匹配， 如果匹配， 则仅仅修改username所对应的password。
    • 存储也可以以调用者的权限执行，而不是定义者的权限执行，为不同权限的用户，在调用同一个存储过程时，依旧能区分权限提供了可能。
      
      • 举例： 一个hr_manager 用户可以运行一个 employee_update（columnName， content） 存储过程来更新员工的薪酬（salary）字段， 因为hr_manager被赋予了更新salary字段的权限。 另一个用户hr_clerk 也可以调用这一存储过程，但是他只被赋予了更新员工地址（addressBook）字段的权限，由于该存储过程被设置成了以调用者权限执行， hr_clerk如果试图更新salary列， 是无法成功的。
• @H_502_1@存储过程的缺点：
  • 不易调试。
    
    • 如果将 sql 逻辑通过应用程序控制， 可以很方便的加断点调试， 查看各个变量的值。 如果直接编写存储过程， 则很难调试， oracle 的提供了一定的存储过程调试功能， 但是依旧不如应用程序的断点调试那么强大。
  • 不易迁移扩展。
    
    • 不同类型数据库的存储过程语法会有区别， 使用了存储过程会使得应用程序不具备迁移扩展能力。 不要问我为什么， 因为我就经历过一个在 sqlserver 有大量存储过程的应用程序迁移到 oracle 数据库的过程。
• @H_502_1@以PL/sql为例的存储过程编写
  • PL/sql Subprogram（子程序）的创建

CREATE PROCEDURE hire_employees (p_last_name VARCHAR2,p_job_id VARCHAR2,p_manager_id NUMBER,p_hire_date DATE,p_salary NUMBER,p_commission_pct NUMBER,p_department_id NUMBER) IS BEGIN . INSERT INTO employees (employee_id,last_name,job_id,manager_id,hire_date,salary,commission_pct,department_id) VALUES (emp_sequence.NEXTVAL,p_last_name,p_job_id,p_manager_id,p_hire_date,p_salary,p_commission_pct,p_department_id);
.
.
END;

• @H_502_1@PL/sql Subprogram存储过程的执行（三种）：
  • 利用oracle集成的工具，如 sql PLUS或者 sql Developer
  • 在数据库应用程序的代码中调用
  • 在另外一个存储过程或触发器（trigger）中调用（触发器的概念不在此文涉猎）
• @H_502_1@PL/sql 语言构成（Language Constructs）
  • 变量（variables）和常量(constants)
    
    • 可以在sql存储过程中声明使用，和一般程序中的变量，常量的概念一致。
  • 游标（Cursor）
    
    • 可以在存储过程中声明使用， 主要用于实现面向数据记录（record oriented）的处理.
  • 异常（Exceptions）
    
    • PL/sql允许为自定义存储过程中会抛出的异常 ，和一般程序中的异常概念类似。
• @H_502_1@PL/sql运行过程：
  • PL/sql支持两种执行方式： native execution (本地执行) 和 interpreted execution (解释执行)
    
    • 解释执行， PL/sql语句集会被编译成所谓的”字节码(bytecode)”的表示方式， 类似于Java 中的bytecode,最后会被一个oracle数据库实现的虚拟机组件解释执行
    • 本地执行,会把PL/sql直接编译成对应平台的object code（可以被链接成可执行程序）， 由于没有解释执行的过程， 所以可以提供更好的性能。

• @H_502_1@图中的PL/sql engine 是用来定义，编译，执行PL/sql语句集的组件单元。 当数据库调用存储过程时， 数据库会将经过编译的PL/sql语句集加载到系统全局区（system global area，SGA) 共享池（shared pool）中 。 （这里不对该概念作赘述，可暂且简单理解为一片内存区域）。 然后PL/sql engine会和语句执行器（statement executor）一同工作，来处理存储过程中的语句。

• @H_502_1@总结： 存储过程思想上很简单，就是数据库sql语言层面的代码封装与重用。 所以广义上来说， 只要是被存储在数据库，可供外部直接调用以修改数据的子程序（包括函数和过程）都可以被看做是存储过程。