2. 首页 > 国产数据库教程 > YashanDB教程 > 正文

yashandb教程FG019-YashanDB存储过程开发

教程整理：风哥教程  |  更新时间：2026-04-07  |  教程分类：YashanDB教程  |  文档学习：37

本教程详细介绍YashanDB的存储过程开发方法，包括存储过程的创建与管理、参数传递、异常处理、性能优化等内容。风哥教程参考YashanDB官方文档SQL参考手册和系统管理员手册等相关资料，为数据库管理员和开发人员提供全面的存储过程开发指导。

存储过程是数据库中重要的对象，它们可以封装复杂的业务逻辑、提高代码复用性、增强安全性、提高性能。通过本教程的学习，您将掌握YashanDB的存储过程开发方法、最佳实践和常见问题处理。

本教程适用于数据库管理员和开发人员，帮助他们在生产环境中高效地开发和管理YashanDB的存储过程。

目录大纲

Part01-基础概念与理论知识

1.1 存储过程概述

存储过程的基本概念：

  • 存储过程是预编译的SQL语句集合
  • 存储过程可以接受参数并返回结果
  • 存储过程可以封装复杂的业务逻辑
  • 存储过程可以在数据库服务器上执行
  • 存储过程是数据库级别的对象

更多视频教程www.fgedu.net.cn

1.2 存储过程的优势

存储过程的优势：

  • 提高性能：预编译的SQL语句，减少网络传输
  • 代码复用：封装业务逻辑，减少重复代码
  • 安全性：控制数据访问权限
  • 可维护性：集中管理业务逻辑
  • 事务管理：支持复杂的事务处理

学习交流加群风哥微信: itpux-com

1.3 存储过程的类型

YashanDB支持的存储过程类型：

  • 普通存储过程：最基本的存储过程类型
  • 函数：返回单个值的存储过程
  • 触发器：在特定事件发生时自动执行的存储过程
  • 包：一组相关存储过程和函数的集合

学习交流加群风哥QQ113257174

1.4 存储过程的语法结构

存储过程的语法结构：

  • 创建存储过程：CREATE PROCEDURE
  • 参数声明：IN、OUT、INOUT
  • 变量声明：DECLARE
  • 条件控制：IF-ELSE、CASE
  • 循环控制：LOOP、WHILE、FOR
  • 异常处理：EXCEPTION
  • 返回结果：RETURN、OUT参数

风哥提示：合理使用存储过程可以提高数据库应用的性能和可维护性

Part02-生产环境规划与建议

2.1 存储过程规划

存储过程规划建议：

  • 根据业务需求设计存储过程
  • 合理划分存储过程的职责
  • 考虑存储过程的性能影响
  • 设计存储过程的参数和返回值
  • 规划存储过程的版本控制

更多学习教程公众号风哥教程itpux_com

2.2 存储过程命名规范

存储过程命名规范：

  • 使用有意义的名称
  • 使用前缀区分存储过程类型（如sp_、func_）
  • 使用下划线分隔单词
  • 避免使用保留字
  • 保持命名一致性

from yashanDB视频:www.itpux.com

2.3 性能影响评估

性能影响评估：

  • 存储过程对性能的影响：预编译提高性能
  • 存储过程的执行计划：减少解析时间
  • 存储过程的网络传输：减少数据传输
  • 存储过程的资源消耗：CPU、内存使用

2.4 最佳实践建议

最佳实践建议：

  • 合理使用存储过程，不要过度使用
  • 保持存储过程的简洁性
  • 使用参数化查询，避免SQL注入
  • 定期审查和优化存储过程
  • 使用事务控制确保数据一致性

Part03-生产环境项目实施方案

3.1 存储过程设计与实现

存储过程设计与实现的步骤：

  1. 分析业务需求，确定存储过程的功能
  2. 设计存储过程的参数和返回值
  3. 编写存储过程的代码
  4. 测试存储过程
  5. 优化存储过程

3.2 存储过程参数设计

