[ORACLE] 프로시저 기본 사용 예시

프로시저 기본 문법

---

물류업무를 하다보니 데이터베이스 SQL 이 가장 중요하다는 것을 깨달았고, 다중 조인은 기본에 프로시저 트리거를 모르면 업무가 불가능하다는 것을 깨달았다.

그래서 내가 이해할겸 프로시저 문법을 정리한다.

스칼라 변수 선언

---

V_EMPNO NUMBER(4) := 100;
V_ENAME VARCHAR2(20) := "lks";

여기서 NUMBER(p, s ) 형식에 대해 추가 설명을 하겠다.

p 는 최대 유효숫자 자릿수를 나타낸다. 유효숫자란 0이아닌 수가 몇개인지를 뜻한다.

s 는 소수점 기준 자리수를 나타낸다.

예시로 123.74라는 숫자가 있다. 이 숫자에 대해 타입지정을 하나씩 해보겠다.

NUMBER(3) 으로 하면 결과는 124가 찍힌다. 이유는 P는 3으로 명시했지만 S 는 명시하지 않아 0으로 들어갔기 때문이다.

즉 NUMBER(3,0) 과 같고 소수점아래를 찍지 않음으로 반올림해서 124 가 출력된다.

유효숫자관련은 나중에 다뤄보자. 지금은 헷갈린다.

레퍼런스 변수 선언

---

레퍼런스 변수는 변수형 타입을 다른 곳을 참조해서 선언해준다.

deptno emp.deptno%type;
job emp.job%type;

프로시저 사용 코드

---

프로시저를 사용하여 동일한 JOB_ID 가 들어오면 신규 insert 대신 날짜를 업데이트 하는 프로시저를 만들어보자.

CREATE OR REPLACE PROCEDURE MY_NEW_JOB_PROC /*프로시저 명 생성하는 부분*/
(    P_JOB_ID    IN    JOBS.JOB_ID%TYPE, /*매개변수명 IN 참조할변수타입*/
     P_JOB_TITLE    IN    JOBS.JOB_ID%TYPE,
     P_MIN_SAL    IN    JOBS.MIN_SALARY%TYPE,
     P_MAX_ID    IN    JOBS.MAX_SALARY%TYPE
)
IS /*변수나 상수 선언부*/
    VN_CNT NUMBER := 0; /*VN_CNT 라는 변수 이름으로 INT 형을 받겟다. 초기화는 0으로.*/
BEGIN
    SELECT COUNT(*)
      INTO VN_CNT    /*VN_CNT 라는 변수에 COUNT 값을 넣어준다. 이 변수를 가지고 비교*/
      FROM JOBS
     WHERE JOB_ID = P_JOB_ID;

         IF VN_CNT > 0 THEN/*중복데이터라면 업데이트 시간만 갱신*/
             UPDATE JOBS
                SET JOB_TITLE = P_JOB_TITLE,
                       MIN_SALARY = P_MIN_SAL,
                       MAX_SALARY = P_MAX_SAL,
                       UPDATE_DATE = SYSDATE
              WHERE JOB_ID = P_JOB_ID;
         ELSE/*V첫데이터라면 생성시간과 업데이트 시간 갱신*/
             INSERT INTO JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY, CREATE_DATE, UPDATE_DATE)
             VALUES (P_JOB_ID, P_JOB_TITLE, P_MIN_SAL, SYSDATE, SYSDATE);
         END IF;

         COMMIT;
END;

실행 방법

EXEC MY_NEW_JOB_PROC('SM_JOB1', 'SAMPLE JOB1', 2000, 6000);

이렇게 실행 했을 시 최초 값이라면 이대로 데이터베이스에 입력이 되고

중복 JOB_ID 라면 UPDATE_DATE 가 SYSDATE 로 새로 갱신된다.