oracle 프로시저, 함수, 패키지

오라클 PL/SQL 프로시저, 함수, 패키지

함수

형식)

CREATE OR REPLACE FUNCTION 함수명(

파라미터 데이터타입,  --파라미터(입력) 

파라미터 데이터타입

)

RETURN 데이터타입  --예약어 (return)

IS [AS]

변수 선언

BEGIN

처리내용...;

RETURN 리턴값;

END;

작성예) 사원번호를 받아서 급여를 출력하게끔 하게 하는 펑션

CREATE OR REPLACE FUNCTION emp_salaries

(

emp_id number   --EMP_ID가 NUMBER이기 떄문에

)

RETURN NUMBER IS

nSalaries NUMBER(9); --SALARY(데이터타입)

BEGIN

nSalaries := 0;

SELECT SALARY

   INTO  nSalaries

   FROM EMPLOYEES

  WHERE EMPLOYEE_ID = emp_id; --(입력재료)

RETURN nSalaries;

END;

사용예)

SELECT EMP_SALARIES(100) FROM DUAL;

문제1) 사원번호, 부서명, 업무명을 FUNCTION을 이용해서 출력하시오

1] 부서명 FUNCTION 생성

CREATE OR REPLACE FUNCTION get_dept_name

(

deptno number

)

RETURN VARCHAR2 IS

dName VARCHAR2(30);

BEGIN

dName := null;

SELECT department_name

   INTO  dName

   FROM DEPARTMENTS

  WHERE  DEPARTMENT_ID = deptno;

RETURN dName;

END;

2] 업무명 FUNCTION 생성

CREATE OR REPLACE FUNCTION HR.get_job_title

(jobid VARCHAR2)

RETURN VARCHAR2 IS

jobTitle VARCHAR2(35);

BEGIN

SELECT JOB_TITLE

    INTO jobTitle

    FROM JOBS

   WHERE JOB_ID = jobid;

RETURN jobTitle;

END;

3] 처리

SELECT   EMPLOYEE_ID

, A.FIRST_NAME || ' ' || A.LAST_NAME AS NAME

            , GET_DEPT_NAME(DEPARTMENT_ID)

, GET_JOB_TITLE(JOB_ID)

   FROM  EMPLOYEES

 WHERE DEPARTMENT_ID = 10

;

-> 서브쿼리를 사용한 경우

SELECT  EMPLOYEE_ID , FIRST_NAME || ' ' || LAST_NAME AS NAME

  , ( SELECT D.DEPARTMENT_NAME

           FROM DEPARTMENTS D

          WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID

       ) AS DEP_NAME

     , (

          SELECT J.JOB_TITLE

            FROM JOBS J

           WHERE J.JOB_ID = E.JOB_ID

       ) AS JOB_NAME

FROM EMPLOYEES E

  WHERE DEPARTMENT_ID = 100

;

;

-> 조인을 사용한 경우

SELECT A.EMPLOYEE_ID 

   , A.FIRST_NAME || ' ' || A.LAST_NAME AS NAME

   , J.JOB_TITLE

           , D.DEPARTMENT_NAME

  FROM EMPLOYEES A 

  INNER JOIN JOBS J

  ON A.JOB_ID = J.JOB_ID

  INNER JOIN DEPARTMENTS D

  ON A.DEPARTMENT_ID = D.DEPARTMENT_ID        

 WHERE A.DEPARTMENT_ID = 100

;

위 세 경우 결과는 동일하지만, 함수를 사용하였을 경우 조인이 발생하지 않는다.

부서번호를 파라미터로 넘기면, 그 부서번호에 대한 부서명을 가져올 뿐이다.

위 두 문장의 경우 테이블에 있는 데이터 양이 적기 때문에 성능 차이가 거의 없지만, 일단 조인을 수행하면 수행하지 않는 것 보다 부하를 주게 되므로 위의 경우 함수를 사용하는 것이 나은 방법이다.

성능상 문제로 무조건 함수를 사용하는 것도 해결 방법은 아니지만, 단순히 어떤 코드에 대해 명(이름)을 가져올 경우에는 자주 사용하는지를 고려하여 조인, 서브쿼리, 함수를 적절하게 사용하는 것이 필요하다.

프로시저

형식)

CREATE OR REPLACE PROCEDURE 프로시저명

( 파라미터 데이터타입 [IN | OUT | INOUT] 

  ...

)

IS [AS]

변수 선언부

