存储过程入门_oracle database concepts guide(11g2)-CSDN博客

本文参考：

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

闲言：开始实习的时候才发现，虽然大学上过了数据库的课程，也算是系统的学习了数据库知识，但是偏偏就对常常听到的名词存储过程不是那么熟悉。百度上搜索“存储过程”的教程，虽然官方文档和书籍是最好的学习材料，但是如果能有入门博文快速地带领新手快速建立一些大致的概念与学习思路，想来还是有益处的。

先修知识：
- 数据库的基本概念
- SQL
什么是存储过程（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). 存储过程的实现语法上也存在细微的差异。但存储过程的概念在不同的数据库中是通用的。
存储过程的好处：
- 性能提升
  - 如之前所提到的，原本由通过代码发送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列，是无法成功的。
存储过程的缺点：
- 不易调试。
  - 如果将 sql 逻辑通过应用程序控制，可以很方便的加断点调试，查看各个变量的值。如果直接编写存储过程，则很难调试， oracle 的提供了一定的存储过程调试功能，但是依旧不如应用程序的断点调试那么强大。
- 不易迁移扩展。
  - 不同类型数据库的存储过程语法会有区别，使用了存储过程会使得应用程序不具备迁移扩展能力。不要问我为什么，因为我就经历过一个在 sqlserver 有大量存储过程的应用程序迁移到 oracle 数据库的过程。
以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;
 
 1
2
3
4
5
6
7
8
9
10
11
12

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

PL/SQL执行过程，图片来源于参考资料1

图中的PL/SQL engine 是用来定义，编译，执行PL/SQL语句集的组件单元。当数据库调用存储过程时，数据库会将经过编译的PL/SQL语句集加载到系统全局区（system global area，SGA) 共享池（shared pool）中。（这里不对该概念作赘述，可暂且简单理解为一片内存区域）。然后PL/SQL engine会和语句执行器（statement executor）一同工作，来处理存储过程中的语句。
总结：存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。所以广义上来说，只要是被存储在数据库，可供外部直接调用以修改数据的子程序（包括函数和过程）都可以被看做是存储过程。