加上exception when no_data_found 的例外处理
如:
10:47:03 SQL> declare
10:47:07 2 v number;
10:47:07 3 begin
10:47:07 4 select aaa into v from t where 1=2;
10:47:13 5 end;
10:47:14 6 /
declare
*
ERROR 位于第 1 行:
ORA-01403: 未找到数据
ORA-06512: 在line 4
已用时间: 00: 00: 00.63
10:47:14 SQL> declare
10:47:17 2 v number;
10:47:17 3 begin
10:47:17 4 select aaa into v from t where 1=2;
10:47:18 5 exception when no_data_found then
10:47:30 6 dbms_output.put_line('no data found!');
10:47:54 7 end;
10:47:56 8 /
no data found!PL/SQL 过程已成功完成。已用时间: 00: 00: 00.47
10:47:57 SQL>
如:
10:47:03 SQL> declare
10:47:07 2 v number;
10:47:07 3 begin
10:47:07 4 select aaa into v from t where 1=2;
10:47:13 5 end;
10:47:14 6 /
declare
*
ERROR 位于第 1 行:
ORA-01403: 未找到数据
ORA-06512: 在line 4
已用时间: 00: 00: 00.63
10:47:14 SQL> declare
10:47:17 2 v number;
10:47:17 3 begin
10:47:17 4 select aaa into v from t where 1=2;
10:47:18 5 exception when no_data_found then
10:47:30 6 dbms_output.put_line('no data found!');
10:47:54 7 end;
10:47:56 8 /
no data found!PL/SQL 过程已成功完成。已用时间: 00: 00: 00.47
10:47:57 SQL>
(ll_calling_nbr VARCHAR2, ll_called_nbr VARCHAR2, ll_pass out number )
IS
calling_area_id NUMBER(12,0);
called_area_id NUMBER(12,0);
calling_type NUMBER(12,0);
called_type NUMBER(12,0);
ll_count integer;
BEGIN
begin .
select area_id
into calling_area_id
from acct
where acc_nbr =ll_calling_nbr
and state = '10A';
.
EXCEPTION
when no_data_found then
NULL;
when others then
raise_application_error( -20232, '出错'||sqlerrm ) ;
END;
begin
select area_id
into called_area_id
from acct
where acc_nbr = ll_called_nbr
and state = '10A';
EXCEPTION
when no_data_found then
NULL;
when others then
raise_application_error( -20233, '出错'||sqlerrm ) ;
END; .
.
.
.
END;
不管用什么开发工具异常肯定是要捕捉处理的,
原因:会有2中异常
<1>no_data_found
<2>too_many_rows
建议用显示游标*/
create or replace procedure LBAS.P_QQ_INTERFACE_FILTER
(
ll_calling_nbr in varchar2 , --建议-> 表名.列名%type
ll_called_nbr in varchar2 , --同上
ll_pass out number
)
is
calling_area_id number(12,0);
called_area_id number(12,0);
calling_type number(12,0);
called_type number(12,0);
ll_count integer;exp_cs1 exception ; --自定义异常第一个查询
exp_cs2 exception ; --自定义异常第二个查询--定义游标cs1
cursor cs1 is
select area_id
from acct
where acc_nbr = ll_calling_nbr
and state = '10A';--定义游标cs2
cursor cs1 is
select area_id
from acct
where acc_nbr = ll_called_nbr
and state = '10A';
begin
--把area_id赋值给变量calling_area_id
for for_cs1 in cs1
loop
calling_area_id := for_cs1.area_id ;
exit ; --在只有一条记录的情况下用exit
end loop ;
if calling_area_id is null then
raise exp_cs1 ; --把area_id赋值给变量called_area_id
for for_cs2 in cs2
loop
called_area_id := for_cs1.area_id ;
exit ; --在只有一条记录的情况下用exit
end loop ;
if called_area_id is null then
raise exp_cs2 ;
exception
when exp_cs1 then
ll_pass := 1 ; --自由发挥,表示第一个查询出错,具体根据实际定
when exp_cs2 then
ll_pass := 2 ; --同上
when others then
ll_pass := 3 ; --其他错误!以上错误可以得到sqlcode、sqlerrm并记录下来
end;
你的操作;
CREATE OR REPLACE PROCEDURE LBAS.P_QQ_INTERFACE_FILTER
(ll_calling_nbr VARCHAR2, ll_called_nbr VARCHAR2, ll_pass out number )
IS
calling_area_id NUMBER(12,0);
called_area_id NUMBER(12,0);
calling_type NUMBER(12,0);
called_type NUMBER(12,0);
ll_count integer;
BEGIN
begin .
select area_id
into calling_area_id
from acct
where acc_nbr =ll_calling_nbr
and state = '10A';
.
EXCEPTION
when no_data_found then
NULL;
when others then
raise_application_error( -20232, '出错'||sqlerrm ) ;
END;
begin
select area_id
into called_area_id
from acct
where acc_nbr = ll_called_nbr
and state = '10A';
EXCEPTION
when no_data_found then
NULL;
when others then
raise_application_error( -20233, '出错'||sqlerrm ) ;
END; .
.
.
.
END;
不管用什么开发工具异常肯定是要捕捉处理的,
赞同这个观点!!!!!