下面是当在主块中找不到数据时必须执行异常块的代码。但是,即使查询没有返回任何记录,异常块也不会执行。查询与另一个表连接,当结果与查询匹配时,它将选择第一条记录,否则它必须为字段分配空值,并将游标返回到UI。下面游标的计数始终为0,并且不会执行异常块。
BEGIN
wf_common.write_debug(vprocname, ' 111=', ts);
OPEN cinfo FOR SELECT
name,
phone,
alt_phone,
email
FROM
abc
WHERE
id IN (
SELECT
id
FROM
xyz
WHERE
emp_no = vempno
)
AND name IS NOT NULL
AND ROWNUM = 1;
wf_common.write_debug(vprocname, 'COUNT' || cinfo%rowcount, ts);
EXCEPTION
WHEN no_data_found THEN
wf_common.write_debug(vprocname, '222=', ts);
OPEN cinfo FOR SELECT
'' name,
'' phone,
'' alt_phone,
'' email
FROM
dual;
wf_common.write_debug(vprocname, 'COUNT' || cinfo%rowcount, ts);
END;发布于 2020-04-02 06:01:19
用WHEN OTHERS替换WHEN NO_DATA_FOUND
发布于 2020-04-02 14:10:11
这是一个光标。它不会引发no_data_found。你怎么知道它是否会返回一些东西呢?你不会的除非你
然后你就知道了。
所以--不幸的是--你得先检查一下,然后再决定该怎么做。例如(基于Scott的示例模式):
SQL> create or replace function f_test (par_deptno in emp.deptno%type)
2 return sys_refcursor
3 is
4 type cinfot is record (empno emp.empno%type,
5 ename emp.ename%type,
6 job emp.job%type);
7 cinfor cinfot;
8 cinfo sys_refcursor;
9 begin
10 -- in order to test whether it returns something, open & fetch
11 open cinfo for 'select empno, ename, job
12 from emp
13 where deptno = :a' using par_deptno;
14 fetch cinfo into cinfor;
15
16 -- has anything been found?
17 if cinfo%found then
18 -- something has been found - process it
19 close cinfo;
20 open cinfo for 'select empno, ename, job
21 from emp
22 where deptno = :a' using par_deptno;
23
24 else
25 -- nothing has been found; open another cursor
26 close cinfo;
27 open cinfo for select deptno, dname, loc
28 from dept;
29 end if;
30
31 return cinfo;
32 end;
33 /
Function created.测试:EMP表中存在部门10,因此-返回其内容:
SQL> select f_test(10) From dual;
F_TEST(10)
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
EMPNO ENAME JOB
---------- ---------- ---------
7782 CLARK MANAGER
7839 KING PRESIDENT
7934 MILLER CLERK部门50不存在,因此返回DEPT表的内容:
SQL> select f_test(50) From dual;
F_TEST(50)
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>https://stackoverflow.com/questions/60980909
复制相似问题