17认证网OceanBase 存储过程概述
存储过程可以用来“封装”一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。
OceanBase PL 总体架构
PL/SQL 是一种程序语言，叫做过程化 SQL语言(Procedural Language/SQL)，从 Ada 语言发展而来。PL/SQL 是数据库对 SQL 语句的扩展，在普通 SQL 语句的基础上增加了编程语言的特点，把数据操作和查询语句组织在 PL/SQL 代码的过程化代码中，通过逻辑判断、循环等操作实现复杂的功能使用 PL/SQL 可以编写具有很多高级功能的程序，能够把业务逻辑封装在数据库内部，提供更好的抽象或者安全性，同时减少了网络的交互，并且调用更快，从而提升整体性能。MySQL 的过程化 SQL(存储过程、函数、触发器等)是解释执行：创建时仅做语法/语义检查，执行时由服务器逐行解析并运行内部的 SQL语句和流程控制逻辑，无预编译为机器码或中间可执行形式。
OceanBase PL 采用编译执行的方式，使用 LLVM 生成 native code，并直接把机器码装载到程序段执行，无需依赖外部 C 编译器和生成动态链接库。编译后的程序比解释执行性能提升 1.05 到 2.4 倍，具体提升的程度取决于程序的特性(因为 OceanBase 中 SQL 语句本身还是解释执行的，如果 PL 的主体是程序控制逻辑，那么性能提升效果会很明显)。
说明：MySQL8.0 等新版本对存储过程执行效率有优化，但“解释执行”的本质逻辑未改变，只是通过缓存执行计划、优化解析流程等手段提升性能。
OceanBase PL引擎的总体流程图
当客户端一条 PL 命令进入 OceanBase 之后，它会依次经过如上的各个组件，最终将执行结果返回给客户端。
接下来我们按照执行顺序简单介绍一下 OceanBase PL 引擎的各个组件，通过了解 PL的各个组件，大家基本就能拼出 PL在 OceanBase 中的执行流程了。
PL 缓存器(pl-cache)
OceanBase 会将 PL 编译后的可执行代码缓存在 pl-cache 中，以便对编译结果进行复用，降低 PL 编译的开销，提升整体的执行效率。对于一条 PL命令而言，如果 pl-cache 中已经缓存了该条命令涉及到的 PL 程序的编译结果，那么 OceanBase 会使用该缓存的编译结果进行执行，否则进入正常的 PL 程序编译流程，生成的编译结果将被添加到 pl-cache 中供后续 PL 命令复用。
语法分析(Parser)
当一条 PL 命令涉及到的 PL程序未命中 pl-cache 的时候，它会进入正常的语法分析流程。语法分析的过程把输入的 PL程序字符串转换为一颗语法树。语法树不包含语义信息，仅仅是是字面的表示。这个过程完全使用 flex 和 bison 自动生成。输出结果是以 ParseNode 为节点的树。
语义分析(Resolver)
语义分析把输入的语法树转换为包含 PL 程序语义的内部数据结构表示(ObPLAST)。在此过程中，还要根据 PL 程序的语义进行语义检查，及时报错。例如，语义检查的内容包括:
1、在使用变量时，变量是否已经进行了定义，类型是否合法。
2、同一个名字空间下符号是否有重复定义，对于重复定义的符号需要及时报错。
3、PL 程序中使用的表、列、自定义类型、包、函数等对象是否已经存在。语义分析的输出为 ObPLAST，这个结构是由符号表、类型表、异常表、标签表以及多个 ObPLStmt 和的集合，多个 stmt 对象表示 PL程序中的每条指令语句，如 IF、While、Goto。
代码生成(Code generator)
OceanBase PL 采用编译执行的方式，因此由语义分析输出的 ObPLAST 还会进一步进行翻译，为后面编译成可执行代码做进一步的准备。OceanBase 的 PL 采用了 LLVM 作为编译器的后端，代码生成则是为通过使用 LLVM 提供的接口，把 AST 树翻译成 IR 中间码的过程。IR 码可以输出，以核对翻译过程是否正确。
编译(Compiler)
通过 LLVM JIT 把 IR 码生成可执行代码的过程，生成的可执行代码会缓存到 pl-cache 中，供其他语句复用。
PL执行(PL Execution)
PL 执行器负责执行编译生成的可执行代码，除了执行代码外，执行器还需要做如下一些事情:
1、构造当前 PL的执行环境，比如设置一些权限相关上下文、准本入参。
2、通过 SPl(Server Programming Interface)与 SQL 引擎交互，将 PL 中需要执行的 SQL 语句交给 SQL 引擎执行，并从 SQL 引擎中返回结果。
3、处理 PL 的出参以及函数的返回值等信息。
说明: SPl(Server Programming Interface)用于在 PL 程序中执行 SQL 操作(表达式计算和 SQL 查询)并返回结果。其广泛应用在 PL实现的各个环节，如 procedure、function、trigger 等。
下图给出了 PL Engine 工作的调用关系图。PL引擎(PL Engine)和 SQL引擎(SQL Engine)可以互相交互，SQL 可以直接访问 PL引擎，比如在一个 SQL 语句中使用了用户自定义函数(function)。PL 引擎可以通过 SPI 接口访问 SQL 引擎，比如在 PL 里进行表达式计算和执行 SQL 语句。
PL 的分布式执行
OceanBase 的分布式特性，天然决定 PL的执行也是分布式的，但是这种分布式体现在 SQL 的分布式执行上，对于 PL本身是不会分布式执行的，PL 自身的解析、编译和执行都在某一台 OBServer 节点上完成。当 PL 里涉及到 SQL 交互时，会通过 SPI调用 SQL Engine，由 SQL Engine 执行 SQL 语句。如果该 SQL 语句是一个分布式的，那么自然而然会进行分布式执行。分布式执行的 SQL 调用了 PL 函数时，PL 函数可能会在多台 OBServer 节点上编译执行，每台OBServer 会保证在相同的环境参数下进行编译，从而编译出的 PL具有相同的行为。
使用存储过程的优势
- 性能优化：预编译执行，减少SQL解析和编译开销
- 代码复用：一次编写，多次调用，减少代码冗余。存储过程只在创建时进行编译，以后每次执行存储过程都不需要重新编译而一般 SQL 语句每执行一次就编译一次，使用存储过程可提高数据库执行速度。
- 安全性：通过权限控制保护数据，避免直接表操作。参数化的存储过程可以防止 SQL 注入式攻击。
- 减少网络传输：批量操作在数据库端完成，减少客户端与服务器间的数据传输。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数即可，降低了网络传输的数据量。
- 事务管理：支持复杂的事务控制，确保数据一致性
说明:
通常情况下，从业务维护和可读性、透明性，以及操作安全性的角度来说，存储过程和触发器这类需求，一般都在业务应用层实现。不鼓励利用数据库中的机制来写非常复杂的业务逻辑，这类业务逻辑放在应用层实现，可读性会更好，而且在数据库迁移的时候，也不用重写逻辑。
数据库中也有函数(Function)的概念和能力，和存储过程不同，优点是可以在 SQL 查询中嵌入计算(如 SELECT fn_add(score,10).)，缺点是不适合封装复杂的 DML 操作和事务，只适合封装其他的计算逻辑(例如四则运算)
存储过程简单示例
在线体验里的示例相对复杂，这里就反过来，给大家写一个最简单的存储过程示例。
现有雇员信息 emp 表(包括员工姓名和薪水)，写一个简单的储存过程来修改员工薪水。
测试表信息
emp 表信息:
存储过程内容
说明:
OceanBase MySQL 模式的租户，兼容 MySQL 语法，需要使用与 MySQL 相同的语法，即需要设置Delimiter //，执行完后需要恢复 Delimiter;
调用存储过程
查看执行结果
存储过程的使用
在 OceanBase 数据库 MySQL 模式下创建存储过程
连接数据库
obclient -h127.0.0.1 -P2881 -uroot@mysql_tenant -A -Dtest创建存储的使用
-- 2. 创建测试表
CREATE TABLE test(n1 INT, d1 DATETIME DEFAULT CURRENT_TIMESTAMP);
-- 3. 更改分隔符
DELIMITER //
-- 4. 创建存储过程
CREATE PROCEDURE test_proc()
BEGIN
-- 插入数据
INSERT INTO test(n1) VALUES(1);
-- 可以添加更多SQL语句
END//
-- 5. 恢复默认分隔符
DELIMITER ;
-- 6. 查看存储过程
SHOW PROCEDURE STATUS WHERE Db = 'procedure_demo' AND Name = 'test_proc';
-- 6. 调用存储过程
CALL test_proc();
-- 7. 删除存储过程
DROP PROCEDURE IF EXISTS test_proc;存储过程批处理案例
批处理（Batch Processing）介绍
存储过程在实际应用中有多种使用场景，其中批处理（Batch Processing）是最能体现其价值的应用之一。本文将通过批处理示例，展示存储过程在数据处理方面的优势。以下是一个典型的批处理应用场景，展示了存储过程如何高效处理批量数据任务：
批处理适合以下场景：
- 数据报表生成：定期生成销售统计、财务报表等
- 数据维护：数据归档、历史数据清理、数据一致性检查
- 业务处理：批量订单处理、会员积分计算、账单生成
- ETL操作：数据抽取、转换和加载过程
每日销售报表统计
利用 OceanBase 存储过程实现电商销售数据的自动化批处理，通过定时任务每日生成销售报表、品类分析和商品排名，为经营决策提供数据支持，同时通过事务管理和错误处理确保数据处理的一致性和可靠性。
创建表结构来存储报表数据
-- 创建表结构
CREATE TABLE daily_sales_report (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
report_date DATE,
total_sales DECIMAL(12,2),
total_orders INT,
avg_order_value DECIMAL(12,2),
total_profit DECIMAL(12,2),
top_selling_category VARCHAR(100),
report_status ENUM('GENERATED', 'PROCESSED', 'ERROR') DEFAULT 'GENERATED',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE daily_category_sales (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
report_date DATE,
category_name VARCHAR(100),
total_quantity INT,
category_sales DECIMAL(12,2),
category_profit DECIMAL(12,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE daily_top_products (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
report_date DATE,
product_id BIGINT,
product_name VARCHAR(255),
total_quantity INT,
total_sales DECIMAL(12,2),
total_profit DECIMAL(12,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- sales 表（销售订单表）
CREATE TABLE sales (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT,
product_id BIGINT,
quantity INT,
total_amount DECIMAL(12,2),
cost_amount DECIMAL(12,2),
order_date DATETIME,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- products 表（产品表）
CREATE TABLE products (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(255),
category_name VARCHAR(100),
price DECIMAL(10,2),
cost DECIMAL(10,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 日志记录表
CREATE TABLE report_generation_log (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
report_type VARCHAR(50),
report_date DATE,
status VARCHAR(20),
records_processed INT,
error_message TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 为表添加索引
CREATE INDEX idx_daily_sales_report_date ON daily_sales_report(report_date);
CREATE INDEX idx_category_sales_date ON daily_category_sales(report_date);
CREATE INDEX idx_top_products_date ON daily_top_products(report_date);创建存储过程
DELIMITER //
CREATE PROCEDURE GenerateDailySalesReport()
BEGIN
-- 声明变量
DECLARE v_current_date DATE;
DECLARE v_total_sales DECIMAL(12,2);
DECLARE v_total_orders INT;
DECLARE v_avg_order_value DECIMAL(12,2);
DECLARE v_total_profit DECIMAL(12,2);
DECLARE v_top_selling_category VARCHAR(100);
DECLARE v_report_status VARCHAR(20);
-- 声明错误处理
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- 记录错误日志
INSERT INTO report_generation_log (
report_type,
report_date,
status,
error_message
) VALUES (
'DAILY_SALES',
CURDATE(),
'ERROR',
'存储过程执行失败'
);
-- 可以添加回滚操作
ROLLBACK;
END;
-- 开始事务
START TRANSACTION;
-- 获取当前日期
SET v_current_date = CURDATE();
-- 计算总销售额
SELECT
COALESCE(SUM(total_amount), 0),
COALESCE(COUNT(DISTINCT order_id), 0)
INTO
v_total_sales,
v_total_orders
FROM
sales
WHERE
DATE(order_date) = v_current_date;
-- 计算平均订单金额
SET v_avg_order_value = CASE
WHEN v_total_orders > 0
THEN v_total_sales / v_total_orders
ELSE 0
END;
-- 计算总利润
SELECT
COALESCE(SUM(total_amount - cost_amount), 0)
INTO
v_total_profit
FROM
sales
WHERE
DATE(order_date) = v_current_date;
-- 获取top销售类目
SELECT
category_name INTO v_top_selling_category
FROM (
SELECT
p.category_name,
SUM(s.total_amount) as category_total_sales
FROM
sales s
JOIN
products p ON s.product_id = p.id
WHERE
DATE(s.order_date) = v_current_date
GROUP BY
p.category_name
ORDER BY
category_total_sales DESC
LIMIT 1
) AS top_category;
-- 设置报告状态
SET v_report_status = 'GENERATED';
-- 插入每日销售报告
INSERT INTO daily_sales_report (
report_date,
total_sales,
total_orders,
avg_order_value,
total_profit,
top_selling_category,
report_status
) VALUES (
v_current_date,
v_total_sales,
v_total_orders,
v_avg_order_value,
v_total_profit,
v_top_selling_category,
v_report_status
);
-- 生成类目销售明细
INSERT INTO daily_category_sales (
report_date,
category_name,
total_quantity,
category_sales,
category_profit
)
SELECT
v_current_date,
p.category_name,
SUM(s.quantity),
SUM(s.total_amount),
SUM(s.total_amount - s.cost_amount)
FROM
sales s
JOIN
products p ON s.product_id = p.id
WHERE
DATE(s.order_date) = v_current_date
GROUP BY
p.category_name;
-- 生成top产品报告
INSERT INTO daily_top_products (
report_date,
product_id,
product_name,
total_quantity,
total_sales,
total_profit
)
SELECT
v_current_date,
p.id,
p.product_name,
SUM(s.quantity),
SUM(s.total_amount),
SUM(s.total_amount - s.cost_amount)
FROM
sales s
JOIN
products p ON s.product_id = p.id
WHERE
DATE(s.order_date) = v_current_date
GROUP BY
p.id, p.product_name
ORDER BY
total_sales DESC
LIMIT 10;
-- 添加日志记录
INSERT INTO report_generation_log (
report_type,
report_date,
status,
records_processed
) VALUES (
'DAILY_SALES',
v_current_date,
'SUCCESS',
v_total_orders
);
-- 提交事务
COMMIT;
END //
DELIMITER ;退出OceanBase命令行
exit
配置定时任务（Crontab）
推荐使用操作系统的 Crontab 来定时执行存储过程。
查看 OBClient 的安装位置
which obclient返回以下结果：
[root@iZbp1gspkxk2w9mxou46nvZ ~]# which obclient
/root/.oceanbase-all-in-one/obclient/u01/obclient/bin/obclient编辑当前用户的 crontab
编辑当前用户的 crontab
crontab -e添加以下内容到 Crontab 文件中：
# 每天凌晨1点执行销售报表
0 1 * * * /root/.oceanbase-all-in-one/obclient/u01/obclient/bin/obclient -h127.0.0.1 -P2881 -uroot@mysql_tenant -Dtest -e "CALL GenerateDailySalesReport();" > /home/admin/daily_report.log 2>&1手动执行测试
/root/.oceanbase-all-in-one/obclient/u01/obclient/bin/obclient -h127.0.0.1 -P2881 -uroot@mysql_tenant -Dtest -e "CALL GenerateDailySalesReport();" > /home/admin/daily_report.log 2>&1查看执行日志
cat /home/admin/daily_report.log想了解更多干货，可通过下方扫码关注
可扫码添加上智启元官方客服微信👇
