041 - [Oracle PL/SQL] Functions - no_data_found

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

 

 

 

# NOW LET TRY THIS 
# no data found not raised in select query, because the function not have exception
# PL/SQL 로 실행하면 오류가 발생한다

begin
    dbms_output.put_line(get_sal(9999))  ;
end;
----------------------------------------------------
Error report -
ORA-01403: no data found
ORA-06512: at "HR.GET_SAL", line 7
ORA-06512: at line 2
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.



# select 에서 호출하면 null 값을 반환한다
select get_sal(9999) from dual;
----------------------------------------------------
GET_SAL(9999)
-------------
null



create or replace function get_sal
    (p_emp_id number)
    return number
is
    v_sal number;
begin 
    select salary into v_sal
    from employees
    where employee_id=p_emp_id;
    
    return v_sal;
    exception 
        when no_data_found then
        return -1;
    
end;



# 예외처리해서 오류가 발생하지 않는다.
select get_sal(9999) from dual;
------------------------------------------
GET_SAL(9999)
-------------
           -1

begin
    dbms_output.put_line(get_sal(9999))  ;
end;
----------------------------------------------------
-1