만들었어요

/*함수
  -- 프로시저와의 차이점
  프로시저는 in, out, in out 모드를 사용해서 값을 반환하기도 하고, 반환하지 않고 종료할 수 있다.
  
  함수는 작업을 수행한 후에 결과를 반환
  
  -- 함수의 구문 형식
    create [or replace] function 함수명
      (파라미터 파라미터데이터타입,
      파라미터1 파라미터데이터타입1,
    ...)
    
    return datatype
    is [as]
    PL/SQL 블럭;    
  
*/

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;
  return max_sal; -- 위에서 기술한 return type과 일치해야...
end;
/

select dept_max_sal(50) from dual;


create or replace function cnt_member
  (dnum number)
return number
is
  total_cnt number;
begin
  select count(*) into total_cnt
  from employees
  where department_id = dnum;
  return total_cnt;
end;
/

select cnt_member(50) from dual;

-- 이런식으로도 쓸 수 있다!
select distinct department_id, cnt_member(department_id) 부서별인원수 from employees;


create or replace function avg_sal
  (dept_id employees.department_id%type)
return number
is
  avg_sal number;

begin
  select round(avg(salary), 2) into avg_sal
  from employees
  where department_id = dept_id;
  return avg_sal;
end;
/


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


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 a, employees b
  where a.department_id = b.department_id
    and b.employee_id = emp_id;
  -- 서브쿼리 사용
--  select department_name into dept_name
--  from departments
--  where department_id = (select department_id from employees
--                          where employee_id = emp_id);
  return emp_id;
end;
/
  

select distinct last_name, department_id, emp_dept_name(employee_id) 
from employees;

/*

/*
PL/SQL 서브 프로그램 : 데이터베이스에 저장된 PL/SQL
  - 서브프로그램은 내장된 함수와 프로시저가 있다.
    
  지금까지 했던건 익명 블록...
  
  <익명 블록의 특징>
    . 이름이 없는 PL/SQL 블록
    . 사용할 때마다 컴파일이 필요
    . 데이터베이스에 저장되어 있지 않다.
    . 다른 응용프로그램에서 사용할 수 없다.
    . 값을 반환할 수 없다.
    . 매개변수를 사용할 수 없다.
    
  <서브프로그램의 특징>
    . 이름이 있는 PL/SQL 블록
    . 최초 실행될 때 한번만 컴파일 한다.
    . 데이터베이스에 저장되어 있다.
    . 다른 응용프로그램에서 사용할 수 있다.
    . 함수일 경우 값을 반환할 수 있다.
    . 매개변수를 사용할 수 있다.
    
*/
/*
프로시저 : 특정 처리를 위한 서브 프로그램의 한 유형
         단독으로 실행 가능하며, 다른 응용프로그램에서 호출되어 실행 가능
  
  구문 형식
    create procedure 프로시저명
      파라미터1[in, out, in out] 데이터타입,
      파라미터2[in, out, in out] 데이터타입
      ...
    is [as]
      변수 선언부;
      
    begin
      프로시저 본문 처리부;
    
    exception
      예외처리부;
      
    end;
    /
*/

-- create or replace로 하면 새로 만들거나 대체하거나...
create or replace procedure update_emp
is
begin
  update employees
  set first_name = '마이클'
  where department_id = 20
    and job_id = 'MK_MAN';
end;
/

-- 프로시저를 실행시키기 위한 구문 : exec 또는 execute 프로시저명;


exec update_emp;


create or replace procedure up_sal
(emp_id employees.employee_id%type) -- 매개변수로 지정하는 것
is
begin
  update employees set salary = 3500
  where employee_id = emp_id;
end;
/

select salary from employees
where employee_id=130;

exec up_sal(130);


create or replace procedure emp_name_sal
  (emp_id employees.employee_id%type)
is
  emp_name employees.last_name%type;
  emp_salary employees.salary%type;
  
begin
  select last_name, salary into emp_name, emp_salary
  from employees
  where employee_id = emp_id;
  
  dbms_output.put_line('사원명 : ' || emp_name);
  dbms_output.put_line('급여 : ' || emp_salary);
  
end;
/

set serveroutput on;

exec emp_name_sal(124);


-- out 모드 사용 예(위엔 in이지만 생략한 것)

