Oracle存储过程插入临时表数据到主表无数据问题咨询

算力阿峰

2026-9-26

问题原因及解决方案

核心根因

主存储过程sp_main的最外层执行块（末尾的begin ... end区间）仅包含null;语句，没有对tbl1_ld、tbl2_ld两个子存储过程的调用逻辑，子过程的插入代码无论如何编写都不会被执行，自然不会产生数据插入动作。
若已补全调用仍无数据，存在两类潜在问题：
1. 动态SQLEXECUTE IMMEDIATE属于运行时解析，编译阶段不会校验SQL的合法性，若tmp1/tmp2无数据、tbl1/tbl2与临时表的字段顺序/数据类型不匹配，执行时会静默报错（未加异常捕获时不会抛出编译错误）
2. 存储过程默认使用定义者权限执行，若权限未直接授予定义者用户（通过角色获取的权限在存储过程内默认失效），会导致动态SQL执行无权限但编译正常。

修复步骤

首先补全主存储过程的执行逻辑，调用子过程并按需提交事务：

create or replace procedure sp_main as

 procedure tbl1_ld as 
 begin
  EXECUTE IMMEDIATE 'insert into tbl1 select * from tmp1';
 end tbl1_ld;

 procedure tbl2_ld as 
 begin
  EXECUTE IMMEDIATE 'insert into tbl2 select * from tmp2';
 end tbl2_ld;

begin
  -- 新增子过程调用
  tbl1_ld;
  tbl2_ld;
  -- 按需添加事务提交，根据业务事务控制逻辑调整
  commit;
end sp_main;
/

补全调用后仍无数据的排查方案：

直接在当前会话执行insert into tbl1 select * from tmp1，验证语句本身可正常执行、插入行数非0
给存储过程的定义者用户授予对应表的显式权限：grant select, insert on tmp1, tbl1, tmp2, tbl2 to <定义者用户名>;
子过程内添加异常捕获逻辑，定位具体报错原因：

procedure tbl1_ld as 
begin
  EXECUTE IMMEDIATE 'insert into tbl1 select * from tmp1';
  DBMS_OUTPUT.PUT_LINE('tbl1成功插入行数：'||SQL%ROWCOUNT);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('tbl1插入失败，错误码：'||SQLCODE||'，错误信息：'||SQLERRM);
end tbl1_ld;