ORACLE 서브프로그램 함수(Function)

스터디/DB(ORACLE)

ORACLE 서브프로그램 함수(Function)

고무곰(GomuGom) 2017. 9. 19. 17:55

[ 서브프로그램의 함수 사용 ]

서브프로그램

- 프로시저

- 함수

[ 함수와 프로시저의 차이? ]

- 프로시저는 in, out, in out모드를 사용해서 값을 반환하기도 하고,

반환하지 않고 종료 할 수도 있다.

- 함수는 어떤 작업을 수행한 후에, 결과를 반환한다.

[ 함수의 구문 형식 ]

create (or replace) function 함수명

파라미터1 파라미터타입,

파라미터2 파라미터타입,...

return datatype

PL/SQL 블럭;

EX)

--EX)
create or replace function dept_max_sal
  (dept_id employees.department_id%type)
return number
IS
  max_sal employees.salary%type;
begin
  select max(salary) into max_sal
  from employees
  where department_id = dept_id;
  
  -- number 타입의 max_sal을 리턴시킴
  return max_sal; 
end;
/
  
select dept_max_sal(50) from dual;

create or replace function cnt_number
  (cnt number)
return number
IS
  total_cnt number;
begin
  select count(*) into total_cnt
  from employees
  where department_id = cnt;
  
  return total_cnt;
end;
/

select distinct cnt_number(50) from employees;
-- 부서별 인원수
select distinct department_id, cnt_number(department_id) from employees;

declare

begin
   dbms_output.put_line(cnt_number(50));
end;

create or replace function avg_sal
  (dept_id employees.department_id%type)
return number
IS
  avg_salary number;
begin
  select round(avg(salary),2) into avg_salary
  from employees
  where department_id = dept_id;
  
  return avg_salary;
end;
/

select department_id, avg_sal(department_id) 부서별평균급여
from employees
group by department_id;

create or replace function emp_dept_name
  (emp_id employees.employee_id%type)
return varchar2
IS
  dept_name departments.department_name%type;
BEGIN
  select department_name into dept_name
  from departments
  where department_id = (select department_id from employees 
                         where employee_id = emp_id);
  
  return dept_name;
END;
/

select distinct employee_id, emp_dept_name(employee_id) from employees;

저작자표시 비영리