[Oracle] 프로시저, 함수

프로시저(Procedure)

-특정 기능을 수행뒤, 값을 반환
-사용자 정의 함수
-자주 사용하는 PL/SQL 블록을 재사용하기 위해 모듈화한 것

 

CREATE OR REPLACE PROCEDURE 함수명 

                   (파라미터1 데이터타입, 파라미터2 데이터타입, ...)

      IS [AS]

        변수선언부 ..

BEGIN

     처리내용..

  EXCEPTION 예외처리

END;

부서번호를 받아 사원리스트 처리

CREATE OR REPLACE PROCEDURE listByDeptno(p_deptno IN employees.department_id%TYPE)

IS
        CURSOR employee_cursors IS
                        SELECT * FROM employees
                                WHERE department_id = p_deptno;
        employee_record employee_cursors%ROWTYPE;
BEGIN
        dbms_output.put_line('===========사원리스트============');

FOR employee_record IN employee_cursors LOOP
          dbms_output.put_line(p_deptno ||'   '||employee_record.employee_id||'    '|| employee_record.last_name);
END LOOP;

END;

--프로시저 실행

EXECUTE listByDeptno(100);

 

--매개변수 디폴트 값 설정

CREATE OR REPLACE PROCEDURE my_new_job_proc3(
    p_job_id IN jobs2.job_id%TYPE,
    p_job_title IN jobs2.job_title%TYPE,
    p_min_salary IN jobs2.min_salary%TYPE := 100,
    p_max_salary IN jobs2.max_salary%TYPE := 1000)
IS
    v_cnt NUMBER := 0;

BEGIN
  --동일한 job_id 체크
  SELECT COUNT(*) INTO v_cnt
    FROM jobs2
    WHERE job_id = p_job_id;

  IF v_cnt = 0 THEN
    INSERT INTO jobs2(job_id, job_title, min_salary, max_salary)
    VALUES(p_job_id, p_job_title, p_min_salary, p_max_salary);
  ELSE
    UPDATE jobs2
        SET job_title = p_job_title,
        min_salary = p_min_salary,
        max_salary = p_max_salary
    WHERE job_id = p_job_id;
  END IF;    
        
    COMMIT;
END;

EXECUTE my_new_job_proc2('a2', 'a222');

---

-OUT, IN 매개변수 사용

CREATE OR REPLACE PROCEDURE my_new_job_proc4(
    p_job_id IN jobs2.job_id%TYPE,
    p_job_title IN jobs2.job_title%TYPE,
    p_min_salary IN jobs2.min_salary%TYPE := 100,
    p_max_salary IN jobs2.max_salary%TYPE := 1000,
    p_result OUT NUMBER)
IS
    v_cnt NUMBER := 0;

BEGIN
  --동일한 job_id 체크
  SELECT COUNT(*) INTO v_cnt
    FROM jobs2
    WHERE job_id = p_job_id;

  IF v_cnt = 0 THEN
    p_result := 1;
    INSERT INTO jobs2(job_id, job_title, min_salary, max_salary)
    VALUES(p_job_id, p_job_title, p_min_salary, p_max_salary);
  ELSE
    p_result := 2;
    UPDATE jobs2
        SET job_title = p_job_title,
        min_salary = p_min_salary,
        max_salary = p_max_salary
    WHERE job_id = p_job_id;
  END IF;    
        
    COMMIT;
END;

DECLARE
    p_result NUMBER;
BEGIN
    my_new_job_proc4('c', 'c222', 111000,555000,p_result);
    dbms_output.put_line(p_result);
END;

---

함수(function)

-프로시저에 비해 실행 결과를 더 유연하게 사용가능 -> 기존 sql문 사용 가능

CREATE OR REPLACE FUNCTION 함수명 

                   (파라미터1 데이터타입, 파라미터2 데이터타입, ...)

      RETURN 데이터타입 IS [AS]

BEGIN

     처리내용

   RETURN 반환값;

END;

 

CREATE OR REPLACE FUNCTION getSalary(
                    p_no employees.employee_id%TYPE)
        RETURN NUMBER IS
                    v_salary NUMBER;

BEGIN 
    SELECT salary INTO v_salary 
            FROM employees
            WHERE employee_id = p_no;

        RETURN v_salary;
END;


SELECT getSalary(100) FROM dual;

사번 입력 받아 이름을 반환하는 함수를 구현, 없으면 => 해당사원 없음


CREATE OR REPLACE FUNCTION get_emp_name (
    p_employee_id employees.employee_id%TYPE)
     RETURN VARCHAR2
  IS
    vs_emp_name employees.last_name%TYPE;
    result VARCHAR2(50) := null;
  BEGIN
    -- 사원명을 가져온다.
    SELECT last_name
      INTO vs_emp_name
      FROM employees
     WHERE employee_id = p_employee_id;
     
  RETURN vs_emp_name;


EXCEPTION
    WHEN NO_DATA_FOUND THEN
             return '해당사원없음';
  

  END;

SELECT get_emp_name(1000) FROM dual;