040 - [Oracle PL/SQL] Creating function

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

 

 

 

# create a function to return the salary for an employee
#  so we need one parameter (in ) number ( employee_id )
# the return value should be also number because it is the salary

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;
end;
------------------------------------------
Function GET_SAL compiled

 

 

 

• Many methods to invoke the function

 

# 1. as part of expression 
declare
    v_sal number;
begin
    v_sal:=get_sal(100);
    dbms_output.put_line (v_sal);
end;
--------------
24100



# 2. as parameter value
begin
    dbms_output.put_line(get_sal(100));
end;
----------------

--also we do this
execute dbms_output.put_line (get_sal(100) );



# 3. using host variable
variable b_salary number;

execute :b_salary:=get_sal(100)

print b_salary
----------------------------------



# 4. as part of select 

select get_sal(100) from dual;

select employee_id,first_name, get_sal(employee_id) as salary
from employees
where department_id=20;
----------------



select * from user_objects
where object_name='GET_SAL';

SELECT LINE, TEXT 
FROM USER_SOURCE
WHERE NAME='GET_SAL';
--------------------------------------------------

 LINE TEXT                                              
----- --------------------------------------------------
    1 function get_sal                                  
    2     (p_emp_id number)                             
    3     return number                                 
    4 is                                                
    5     v_sal number;                                 
    6 begin                                             
    7     select salary into v_sal                      
    8     from employees                                
    9     where employee_id=p_emp_id;                   
   10                                                   
   11     return v_sal;                                 
   12 end;

12 rows selected.