1,procedure的参数若有default值,则调用时,可无此参数,若有此参数但未赋值,默认为传递了一个null值,在异常捕捉时用is null,而不是=null;
2,procedure 的参数若无default值,调用时必须有参数;
3,exception声明,exceptionname EXCEPTION;
4,exception初始化,PRAGMA EXCEPTION_INIT(exceptionname,exceptionselfdefinitioncode);
create or replace procedure XX425457_ADD_PRODUCT_P(
p_product_category_code IN xx425457_products.product_category_code%type,
p_description IN xx425457_products.description%type,
p_price IN xx425457_products.price%type,
p_qty_on_hand IN xx425457_products.qty_on_hand%type default 0
) is
e_category_is_null exception;
pragma exception_init(e_category_is_null,-101);
e_description_is_null exception;
pragma exception_init(e_description_is_null,-102);
e_price_is_zero exception;
pragma exception_init(e_price_is_zero,-103);
begin
if p_product_category_code is null then
raise e_category_is_null;
elsif p_description is null then
raise e_description_is_null;
elsif p_price is null then
raise e_price_is_zero;
end if;
insert into xx425457_products(productNo,product_category_code,description,price,qty_on_hand)
values(xx425457_seq_product.nextval,p_product_category_code,p_description,p_price,p_qty_on_hand);
exception
when e_category_is_null then
dbms_output.put_line('e_product_category_code_is_null'||'---'||SQLCODE||'---'||SQLERRM);
when e_description_is_null then
dbms_output.put_line('e_description_is_null'||'---'||SQLCODE||'---'||SQLERRM);
when e_price_is_zero then
dbms_output.put_line('e_price_is_zero'||'---'||SQLCODE||'---'||SQLERRM);
when others then
dbms_output.put_line('others exceptin'||'---'||SQLCODE||'---'||SQLERRM);
end XX425457_ADD_PRODUCT_P;
Anonymous block:
declare
v_product_category_code xx425457_products.product_category_code%type := 'A';
v_description xx425457_products.description%type :='very good shirt';
v_price xx425457_products.price%type;
begin
XX425457_ADD_PRODUCT_P(v_product_category_code,v_description,v_price);
end;
注意:
1.Oracle 标准的错误信息的代码范围是
-20000 到 -00001
2.自定义的Oracle错误代码范围是
-20999到-20001
Example:自定义异常-错误码SQLCODE-错误信息SQLERRM-raise_application_error(SQLCODE,SQLERRM)
/* Formatted on 2012/02/21 18:42 (Formatter Plus v4.8.7) */
DECLARE
org_id NUMBER:=-1;
excp_org_id_error EXCEPTION;
PRAGMAEXCEPTION_INIT(excp_org_id_error,-20001);
BEGIN
IF org_id<0
THEN
raise_application_error(-20001,'org_id should be greater than 0!');
ENDIF;
EXCEPTION
WHEN excp_org_id_error
THEN
DBMS_OUTPUT.put_line('ORA' ||SQLCODE ||':' ||SQLERRM);
END;
DBMS Output:
ORA-20001:ORA-20001: org_id should be greater than 0!
说明:异常name,异常message通过异常code联系起来