....

BEGIN

프로시저 처리부

...

EXCEPTION

예외처리부

...

END;

문제2) 신입사원 입사 등록 프로시저를 생성하시오

[조건]

1. 프로시저 이름 : REGISTER_EMP
2. EMPLOYEE_ID : 사원번호, EMPLOYEES_SEQ(시퀀스)를 사용하여 입력하시오
3. FIRST_NAME 
4. LAST_NAME 
5. EMAIL
6. HIRE_DATE : 입사일, 현재 날짜로 입력하시오
7. JOB_ID ; 업무구분
8. DEPARTMENT_ID : 부서번호
9. MANAGER_ID¹ : 해당 부서의 MANAGER_ID를 입력하시오
10. SALARY : 급여값
11. PHONE : 전화번호

[신입사원 입력 정보]

1. FIRST_NAME : '길동'
2. LAST_NAME : '홍'
3. EMAIL : 'AAA@BBB.COM'
4. JOB_ID : 'IT_PROG'
5. DEPARTMENT_ID : 60
6. SALARY : 4500
7. PHONE : 000-1111-2222

 CREATE OR REPLACE PROCEDURE REGISTER_EMP

       (

           p_fname VARCHAR2 ,

           p_lname VARCHAR2 ,

           p_email VARCHAR2 ,

           p_jobid VARCHAR2 ,

           p_deptno NUMBER ,

           p_salary NUMBER , 

     p_phone VARCHAR2

       )

   IS

       v_Manager_id EMPLOYEES.MANAGER_ID%TYPE;

BEGIN

    -- 해당부서의 매니저 아이디값을 가져온다

    SELECT MANAGER_ID INTO v_Manager_id

      FROM DEPARTMENTS

     WHERE DEPARTMENT_ID = p_deptno;

    -- 사원테이블에 사원번호, 이름, 성, 이메일, 입사일, 업무구분, 부서번호,

    -- 메니저번호, 급여를 입력한다.

    INSERT INTO EMPLOYEES

           (

               EMPLOYEE_ID ,

               FIRST_NAME ,

               LAST_NAME ,

               EMAIL ,

               HIRE_DATE ,

               JOB_ID ,

               DEPARTMENT_ID ,

               MANAGER_ID ,

               SALARY,

   PHONE_NUMBER

           )

           VALUES

           (

               EMPLOYEES_SEQ.NEXTVAL,

               p_fname,

               p_lname,

               p_email,

               SYSDATE,

               p_jobid,

               p_deptno,

               v_Manager_id,

               p_salary,

   p_phone

           );

COMMIT;

-- 예외처리 : 오류 발생시 예외처리 한다.

  EXCEPTION WHEN OTHERS THEN

   DBMS_OUTPUT.PUT_LINE(p_fname || ' ' || p_lname || ' REGISTER IS FAILED!');

    ROLLBACK;

END;

실행]

EXEC register_emp('길동','홍','AAA@BBB.COM','IT_PROG',60,4500,'000-1111-2222');

SELECT * FROM EMPLOYEES WHERE LAST_NAME = '홍';

문제3) 사원의 부서 및 업무 이동 프로시저를 생성하시오. [예외처리 예제]

조건] 

1. 이동할 업무의 최대급여와 최소급여를 확인하여 사원의 부서, 업무 이동시 급여를 입력 오류시 예외처리를 하시오.
2. 판매부서(SA_REP)에서 프로그램(IT_PROG) 부서로 이동
3. SELECT * FROM JOBS WHERE JOB_ID IN ('SA_REP','IT_PROG');

4. 프로시저 이름 : EMP_TRANSFER

CREATE OR REPLACE PROCEDURE emp_transfer (

 emp_id NUMBER,

                               trans_dept_id NUMBER,

                               trans_job_id VARCHAR2,

                               up_salary NUMBER)

IS 

  new_dept_id employees.department_id%TYPE; -- 이동할 부서번호 변수

  new_job_id employees.job_id%TYPE;          -- 새로운 직급번호 변수

  max_salaries jobs.max_salary%TYPE;         -- 직급에 따른 최대급여액 변수

  min_salaries jobs.max_salary%TYPE;          -- 직급에 따른 최소급여액 변수

  

  salary_too_high EXCEPTION;           -- 급여가 너무 높을 경우 처리할 exception

  salary_too_low  EXCEPTION;          -- 급여가 너무 낮을 경우 처리할 exception

