贴段代码. 请大家评论,指点.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;
异常处理?begin ... begin ... exception when ... end; ... exception ... end;
另外,建议创建一个错误信息记录表,保存错误信息 如 ... exception when others then insert into tb_err(sqlcode,sqlerrm,...); ... end; ...
insert into t_errlog values(sysdate,spname,错误位置标签,to_char(errcode,errmsg);
至少要保存错误编码,或者错误描述 此外,最好保存 出错的位置 例如 存储过程名称,或者 函数名称等。 如: 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: 未找到数据PL/SQL 过程已成功完成。已用时间: 00: 00: 00.11 SQL>
有个问题是. 比如我想得到一个 product 的 cost.sp_cost(i_prodcode varchar2) as v_cost number(15,6); begin
select cost into v_cost from products where prodcode = i_prodcode;
exception when no_data_found then return ???; when other then insert into tb_err(sqlcode,sqlerrm,...); return ???; end;就是如果产生异常后, 那么这个 sp_cost 是返回 0.0? 还是返回出错代码 sqlcode? 还是其他的什摸值 ...?
搞错了, 应该是函数 sf_costsf_cost(i_prodcode varchar2) return numberas v_cost number(15,6); begin
select cost into v_cost from products where prodcode = i_prodcode;
exception when no_data_found then return ???; when other then insert into tb_err(sqlcode,sqlerrm,...); return ???; end;
这要根据你的业务规则来定义 比如 你的数据肯定不会返回负数 此时可以定义 when no_data_found then return -1; when other then insert into tb_err(sqlcode,sqlerrm,...); return -2;
多谢 bzszp(SongZip) ( ) 信誉:159 !
procedure(function)要加上注释,例外处理放在一块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; --
/** 定义例外 **/ exceptionDataNotFind EXCEPTION; --没有数据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;
WHEN OTHERS THEN .......end sf_qtys;
觉得把注释放在 proce/funct 的上面. 更常规一些.
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;
EXCEPTION when others then --异常处理 dbms_output.put_line(sqlcode); dbms_output.put_line(sqlerrm); close cps_cursor; END TEST; 请问各位达人,我刚写的这个Procedure执行时没有报错,但为什么dbms_output不能输出信息 而且还不能按要求向库中填入数据呢?????谢谢
(
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;
...
begin
...
exception when ...
end;
...
exception
...
end;
如
...
exception
when others then
insert into tb_err(sqlcode,sqlerrm,...);
...
end;
...
values(sysdate,spname,错误位置标签,to_char(errcode,errmsg);
此外,最好保存 出错的位置 例如 存储过程名称,或者 函数名称等。
如:
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: 未找到数据PL/SQL 过程已成功完成。已用时间: 00: 00: 00.11
SQL>
as
v_cost number(15,6);
begin
select cost
into v_cost
from products
where prodcode = i_prodcode;
exception
when no_data_found then
return ???;
when other then
insert into tb_err(sqlcode,sqlerrm,...);
return ???;
end;就是如果产生异常后, 那么这个 sp_cost 是返回 0.0? 还是返回出错代码 sqlcode?
还是其他的什摸值 ...?
v_cost number(15,6);
begin
select cost
into v_cost
from products
where prodcode = i_prodcode;
exception
when no_data_found then
return ???;
when other then
insert into tb_err(sqlcode,sqlerrm,...);
return ???;
end;
比如
你的数据肯定不会返回负数
此时可以定义
when no_data_found then
return -1;
when other then
insert into tb_err(sqlcode,sqlerrm,...);
return -2;
/** ------------------------------------------------------------------------------
'
'
'
'
------------------------------------------------------------------------------ **/
(
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; --
/** 定义例外 **/
exceptionDataNotFind EXCEPTION; --没有数据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;
WHEN OTHERS THEN
.......end sf_qtys;
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;
v_productid := row_download.productid; --将row_download中的productId字段值赋值给v_productid
v_contentid := row_download.contentid; --将row_download中的contentid字段值赋值给v_contentid
dbms_output.put_line('产品ID:'||row_download.productid);
--入库
insert into wap_d_cp_gain_test(pid,contentid) values (v_productid,v_contentid);
commit; --提交事务
END LOOP;
EXCEPTION when others then --异常处理
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);
close cps_cursor;
END TEST;
请问各位达人,我刚写的这个Procedure执行时没有报错,但为什么dbms_output不能输出信息
而且还不能按要求向库中填入数据呢?????谢谢