opengauss教程FG016-openGauss存储过程与函数
目录大纲
Part01-基础概念与理论知识
1.1 存储过程概述
存储过程是一组预编译的SQL语句，了解存储过程的概念和使用方法对于数据库的操作和管理至关重要。风哥教程参考opengauss官方文档，存储过程具有以下特点：
- 预编译：存储过程在创建时被编译，执行时不需要重新编译
- 可重用：存储过程可以被多次调用，提高代码复用性
- 模块化：将复杂的业务逻辑封装在存储过程中，提高代码的可维护性
- 安全性：存储过程可以控制用户对数据的访问权限
- 性能优化：存储过程可以减少网络传输量，提高执行效率
1.2 函数概述
函数是一种特殊的存储过程，用于返回一个值，了解函数的概念和使用方法对于数据库的操作和管理也非常重要：
- 返回值：函数必须返回一个值
- 可嵌入：函数可以嵌入到SQL语句中使用
- 模块化：将复杂的计算逻辑封装在函数中，提高代码的可维护性
- 可重用：函数可以被多次调用，提高代码复用性
1.3 存储过程与函数的区别
存储过程与函数的主要区别：
- 返回值：
- 存储过程：可以返回多个值，也可以不返回值
- 函数：必须返回一个值
- 调用方式：
- 存储过程：使用CALL语句调用
- 函数：可以嵌入到SQL语句中使用，也可以使用SELECT语句调用
- 使用场景：
- 存储过程：适用于复杂的业务逻辑，如数据处理、事务管理等
- 函数：适用于计算、转换等操作，如日期处理、字符串处理等
风哥提示：合理使用存储过程和函数可以提高数据库的性能和可维护性。
Part02-生产环境规划与建议
2.1 存储过程与函数设计规划
生产环境存储过程与函数设计规划
- 命名规范：
- 存储过程：使用proc_前缀，如proc_update_employee
- 函数：使用func_前缀，如func_calculate_salary
- 使用小写字母和下划线
- 参数设计：
- 参数名称使用p_前缀，如p_employee_id
- 明确参数类型和默认值
- 合理设计参数数量，避免参数过多
- 逻辑设计：
- 将复杂的业务逻辑分解为多个存储过程或函数
- 使用模块化设计，提高代码的可维护性
- 添加适当的错误处理
风哥提示：
2.2 性能优化建议
生产环境的存储过程与函数性能优化建议：
- 代码优化：
- 减少存储过程和函数的复杂度
- 避免在存储过程中使用游标
- 合理使用临时表
- 执行计划优化：
- 使用参数化查询
- 合理使用索引
- 避免全表扫描
- 资源管理：
- 控制存储过程和函数的执行时间
- 避免长时间持有锁
- 合理设置事务隔离级别
2.3 最佳实践建议
存储过程与函数使用的最佳实践建议：
- 安全性：学习交流加群风哥微信: itpux-com
- 使用参数化查询，避免SQL注入
- 限制存储过程和函数的权限
- 使用视图控制数据访问
- 可维护性：
- 编写清晰的注释
- 使用一致的命名规范
- 定期审查和优化存储过程和函数
- 可靠性：
- 添加错误处理
- 使用事务确保数据一致性
- 进行充分的测试
Part03-生产环境项目实施方案
3.1 存储过程创建与管理
# 创建存储过程
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss
Password for user opengauss:
gsql ((openGauss 5.0.0 build 12345) compiled at 2024-01-01 00:00:00)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type “help” for help.
fgedudb=# CREATE OR REPLACE PROCEDURE proc_update_employee_salary(p_employee_id INTEGER, p_percentage DECIMAL)
fgedudb-# AS
fgedudb-# $$
fgedudb$# BEGIN
fgedudb$# UPDATE fgedu_employee
fgedudb$# SET salary = salary * (1 + p_percentage / 100)
fgedudb$# WHERE id = p_employee_id;
fgedudb$# COMMIT;
学习交流加群风哥QQ113257174
fgedudb$# END;
fgedudb$# $$
fgedudb-# LANGUAGE plpgsql;
CREATE PROCEDURE
# 调用存储过程
fgedudb=# CALL proc_update_employee_salary(1, 10);
CALL
# 删除存储过程
fgedudb=# DROP PROCEDURE IF EXISTS proc_update_employee_salary(INTEGER, DECIMAL);
DROP PROCEDURE
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss
Password for user opengauss:
gsql ((openGauss 5.0.0 build 12345) compiled at 2024-01-01 00:00:00)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type “help” for help.
fgedudb=# CREATE OR REPLACE PROCEDURE proc_update_employee_salary(p_employee_id INTEGER, p_percentage DECIMAL)
fgedudb-# AS
fgedudb-# $$
fgedudb$# BEGIN
fgedudb$# UPDATE fgedu_employee
fgedudb$# SET salary = salary * (1 + p_percentage / 100)
fgedudb$# WHERE id = p_employee_id;
fgedudb$# COMMIT;
学习交流加群风哥QQ113257174
fgedudb$# END;
fgedudb$# $$
fgedudb-# LANGUAGE plpgsql;
CREATE PROCEDURE
# 调用存储过程
fgedudb=# CALL proc_update_employee_salary(1, 10);
CALL
# 删除存储过程
fgedudb=# DROP PROCEDURE IF EXISTS proc_update_employee_salary(INTEGER, DECIMAL);
DROP PROCEDURE
3.2 函数创建与管理
# 创建函数
fgedudb=# CREATE OR REPLACE FUNCTION func_calculate_bonus(p_salary DECIMAL, p_performance INTEGER)
fgedudb-# RETURNS DECIMAL
fgedudb-# AS
fgedudb-# $$
fgedudb$# DECLARE
fgedudb$# v_bonus DECIMAL;
fgedudb$# BEGIN
fgedudb$# IF p_performance >= 90 THEN
fgedudb$# v_bonus := p_salary * 0.2;
fgedudb$# ELSIF p_performance >= 80 THEN
fgedudb$# v_bonus := p_salary * 0.15;
fgedudb$# ELSIF p_performance >= 70 THEN
fgedudb$# v_bonus := p_salary * 0.1;
fgedudb$# ELSE
fgedudb$# v_bonus := p_salary * 0.05;
fgedudb$# END IF;
fgedudb$# RETURN v_bonus;
fgedudb$# END;
fgedudb$# $$
fgedudb-# LANGUAGE plpgsql;
CREATE FUNCTION
# 调用函数
fgedudb=# SELECT func_calculate_bonus(10000.00, 95);
func_calculate_bonus
———————
更多视频教程www.fgedu.net.cn
2000.00
(1 row)
# 删除函数
fgedudb=# DROP FUNCTION IF EXISTS func_calculate_bonus(DECIMAL, INTEGER);
DROP FUNCTION
fgedudb=# CREATE OR REPLACE FUNCTION func_calculate_bonus(p_salary DECIMAL, p_performance INTEGER)
fgedudb-# RETURNS DECIMAL
fgedudb-# AS
fgedudb-# $$
fgedudb$# DECLARE
fgedudb$# v_bonus DECIMAL;
fgedudb$# BEGIN
fgedudb$# IF p_performance >= 90 THEN
fgedudb$# v_bonus := p_salary * 0.2;
fgedudb$# ELSIF p_performance >= 80 THEN
fgedudb$# v_bonus := p_salary * 0.15;
fgedudb$# ELSIF p_performance >= 70 THEN
fgedudb$# v_bonus := p_salary * 0.1;
fgedudb$# ELSE
fgedudb$# v_bonus := p_salary * 0.05;
fgedudb$# END IF;
fgedudb$# RETURN v_bonus;
fgedudb$# END;
fgedudb$# $$
fgedudb-# LANGUAGE plpgsql;
CREATE FUNCTION
# 调用函数
fgedudb=# SELECT func_calculate_bonus(10000.00, 95);
func_calculate_bonus
———————
更多视频教程www.fgedu.net.cn
2000.00
(1 row)
# 删除函数
fgedudb=# DROP FUNCTION IF EXISTS func_calculate_bonus(DECIMAL, INTEGER);
DROP FUNCTION
3.3 参数传递与返回值
# 创建带输出参数的存储过程
fgedudb=# CREATE OR REPLACE PROCEDURE proc_get_employee_info(p_employee_id INTEGER, OUT p_name VARCHAR, OUT p_salary DECIMAL)
fgedudb-# AS
fgedudb-# $$
fgedudb$# BEGIN
fgedudb$# SELECT name, salary INTO p_name, p_salary
fgedudb$# FROM fgedu_employee
fgedudb$# WHERE id = p_employee_id;
fgedudb$# END;
fgedudb$# $$
fgedudb-# LANGUAGE plpgsql;
CREATE PROCEDURE
# 调用带输出参数的存储过程
fgedudb=# DO
fgedudb-# $$
fgedudb$# DECLARE
fgedudb$# v_name VARCHAR;
fgedudb$# v_salary DECIMAL;
fgedudb$# BEGIN
fgedudb$# CALL proc_get_employee_info(1, v_name, v_salary);
fgedudb$# RAISE NOTICE ‘Employee: %, Salary: %’, v_name, v_salary;
fgedudb$# END;
fgedudb$# $$
fgedudb-# LANGUAGE plpgsql;
NOTICE: Employee: Zhang San, Salary: 9300.00
DO
fgedudb=# CREATE OR REPLACE PROCEDURE proc_get_employee_info(p_employee_id INTEGER, OUT p_name VARCHAR, OUT p_salary DECIMAL)
fgedudb-# AS
fgedudb-# $$
fgedudb$# BEGIN
fgedudb$# SELECT name, salary INTO p_name, p_salary
fgedudb$# FROM fgedu_employee
fgedudb$# WHERE id = p_employee_id;
fgedudb$# END;
fgedudb$# $$
fgedudb-# LANGUAGE plpgsql;
CREATE PROCEDURE
# 调用带输出参数的存储过程
fgedudb=# DO
fgedudb-# $$
fgedudb$# DECLARE
fgedudb$# v_name VARCHAR;
fgedudb$# v_salary DECIMAL;
fgedudb$# BEGIN
fgedudb$# CALL proc_get_employee_info(1, v_name, v_salary);
fgedudb$# RAISE NOTICE ‘Employee: %, Salary: %’, v_name, v_salary;
fgedudb$# END;
fgedudb$# $$
fgedudb-# LANGUAGE plpgsql;
NOTICE: Employee: Zhang San, Salary: 9300.00
DO
Part04-生产案例与实战讲解
4.1 存储过程实战
更多学习教程公众号风哥教程itpux_com
# 创建员工管理存储过程
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE OR REPLACE PROCEDURE proc_employee_management(p_action VARCHAR, p_id INTEGER DEFAULT NULL, p_name VARCHAR DEFAULT NULL, p_age INTEGER DEFAULT NULL, p_dept_id INTEGER DEFAULT NULL, p_salary DECIMAL DEFAULT NULL) AS $$ BEGIN IF p_action = ‘insert’ THEN INSERT INTO fgedu_employee (name, age, dept_id, salary) VALUES (p_name, p_age, p_dept_id, p_salary); ELSIF p_action = ‘update’ THEN UPDATE fgedu_employee SET name = p_name, age = p_age, dept_id = p_dept_id, salary = p_salary WHERE id = p_id; ELSIF p_action = ‘delete’ THEN DELETE FROM fgedu_employee WHERE id = p_id; END IF; COMMIT;
END; $$ LANGUAGE plpgsql;
Password for user opengauss:
CREATE PROCEDURE
# 调用存储过程插入员工
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CALL proc_employee_management(‘insert’, NULL, ‘Sun Qi’, 28, 1, 7500.00);”
Password for user opengauss:
CALL
# 调用存储过程更新员工
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CALL proc_employee_management(‘update’, 5, ‘Sun Qi’, 29, 1, 8000.00);”
Password for user opengauss:
CALL
# 调用存储过程删除员工
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CALL proc_employee_management(‘delete’, 5);”
Password for user opengauss:
CALL
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE OR REPLACE PROCEDURE proc_employee_management(p_action VARCHAR, p_id INTEGER DEFAULT NULL, p_name VARCHAR DEFAULT NULL, p_age INTEGER DEFAULT NULL, p_dept_id INTEGER DEFAULT NULL, p_salary DECIMAL DEFAULT NULL) AS $$ BEGIN IF p_action = ‘insert’ THEN INSERT INTO fgedu_employee (name, age, dept_id, salary) VALUES (p_name, p_age, p_dept_id, p_salary); ELSIF p_action = ‘update’ THEN UPDATE fgedu_employee SET name = p_name, age = p_age, dept_id = p_dept_id, salary = p_salary WHERE id = p_id; ELSIF p_action = ‘delete’ THEN DELETE FROM fgedu_employee WHERE id = p_id; END IF; COMMIT;
END; $$ LANGUAGE plpgsql;
“
Password for user opengauss:
CREATE PROCEDURE
# 调用存储过程插入员工
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CALL proc_employee_management(‘insert’, NULL, ‘Sun Qi’, 28, 1, 7500.00);”
Password for user opengauss:
CALL
# 调用存储过程更新员工
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CALL proc_employee_management(‘update’, 5, ‘Sun Qi’, 29, 1, 8000.00);”
Password for user opengauss:
CALL
# 调用存储过程删除员工
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CALL proc_employee_management(‘delete’, 5);”
Password for user opengauss:
CALL
4.2 函数实战
# 创建部门统计函数
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE OR REPLACE FUNCTION func_department_statistics(p_dept_id INTEGER) RETURNS TABLE(dept_name VARCHAR, employee_count INTEGER, avg_salary DECIMAL) AS $$ BEGIN RETURN QUERY SELECT d.name, COUNT(e.id), AVG(e.salary) FROM fgedu_department d LEFT JOIN fgedu_employee e ON d.id = e.dept_id WHERE d.id = p_dept_id GROUP BY d.name; END; $$ LANGUAGE plpgsql;
“
Password for user opengauss:
CREATE FUNCTION
# 调用函数
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “SELECT * FROM func_department_statistics(1);
“
Password for user opengauss:
dept_name | employee_count | avg_salary
———–+—————-+———————
from DB视频:www.itpux.com
IT | 2 | 8800.00000000000000
(1 row)
# 创建日期处理函数
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE OR REPLACE FUNCTION func_get_workdays(p_start_date DATE, p_end_date DATE) RETURNS INTEGER AS $$ DECLARE v_workdays INTEGER := 0; v_current_date DATE := p_start_date; BEGIN WHILE v_current_date <= p_end_date LOOP IF EXTRACT(DOW FROM v_current_date) NOT IN (0, 6) THEN v_workdays := v_workdays + 1; END IF; v_current_date := v_current_date + INTERVAL '1 day'; END LOOP; RETURN v_workdays; END; $$ LANGUAGE plpgsql;
“
Password for user opengauss:
CREATE FUNCTION
# 调用日期处理函数
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “SELECT func_get_workdays(‘2024-01-01’, ‘2024-01-31’);
“
Password for user opengauss:
func_get_workdays
——————-
22
(1 row)
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE OR REPLACE FUNCTION func_department_statistics(p_dept_id INTEGER) RETURNS TABLE(dept_name VARCHAR, employee_count INTEGER, avg_salary DECIMAL) AS $$ BEGIN RETURN QUERY SELECT d.name, COUNT(e.id), AVG(e.salary) FROM fgedu_department d LEFT JOIN fgedu_employee e ON d.id = e.dept_id WHERE d.id = p_dept_id GROUP BY d.name; END; $$ LANGUAGE plpgsql;
“
Password for user opengauss:
CREATE FUNCTION
# 调用函数
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “SELECT * FROM func_department_statistics(1);
“
Password for user opengauss:
dept_name | employee_count | avg_salary
———–+—————-+———————
from DB视频:www.itpux.com
IT | 2 | 8800.00000000000000
(1 row)
# 创建日期处理函数
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE OR REPLACE FUNCTION func_get_workdays(p_start_date DATE, p_end_date DATE) RETURNS INTEGER AS $$ DECLARE v_workdays INTEGER := 0; v_current_date DATE := p_start_date; BEGIN WHILE v_current_date <= p_end_date LOOP IF EXTRACT(DOW FROM v_current_date) NOT IN (0, 6) THEN v_workdays := v_workdays + 1; END IF; v_current_date := v_current_date + INTERVAL '1 day'; END LOOP; RETURN v_workdays; END; $$ LANGUAGE plpgsql;
“
Password for user opengauss:
CREATE FUNCTION
# 调用日期处理函数
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “SELECT func_get_workdays(‘2024-01-01’, ‘2024-01-31’);
“
Password for user opengauss:
func_get_workdays
——————-
22
(1 row)
4.3 性能优化实战
# 创建优化的存储过程
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE OR REPLACE PROCEDURE proc_batch_update_salary(p_dept_id INTEGER, p_percentage DECIMAL) AS $$ BEGIN — 使用批量更新，减少事务开销 UPDATE fgedu_employee SET salary = salary * (1 + p_percentage / 100) WHERE dept_id = p_dept_id; COMMIT;
END; $$ LANGUAGE plpgsql;
Password for user opengauss:
CREATE PROCEDURE
# 测试存储过程性能
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “EXPLAIN ANALYZE CALL proc_batch_update_salary(1, 5);”
Password for user opengauss:
QUERY PLAN
—————————————————————————–
Update on fgedu_employee (cost=0.29..8.30 rows=1 width=37)
-> Index Scan using idx_fgedu_employee_dept on fgedu_employee (cost=0.29..8.30 rows=1 width=37)
Index Cond: (dept_id = 1)
Planning Time: 0.123 ms
Execution Time: 0.234 ms
(5 rows)
# 创建使用临时表的存储过程
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE OR REPLACE PROCEDURE proc_complex_calculation() AS $$ DECLARE BEGIN — 创建临时表 CREATE TEMP TABLE temp_result AS SELECT dept_id, AVG(salary) as avg_salary FROM fgedu_employee GROUP BY dept_id; — 使用临时表进行后续计算 UPDATE fgedu_department d SET description = ‘Average salary: ‘ || (SELECT avg_salary FROM temp_result WHERE dept_id = d.id) WHERE EXISTS (SELECT 1 FROM temp_result WHERE dept_id = d.id); — 清理临时表 DROP TABLE IF EXISTS temp_result; COMMIT;
END; $$ LANGUAGE plpgsql;
Password for user opengauss:
CREATE PROCEDURE
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE OR REPLACE PROCEDURE proc_batch_update_salary(p_dept_id INTEGER, p_percentage DECIMAL) AS $$ BEGIN — 使用批量更新，减少事务开销 UPDATE fgedu_employee SET salary = salary * (1 + p_percentage / 100) WHERE dept_id = p_dept_id; COMMIT;
END; $$ LANGUAGE plpgsql;
“
Password for user opengauss:
CREATE PROCEDURE
# 测试存储过程性能
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “EXPLAIN ANALYZE CALL proc_batch_update_salary(1, 5);”
Password for user opengauss:
QUERY PLAN
—————————————————————————–
Update on fgedu_employee (cost=0.29..8.30 rows=1 width=37)
-> Index Scan using idx_fgedu_employee_dept on fgedu_employee (cost=0.29..8.30 rows=1 width=37)
Index Cond: (dept_id = 1)
Planning Time: 0.123 ms
Execution Time: 0.234 ms
(5 rows)
# 创建使用临时表的存储过程
[opengauss@fgedu.net.cn ~]$ gsql -d fgedudb -U opengauss -c “CREATE OR REPLACE PROCEDURE proc_complex_calculation() AS $$ DECLARE BEGIN — 创建临时表 CREATE TEMP TABLE temp_result AS SELECT dept_id, AVG(salary) as avg_salary FROM fgedu_employee GROUP BY dept_id; — 使用临时表进行后续计算 UPDATE fgedu_department d SET description = ‘Average salary: ‘ || (SELECT avg_salary FROM temp_result WHERE dept_id = d.id) WHERE EXISTS (SELECT 1 FROM temp_result WHERE dept_id = d.id); — 清理临时表 DROP TABLE IF EXISTS temp_result; COMMIT;
END; $$ LANGUAGE plpgsql;
“
Password for user opengauss:
CREATE PROCEDURE
Part05-风哥经验总结与分享
5.1 存储过程与函数编写技巧
- 命名规范：
- 使用有意义的名称
- 使用一致的命名规范
- 避免使用保留字
- 代码结构：
- 使用适当的缩进和换行
- 添加清晰的注释
- 使用模块化设计
- 参数设计：
- 使用有意义的参数名称
- 明确参数类型和默认值
- 合理设计参数数量
5.2 性能优化要点
- 代码优化：
- 减少存储过程和函数的复杂度
- 避免在存储过程中使用游标
- 合理使用临时表
- 避免在循环中执行SQL语句
- 执行计划优化：
- 使用参数化查询
- 合理使用索引
- 避免全表扫描
- 使用批量操作
- 资源管理：
- 控制存储过程和函数的执行时间
- 避免长时间持有锁
- 合理设置事务隔离级别
- 定期清理临时表和游标
5.3 常见问题与解决方案
常见存储过程与函数问题及解决方法
- 问题1：存储过程执行缓慢
- 症状：存储过程执行时间长
- 解决方案：分析执行计划，优化SQL语句，合理使用索引，避免游标
- 问题2：函数返回值错误
- 症状：函数返回值与预期不符
- 解决方案：检查函数逻辑，确保正确设置返回值
- 问题3：存储过程权限不足
- 症状：执行存储过程时提示权限不足
- 解决方案：授予用户执行存储过程的权限
- 问题4：事务处理不当
- 症状：数据不一致或死锁
- 解决方案：合理使用事务，确保事务的原子性、一致性、隔离性和持久性
风哥提示：存储过程与函数是数据库编程的重要组成部分，合理使用存储过程和函数可以提高数据库的性能和可维护性。在生产环境中，应该编写高效、安全的存储过程和函数，定期监控和优化其性能，确保数据库的稳定运行和高性能。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息