032 - [Oracle PL/SQL] Procedure - (IN parameter)/ Error 처리

<샘플코드에서 사용한 데이터는 HR 스키마이고, 오라클 설치시 생성할 수 있는 기본 스키마 입니다>

 

 

 

 

 

 

 

 

# CLI 환경에서 컴파일하는 방법
# 세미콜론을 누락해서 오류가 발생한 상황과 오류를 조회하는 방법

SQL> CREATE OR REPLACE PROCEDURE UPDATE_SAL
    (P_EMP_ID IN NUMBER, P_AMOUNT IN NUMBER)
IS
--here you define variables
--n number;
BEGIN

    UPDATE employees
    set salary=salary+P_AMOUNT
    where employee_id=P_EMP_ID -- 세미콜른 삭제하고 컴파일해서 오류 확인하기
    commit;

    exception
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE (SQLCODE);
        DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17
 18  /

Warning: Procedure created with compilation errors.

SQL> show error;
Errors for PROCEDURE UPDATE_SAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/5	 PL/SQL: SQL Statement ignored
11/5	 PL/SQL: ORA-00933: SQL command not properly ended
SQL>

 

 

 

# SQL Developer 에서 컴파일하는 방법과 오류 확인하는 방법

CREATE OR REPLACE PROCEDURE UPDATE_SAL
    (P_EMP_ID IN NUMBER, P_AMOUNT IN NUMBER)
IS
--here you define variables
--n number;
BEGIN

    UPDATE employees
    set salary=salary+P_AMOUNT
    where employee_id=P_EMP_ID -- 세미콜른 삭제하고 컴파일해서 오류 확인하기
    commit;

    exception
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE (SQLCODE);
        DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
--try to do it in sqlplus

--also the error can be found in user_errors
select * from user_errors
where name='UPDATE_SAL'

 

 

 

 

# Procedure UPDATE_SAL의 오류를 수정해서 컴파일.

select employee_id, first_name, last_name, salary from employees
where employee_id=100;
--------------------------------------------------------------------
EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        100 Steven               King                           24000
        
        
--call the PROCEDURE alone
execute UPDATE_SAL (100,50);


select employee_id, first_name, last_name, salary from employees
where employee_id=100;
-----------------------------------------------------------------
EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        100 Steven               King                           24050

 

 

 

--calling the PROCEDURE for a block
# 아래코드를 실행하고 입력박스에는 100, 50을 입력한다.

begin
    UPDATE_SAL (&emp_id,&amount); --UPDATE_SAL (100,50)
end;



select employee_id, first_name, last_name, salary from employees
where employee_id=100;
---------------------------------------------------------------------
EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        100 Steven               King                           24100

 

 

 

--Procedure 조회.
select * from user_objects
where object_name='UPDATE_SAL';


--Procedure 소스 조회.
select * from user_source
where name='UPDATE_SAL'
order by line;
----------------------------------------------------------------------

NAME        TYPE         LINE TEXT                                                               
----------- ----------- ----- -------------------------------------------------------------------
UPDATE_SAL  PROCEDURE       1 PROCEDURE UPDATE_SAL                                               
UPDATE_SAL  PROCEDURE       2     (P_EMP_ID IN NUMBER, P_AMOUNT IN NUMBER)                       
UPDATE_SAL  PROCEDURE       3 IS                                                                 
UPDATE_SAL  PROCEDURE       4 --here you define variables                                        
UPDATE_SAL  PROCEDURE       5 --n number;                                                        
UPDATE_SAL  PROCEDURE       6 BEGIN                                                              
UPDATE_SAL  PROCEDURE       7                                                                    
UPDATE_SAL  PROCEDURE       8     UPDATE employees                                               
UPDATE_SAL  PROCEDURE       9     set salary=salary+P_AMOUNT                                     
UPDATE_SAL  PROCEDURE      10     where employee_id=P_EMP_ID; -- 세미콜른 삭제하고 컴파일해서 오류 확인하기
UPDATE_SAL  PROCEDURE      11     commit;                                                        
UPDATE_SAL  PROCEDURE      12                                                                    
UPDATE_SAL  PROCEDURE      13     exception                                                      
UPDATE_SAL  PROCEDURE      14         WHEN OTHERS THEN                                           
UPDATE_SAL  PROCEDURE      15         DBMS_OUTPUT.PUT_LINE (SQLCODE);                            
UPDATE_SAL  PROCEDURE      16         DBMS_OUTPUT.PUT_LINE (SQLERRM);                            
UPDATE_SAL  PROCEDURE      17 END;                                                               

17 rows selected. 


--to drop the procedure
drop procedure UPDATE_SAL;