Oracle教程FG067-PL/SQL存储过程

1.2.2 执行存储过程

— 使用EXECUTE命令
EXECUTE procedure_name([parameter1, parameter2, …]);

— 使用CALL命令
CALL procedure_name([parameter1, parameter2, …]);

— 在PL/SQL块中执行
BEGIN
procedure_name([parameter1, parameter2, …]);
END
;
/

1.3 存储过程的参数

存储过程可以接受以下类型的参数：更多视频教程www.fgedu.net.cn

1.3.1 IN
参数

输入参数，用于向存储过程传递值，默认类型。

1.3.2 OUT
参数

输出参数，用于从存储过程返回值。学习交流加群风哥微信: itpux-com

1.3.3 IN OUT
参数

输入输出参数，既可以向存储过程传递值，也可以从存储过程返回值。

1.3.4 参数默认值

可以为参数指定默认值，当调用存储过程时如果不提供该参数，则使用默认值。

1.4 存储过程的管理

1.4.1 查看存储过程

— 查看存储过程的源代码
SELECT
text
FROM user_source
WHERE name = ‘PROCEDURE
_NAME’ ORDER BY line;

— 查看存储过程的信息
SELECT
*
FROM user_procedures
WHERE procedure_name = ‘PROCEDURE
_NAME’;

1.4.2 修改存储过程

使用CREATE OR REPLACE
PROCEDURE
语句修改存储过程。 from oracle:www.itpux.com

1.4.3 删除存储过程

DROP PROCEDURE
procedure_name;

1.4.4 编译存储过程

ALTER PROCEDURE
procedure_name COMPILE;

Part02-生产环境规划与建议

2.1 存储过程设计最佳实践

命名规范：使用有意义的名称，遵循一致的命名规范
参数设计：合理设计参数，使用默认值减少调用复杂度
错误处理：实现完善的异常处理机制
代码注释：添加详细的代码注释，提高可维护性
模块化设计：将复杂的业务逻辑分解为多个存储过程
事务管理：合理管理事务，确保数据一致性
日志记录：添加适当的日志记录，便于调试和监控

2.2 存储过程性能考虑

避免硬解析：使用绑定变量，避免在循环中拼接SQL语句
减少网络往返：使用批量操作，减少SQL语句的执行次数
优化SQL语句：使用EXPLAIN
PLAN分析SQL语句的执行计划
使用并行执行：对于大型处理，考虑使用并行执行
监控执行计划：定期分析存储过程的执行计划
合理使用游标：对于结果集处理，使用游标变量和批量操作

2.3 存储过程安全考虑

权限管理：合理授予存储过程的执行权限
数据访问控制：使用最小权限原则，只授予必要的权限
输入验证：对输入参数进行验证，防止SQL注入
加密敏感数据：对敏感数据进行加密处理
审计日志：记录存储过程的执行情况，便于审计

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

3.1 存储过程在数据处理中的应用

存储过程在数据处理中的应用场景包括：

数据转换：将数据从一种格式转换为另一种格式
数据清洗：清理和标准化数据
数据迁移：将数据从一个系统迁移到另一个系统
数据加载：批量加载数据到数据库
数据聚合：计算汇总数据和统计信息

3.2 存储过程在业务逻辑中的应用

存储过程在业务逻辑中的应用场景包括：

业务规则实现：实现复杂的业务规则和逻辑
工作流处理：处理业务流程和工作流
审批流程：实现审批流程和状态管理
订单处理：处理订单的创建、修改和删除
库存管理：管理库存的入库、出库和盘点

3.3 存储过程在批量操作中的应用

存储过程在批量操作中的应用场景包括：

批量更新：批量更新数据
批量删除：批量删除数据
批量插入：批量插入数据
批量计算：批量计算数据
批量报表生成：批量生成报表数据

Part04-生产案例与实战讲解

4.1 基本存储过程创建与执行

示例1：创建一个简单的存储过程

— 创建存储过程
CREATE OR REPLACE
PROCEDURE
fgedu_hello_world
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Hello, World!’);
END
;
/

— 执行存储过程
SET SERVEROUTPUT ON;
EXECUTE fgedu_hello_world;

Hello, World!

示例2：创建一个查询员工信息的存储过程

— 创建存储过程
CREATE OR REPLACE
PROCEDURE
fgedu_get_employee_info
IS
CURSOR c_employees IS
SELECT
employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 90;
v_employee_id employees.employee_id%TYPE
;
v_first_name employees.first_name%TYPE
;
v_last_name employees.last_name%TYPE
;
v_salary employees.salary%TYPE
;
BEGIN
OPEN c_employees;
LOOP
FETCH c_employees IN
TO v_employee_id, v_first_name, v_last_name, v_salary;
EXIT WHEN c_employees%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘ID: ‘ || v_employee_id || ‘, Name: ‘ || v_first_name || ‘ ‘ || v_last_name || ‘, Salary: ‘ || v_salary);
END LOOP
;
CLOSE c_employees;
END
;
/

