异常剂量在预言机中执行立即插入不起作用

Question

我在执行立即插入语句异常部分时遇到一些问题。

我有一个包含两列(DEPT和SOURCE_VALUE)的表query_tb

该列包含以下数据

店员

select a.empno,a.ename,a.job,a.mgr,a.hiredate,b.deptno,b.dname,b.loc 
from emp a,dept b where a.deptno=b.deptno and a.empno= '#V_GCIF#'

推销员

select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc from emp e,dept 
d where e.deptno=d.deptno and e.empno= '#V_GCIF#'

经理

select a.empno,a.ename,a.job,b.deptno,b.dname,b.loc from employee 
a,department b where a.deptno=b.deptno and a.empno= '#V_GCIF#'

管理

select a.empno,a.ename,a.job,b.deptno,b.dname,b.loc from employee 
a,department b where a.deptno=b.deptno and a.empno= '#V_GCIF#'

如果我传递正确的empno，该保留在emp表上，它运行良好。但是如果我传递了不正确的empno(没有数据)，那么异常部分不起作用。

create or replace
PROCEDURE test_emp_sp(
p_id IN VARCHAR2)
AS
CURSOR rec
IS
SELECT dept,
source_value
FROM query_tb;
v_query  VARCHAR2(1000);
BEGIN
FOR rec IN
(SELECT dept,source_value FROM query_tb
)
LOOP
IF rec.dept='CLERK' THEN
v_query :=REPLACE(rec.source_value,'#V_GCIF#',p_id);
EXECUTE IMMEDIATE 'INSERT INTO emp_tb (empno,ename,job,mgr,hiredate,deptno,dname,loc) ('||v_query|| ')';
dbms_output.put_line(v_query||'    inserted');
ELSE
v_query:=REPLACE(rec.source_value,'#V_GCIF#',p_id);
EXECUTE IMMEDIATE 'INSERT INTO emp_tb (empno,ename,job,deptno,dname,loc) ('||v_query||')';
dbms_output.put_line(v_query||'    inserting others');
END IF;
END LOOP;
commit;
EXCEPTION
WHEN others THEN
dbms_output.put_line('No data Found...');
END;

Answer 1

那是因为你没有运行select命令，它是一个insert命令(insert select)，这意味着如果select不返回行，它只是不插入任何内容，也不会为此抛出任何错误。您应该检查插入命令是否影响了任何行。在 Oracle 中执行此操作的方式是在执行后立即检查SQL%ROWCOUNT，如果它变为 0，那么您的工作会引发异常。它将是这样的：

DECLARE
customException EXCEPTION;
PRAGMA EXCEPTION_INIT( customException, -20001 );
....
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO emp_tb (empno,ename,job,mgr,hiredate,deptno,dname,loc) 
('||v_query|| ')';
IF (SQL%ROWCOUNT) = 0 then
raise_application_error( -20001, 'This is a custom error' );
end if;
EXCEPTION
WHEN customException THEN
dbms_output.put_line('No data Found...');  
END;

很长一段时间没有在Oracle PLSql中编程 所以提供的代码上的某些东西可能无法编译，但它都是在互联网上查看它，你会很好。

Answer 2

异常不起作用，因为没有异常。 如果 SELECT 返回 NULL 则 0 行将为 INSERT。 例：

insert into t1(c1)
select 100 from dual where 1=0;

结果：插入了 0 行。

Answer 3

insert into ... select from..不会引发no_data_found异常。它只会插入 0 条记录。

若要测试是否插入了任何记录，可以在插入语句后使用 SQL%ROWCOUNT。

execute immedate 'INSERT...;
if SQL%ROWCOUNT=0 
then
dbms_output.put_line('no records inserted');
else
...
end if;

此外，您可能还需要考虑将 #V_GCIF# 更改为可用作绑定可初始化的内容，例如 :P_ID。

您可以跳过 replace 语句并将立即执行更改为以下内容：

execute immediate 'INSERT INTO ...'||v_query
using p_id;

这会将 p_id 的值绑定到语句中的 :p_id。

Answer 4

这里最基本的事情是适当地捕获错误，以便能够追溯。在这里，我放置了记录器和EXCEPTION处理程序，只是为了检查错误。我也放了一个RAISE_APPLICATION_ERROR。在狙击中也做了一些修改。希望对您有所帮助。

PS：没有检查语法，因为我现在没有工作区。

CREATE OR REPLACE
PROCEDURE test_emp_sp(
p_id IN VARCHAR2)
AS
--Not needed
--  CURSOR rec
--  IS
--    SELECT dept,
--      source_value
--    FROM query_tb;
--Not needed
v_query VARCHAR2(1000);
BEGIN
FOR rec IN
(SELECT dept,source_value FROM query_tb
)
LOOP
IF rec.dept='CLERK' THEN
v_query :=REPLACE(rec.source_value,'#V_GCIF#',p_id);
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO emp_tb (empno,ename,job,mgr,hiredate,deptno,dname,loc) ('||v_query|| ')';
dbms_output.put_line(v_query||'    inserted');
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(' Error while inserting data in emp_tab for Clerk--> '||SQLCODE||'---'||SQLERRM);
END;
ELSE
v_query:=REPLACE(rec.source_value,'#V_GCIF#',p_id);
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO emp_tb (empno,ename,job,deptno,dname,loc) ('||v_query||')';
dbms_output.put_line(v_query||'    inserting others');
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(' Error while inserting data in emp_tab for other then clerk --> '||SQLCODE||'---'||SQLERRM);
END;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'Error occurred in the plsql block',TRUE);
END;
/