Oracle 存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
Oracle 存储过程是存储在数据库中的一组为完成特定功能而预编译的 PL/SQL 语句集合。它们可以接收参数、执行复杂操作，并返回结果。其结构包含输入参数（IN）、输出参数（OUT）、变量声明、执行体（BEGIN-END块）及异常处理模块，支持事务控制（COMMIT/ROLLBACK）和错误日志记录。
Oracle 存储过程通过PL/SQL语法实现数据操作，包含条件分支（IF/ELSIF）、循环（WHILE/FOR）及游标遍历数据集，支持动态SQL执行与结果集处理。代码需遵循规范：使用验证操作记录存在性，为变量赋值，主动抛出异常，并通过输出调试信息。调试时可借助PL/SQL Developer工具进行逐行跟踪。
作为 Oracle 数据库的核心功能，存储过程随 PL/SQL 语言迭代不断完善，通过封装复杂逻辑提升执行效率与代码复用性，广泛应用于数据批量处理与业务规则实现。
优点
存储过程的主要优点：
1、提高性能：存储过程在创建时编译，后续调用无需重新编译
2、减少网络流量：只需传递存储过程名和参数，而非多条SQL语句
3、增强安全性：可以限制用户通过存储过程访问数据
4、代码复用：一次编写，多次调用
5、模块化编程：复杂业务逻辑可以封装在存储过程中
基本语法
存储过程的基本创建语法如下：
CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter_list) ]
IS
[declaration_section] -- 声明局部变量、游标、异常等
BEGIN
[executable_section] -- 核心执行逻辑
EXCEPTION
[exception_section] -- 异常处理部分
END [procedure_name];关键字说明：
●CREATE OR REPLACE：如果过程已存在则替换，否则新建。
●procedure_name：存储过程的名称。
●IS：引入声明部分（两者通常可互换）。
●declaration_section：可选部分，声明局部变量、常量、游标、异常等。
●BEGIN … END：存储过程的核心，包含要执行的 SQL 和 PL/SQL 语句。
●EXCEPTION：可选部分，用于处理运行时错误。
实战示例
create or replace procedure insert_employee
is
begin
insert into employees (emp_id, emp_name, salary) values (1001, '张三', 8000);
commit; -- 提交事务
end insert_employee;create or replace procedure get_dept_emp_count(
p_dept_id IN NUMBER -- 输入参数：部门ID
)
is
v_count NUMBER; -- 局部变量：员工数量
begin
SELECT COUNT(*) INTO v_count FROM employees WHERE dept_id = p_dept_id;
DBMS_OUTPUT.PUT_LINE('部门 ' || p_dept_id || ' 有 ' || v_count || ' 名员工');
exception
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('部门ID ' || p_dept_id || ' 不存在');
end get_dept_emp_count;调用前需开启输出：
set serveroutput on;
exec get_dept_emp_count(20); -- 传入部门ID 20将本地表 zzy_local_tab 中未标记（bz is null）的记录与远程表 isys.tran_bak_info@szcore 进行匹配，若远程表存在相同记录，则更新本地表的标记字段 bz=1 并同步远程表的 bak_id 值。
![图片[1]-Oracle 数据库 PL/SQL 存储过程介绍与实战-十一张](https://www.11zhang.com/wp-content/uploads/2025/06/4a47a0db6e20250604123926.png)
代码：
create or replace procedure zzy_cursor_pro authid current_user is
--declare
v_card_id zzy_local_tab.card_id%type;
v_trans_type zzy_local_tab.trans_type%type;
v_eqp_id zzy_local_tab.eqp_id%type;
v_trans_date zzy_local_tab.trans_date%type;
v_trans_time zzy_local_tab.trans_time%type;
v_trans_amt zzy_local_tab.trans_amt%type;
v_bak_id zzy_local_tab.bak_id%type;
v_exist int;
cursor zzy_cursor is
select card_id,trans_type,eqp_id,trans_date,trans_time,trans_amt,bak_id from zzy_local_tab
where bz is null
order by trans_date,trans_time;
begin
open zzy_cursor;
fetch zzy_cursor into v_card_id,v_trans_type,v_eqp_id,v_trans_date,v_trans_time,v_trans_amt,v_bak_id;
while zzy_cursor%found loop
begin
v_exist := 0;
select count(*) into v_exist from isys.tran_bak_info@szcore where card_id = v_card_id and trans_type = v_trans_type and eqp_id = v_eqp_id and trans_date = v_trans_date and trans_time = v_trans_time and trans_amt = v_trans_amt;
if v_exist > 0 then
select bak_id into v_bak_id from isys.tran_bak_info@szcore where card_id = v_card_id and trans_type = v_trans_type and eqp_id = v_eqp_id and trans_date = v_trans_date and trans_time = v_trans_time and trans_amt = v_trans_amt and rownum < 2;
update zzy_local_tab set bz = 1,bak_id = v_bak_id where card_id = v_card_id and trans_type = v_trans_type and eqp_id = v_eqp_id and trans_date = v_trans_date and trans_time = v_trans_time and trans_amt = v_trans_amt;
end if;
commit;
end;
fetch zzy_cursor into v_card_id,v_trans_type,v_eqp_id,v_trans_date,v_trans_time,v_trans_amt,v_bak_id;
end loop;
close zzy_cursor;
--end;
end zzy_cursor_pro;![图片[2]-Oracle 数据库 PL/SQL 存储过程介绍与实战-十一张](https://www.11zhang.com/wp-content/uploads/2025/06/fb5c81ed3a20250604124455.png)
①过程声明与权限设置
create or replace procedure zzy_cursor_pro authid current_user is●create or replace procedure：创建或替换存储过程
●zzy_cursor_pro：过程名称
●authid current_user：关键权限设置，表示该过程以调用者的身份和权限执行，而不是以过程创建者的权限执行。这会影响后续对远程数据库 @szcore 的访问权限。
②变量声明部分
v_card_id zzy_local_tab.card_id%type;
v_trans_type zzy_local_tab.trans_type%type;
-- ... 其他类似字段
v_exist int;●声明了多个变量，用于临时存储游标获取的数据
●使用 %TYPE 关键字：这是一种良好的编程习惯，让变量自动继承对应表字段的数据类型和长度
●v_exist int：用于存储查询结果的计数器
③游标定义
cursor zzy_cursor is
select card_id,trans_type,eqp_id,trans_date,trans_time,trans_amt,bak_id
from zzy_local_tab
where bz is null
order by trans_date,trans_time;●定义了一个名为 zzy_cursor 的显式游标
●查询 zzy_local_tab 表中所有 bz 字段为空的记录
●ORDER BY trans_date,trans_time: 按交易日期和时间排序处理
④打开游标并获取第一行数据
open zzy_cursor;
fetch zzy_cursor into v_card_id,v_trans_type,v_eqp_id,v_trans_date,v_trans_time,v_trans_amt,v_bak_id;●OPEN：打开游标，准备读取数据
●FETCH：获取第一行数据并存入相应变量
⑤主循环逻辑
while zzy_cursor%found loop●%FOUND：游标属性，判断最近一次 FETCH 操作是否成功获取到数据
●只要游标中有数据，就继续循环执行
⑥检查远程表是否存在相同记录
select count(*) into v_exist from isys.tran_bak_info@szcore
where card_id = v_card_id and trans_type = v_trans_type
and eqp_id = v_eqp_id and trans_date = v_trans_date
and trans_time = v_trans_time and trans_amt = v_trans_amt;●通过数据库链接 @szcore 访问远程数据库
●使用所有关键字段进行精确匹配查询
●将查询结果计数存入 v_exist 变量
⑦如果存在则更新本地表
if v_exist > 0 then
select bak_id into v_bak_id from isys.tran_bak_info@szcore
where card_id = v_card_id and trans_type = v_trans_type
and eqp_id = v_eqp_id and trans_date = v_trans_date
and trans_time = v_trans_time and trans_amt = v_trans_amt
and rownum < 2;
update zzy_local_tab set bz = 1, bak_id = v_bak_id
where card_id = v_card_id and trans_type = v_trans_type
and eqp_id = v_eqp_id and trans_date = v_trans_date
and trans_time = v_trans_time and trans_amt = v_trans_amt;
end if;●rownum < 2：确保只获取一条记录（防止重复数据）
●更新本地表：设置 bz = 1（表示已核对）和 bak_id（从远程表获取的备份ID）
⑧提交和获取下一行
commit;
fetch zzy_cursor into v_card_id,v_trans_type,v_eqp_id,v_trans_date,v_trans_time,v_trans_amt,v_bak_id;●每处理一行就提交一次：这种设计有利有弊
●再次使用 FETCH获取下一行数据
⑨关闭游标
close zzy_cursor;●循环结束后关闭游标，释放资源
问题①：对每行数据都执行 2 次远程查询和 1 次更新操作，效率较低
建议：考虑使用批量处理 (BULK COLLECT, FORALL)
问题②：无法整体回滚，如果过程中途失败，已处理的数据无法撤销
建议：根据业务需求调整提交频率，或改为最后一次性提交
问题③：任何错误都会导致过程终止，可能造成游标未正确关闭
建议：添加EXCEPTION异常处理，确保游标正确关闭
CREATE OR REPLACE PROCEDURE zzy_cursor_pro_optimized AUTHID CURRENT_USER IS
BEGIN
FOR rec IN (
SELECT card_id, trans_type, eqp_id, trans_date, trans_time, trans_amt, bak_id FROM zzy_local_tab
WHERE bz IS NULL ORDER BY trans_date,trans_time
) LOOP
BEGIN
FOR remote_rec IN (
SELECT bak_id FROM isys.tran_bak_info@szcore WHERE card_id = rec.card_id
AND trans_type = rec.trans_type
AND eqp_id = rec.eqp_id
AND trans_date = rec.trans_date
AND trans_time = rec.trans_time
AND trans_amt = rec.trans_amt
AND ROWNUM = 1
) LOOP
UPDATE zzy_local_tab SET bz = 1, bak_id = remote_rec.bak_id
WHERE card_id = rec.card_id
AND trans_type = rec.trans_type
AND eqp_id = rec.eqp_id
AND trans_date = rec.trans_date
AND trans_time = rec.trans_time
AND trans_amt = rec.trans_amt;
END LOOP;
END;
END LOOP;
COMMIT; -- 一次性提交
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END zzy_cursor_pro_optimized;其它示例
create or replace procedure zzy_tran_2025_pro authid current_user is
--declare
v_card_id zzy_local_tab.card_id%type;
v_trade_type zzy_local_tab.trade_type%type;
v_terminal_id zzy_local_tab.terminal_id%type;
v_trade_date zzy_local_tab.trade_date%type;
v_trade_time zzy_local_tab.trade_time%type;
v_trade_money zzy_local_tab.trade_money%type;
v_settle_date zzy_local_tab.settle_date%type;
v_trans_value zzy_local_tab.trans_value%type;
v_discount_amt zzy_local_tab.discount_amt%type;
v_unit_id zzy_local_tab.unit_id%type;
v_exist int;
cursor zzy_cursor is
select card_id,trade_type,terminal_id,trade_date,trade_time,trade_money,settle_date,trans_value,discount_amt,unit_id from zzy_local_tab
where flag is null
order by trade_date,trade_time;
begin
open zzy_cursor;
fetch zzy_cursor into v_card_id,v_trade_type,v_terminal_id,v_trade_date,v_trade_time,v_trade_money,v_settle_date,v_trans_value,v_discount_amt,v_unit_id;
while zzy_cursor%found loop
begin
v_exist := 0;
select count(*) into v_exist from isztsys.tran_normal_info_tran_0101@sztcore
where card_id = v_card_id
and trans_id = v_trade_type
and eqp_id = v_terminal_id
and trans_date = v_trade_date
and trans_time = v_trade_time
and trans_amt = v_trade_money;
if v_exist > 0 then
select settle_date,trans_value,discount_amt,unit_id into v_settle_date,v_trans_value,v_discount_amt,v_unit_id from isztsys.tran_normal_info_tran_0101@sztcore
where card_id = v_card_id
and trans_id = v_trade_type
and eqp_id = v_terminal_id
and trans_date = v_trade_date
and trans_time = v_trade_time
and trans_amt = v_trade_money
and rownum < 2;
update zzy_local_tab set flag = 1,settle_date = v_settle_date,trans_value = v_trans_value,discount_amt = v_discount_amt,unit_id = v_unit_id
where card_id = v_card_id
and trade_type = v_trade_type
and terminal_id = v_terminal_id
and trade_date = v_trade_date
and trade_time = v_trade_time
and trade_money = v_trade_money;
end if;
commit;
end;
fetch zzy_cursor into v_card_id,v_trade_type,v_terminal_id,v_trade_date,v_trade_time,v_trade_money,v_settle_date,v_trans_value,v_discount_amt,v_unit_id;
end loop;
close zzy_cursor;
--end;
end zzy_tran_2025_pro;create or replace procedure zzy_tran_2025_pro authid current_user is
--declare
v_card_id zzy_local_tab.card_id%type;
v_trade_type zzy_local_tab.trade_type%type;
v_terminal_id zzy_local_tab.terminal_id%type;
v_trade_date zzy_local_tab.trade_date%type;
v_trade_time zzy_local_tab.trade_time%type;
v_trade_money zzy_local_tab.trade_money%type;
v_settle_date zzy_local_tab.settle_date%type;
v_trans_value zzy_local_tab.trans_value%type;
v_discount_amt zzy_local_tab.discount_amt%type;
v_unit_id zzy_local_tab.unit_id%type;
v_exist int;
v_exist_a number(4);
v_exist_b number(4);
v_sql_a1 varchar2(2048);
v_sql_a2 varchar2(2048);
v_sql_b1 varchar2(2048);
v_sql_b2 varchar2(2048);
cursor zzy_cursor is
select card_id,trade_type,terminal_id,trade_date,trade_time,trade_money,settle_date,trans_value,discount_amt,unit_id from zzy_local_tab
where flag is null
order by trade_date,trade_time;
begin
open zzy_cursor;
fetch zzy_cursor into v_card_id,v_trade_type,v_terminal_id,v_trade_date,v_trade_time,v_trade_money,v_settle_date,v_trans_value,v_discount_amt,v_unit_id;
while zzy_cursor%found loop
begin
v_exist := 0;
v_exist_a:=0;
v_exist_b:=0;
--------------------------------------------------------------[动态SQL]---------------------------------------------------------------
if v_trade_date >= to_char(sysdate-365,'yyyymmdd') then
v_sql_a1 := 'select count(1) from isztsys.tran_normal_info_tran_' || substr(v_trade_date,5,4)|| '@sztcore' || /*生产库*/
' where card_id='|| v_card_id ||
' and trans_id=' || v_trade_type ||
' and eqp_id=' || v_terminal_id ||
' and trans_date=' || v_trade_date ||
' and trans_time=' || v_trade_time ||
' and trans_amt=' || v_trade_money ||
' and err_id = ''0''';
execute immediate(v_sql_a1) into v_exist_a;
else
v_sql_b1 := 'select count(1) from histszt.tran_normal_info_tran_' || substr(v_trade_date,5,4) || '@stocore' || /*历史库*/
' where card_id='|| v_card_id ||
' and trans_id=' || v_trade_type ||
' and eqp_id=' || v_terminal_id ||
' and trans_date=' || v_trade_date ||
' and trans_time=' || v_trade_time ||
' and trans_amt=' || v_trade_money ||
' and err_id = ''0''';
execute immediate(v_sql_b1) into v_exist_b;
end if;
if v_exist_a >0 or v_exist_b >0 then
if v_exist_a >0 then
v_sql_a2 := 'select settle_date,trans_value,discount_amt,unit_id from isztsys.tran_normal_info_tran_' || substr(v_trade_date,5,4)|| '@sztcore' ||
' where card_id='|| v_card_id ||
' and trans_id=' || v_trade_type ||
' and eqp_id=' || v_terminal_id ||
' and trans_date=' || v_trade_date ||
' and trans_time=' || v_trade_time ||
' and trans_amt=' || v_trade_money ||
' and err_id = ''0''' ;
execute immediate(v_sql_a2) into v_settle_date,v_trans_value,v_discount_amt,v_unit_id;
end if;
if v_exist_b >0 then
v_sql_b2 := 'select settle_date,trans_value,discount_amt,unit_id from histszt.tran_normal_info_tran_' || substr(v_trade_date,5,4) || '@stocore' ||
' where card_id='|| v_card_id ||
' and trans_id=' || v_trade_type ||
' and eqp_id=' || v_terminal_id ||
' and trans_date=' || v_trade_date ||
' and trans_time=' || v_trade_time ||
' and trans_amt=' || v_trade_money ||
' and err_id = ''0''' ;
execute immediate(v_sql_b2) into v_settle_date,v_trans_value,v_discount_amt,v_unit_id;
end if;
update zzy_local_tab set flag = 1,settle_date = v_settle_date,trans_value = v_trans_value,discount_amt = v_discount_amt,unit_id = v_unit_id
where card_id = v_card_id
and trade_type = v_trade_type
and terminal_id = v_terminal_id
and trade_date = v_trade_date
and trade_time = v_trade_time
and trade_money = v_trade_money;
commit;
else
update zzy_local_tab set flag = 0
where card_id = v_card_id
and trade_type = v_trade_type
and terminal_id = v_terminal_id
and trade_date = v_trade_date
and trade_time = v_trade_time
and trade_money = v_trade_money;
commit;
end if;
--------------------------------------------------------------------------------------------------------------------------------------
end;
fetch zzy_cursor into v_card_id,v_trade_type,v_terminal_id,v_trade_date,v_trade_time,v_trade_money,v_settle_date,v_trans_value,v_discount_amt,v_unit_id;
end loop;
close zzy_cursor;
--end;
end zzy_tran_2025_pro; 温馨提示：本文最后更新于
2025-11-15 22:37:12，某些文章具有时效性，若有错误或下载地址失效，请在文末评论区留言© 版权声明
THE END
暂无评论内容