存储过程入门

灰太狼 2024-02-19 16:50 41阅读 0赞

版权声明：本文为博主学习笔记， 注明来源情况下随意转载 https://blog.csdn.net/lengxiao1993/article/details/53427266  
参考文章  
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，if, else, loop。  
“存储在数据库”  
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), 不具备访问 User 表的数据库用户， 依旧可以执行该存储。 而该存储过程则可实现严格的校验逻辑，即首先检查useranme, oldpassword是否匹配， 如果匹配， 则仅仅修改username所对应的password。  
存储过程也能以调用者的权限执行，而不是定义者的权限执行。 这使得不同权限的用户，在调用同一个存储过程时，依旧能对数据访问能力进行区分限制。  
举例： 一个hr\_manager 用户可以运行一个 employee\_update（columnName， content） 存储过程来更新员工的薪酬（salary）字段， 因为hr\_manager被赋予了更新salary字段的权限。 另一个用户hr\_clerk 也可以调用这一存储过程，但是他只被赋予了更新员工地址（addressBook）字段的权限，由于该存储过程被设置成了以调用者权限执行， hr\_clerk如果试图更新salary列， 是无法成功的。  
存储过程的缺点  
不易调试。  
如果将 sql 逻辑通过应用程序控制， 可以很方便的加断点调试， 查看各个变量的值。 如果直接编写存储过程， 则很难调试， oracle 数据库的开发公爵 plsql developer 提供了一定的存储过程调试功能， 但是依旧不如应用程序的断点调试那么强大。  
不易迁移扩展。  
不同类型数据库的存储过程语法会有区别， 使用了存储过程会使得应用程序不具备迁移扩展能力。 不要问我为什么， 因为我就经历过一个在 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）中调用（触发器的概念不在此文涉猎）![æ°æ®åºåºç¨ç¨åºå¯¹åå¨è¿ç¨è°ç¨åºæ¯çç¤ºæå¾ï¼å¾çæ¥æºäºåèèµæ1][1]

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_1]

图中的PL/SQL engine 是用来定义，编译，执行PL/SQL语句集的组件单元。 当数据库调用存储过程时， 数据库会将经过编译的PL/SQL语句集加载到系统全局区（system global area，SGA) 共享池（shared pool）中 。 （这里不对该概念作赘述，可暂且简单理解为一片内存区域）。 然后PL/SQL engine会和语句执行器（statement executor）一同工作，来处理存储过程中的语句。  
总结  
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。 所以广义上来说， 只要是被存储在数据库，可供外部直接调用以修改数据的子程序（包括函数和过程）都可以被看做是存储过程。  
\---------------------  
作者：萧萧冷  
来源：CSDN  
原文：https://blog.csdn.net/lengxiao1993/article/details/53427266  
版权声明：本文为博主原创文章，转载请附上博文链接！

[1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/1353a01d2ca0456baed5ef9d189120e6.png
[PL_SQL_1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/3d3570b1db524cf29ffb6223b2128a19.png