BEGIN

   

   -- 부서이동이 있는 경우...

   IF trans_dept_id IS NOT NULL THEN

      new_dept_id := trans_dept_id; 

   END IF;

   -- 직급이동 있는 경우...

   IF trans_dept_id IS NOT NULL THEN

      --  새로운 직급ID, 최대급여액, 최소급여액을 가져온다.

      SELECT job_id, max_salary, min_salary

        INTO new_job_id, max_salaries, min_salaries

        FROM JOBS

       WHERE job_id = trans_job_id;

        

--입력한 급여액이 최대급여액보다 클 경우...

IF up_salary > max_salaries THEN

error_msg := '너무 많은 급여입니다.'; <-에러를 메시지화 한다.

   RAISE salary_too_high;  -- 사용자 정의 예외(EXCEPTION)를 발생시킨다. 

--입력한 급여액이 최소급여액보다 작을 경우... 

ELSIF up_salary < min_salaries THEN

   RAISE salary_too_low;   -- 사용자 정의 예외(EXCEPTION)를 발생시킨다. 

END IF;

      

   END IF;

   

   -- 부서, 직급, 급여 내역을 갱신한다. 

   UPDATE employees

      SET department_id = NVL(new_dept_id, department_id), null이면 기존값으로 셋팅한다.,(nyl ->널값없게)

           job_id = NVL(new_job_id, job_id),                                                           

   salary = NVL(up_salary, salary)

WHERE employee_id = emp_id;

   COMMIT; --<-끝

EXCEPTION WHEN salary_too_high THEN

                -- 위에서 RAISE salary_too_high가 발생되면 여기서 처리한다.

                DBMS_OUTPUT.PUT_LINE('Salary is exceed maximum salary!');

        ROLLBACK;

WHEN salary_too_low THEN

-- 위에서 RAISE salary_too_low가 발생되면 여기서 처리한다.

DBMS_OUTPUT.PUT_LINE('Salary is lower than minimum salary');

ROLLBACK;

    WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

ROLLBACK;

  

END;

확인1) 급여 3200, 오류 발생

EXEC emp_transfer(198, 60, 'IT_PROG', 3200);

SELECT last_name || first_name names, job_id, salary, department_id

  FROM employees

 WHERE employee_id = 198;

오류메시지)

확인2)

EXEC emp_transfer(198, 60, 'IT_PROG', 4500);

SELECT last_name || first_name names, job_id, salary, department_id

  FROM employees

 WHERE employee_id = 198;

정상처리)

---

--업무명을 가져오는 펑션

CREATE OR REPLACE FUNCTION get_job_title

(

jobid VARCHAR2

)

RETURN VARCHAR2

  IS jobtitle VARCHAR2(35);

  BEGIN jobtitle := null;

  SELECT JOB_TITLE

  INTO jobtitle

    FROM JOBS

   WHERE  JOB_id= jobid;

   RETURN jobtitle;

   

   

   END get_job_title ;

   

---

부서명을 가져오는 (펑션)

CREATE OR REPLACE FUNCTION HR.get_dept_name --테이블명

(

deptId NUMBER -- 부서명을 가져오기 위해 deptId를 받아온다 (입력값),생성자

)

 RETURN VARCHAR2 --가져올 데이터 타입

  IS deptName VARCHAR2(30);-- 리턴할 변수를 선언한다. ;

  BEGIN deptName := NULL; -- deptName 초기화 

 SELECT DEPARTMENT_NAME -- 조회해서 집어넣을 값

   INTO deptName -- 집어넣을 변수

   FROM DEPARTMENTS -- 가져올 테이블

  WHERE DEPARTMENT_ID = deptId; --비교값

 RETURN deptName; --리턴 받을 자료 (변수)

   END

;

 

---

CREATE OR REPLACE PROCEDURE REGISTER_EMP

  (

  p_fname VARCHAR2,

    p_lname VARCHAR2,

    p_email VARCHAR2,

    p_jobid VARCHAR2,

    p_deptno NUMBER,

    p_salary NUMBER,

    p_phone VARCHAR2

  )

  IS

  v_Manager_id EMPLOYEES.MANAGER_ID%TYPE; --변수선언

BEGIN 