— 执行存储过程
SET SERVEROUTPUT ON;
EXECUTE fgedu_get_employee_info;

ID: 100, Name: Steven King, Salary: 24000
ID: 101, Name: Neena Kochhar, Salary: 17000
ID: 102, Name: Lex De Haan, Salary: 17000

4.2 带参数的存储过程

示例1：创建带IN
参数的存储过程

— 创建存储过程
CREATE OR REPLACE
PROCEDURE
fgedu_get_employee_by_id (
p_employee_id IN
employees.employee_id%TYPE
)
IS
v_employee employees%ROWTYPE
;
BEGIN
SELECT
* IN
TO v_employee
FROM employees
WHERE employee_id = p_employee_id;
DBMS_OUTPUT.PUT_LINE(‘ID: ‘ || v_employee.employee_id);
DBMS_OUTPUT.PUT_LINE(‘Name: ‘ || v_employee.first_name || ‘ ‘ || v_employee.last_name);
DBMS_OUTPUT.PUT_LINE(‘Job ID: ‘ || v_employee.job_id);
DBMS_OUTPUT.PUT_LINE(‘Salary: ‘ || v_employee.salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Error: No employee found with ID ‘ || p_employee_id);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error: ‘ || SQLCODE || ‘ – ‘ || SQLERRM);
END
;
/

— 执行存储过程
EXECUTE fgedu_get_employee_by_id(100);

ID: 100
Name: Steven King
Job ID: AD_PRES
Salary: 24000

示例2：创建带OUT
参数的存储过程

— 创建存储过程
CREATE OR REPLACE
PROCEDURE
fgedu_get_department_avg_salary (
p_department_id IN
departments.department_id%TYPE
,
p_avg_salary OUT
NUMBER
)
IS
BEGIN
SELECT
AVG(salary) IN
TO p_avg_salary
FROM employees
WHERE department_id = p_department_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_avg_salary :=
0;
WHEN OTHERS THEN
p_avg_salary :=
0;
DBMS_OUTPUT.PUT_LINE(‘Error: ‘ || SQLCODE || ‘ – ‘ || SQLERRM);
END
;
/

— 执行存储过程
SET SERVEROUTPUT ON;
DECLARE
v_avg_salary NUMBER;
BEGIN
fgedu_get_department_avg_salary(90, v_avg_salary);
DBMS_OUTPUT.PUT_LINE(‘Average salary for department 90: ‘ || v_avg_salary);
END
;
/

Average salary for department 90: 19333.3333333333333333

示例3：创建带默认参数的存储过程

— 创建存储过程
CREATE OR REPLACE
PROCEDURE
fgedu_get_employees_by_department (
p_department_id IN
departments.department_id%TYPE
DEFAULT 90
)
IS
CURSOR c_employees IS
SELECT
employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = p_department_id;
v_employee_id employees.employee_id%TYPE
;
v_first_name employees.first_name%TYPE
;
v_last_name employees.last_name%TYPE
;
v_salary employees.salary%TYPE
;
BEGIN
OPEN c_employees;
LOOP
FETCH c_employees IN
TO v_employee_id, v_first_name, v_last_name, v_salary;
EXIT WHEN c_employees%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘ID: ‘ || v_employee_id || ‘, Name: ‘ || v_first_name || ‘ ‘ || v_last_name || ‘, Salary: ‘ || v_salary);
END LOOP
;
CLOSE c_employees;
END
;
/

— 执行存储过程（使用默认参数）
EXECUTE fgedu_get_employees_by_department;

— 执行存储过程（指定参数）
EXECUTE fgedu_get_employees_by_department(60);

ID: 100, Name: Steven King, Salary: 24000
ID: 101, Name: Neena Kochhar, Salary: 17000
ID: 102, Name: Lex De Haan, Salary: 17000
ID: 103, Name: Alexander Hunold, Salary: 9000
ID: 104, Name: Bruce Ernst, Salary: 6000
ID: 105, Name: David Austin, Salary: 4800
ID: 106, Name: Valli Pataballa, Salary: 4800
ID: 107, Name: Diana Lorentz, Salary: 4200

4.3 存储过程异常处理

示例1：存储过程中的异常处理