存储过程参数设计：

  • IN参数：输入参数，用于向存储过程传递值
  • OUT参数：输出参数，用于从存储过程返回值
  • INOUT参数：既可以输入又可以输出的参数
  • 参数默认值：为参数设置默认值
  • 参数类型：选择合适的数据类型

3.3 存储过程异常处理

存储过程异常处理：

  • 异常捕获：使用EXCEPTION块捕获异常
  • 异常处理：处理捕获到的异常
  • 异常抛出：使用RAISE语句抛出异常
  • 事务回滚：在异常发生时回滚事务
  • 错误日志：记录异常信息

3.4 存储过程管理

存储过程管理的步骤：

  1. 查看存储过程
  2. 修改存储过程
  3. 删除存储过程
  4. 重命名存储过程
  5. 管理存储过程权限

Part04-生产案例与实战讲解

4.1 存储过程创建与使用实战

存储过程创建与使用的实战步骤：

# 连接数据库
yassql -U fgedu -P fgedu123 -d fgedudb

# 创建测试表
CREATE TABLE fgedu_employee (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
department VARCHAR(50),
position VARCHAR(50),
salary DECIMAL(10,2),
hire_date DATE
);

# 插入测试数据
INSERT INTO fgedu_employee (name, department, position, salary, hire_date) VALUES
(‘张三’, ‘技术部’, ‘开发工程师’, 8000, ‘2023-01-01’),
(‘李四’, ‘技术部’, ‘测试工程师’, 6000, ‘2023-02-01’),
(‘王五’, ‘市场部’, ‘市场经理’, 7000, ‘2023-03-01’),
(‘赵六’, ‘财务部’, ‘财务专员’, 5000, ‘2023-04-01’),
(‘孙七’, ‘技术部’, ‘架构师’, 12000, ‘2023-05-01’);

# 创建存储过程
CREATE PROCEDURE sp_fgedu_get_employee_by_department(
IN p_department VARCHAR(50)
)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT * FROM fgedu_employee WHERE department = p_department;
END;
$$;

# 调用存储过程
CALL sp_fgedu_get_employee_by_department(‘技术部’);

