디비이야기 25. PL/SQL(절차형 SQL)

1.절차형 SQL 특징

1) 블록 구조로 되어있어 기능별 모듈화

2) 번수, 상수 선언 가능

3) IF/ROOF 등 절차적 언어로 제어가능

4) 에러 제어 가능

5) 성능과 통신량을 줄일 수 있다 

 

2. PL/SQL 구조

기본문법) 

DECLARE (선언부 / 상수)

-> 인수 선언 / 데이터 타입 등 설정

 

BEGINE (실행부)-> 개발자가 처리하고픈 SQL문과 실행문 정의
EXCEPTION || ERROR(예외처리)-> 선택사항이자 에러사항 정의
END

3. 프로시저 생성과 활동 = 프로시저는 함수와 같다. / 기능에 따라 분류해놓으면 수정, 관리, 재사용이 유용하다 
EX) 분류별 기능(논리적)1) 입력할 부서 데이터 입력 -> 부서가 존재하는지 확인 -> 있으면 부서데이터 입력 -> commit -> 입력완료 -> 종료 2) 입력할 부서 데이터 입력 -> 이미 있으면 -> 종료3) 입력할 부서 데이터 입력 -> 없으면 ERROR 발생 -> 종료 
=> EXCUTE 명령어로 실행 
EX) 예시(물리적) CREATE OR REPLACE Procedure p_DEPT_insert
 ( v_DEPTNO in number,
   v_dname in varchar2,
   v_loc in varchar2,
   v_result out varchar2

)
IS
cnt number := 0; // 0으로 들어감
BEGIN
  SELECT COUNT(*) INTO CNT
    FROM DEPT WHERE DEPTNO = v_DEPTNO
     AND ROWNUM = 1;
if cnt > 0 then
  v_result := '이미 등록된 부서번호이다';
else
  INSERT INTO DEPT (DEPTNO, DNAME, LOC)  VALUES (v_DEPTNO, v_dname, v_loc);
  COMMIT;
  v_result := '입력 완료';
end if;
EXCEPTION
   WHEN OTHERS THEN
   ROLLBACK;
   v_result := 'ERROR 발생';
END;

 

* OUT 변수 사용법

DECLARE

VARIABLE CD VARCHAR2;           /* output 변수 선언 */
VARIABLE CD_DESC VARCHAR2;   /* output 변수 선언 */

 

BEGIN

SPHRM_BSC_EVL_SM( 'C'

                       , '2020'

                       , '2'

                       , '20040181'

                       , '20040169'

                       , :CD        /* output 변수 */

                       , :CD_DESC   /* output 변수 */

                       );

END;

 

=> SQLDEVELOPER나 테스트시 사용하기

4. 트리거 생성과 활용 = 특별한 TABLE이 DML이 실행될 떄 DB를 자동 동작시키게 한다그리고, 테이블, 뷰, DB 작업대상 정의 가능 
EX)  CREATE OR REPLACE Trigger SUMMARY_SALES : 트리거 생성
      AFTER INSERT : 레코드가 입력된 후 트리거 발생 
      on ORDER_LIST : ORDER_LIST 테이블에 트리거 설정 
      FOR EACH ROW : 각 로우마다 트리거 적용
    DECLARE
      o_date ORDER_LIST.order_date%TYPE;
      o_prod ORDER_LIST.product%TYPE;
    BEGIN
      o_date := :NEW.order_date;
     o_prod := :NEW.product;
     UPDATE SALES_PER_DATE
      SET qty = qty + :NEW.qty,
          amount = amount + :NEW.amount
      WHERE sale_date = o_date
       AND product = o_prod;
   if SQL%NOTFOUND then
     INSERT INTO SALES_PER_DATE
     VALUES(o_date, o_prod, :NEW.qty, :NEW.amount);
    end if;
   END;
=> 주의) ROLLBACK, COMMIT 불가 

3. 커서