Exception 예

▣ Exception 의 예

S SCOTT> r

  1  create or replace procedure insertDept(

  2     deptnox dept.deptno%type,

  3     dname dept.dname%type,

  4     locxdept.loc%type

  5  ) as

  6  begin

  7     insert into dept values(deptnox,dnamex,locx);

  8* end;

S SCOTT> @insertDept

Procedure created.

S SCOTT> exec insertDept(1,'aaa','bb');

PL/SQL procedure successfully completed.

S SCOTT> select * from dept;

S SCOTT> exec insertDept(10,'bbbb','bbbb');

BEGIN insertDept(10,'bbbb','bbbb'); END;

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

ORA-06512: at "SCOTT.INSERTDEPT", line 7

ORA-06512: at line 1

create or replace procedure insertDept(

        deptnox dept.deptno%type,

        dnamex dept.dname%type,

        locx dept.loc%type

) as

begin

        insert into dept values(deptnox,dnamex,locx);

exception

        when DUP_VAL_ON_INDEX then

                for d in (select * from dept where deptno=deptnox) loop

                        dbms_output.put_line(d.deptno||','||d.dname||' is exist');

                end loop;

        WHEN OTHERS THEN

                dbms_output.put_line(SQLERRM);

                dbms_output.put_line(SQLCODE);

end;

/

   

▣ 급여가 500 이하면 작업을 롤백 시키는 기능을 추가하시오

S SCOTT> create or replace procedure raiseSal(empnox emp.empno%type,raiseSal number)

  2  as

  3  begin

  4     update emp set sal=sal+raiseSal where empno=empnox;

  5  end;

  6  /

Procedure created.

create or replace procedure raiseSal(empnox emp.empno%type,raiseSal number)

as

        low_sal_err EXCEPTION;

begin

        update emp set sal=sal+raiseSal where empno=empnox;

        for i in (select sal from emp where empno=empnox) loop

                if i.sal<500 then

                        raise low_sal_err;

                else

                        commit;

                end if;

        end loop;

exception

        when low_sal_err then

                rollback;

                dbms_output.put_line(raiseSal || ' is too low sal');

end;

/

S SCOTT> @raiseSal

Procedure created.

S SCOTT> exec raiseSal(7369,-500);

-500 is too low sal

PL/SQL procedure successfully completed.