存储过程 Procedure

运维指南 SQL开发
1

  通过本文学习，您将能了解存储过程的目的以及使用存储过程的好处。

1.什么是存储过程？

  存储过程 （stored procedures） 是以对象的方式存储在数据库中的 SQL 语句和 SPL（Stored Procedure Language，存储过程语言）语句。存储过程的特征如下：

  • 存储过程可以包含除 CREATE PROCEDURE和数据库语句（CREATE DATABASE、 DATABASE、CLOSE DATABASE）之外的任何 SQL 语句。

  • 允许在存储过程中使用的唯一非 SQL 语句是专门的 SPL（存储过程语言）语句。

  • 该存储过程存储在数据库中的一组系统目录表中。首次执行存储过程时必须从数据库中进行检索。

2. 存储过程示例

  示例展示了一个把顾客订单改为已付款状态的存储过程。订单编号由调用程序传递进来。

CREATE PROCEDURE credit_order(p_order_num INT)
   UPDATE orders
        SET paid_date = TODAY
        WHERE order_num = p_order_num;
END PROCEDURE

3. 存储过程中的SQL语句

image
image775×349 21.6 KB

  上述对比了从应用程序发送的常规的SQL语句与存储过程中的 SQL 语句的执行过程。

常规 SQL 语句：
  SQL 语句通常是从前端应用程序传递到数据库服务器。SQL语句将会被解析、优化并执行。运行结果会回传给应用程序。已预处理的 SQL 语句比未进行预处理的 SQL 语句更高效，但是所有语句必须在每次执行程序时至少被解析和优化一次。

存储过程中的 SQL 语句：
  由于存储时已经完成了语句解析操作，因此存储过程中的 SQL 语句不再需要被解析。可能会需要进行优化操作。只有用于执行存储过程 (EXECUTE PROCEDURE) 的语句从前端应用程序传递过来。如果存储过程中有几条 SQL 语句，两个进程间的通信远远少于不使用存储过程执行的情况。

编译存储过程
  执行 CREATE PROCEDURE 语句时，存储过程被编译。SPL语句被解析和优化，且被转换为 pcode （解释器快速执行的伪代码)。

调用存储过程

  可以使用以下任意一种方式调用存储过程：

  • ① 使用 SQL 语句 EXECUTE PROCEDURE 调用存储过程。例如：

EXECUTE PROCEDURE credit_order(1012);

  • ② 可以作为 SELECT 语句的一部分隐式的调用存储过程。例如，以下过程计算了给某个制造商的折扣价：
CREATE PROCEDURE pro_discount(manuf CHAR(3), price MONEY) RETURNING MONEY
     IF manuf = "HSK" THEN
         RETURN price * .9;
     ELSE
         RETURN price;
    END IF;
END PROCEDURE;

SELECT order_num, item_num, manu_code,
   pro_discount (manu_code, total_price)
FROM items
WHERE order_num = 1014;
  • ③ 存储过程可以作为触发器行动的一部分执行。

存储过程如何执行：

  • ① 在SinoDB数据库中，存储过程被缓存到星瑞格数据库服务器共享内存的虚拟内存部分。当任何会话请求首次使用存储过程时，数据库服务器读取系统目录表以检索存储过程代码。从系统目录检索 pcode，并转换为二进制格式。

  • ② 解析并计算由 EXECUTE PROCEDURE 或 CALL 语句传递的参数。

  • ③ 如果数据库表中的变更要求重新优化，优化将在此时进行。如果 SQL 语句执行时所需的条目缺失（例如，如果已经删除了列或表），那么将出现错误。

  • ④ 解析器执行 pcode 指令。

4. 存储过程的一些优势

  • 存储过程可以通过从程序中抽取可完成特定功能代码，并把它存放于数据库服务器中来降低程序复杂性。例如，借记客户储蓄帐户并贷记支票帐户的常见操作可能是存储过程的完美候选项。程序会执行该过程，同时传递所需的变量信息。

  • 通过把多个 SQL 语句放于存储过程中，可能会提升性能。性能改善来自客户端应用程序和数据库服务器之间通信的减少，尤其是如果应用程序位于另一台机器上。因为解析和优化程序的需求减少，可以带来性能改善。

  • 存储过程提供限制访问表的能力。不使用存储过程，如果管理员把插入权限授予用户，那么该用户可以通过使用dbaccess 或程序插入行。如果管理员想要强制执行任何业务规则（请参考下一条），这可能会是一个问题。不授予插入特权，管理员可以强制用户通过执行存储过程来进行插入操作。

  • 使用存储过程提供附加的安全级别，可以使用存储过程强制业务规则。例如，可以通过编写一个在用户不把行存储到归档表之前禁止删除行的存储过程去完成任务并禁止用户直接访问表。

  • 不同的应用程序可以共享相同的代码，而非在每个程序中维护相同的代码。代码只存储在一个地方，消除了代码冗余。

  • 存储过程在客户端/服务器环境里尤其有用。如果更改应用程序代码，那么代码必须分发给每个客户端。如果更改存储过程，它只存在于服务器上。

  • 相比将数据库代码集中到应用程序内，将代码移动到数据库服务器中是更好的选择。这将允许应用程序专注于用户接口交互。在需要多种用户接口类型的情况下，这就显得尤为重要。

5. 存储过程性能

存储过程有与执行相关的额外成本而 SQL 没有：

  • 存储过程存储在磁盘上的系统目录表中。在首次使用之前，必须从磁盘检索它们。如果已被执行过，SinoDB服务器将直接使用共享内存里的存储过程。存储过程被保存在访问最频繁的存储过程缓冲区上。

  • 使用过程之前，需要从字符格式转换为可执行格式。

  • 如果过程中涉及到的列、表或索引被改变，那么过程需要重新优化。每次执行过程时都会进行这个检查。

因为执行存储过程带来的额外成本，仅使用存储过程执行单一 SQL 语句，可能无法看到任何性能改善。