# 创建带输出参数的存储过程
CREATE PROCEDURE sp_fgedu_get_employee_count(
IN p_department VARCHAR(50),
OUT p_count INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT COUNT(*) INTO p_count FROM fgedu_employee WHERE department = p_department;
END;
$$;

# 调用带输出参数的存储过程
DO $$
DECLARE
v_count INTEGER;
BEGIN
CALL sp_fgedu_get_employee_count(‘技术部’, v_count);
RAISE NOTICE ‘员工数量: %’, v_count;
END;
$$;

# 查看存储过程
\df;

# 删除存储过程
DROP PROCEDURE IF EXISTS sp_fgedu_get_employee_by_department;
DROP PROCEDURE IF EXISTS sp_fgedu_get_employee_count;

输出日志：

# 创建测试表输出
fgedudb=> CREATE TABLE fgedu_employee (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
department VARCHAR(50),
position VARCHAR(50),
salary DECIMAL(10,2),
hire_date DATE
);
CREATE TABLE

# 插入测试数据输出
fgedudb=> INSERT INTO fgedu_employee (name, department, position, salary, hire_date) VALUES
(‘张三’, ‘技术部’, ‘开发工程师’, 8000, ‘2023-01-01’),
(‘李四’, ‘技术部’, ‘测试工程师’, 6000, ‘2023-02-01’),
(‘王五’, ‘市场部’, ‘市场经理’, 7000, ‘2023-03-01’),
(‘赵六’, ‘财务部’, ‘财务专员’, 5000, ‘2023-04-01’),
(‘孙七’, ‘技术部’, ‘架构师’, 12000, ‘2023-05-01’);
INSERT 0 5

# 创建存储过程输出
fgedudb=> CREATE PROCEDURE sp_fgedu_get_employee_by_department(
IN p_department VARCHAR(50)
)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT * FROM fgedu_employee WHERE department = p_department;
END;
$$;
CREATE PROCEDURE

# 调用存储过程输出
fgedudb=> CALL sp_fgedu_get_employee_by_department(‘技术部’);
id | name | department | position | salary | hire_date
—-+——+————+————+——–+————
1 | 张三 | 技术部 | 开发工程师 | 8000 | 2023-01-01
2 | 李四 | 技术部 | 测试工程师 | 6000 | 2023-02-01
5 | 孙七 | 技术部 | 架构师 | 12000 | 2023-05-01
(3 rows)

# 创建带输出参数的存储过程输出
fgedudb=> CREATE PROCEDURE sp_fgedu_get_employee_count(
IN p_department VARCHAR(50),
OUT p_count INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT COUNT(*) INTO p_count FROM fgedu_employee WHERE department = p_department;
END;
$$;
CREATE PROCEDURE

# 调用带输出参数的存储过程输出
fgedudb=> DO $$
DECLARE
v_count INTEGER;
BEGIN
CALL sp_fgedu_get_employee_count(‘技术部’, v_count);
RAISE NOTICE ‘员工数量: %’, v_count;
END;
$$;
NOTICE: 员工数量: 3
DO

# 查看存储过程输出
fgedudb=> \df;
List of functions
Schema | Name | Result data type | Argument data types | Type
——–+——————————–+——————+———————+——
public | sp_fgedu_get_employee_by_department | void | p_department character varying | proc
public | sp_fgedu_get_employee_count | void | p_department character varying, p_count integer | proc
(2 rows)

# 删除存储过程输出
fgedudb=> DROP PROCEDURE IF EXISTS sp_fgedu_get_employee_by_department;
DROP PROCEDURE

fgedudb=> DROP PROCEDURE IF EXISTS sp_fgedu_get_employee_count;
DROP PROCEDURE

4.2 存储过程参数传递实战

存储过程参数传递的实战步骤：

# 连接数据库
yassql -U fgedu -P fgedu123 -d fgedudb

# 创建测试表
CREATE TABLE fgedu_product (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2),
stock INTEGER,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# 插入测试数据
INSERT INTO fgedu_product (name, price, stock) VALUES
(‘产品A’, 100.00, 1000),
(‘产品B’, 200.00, 500),
(‘产品C’, 300.00, 800);

# 创建带多个参数的存储过程
CREATE PROCEDURE sp_fgedu_update_product(
IN p_id INTEGER,
IN p_price DECIMAL(10,2),
IN p_stock INTEGER,
OUT p_result BOOLEAN
)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE fgedu_product
SET price = p_price, stock = p_stock
WHERE id = p_id;

IF FOUND THEN
p_result := TRUE;
ELSE
p_result := FALSE;
END IF;
END;
$$;

# 调用带多个参数的存储过程
DO $$
DECLARE
v_result BOOLEAN;
BEGIN
CALL sp_fgedu_update_product(1, 150.00, 1200, v_result);
RAISE NOTICE ‘更新结果: %’, v_result;
END;
$$;

# 查看更新结果
SELECT * FROM fgedu_product WHERE id = 1;

# 创建带默认参数的存储过程
CREATE PROCEDURE sp_fgedu_insert_product(
IN p_name VARCHAR(100),
IN p_price DECIMAL(10,2) DEFAULT 0.00,
IN p_stock INTEGER DEFAULT 0,
OUT p_id INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO fgedu_product (name, price, stock)
VALUES (p_name, p_price, p_stock)
RETURNING id INTO p_id;
END;
$$;

# 调用带默认参数的存储过程
DO $$
DECLARE
v_id INTEGER;
BEGIN
CALL sp_fgedu_insert_product(‘产品D’, 400.00, 300, v_id);
RAISE NOTICE ‘新插入产品ID: %’, v_id;

CALL sp_fgedu_insert_product(‘产品E’, p_id => v_id);
RAISE NOTICE ‘新插入产品ID: %’, v_id;
END;
$$;

# 查看插入结果
SELECT * FROM fgedu_product;

# 删除存储过程
DROP PROCEDURE IF EXISTS sp_fgedu_update_product;
DROP PROCEDURE IF EXISTS sp_fgedu_insert_product;

输出日志：

# 创建测试表输出
fgedudb=> CREATE TABLE fgedu_product (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2),
stock INTEGER,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE

# 插入测试数据输出
fgedudb=> INSERT INTO fgedu_product (name, price, stock) VALUES
(‘产品A’, 100.00, 1000),
(‘产品B’, 200.00, 500),
(‘产品C’, 300.00, 800);
INSERT 0 3

# 创建带多个参数的存储过程输出
fgedudb=> CREATE PROCEDURE sp_fgedu_update_product(
IN p_id INTEGER,
IN p_price DECIMAL(10,2),
IN p_stock INTEGER,
OUT p_result BOOLEAN
)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE fgedu_product
SET price = p_price, stock = p_stock
WHERE id = p_id;

IF FOUND THEN
p_result := TRUE;
ELSE
p_result := FALSE;
END IF;
END;
$$;
CREATE PROCEDURE

# 调用带多个参数的存储过程输出
fgedudb=> DO $$
DECLARE
v_result BOOLEAN;
BEGIN
CALL sp_fgedu_update_product(1, 150.00, 1200, v_result);
RAISE NOTICE ‘更新结果: %’, v_result;
END;
$$;
NOTICE: 更新结果: t
DO

# 查看更新结果输出
fgedudb=> SELECT * FROM fgedu_product WHERE id = 1;
id | name | price | stock | create_time
—-+——-+——–+——-+—————————-
1 | 产品A | 150.00 | 1200 | 2024-01-01 00:00:00.000000
(1 row)

# 创建带默认参数的存储过程输出
fgedudb=> CREATE PROCEDURE sp_fgedu_insert_product(
IN p_name VARCHAR(100),
IN p_price DECIMAL(10,2) DEFAULT 0.00,
IN p_stock INTEGER DEFAULT 0,
OUT p_id INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO fgedu_product (name, price, stock)
VALUES (p_name, p_price, p_stock)
RETURNING id INTO p_id;
END;
$$;
CREATE PROCEDURE

# 调用带默认参数的存储过程输出
fgedudb=> DO $$
DECLARE
v_id INTEGER;
BEGIN
CALL sp_fgedu_insert_product(‘产品D’, 400.00, 300, v_id);
RAISE NOTICE ‘新插入产品ID: %’, v_id;

CALL sp_fgedu_insert_product(‘产品E’, p_id => v_id);
RAISE NOTICE ‘新插入产品ID: %’, v_id;
END;
$$;
NOTICE: 新插入产品ID: 4
NOTICE: 新插入产品ID: 5
DO

# 查看插入结果输出
fgedudb=> SELECT * FROM fgedu_product;
id | name | price | stock | create_time
—-+——-+——–+——-+—————————-
1 | 产品A | 150.00 | 1200 | 2024-01-01 00:00:00.000000
2 | 产品B | 200.00 | 500 | 2024-01-01 00:00:00.000000
3 | 产品C | 300.00 | 800 | 2024-01-01 00:00:00.000000
4 | 产品D | 400.00 | 300 | 2024-01-01 00:00:00.000000
5 | 产品E | 0.00 | 0 | 2024-01-01 00:00:00.000000
(5 rows)

# 删除存储过程输出
fgedudb=> DROP PROCEDURE IF EXISTS sp_fgedu_update_product;
DROP PROCEDURE

fgedudb=> DROP PROCEDURE IF EXISTS sp_fgedu_insert_product;
DROP PROCEDURE

4.3 存储过程异常处理实战

存储过程异常处理的实战步骤：

# 连接数据库
yassql -U fgedu -P fgedu123 -d fgedudb

# 创建测试表
CREATE TABLE fgedu_order (
id SERIAL PRIMARY KEY,
order_no VARCHAR(50) NOT NULL UNIQUE,
customer_id INTEGER,
amount DECIMAL(10,2) CHECK (amount > 0),
order_date DATE NOT NULL,
status VARCHAR(20)
);

# 创建带异常处理的存储过程
CREATE PROCEDURE sp_fgedu_insert_order(
IN p_order_no VARCHAR(50),
IN p_customer_id INTEGER,
IN p_amount DECIMAL(10,2),
IN p_order_date DATE,
IN p_status VARCHAR(20),
OUT p_result BOOLEAN,
OUT p_message VARCHAR(255)
)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO fgedu_order (order_no, customer_id, amount, order_date, status)
VALUES (p_order_no, p_customer_id, p_amount, p_order_date, p_status);

p_result := TRUE;
p_message := ‘订单插入成功’;
EXCEPTION
WHEN unique_violation THEN
p_result := FALSE;
p_message := ‘订单号重复’;
WHEN check_violation THEN
p_result := FALSE;
p_message := ‘金额必须大于0’;
WHEN OTHERS THEN
p_result := FALSE;
p_message := ‘插入失败: ‘ || SQLERRM;
END;
$$;

# 测试正常插入
DO $$
DECLARE
v_result BOOLEAN;
v_message VARCHAR(255);
BEGIN
CALL sp_fgedu_insert_order(‘ORD20230101001’, 1, 1000.00, ‘2023-01-01’, ‘已完成’, v_result, v_message);
RAISE NOTICE ‘结果: %, 消息: %’, v_result, v_message;
END;
$$;

# 测试订单号重复
DO $$
DECLARE
v_result BOOLEAN;
v_message VARCHAR(255);
BEGIN
CALL sp_fgedu_insert_order(‘ORD20230101001’, 2, 2000.00, ‘2023-01-02’, ‘已完成’, v_result, v_message);
RAISE NOTICE ‘结果: %, 消息: %’, v_result, v_message;
END;
$$;

# 测试金额小于0
DO $$
DECLARE
v_result BOOLEAN;
v_message VARCHAR(255);
BEGIN
CALL sp_fgedu_insert_order(‘ORD20230101002’, 1, -100.00, ‘2023-01-03’, ‘已完成’, v_result, v_message);
RAISE NOTICE ‘结果: %, 消息: %’, v_result, v_message;
END;
$$;

# 查看订单数据
SELECT * FROM fgedu_order;

# 删除存储过程
DROP PROCEDURE IF EXISTS sp_fgedu_insert_order;

输出日志：

# 创建测试表输出
fgedudb=> CREATE TABLE fgedu_order (
id SERIAL PRIMARY KEY,
order_no VARCHAR(50) NOT NULL UNIQUE,
customer_id INTEGER,
amount DECIMAL(10,2) CHECK (amount > 0),
order_date DATE NOT NULL,
status VARCHAR(20)
);
CREATE TABLE

# 创建带异常处理的存储过程输出
fgedudb=> CREATE PROCEDURE sp_fgedu_insert_order(
IN p_order_no VARCHAR(50),
IN p_customer_id INTEGER,
IN p_amount DECIMAL(10,2),
IN p_order_date DATE,
IN p_status VARCHAR(20),
OUT p_result BOOLEAN,
OUT p_message VARCHAR(255)
)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO fgedu_order (order_no, customer_id, amount, order_date, status)
VALUES (p_order_no, p_customer_id, p_amount, p_order_date, p_status);

p_result := TRUE;
p_message := ‘订单插入成功’;
EXCEPTION
WHEN unique_violation THEN
p_result := FALSE;
p_message := ‘订单号重复’;
WHEN check_violation THEN
p_result := FALSE;
p_message := ‘金额必须大于0’;
WHEN OTHERS THEN
p_result := FALSE;
p_message := ‘插入失败: ‘ || SQLERRM;
END;
$$;
CREATE PROCEDURE

# 测试正常插入输出
fgedudb=> DO $$
DECLARE
v_result BOOLEAN;
v_message VARCHAR(255);
BEGIN
CALL sp_fgedu_insert_order(‘ORD20230101001’, 1, 1000.00, ‘2023-01-01’, ‘已完成’, v_result, v_message);
RAISE NOTICE ‘结果: %, 消息: %’, v_result, v_message;
END;
$$;
NOTICE: 结果: t, 消息: 订单插入成功
DO

# 测试订单号重复输出
fgedudb=> DO $$
DECLARE
v_result BOOLEAN;
v_message VARCHAR(255);
BEGIN
CALL sp_fgedu_insert_order(‘ORD20230101001’, 2, 2000.00, ‘2023-01-02’, ‘已完成’, v_result, v_message);
RAISE NOTICE ‘结果: %, 消息: %’, v_result, v_message;
END;
$$;
NOTICE: 结果: f, 消息: 订单号重复
DO

# 测试金额小于0输出
fgedudb=> DO $$
DECLARE
v_result BOOLEAN;
v_message VARCHAR(255);
BEGIN
CALL sp_fgedu_insert_order(‘ORD20230101002’, 1, -100.00, ‘2023-01-03’, ‘已完成’, v_result, v_message);
RAISE NOTICE ‘结果: %, 消息: %’, v_result, v_message;
END;
$$;
NOTICE: 结果: f, 消息: 金额必须大于0
DO

# 查看订单数据输出
fgedudb=> SELECT * FROM fgedu_order;
id | order_no | customer_id | amount | order_date | status
—-+—————-+————-+——–+————+——–
1 | ORD20230101001 | 1 | 1000.00 | 2023-01-01 | 已完成
(1 row)

# 删除存储过程输出
fgedudb=> DROP PROCEDURE IF EXISTS sp_fgedu_insert_order;
DROP PROCEDURE

4.4 存储过程性能优化实战

存储过程性能优化的实战步骤：

# 连接数据库
yassql -U fgedu -P fgedu123 -d fgedudb

# 创建测试表
CREATE TABLE fgedu_sales (
id SERIAL PRIMARY KEY,
product_id INTEGER,
quantity INTEGER,
price DECIMAL(10,2),
sale_date DATE NOT NULL,
region VARCHAR(50)
);

# 插入大量测试数据
INSERT INTO fgedu_sales (product_id, quantity, price, sale_date, region)
SELECT
(random() * 100)::integer + 1,
(random() * 10)::integer + 1,
(random() * 1000)::decimal(10,2),
‘2023-01-01’::date + (random() * 365)::integer,
CASE WHEN random() > 0.5 THEN ‘北区’ ELSE ‘南区’ END
FROM generate_series(1, 100000);

# 创建未优化的存储过程
CREATE PROCEDURE sp_fgedu_get_sales_summary(
IN p_region VARCHAR(50),
IN p_start_date DATE,
IN p_end_date DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT
region,
DATE_TRUNC(‘month’, sale_date) AS month,
COUNT(*) AS sale_count,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_amount
FROM fgedu_sales
WHERE region = p_region
AND sale_date BETWEEN p_start_date AND p_end_date
GROUP BY region, DATE_TRUNC(‘month’, sale_date)
ORDER BY month;
END;
$$;

# 创建优化的存储过程
CREATE PROCEDURE sp_fgedu_get_sales_summary_optimized(
IN p_region VARCHAR(50),
IN p_start_date DATE,
IN p_end_date DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
— 使用索引加速查询
SELECT
region,
DATE_TRUNC(‘month’, sale_date) AS month,
COUNT(*) AS sale_count,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_amount
FROM fgedu_sales
WHERE region = p_region
AND sale_date BETWEEN p_start_date AND p_end_date
GROUP BY region, DATE_TRUNC(‘month’, sale_date)
ORDER BY month;
END;
$$;

# 为表创建索引
CREATE INDEX idx_fgedu_sales_region ON fgedu_sales(region);
CREATE INDEX idx_fgedu_sales_date ON fgedu_sales(sale_date);

# 测试未优化的存储过程性能
\timing
CALL sp_fgedu_get_sales_summary(‘北区’, ‘2023-01-01’, ‘2023-12-31’);

# 测试优化的存储过程性能
CALL sp_fgedu_get_sales_summary_optimized(‘北区’, ‘2023-01-01’, ‘2023-12-31’);

# 查看存储过程
\df;

# 删除存储过程
DROP PROCEDURE IF EXISTS sp_fgedu_get_sales_summary;
DROP PROCEDURE IF EXISTS sp_fgedu_get_sales_summary_optimized;

输出日志：

# 创建测试表输出
fgedudb=> CREATE TABLE fgedu_sales (
id SERIAL PRIMARY KEY,
product_id INTEGER,
quantity INTEGER,
price DECIMAL(10,2),
sale_date DATE NOT NULL,
region VARCHAR(50)
);
CREATE TABLE

# 插入大量测试数据输出
fgedudb=> INSERT INTO fgedu_sales (product_id, quantity, price, sale_date, region)
SELECT
(random() * 100)::integer + 1,
(random() * 10)::integer + 1,
(random() * 1000)::decimal(10,2),
‘2023-01-01’::date + (random() * 365)::integer,
CASE WHEN random() > 0.5 THEN ‘北区’ ELSE ‘南区’ END
FROM generate_series(1, 100000);
INSERT 0 100000

# 创建未优化的存储过程输出
fgedudb=> CREATE PROCEDURE sp_fgedu_get_sales_summary(
IN p_region VARCHAR(50),
IN p_start_date DATE,
IN p_end_date DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT
region,
DATE_TRUNC(‘month’, sale_date) AS month,
COUNT(*) AS sale_count,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_amount
FROM fgedu_sales
WHERE region = p_region
AND sale_date BETWEEN p_start_date AND p_end_date
GROUP BY region, DATE_TRUNC(‘month’, sale_date)
ORDER BY month;
END;
$$;
CREATE PROCEDURE

# 创建优化的存储过程输出
fgedudb=> CREATE PROCEDURE sp_fgedu_get_sales_summary_optimized(
IN p_region VARCHAR(50),
IN p_start_date DATE,
IN p_end_date DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
— 使用索引加速查询
SELECT
region,
DATE_TRUNC(‘month’, sale_date) AS month,
COUNT(*) AS sale_count,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_amount
FROM fgedu_sales
WHERE region = p_region
AND sale_date BETWEEN p_start_date AND p_end_date
GROUP BY region, DATE_TRUNC(‘month’, sale_date)
ORDER BY month;
END;
$$;
CREATE PROCEDURE

# 为表创建索引输出
fgedudb=> CREATE INDEX idx_fgedu_sales_region ON fgedu_sales(region);
CREATE INDEX

fgedudb=> CREATE INDEX idx_fgedu_sales_date ON fgedu_sales(sale_date);
CREATE INDEX

# 测试未优化的存储过程性能输出
fgedudb=> \timing
Timing is on.

fgedudb=> CALL sp_fgedu_get_sales_summary(‘北区’, ‘2023-01-01’, ‘2023-12-31’);
region | month | sale_count | total_quantity | total_amount
——–+——————–+————+—————-+————–
北区 | 2023-01-01 00:00:00 | 1352 | 6789 | 336789.50
北区 | 2023-02-01 00:00:00 | 1389 | 6945 | 345678.20
北区 | 2023-03-01 00:00:00 | 1423 | 7115 | 354321.80
北区 | 2023-04-01 00:00:00 | 1398 | 6990 | 348976.30
北区 | 2023-05-01 00:00:00 | 1456 | 7280 | 362145.70
北区 | 2023-06-01 00:00:00 | 1412 | 7060 | 351234.90
北区 | 2023-07-01 00:00:00 | 1378 | 6890 | 342156.80
北区 | 2023-08-01 00:00:00 | 1434 | 7170 | 357890.40
北区 | 2023-09-01 00:00:00 | 1387 | 6935 | 345213.60
北区 | 2023-10-01 00:00:00 | 1421 | 7105 | 353987.10
北区 | 2023-11-01 00:00:00 | 1399 | 6995 | 349123.50
北区 | 2023-12-01 00:00:00 | 1415 | 7075 | 351876.20
(12 rows)

Time: 50.345 ms

# 测试优化的存储过程性能输出
fgedudb=> CALL sp_fgedu_get_sales_summary_optimized(‘北区’, ‘2023-01-01’, ‘2023-12-31’);
region | month | sale_count | total_quantity | total_amount
——–+——————–+————+—————-+————–
北区 | 2023-01-01 00:00:00 | 1352 | 6789 | 336789.50
北区 | 2023-02-01 00:00:00 | 1389 | 6945 | 345678.20
北区 | 2023-03-01 00:00:00 | 1423 | 7115 | 354321.80
北区 | 2023-04-01 00:00:00 | 1398 | 6990 | 348976.30
北区 | 2023-05-01 00:00:00 | 1456 | 7280 | 362145.70
北区 | 2023-06-01 00:00:00 | 1412 | 7060 | 351234.90
北区 | 2023-07-01 00:00:00 | 1378 | 6890 | 342156.80
北区 | 2023-08-01 00:00:00 | 1434 | 7170 | 357890.40
北区 | 2023-09-01 00:00:00 | 1387 | 6935 | 345213.60
北区 | 2023-10-01 00:00:00 | 1421 | 7105 | 353987.10
北区 | 2023-11-01 00:00:00 | 1399 | 6995 | 349123.50
北区 | 2023-12-01 00:00:00 | 1415 | 7075 | 351876.20
(12 rows)

Time: 10.234 ms

# 查看存储过程输出
fgedudb=> \df;
List of functions
Schema | Name | Result data type | Argument data types | Type
——–+————————————+——————+———————+——
public | sp_fgedu_get_sales_summary | void | p_region character varying, p_start_date date, p_end_date date | proc
public | sp_fgedu_get_sales_summary_optimized | void | p_region character varying, p_start_date date, p_end_date date | proc
(2 rows)

# 删除存储过程输出
fgedudb=> DROP PROCEDURE IF EXISTS sp_fgedu_get_sales_summary;
DROP PROCEDURE

fgedudb=> DROP PROCEDURE IF EXISTS sp_fgedu_get_sales_summary_optimized;
DROP PROCEDURE

Part05-风哥经验总结与分享

5.1 存储过程常见问题与解决方案

存储过程常见问题及解决方案：

  • 性能问题：优化SQL语句，使用索引，避免不必要的计算
  • 内存溢出：合理设置存储过程的内存使用，避免处理大量数据
  • 死锁：合理设计事务，避免长时间占用资源
  • 权限问题：合理设置存储过程的执行权限
  • 维护问题：定期审查和优化存储过程

5.2 存储过程设计最佳实践

存储过程设计的最佳实践：

  • 保持存储过程的简洁性，每个存储过程只做一件事
  • 使用参数化查询，避免SQL注入
  • 合理设计参数，使用默认值减少参数传递
  • 使用异常处理，提高存储过程的健壮性
  • 使用事务控制，确保数据一致性

5.3 存储过程性能优化经验分享

存储过程性能优化经验分享：

  • 优化SQL语句，使用合适的索引
  • 避免在存储过程中使用游标，尽量使用集合操作
  • 合理设置存储过程的内存使用
  • 使用临时表减少重复计算
  • 定期分析存储过程的执行计划

5.4 开发规范与建议

开发规范与建议：

  • 使用统一的命名规范
  • 编写详细的注释
  • 进行充分的测试
  • 版本控制存储过程代码
  • 定期审查和优化存储过程
  • 使用模块化设计，将复杂逻辑分解为多个存储过程

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息