026 - [Oracle PL/SQL] Exceptions

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

 

PL/SQL Basic structure

 

 

 

 

Handling Exceptions with PL/SQL

 

 

 

 

 

 

 

 

 

 

 

 

 

# no exception case.

declare
    v_first_name employees.first_name%type;
begin
  
  select first_name into v_first_name
  from
  employees
  where employee_id=1; -- there is no emp_id=1
  
end;
-------------------------------------------------
Error report -
ORA-01403: no data found
ORA-06512: at line 5
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.



# 상기 코드를 예외처리한 예시

declare
    v_first_name employees.first_name%type;
begin
  
    select first_name into v_first_name
    from
    employees
    where employee_id=1; -- there is not emp_id=1
    dbms_output.put_line(v_first_name);
    
    exception
    when no_data_found then
    dbms_output.put_line('The query doesn''t retrieve any record');
end;
---------------------------------------------------
The query doesn't retrieve any record

 

 

 

# many exceptions
# 'xyx'  입력시 "no_data_found" 예외발생
# 'John' 입력시 "too_many_rows" 예외발생
# 1 숫자  입력시 "others"        예외발생

declare
    v_emp_id employees.employee_id%type;
begin
  
    select employee_id into v_emp_id
    from
    employees
    where first_name=&name; --try 'xyx' then try 'John' then try 1
    
    exception
    when no_data_found then
    dbms_output.put_line('The query doesn''t retrieve any record');
    
    when too_many_rows then
    dbms_output.put_line('The query  retrieve more than one record');
    
    when others then 
    dbms_output.put_line('Other ERROR');
end;
----------------------
The query doesn't retrieve any record

The query  retrieve more than one record

Other ERROR

# "Other ERROR" 에 해당하는 상세 내용은 아래와 같다.
Error report -
ORA-01722: invalid number
ORA-06512: at line 5
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.