dws
EXCEPTION
更新时间:
2019/08/09 GMT+08:00
默认配置是 supportDupValOnIndex=UNIQUE_VIOLATION(仅适用于Gauss V1R8C版本)。
dup_val_on_index(默认配置)
输入
CREATE OR REPLACE PACKAGE BODY test AS PROCEDURE test1234(i1 int) IS out_flag VARCHAR(1); BEGIN INSERT INTO tab1 VALUES (i1, sysdate); RETURN; EXCEPTION when no_data_found then out_flag := '1'; DBMS_OUTPUT.PUT_LINE(out_flag); return; WHEN DUP_VAL_ON_INDEX THEN out_flag := '2'; DBMS_OUTPUT.PUT_LINE(out_flag); return; end test1234; end test; /
输出
CREATE OR REPLACE PROCEDURE test.test1234 ( i1 int ) IS out_flag VARCHAR( 1 ) ; BEGIN INSERT INTO tab1 VALUES ( i1 ,sysdate ) ; RETURN ; EXCEPTION when no_data_found then out_flag := '1' ; DBMS_OUTPUT.PUT_LINE ( out_flag ) ; return ; WHEN UNIQUE_VIOLATION THEN out_flag := '2' ; DBMS_OUTPUT.PUT_LINE ( out_flag ) ; return ; END ; /
invalid_number(默认配置)
输入
CREATE OR REPLACE PACKAGE BODY test AS PROCEDURE test123(i1 int) IS out_flag VARCHAR(1); BEGIN INSERT INTO tab1 VALUES (i1 / 0, sysdate); RETURN; EXCEPTION when zero_divide then out_flag := '1'; DBMS_OUTPUT.PUT_LINE(out_flag); return; WHEN invalid_number then out_flag := '2'; DBMS_OUTPUT.PUT_LINE(out_flag); return; WHEN OTHERS THEN out_flag := '-1'; DBMS_OUTPUT.PUT_LINE(out_flag); return; end test123; end test; /
输出
CREATE OR REPLACE PROCEDURE test.test123 ( i1 int ) IS out_flag VARCHAR( 1 ) ; BEGIN INSERT INTO tab1 VALUES ( i1 / 0 ,sysdate ) ; RETURN ; EXCEPTION when zero_divide then out_flag := '1' ; DBMS_OUTPUT.PUT_LINE ( out_flag ) ; return ; WHEN invalid_number then out_flag := '2' ; DBMS_OUTPUT.PUT_LINE ( out_flag ) ; return ; WHEN OTHERS THEN out_flag := '-1' ; DBMS_OUTPUT.PUT_LINE ( out_flag ) ; return ; END ; /
zero_divide(默认配置)
输入
CREATE OR REPLACE PACKAGE BODY test AS PROCEDURE test123(i1 int) IS out_flag VARCHAR(1); BEGIN INSERT INTO tab1 VALUES (i1 / 0, sysdate); RETURN; EXCEPTION when zero_divide then out_flag := '1'; DBMS_OUTPUT.PUT_LINE(out_flag); return; WHEN DUP_VAL_ON_INDEX THEN out_flag := '2'; DBMS_OUTPUT.PUT_LINE(out_flag); return; WHEN OTHERS THEN out_flag := '-1'; DBMS_OUTPUT.PUT_LINE(out_flag); return; end test123; end test; /
输出
CREATE OR REPLACE PROCEDURE test.test123 ( i1 int ) IS out_flag VARCHAR( 1 ) ; BEGIN INSERT INTO tab1 VALUES ( i1 / 0 ,sysdate ) ; RETURN ; EXCEPTION when zero_divide then out_flag := '1' ; DBMS_OUTPUT.PUT_LINE ( out_flag ) ; return ; WHEN UNIQUE_VIOLATION THEN out_flag := '2' ; DBMS_OUTPUT.PUT_LINE ( out_flag ) ; return ; WHEN OTHERS THEN out_flag := '-1' ; DBMS_OUTPUT.PUT_LINE ( out_flag ) ; return ; END ; /
如果配置为 supportDupValOnIndex=OTHERS(仅适用于非Gauss V1R8C版本),则:
dup_val_on_index
输入
CREATE OR REPLACE PACKAGE BODY test AS PROCEDURE test1234(i1 int) IS out_flag VARCHAR(1); BEGIN INSERT INTO tab1 VALUES (i1, sysdate); RETURN; EXCEPTION when no_data_found then out_flag := '1'; DBMS_OUTPUT.PUT_LINE(out_flag); return; WHEN DUP_VAL_ON_INDEX THEN out_flag := '2'; DBMS_OUTPUT.PUT_LINE(out_flag); return; end test1234; end test; /
输出
CREATE OR REPLACE PROCEDURE test.test1234 ( i1 int ) IS out_flag VARCHAR( 1 ) ; BEGIN INSERT INTO tab1 VALUES ( i1 ,sysdate ) ; RETURN ; EXCEPTION when no_data_found then out_flag := '1' ; DBMS_OUTPUT.PUT_LINE ( out_flag ) ; return ; WHEN OTHERS THEN IF sqlstate = 23505 THEN out_flag := '2' ; DBMS_OUTPUT.PUT_LINE ( out_flag ) ; return ; END IF ; END ; /
invalid_number
输入
CREATE OR REPLACE PACKAGE BODY test AS PROCEDURE test123(i1 int) IS out_flag VARCHAR(1); BEGIN INSERT INTO tab1 VALUES (i1 / 0, sysdate); RETURN; EXCEPTION when zero_divide then out_flag := '1'; DBMS_OUTPUT.PUT_LINE(out_flag); return; WHEN invalid_number then out_flag := '2'; DBMS_OUTPUT.PUT_LINE(out_flag); return; WHEN OTHERS THEN out_flag := '-1'; DBMS_OUTPUT.PUT_LINE(out_flag); return; end test123; end test; /
输出
CREATE OR REPLACE PROCEDURE test.test123 ( i1 int ) IS out_flag VARCHAR( 1 ) ; BEGIN INSERT INTO tab1 VALUES ( i1 / 0 ,sysdate ) ; RETURN ; EXCEPTION when DIVISION_BY_ZERO then out_flag := '1' ; DBMS_OUTPUT.PUT_LINE ( out_flag ) ; return ; WHEN INVALID_PARAMETER_VALUE then out_flag := '2' ; DBMS_OUTPUT.PUT_LINE ( out_flag ) ; return ; WHEN OTHERS THEN out_flag := '-1' ; DBMS_OUTPUT.PUT_LINE ( out_flag ) ; return ; END ; /
zero_divide
输入
CREATE OR REPLACE PACKAGE BODY test AS PROCEDURE test123(i1 int) IS out_flag VARCHAR(1); BEGIN INSERT INTO tab1 VALUES (i1 / 0, sysdate); RETURN; EXCEPTION when zero_divide then out_flag := '1'; DBMS_OUTPUT.PUT_LINE(out_flag); return; WHEN DUP_VAL_ON_INDEX THEN out_flag := '2'; DBMS_OUTPUT.PUT_LINE(out_flag); return; WHEN OTHERS THEN out_flag := '-1'; DBMS_OUTPUT.PUT_LINE(out_flag); return; end test123; end test; /
输出
CREATE OR REPLACE PROCEDURE test.test123 ( i1 int ) IS out_flag VARCHAR( 1 ) ; BEGIN INSERT INTO tab1 VALUES ( i1 / 0 ,sysdate ) ; RETURN ; EXCEPTION when DIVISION_BY_ZERO then out_flag := '1' ; DBMS_OUTPUT.PUT_LINE ( out_flag ) ; return ; WHEN OTHERS THEN IF sqlstate = 23505 THEN out_flag := '2' ; DBMS_OUTPUT.PUT_LINE ( out_flag ) ; return ; ELSE out_flag := '-1' ; DBMS_OUTPUT.PUT_LINE ( out_flag ) ; return ; END IF ; END ; /
父主题:
模式对象
相关文档
相关产品