— 创建存储过程
CREATE OR REPLACE
PROCEDURE
fgedu_insert_employee (
p_employee_id IN
employees.employee_id%TYPE
,
p_first_name IN
employees.first_name%TYPE
,
p_last_name IN
employees.last_name%TYPE
,
p_email IN
employees.email%TYPE
,
p_hire_date IN
employees.hire_date%TYPE
,
p_job_id IN
employees.job_id%TYPE
,
p_salary IN
employees.salary%TYPE
,
p_department_id IN
employees.department_id%TYPE
)
IS
BEGIN
INSERT
IN
TO employees (
employee_id,
first_name,
last_name,
email,
hire_date,
job_id,
salary,
department_id
) VALUES (
p_employee_id,
p_first_name,
p_last_name,
p_email,
p_hire_date,
p_job_id,
p_salary,
p_department_id
);
COMMIT
;
DBMS_OUTPUT.PUT_LINE(‘Employee inserted successfully’);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK
;
DBMS_OUTPUT.PUT_LINE(‘Error: Employee ID already exists’);
WHEN OTHERS THEN
ROLLBACK
;
DBMS_OUTPUT.PUT_LINE(‘Error: ‘ || SQLCODE || ‘ – ‘ || SQLERRM);
END
;
/

— 执行存储过程（成功）
EXECUTE fgedu_insert_employee(207, ‘John’, ‘Doe’, ‘JDOE’, SYSDATE, ‘IT_PROG’, 6000, 60);

— 执行存储过程（失败 – 主键冲突）
EXECUTE fgedu_insert_employee(207, ‘Jane’, ‘Smith’, ‘JSMITH’, SYSDATE, ‘IT_PROG’, 6000, 60);

Employee inserted successfully
Error: Employee ID already exists

4.4 存储过程性能优化

示例1：使用批量操作优化存储过程

— 创建存储过程
CREATE OR REPLACE
PROCEDURE
fgedu_update_salaries (
p_department_id IN
departments.department_id%TYPE
,
p_percentage IN
NUMBER
)
IS
TYPE
employee_id_table IS
TABLE
OF employees.employee_id%TYPE
;
TYPE
salary_table IS
TABLE
OF employees.salary%TYPE
;
v_employee_ids employee_id_table;
v_salaries salary_table;
CURSOR c_employees IS
SELECT
employee_id, salary
FROM employees
WHERE department_id = p_department_id;
BEGIN
— 批量获取数据
OPEN c_employees;
FETCH c_employees BULK COLLECT IN
TO v_employee_ids, v_salaries;
CLOSE c_employees;

— 批量更新数据
FOR
ALL i IN
1..v_employee_ids.COUNT
UPDATE
employees
SET salary = v_salaries(i) * (1 + p_percentage /
100)
WHERE employee_id = v_employee_ids(i);

COMMIT
;
DBMS_OUTPUT.PUT_LINE(‘Updated ‘ || v_employee_ids.COUNT || ‘ employees’);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK
;
DBMS_OUTPUT.PUT_LINE(‘Error: ‘ || SQLCODE || ‘ – ‘ || SQLERRM);
END
;
/

— 执行存储过程
EXECUTE fgedu_update_salaries(60, 10);

Updated 5 employees

Part05-风哥经验总结与分享

5.1 存储过程使用技巧

使用命名规范：存储过程名使用动词+名词的形式，参数名使用p_前缀
使用注释：为存储过程添加详细的注释，包括功能、参数、返回值等
使用异常处理：实现完善的异常处理机制，确保存储过程的健壮性
使用事务管理：合理管理事务，确保数据一致性
使用批量操作：对于大量数据处理，使用BULK COLLECT和 FOR
ALL提高性能
使用绑定变量：避免硬解析，提高性能
使用游标变量：对于动态查询，使用游标变量
使用包：将相关的存储过程组织到包中，提高代码的可维护性

5.2 常见错误与解决方案

编译错误：检查语法错误，确保所有变量都已声明
运行时错误：使用异常处理捕获并处理运行时错误
权限错误：确保用户有执行存储过程的权限
性能问题：优化SQL语句，使用批量操作，避免在循环中执行SQL语句
死锁：合理设计事务，避免长时间持有锁
内存不足：对于大型批量操作，调整PGA内存参数

5.3 性能调优建议

使用绑定变量：避免硬解析，提高性能
使用批量操作：对于大量数据处理，使用BULK COLLECT和 FOR
ALL提高性能
优化SQL语句：使用EXPLAIN
PLAN分析SQL语句的执行计划，优化SQL语句
使用并行执行：对于大型处理，考虑使用并行执行
监控执行计划：定期分析存储过程的执行计划
合理使用索引：为经常查询的列创建索引
减少网络往返：使用批量操作，减少SQL语句的执行次数
使用结果缓存：对于频繁执行的查询，使用结果缓存

风哥提示：

本文档风哥主要介绍Oracle PL/SQL存储过程的创建、执行、管理和优化，由风哥教程参考Oracle官方文档Development内容编写，适合DBA人员和开发人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

学习交流加群风哥QQ113257174

生产环境建议：请根据实际情况调整配置和参数，确保生产环境的安全性和稳定性。

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

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