set serveroutput on --打开服务器端的输出功能
create or replace procedure TEST(
cur_date IN date
)
as
v_productid wap_d_download.productid%TYPE; --产品ID
v_contentid wap_d_download.contentid%TYPE; --内容ID
--从download表中查询
CURSOR cps_cursor is
select t.productid, t.contentid from wap_d_download t where t.id = '17826198';
row_download cps_cursor%ROWTYPE;
BEGIN
open cps_cursor;
LOOP
fetch cps_cursor into row_download;
exit when cps_cursor%NOTFOUND;
至少要保存错误编码,或者错误描述
此外,最好保存 出错的位置 例如 存储过程名称,或者 函数名称等。
如:
SQL> set serveroutput on
SQL> declare
2 n number;
3 begin
4 select 1 into n from dual where 1=2;
5 exception when others then
6 dbms_output.put_line(sqlcode);
7 dbms_output.put_line(sqlerrm);
8 end;
9 /
100
ORA-01403: 未找到数据
create or replace function sf_qtys
(
i_set_id in number,
i_partnum in varchar2,
i_date_from in date,
i_date_to in date
)
return number
as
v_qtys number(15,6) := 0.0;
begin
select sum(prodcap1)
into v_qtys
from k_worder_l
where set_id = i_set_id
and prodcode = i_partnum
and proddate >= i_date_from
and proddate <= i_date_to;
return v_qtys;
exception
when no_data_found then
return 0.0;
end sf_qtys;
create or replace function sf_qtys
/** ------------------------------------------------------------------------------
'
'
'
'
------------------------------------------------------------------------------ **/
(
i_set_id in number, --
i_partnum in varchar2, --
i_date_from in date, --
i_date_to in date --
)
return number
as
v_qtys number(15,6) := 0.0; --
begin
select sum(prodcap1)
into v_qtys
from k_worder_l
where set_id = i_set_id
and prodcode = i_partnum
and proddate >= i_date_from
and proddate <= i_date_to;
return v_qtys;
exception
when no_data_found then
RAISE exceptionDataNotFind;
--例外处理
EXCEPTION
WHEN exceptionDataNotFind THEN
return 0.0;