create or replace procedure emp_info
  (emp_id in employees.employee_id%type,  -- in은 생략 가능
  emp_name out employees.last_name%type,  -- 다른 응용프로그램으로 그 값이 전달된다는 의미
  emp_salary out employees.salary%type
  )
is
begin
  select last_name, salary into emp_name, emp_salary
  from employees
  where employee_id = emp_id;
end;
/

-- 다른 응용프로그램에서 사용 가능한지 테스트
declare
  emp_name employees.last_name%type;
  salary employees.salary%type;
  
begin
  emp_info(124, emp_name, salary);  -- 124를 프로시저에 전달하여 두 값(emp_name, salary)을 가져오는 형식.
  dbms_output.put_line(emp_name || ' 사원의 급여는 ' || salary || ' 입니다.');
end;
/


variable emp_name varchar2(10); -- 변수를 선언하기 위한 예약어
variable salary number;
exec emp_info(124, :emp_name, :salary);

print emp_name salary;  -- 출력


declare
  emp_name employees.last_name%type;
  salary employees.salary%type;
begin
  emp_info(emp_id => 124,
          emp_name => emp_name,
          emp_salary => salary); -- 연산자 =>를 이용해서 파라미터의 값을 직접 지정
  dbms_output.put_line(emp_name || ' 사원의 급여는 ' || salary || ' 입니다.');
end;
/

/*
  예외 처리
  -- 에러
    1. 컴파일 에러 : PL/SQL 블록이 parse(구문 분석)되는 동안에 발생되는 에러(사용자의 오타...)
    2. 런타임 에러(=exception) : 
      PL/SQL 블록이 실행되는 동안 발생하는 에러
        a. 오라클 예외
          - 오라클에서 제공되는 예외
            ㄱ. Predefined Oracle Exception
              사전에 정해진 예외
            ㄴ. Non-predefined ORACLE Exception
              사전에 정해지지 않은 예외
        b. 사용자 정의 예외
          사용자에 의해 정의되는 예외
            declare(선언) 부에서
              예외명 exception;
            begin(실행)부나 exception(예외처리)부에서 raise 문을 이용해서 예외를 발생

          예외처리부 : 예외 발생시 어떻게 처리할 것인지에 대한 예외 처리내용이 들어감
          - 예외처리부 형식  
          Exception
            when 예외명 then
              실행문..
            when 예외명2 then
              실행문..
            ...
            when OTHERS then  -- 예외상황이 아닐 경우
              실행문..

*/

-- predefined oracle Exception

declare
  emp_lastname employees.last_name%type;
begin
  select last_name into emp_lastname
  from employees
  where last_name like 'B%';
  
  dbms_output.put_line('사원명은 : ' || emp_lastname || '입니다');
exception -- 결과가 없거나 2개 이상일 경우에 대한 예외처리
  when no_data_found then
    dbms_output.put_line('해당 사원이 없습니다.');
  when too_many_rows then
    dbms_output.put_line('사원이 2명 이상입니다.');
end;
/

set serveroutput on;


-- 사용자 정의 예외처리
-- pragma 이용한 예외처리
declare
  emp_exist exception; -- 예외명 적어주기
  pragma exception_init(emp_exist, -2292); -- pragma 키워드 : 컴파일러에게 지시를 해줌(emp_eixst 예외를 -2292코드로 처리하겠다...)
begin
  delete departments
  where department_id = &deptno;  -- 사용자로부터 입력받아서 지우겠다
  commit;

exception
  when emp_exist then 
    dbms_output.put_line('사원이 존재합니다..... 지울 수 없어요!!');
  
end;
/

-- raise 이용한 예외처리
declare
  emp_id employees.employee_id%type;
  emp_name employees.last_name%type;
  
  no_emp exception;
  
begin
  delete employees
  where employee_id = &empid;
  
  if sql%notfound then-- 묵시적 커서 이용(employee_id가 없을 경우)
    raise no_emp; -- no_emp로 발생시키겠다
  end if;
exception
  when no_emp then
    dbms_output.put_line('해당 사원이 없습니다.');
  
end;
/


-- raise_application_error 프로시저 이용 (이용 가능한 번호 : 20000~29999)
declare
  emp_id employees.employee_id%type;
  emp_name employees.last_name%type;
  
begin
  delete employees
  where employee_id = &empi;
  
  if sql%notfound then
    raise_application_error(-20111, '사원이 없습니다..');
  end if;
end;
/