SELECT MANAGER_ID INTO v_Manager_id

    FROM DEPARTMENTS

   WHERE DEPARTMENT_ID = p_deptno; -- 입력한 부서번호의 매니저아이디를 가져온다

   

   INSERT INTO EMPLOYEES

   (

    EMPLOYEE_ID,

      FIRST_NAME,

      LAST_NAME,

      EMAIL,

      HIRE_DATE,

      JOB_ID,

      DEPARTMENT_ID,

      MANAGER_ID,

      SALARY,

      PHONE_NUMBER

   )

   VALUES

   (

    EMPLOYEES_SEQ.NEXTVAL, --새로운 사원번호 부여//시퀀스 명 NEXT VAL(값을 불러오는 명령어)

      p_fname,

      p_lname,

      p_email,

      SYSDATE,

      p_jobid,

      p_deptno,

      v_Manager_id,

      p_salary,

      p_phone

   );

   COMMIT; --저지르다

   EXCEPTION WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE

      (p_fname || ' ' || p_lname || 'REGISTER IS FAILED!');

      

      ROLLBACK;

   END;

---

CREATE OR REPLACE PROCEDURE emp_transfer(

emp_id NUMBER,

trans_dept_id NUMBER,

              trans_job_id VARCHAR2,

              up_salary NUMBER)

IS

new_dept_id employees.department_id%TYPE;

  new_job_id employees.job_id%TYPE;

  max_salaries jobs.max_salary%TYPE;

  min_salaries jobs.min_salary%TYPE;

  

  salary_too_high EXCEPTION;

  salary_too_low EXCEPTION;

BEGIN

IF trans_dept_id IS NOT NULL THEN

  new_dept_id := trans_dept_id;

  END IF;

  

  IF trans_dept_id IS NOT NULL THEN 

  

  SELECT job_id, max_salaries, min_salary

  INTO new_job_id, max_salaries, min_salaries

    FROM jobs

   WHERE job_id = trans_dept_id;

   

   IF up_salary > max_salaries THEN

   RAISE salary_too_high;

   ELSIF up_salary < min_salaries THEN

   RAISE salary_too_low;

   END IF;

  

END IF;

UPDATE EMPLOYEES

    SET department_id = NVL(new_dept_id, department_id),

      job_id = NVL(new_job_id, job_id),

      salary = NVL(up_salary, salary)

      WHERE employee_id = emp_id;

      

    COMMIT;

  EXCEPTION WHEN salary_too_high THEN

  DBMS_OUTPUT.put_line('salary is exceed maximum salary!');

        ROLLBACK;

  WHEN salary_too_low THEN

  DBMS_OUTPUT.put_line('salary is lower than minimum salary');

        ROLLBACK;

        WHEN others THEN

        DBMS_OUTPUT.put_line(SQLERRM);

        ROLLBACK;

  END;

---

CREATE OR REPLACE PROCEDURE EMP_TRANSFER

(

p_empid NUMBER

  , p_transdeptid NUMBER 

  , p_transjobid VARCHAR2

  , p_upsalary NUMBER

)

IS

BEGIN

UPDATE EMPLOYEES

    SET DEPARTMENT_ID = NVL(p_transdeptid, DEPARTMENT_ID)

        , JOB_ID = NVL(p_transjobid, JOB_ID)

         , SALARY = NVL(p_upsalary, SALARY)

  WHERE EMPLOYEE_ID  = p_empid;

END;

/

---

CREATE OR REPLACE PROCEDURE REGISTER_EMP

(

 p_fName VARCHAR2

  , p_lName VARCHAR2

  , p_email VARCHAR2

  , p_jobid VARCHAR2

  , p_deptid NUMBER

  , p_salary NUMBER

  , p_phone VARCHAR2

)

IS v_manager_id DEPARTMENTS.MANAGER_ID%TYPE;

BEGIN

SELECT MANAGER_ID

INTO v_manager_id

FROM DEPARTMENTS

 WHERE DEPARTMENT_ID = p_deptid;

INSERT INTO EMPLOYEES

(

 EMPLOYEE_ID

  , FIRST_NAME

  , LAST_NAME

  , EMAIL

  , HIRE_DATE

  , JOB_ID

  , DEPARTMENT_ID

  , MANAGER_ID

  , SALARY

  , PHONE_NUMBER

)

VALUES

(

EMPLOYEES_SEQ.NEXTVAL

  , p_fName

  , p_lName

  , p_email

  , SYSDATE

  , p_jobid

  , p_deptid

  , v_manager_id

  , p_salary

  , p_phone

);

COMMIT;

  EXCEPTION WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE(' FAILED ');

    ROLLBACK;

END;

/