[SQL] 절차형 SQL

절차형 SQL의 정의

절차형 SQL
1. SQL문을 통해 절차지향적인 프로그램 작성이 가능하도록 함
2. 절차형 SQL을 사용하면 SQL문의 연속적인 실행이나 조건에 따른 분기 처리를 수행하는 모듈을 생성할 수 있다.
3. 오라클 기준 절차형 모듈의 종류는 사용자정의함수, 프로시저, 트리거가 있다.
4. 오라클 기준 이러한 절차형 모듈을 PL/SQL이라고 부른다.

PL/SQL 개요
1. PL/SQL은 Block 구조로 되어 있고 Block 내에는 SQL문, IF, LOOP 등이 존재한다.
2. PL/SQL을 이용해서 다양한 모듈을 개발 가능하다.

PL/SQL 개요
1. BLOCK 구조로 되어있으며 각 기능별로 모듈화가 가능하다.
2. 변수/상수 선언 및 IF/LOOP문 등의 사용이 가능하다.
3. DBMS 에러나 사용자 에러 정의를 할 수 있다.
4. PL/SQL은 오라클에 내장 시킬 수 있으므로, 어떠한 오라클 서버로도 이식 가능
5. PL/SQL은 여러 SQL 문장을 BLOCK으로 묶고 한 번에 BLOCK 전부를 서버로 보내므로 네트워크 패킷 수를 감소 시킨다.

---

PL/SQL block 구조

구조명	필수/선택 여부	설명
DECLARE(선언부)	필수	- BEGIN - END 에서 사용할 변수나 인수에 대한 정의 및 데이터 타입 선언
BEGIN(실행부)	필수	- 개발자가 처리하고자 하는 SQL문과 필요한 LOGIC (비교문,제어문)이 정의되는 실행부
EXCEPTION(예외처리부)	선택	- BEGIN - END 실행되는 SQL문에 발생된 에러를 처리하는 에러 처리부
END	필수

PL/SQL은 DECLARE, BEGIN, EXCEPTION, END로 이루어져 있으며 그중 EXCEPTION은 선택항목이고 나머지는 필수 항목이다.

---

오라클 프로시저 실습

• 연별 직원 급여 지급 내역 테이블을 생성

DROP TABLE TB_EMP_PAY_BY_YEAR; 
CREATE TABLE TB_EMP_PAY_BY_YEAR 
(
  EMP_NO CHAR(10) NOT NULL 
, STD_YEAR CHAR(4) NOT NULL 
, PAY_AMT NUMBER(15) NOT NULL 
, CONSTRAINT PK_TB_EMP_PAY_BY_YEAR PRIMARY KEY(EMP_NO, STD_YEAR)
)
;

오라클 프로시저 생성

• 입력한 연도(YYYY) 기준 직원별 연봉 액수를 TB_EMP_PAY_BY_YEAR 테이블에 저장
• 성별이 남자인 직원에 한해서만 저장함

CREATE OR REPLACE PROCEDURE SP_INSERT_TB_EMP_PAY_BY_YEAR
(IN_STD_YEAR IN TB_EMP_PAY_BY_YEAR.STD_YEAR%TYPE)
IS 
V_EMP_NO TB_SAL_HIS.EMP_NO%TYPE; 
V_SEX_CD TB_EMP.SEX_CD%TYPE; 
V_STD_YEAR TB_EMP_PAY_BY_YEAR.STD_YEAR%TYPE;  
V_PAY_AMT TB_SAL_HIS.PAY_AMT%TYPE;  
CURSOR SELECT_TB_EMP IS 
SELECT B.EMP_NO
     , (SELECT L.SEX_CD FROM TB_EMP L WHERE L.EMP_NO = B.EMP_NO) AS SEX_CD
     , SUBSTR(B.PAY_DE, 1, 4) AS STD_YEAR
     , SUM(B.PAY_AMT) AS PAY_AMT
  FROM TB_SAL_HIS B
 WHERE B.PAY_DE BETWEEN IN_STD_YEAR||'0101' AND IN_STD_YEAR||'1231'
 GROUP BY B.EMP_NO, SUBSTR(B.PAY_DE, 1, 4)
 ORDER BY B.EMP_NO;

BEGIN 
    OPEN SELECT_TB_EMP;
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------'); 
    LOOP 
        FETCH SELECT_TB_EMP INTO V_EMP_NO, V_SEX_CD, V_STD_YEAR, V_PAY_AMT; 
        EXIT WHEN SELECT_TB_EMP%NOTFOUND; 
        
        DBMS_OUTPUT.PUT_LINE('V_EMP_NO   : '  || '[' || V_EMP_NO ||']');
        DBMS_OUTPUT.PUT_LINE('V_SEX_CD   : '  || '[' || V_SEX_CD ||']');
        DBMS_OUTPUT.PUT_LINE('V_STD_YEAR : '  || '[' || V_STD_YEAR ||']');
        DBMS_OUTPUT.PUT_LINE('V_PAY_AMT  : '  || '[' || V_PAY_AMT ||']');
        
        IF V_SEX_CD = '1' THEN 
            INSERT INTO TB_EMP_PAY_BY_YEAR VALUES (V_EMP_NO, V_STD_YEAR, V_PAY_AMT); 
        END IF;                
    END LOOP;     
    CLOSE SELECT_TB_EMP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------'); 
END SP_INSERT_TB_EMP_PAY_BY_YEAR;
/
;

오라클 프로시저 실행

TRUNCATE TABLE TB_EMP_PAY_BY_YEAR;       
EXEC SP_INSERT_TB_EMP_PAY_BY_YEAR('2019');

SELECT * FROM TB_EMP_PAY_BY_YEAR; 


CREATE OR REPLACE FUNCTION FUNC_EMP_CNT_BY_DEPT_CD(IN_DEPT_CD IN TB_DEPT.DEPT_CD%TYPE)
RETURN NUMBER IS V_EMP_CNT NUMBER;

BEGIN
SELECT COUNT(*) CNT
  INTO V_EMP_CNT
  FROM TB_EMP
 WHERE DEPT_CD = IN_DEPT_CD
 ;
 
RETURN V_EMP_CNT;
END;
/

SELECT A.DEPT_CD
     , A.DEPT_NM
     , FUNC_EMP_CNT_BY_DEPT_CD(A.DEPT_CD) AS EMP_CNT
FROM TB_DEPT A
;

DROP TABLE TB_EMP_PAY_SUM; 

CREATE TABLE TB_EMP_PAY_SUM 
(
   EMP_NO   CHAR(10)Not Null 
,  PAY_AMT_SUM NUMBER(15) Not Null 
, CONSTRAINT PK_TB_SAL_HIS_SUM PRIMARY KEY(EMP_NO)
)
;

INSERT INTO TB_EMP_PAY_SUM
SELECT EMP_NO, SUM(PAY_AMT) FROM TB_SAL_HIS
GROUP BY EMP_NO
ORDER BY EMP_NO 
;

COMMIT; 


CREATE OR REPLACE TRIGGER TRIG_TB_SAL_HIS_INSERT
AFTER INSERT 
ON TB_SAL_HIS 
FOR EACH ROW 
DECLARE 
V_EMP_NO TB_SAL_HIS.EMP_NO%TYPE;

BEGIN 
V_EMP_NO := :NEW.EMP_NO;

UPDATE TB_EMP_PAY_SUM A  
SET A.PAY_AMT_SUM = A.PAY_AMT_SUM + :NEW.PAY_AMT 
WHERE A.EMP_NO = V_EMP_NO;

IF SQL%NOTFOUND THEN 
    INSERT INTO TB_EMP_PAY_SUM VALUES (V_EMP_NO, :NEW.PAY_AMT);
END IF; 
END; 
/


INSERT INTO TB_SAL_HIS VALUES (
(  SELECT TO_CHAR(NVL(MAX(SAL_HIS_NO), 0) + 1) AS SAL_HIS_NO FROM TB_SAL_HIS)
 , TO_CHAR(SYSDATE, 'YYYYMMDD'), 1000000 , '9999999999'
);
 COMMIT; 


SELECT A.EMP_NO
     , A.PAY_AMT_SUM
  FROM TB_EMP_PAY_SUM a 
 WHERE EMP_NO = '9999999999'
; 


ALTER TABLE TB_SAL_HIS DROP CONSTRAINT FK_TB_SAL_HIS01; 


INSERT INTO TB_SAL_HIS VALUES (
( SELECT TO_CHAR(NVL(MAX(SAL_HIS_NO), 0) + 1) AS SAL_HIS_NO FROM TB_SAL_HIS)
, TO_CHAR(SYSDATE, 'YYYYMMDD'), 1000000, '1234567890'
); 
COMMIT; 


SELECT A.EMP_NO
     , A.PAY_AMT_SUM
  FROM TB_EMP_PAY_SUM A
 WHERE EMP_NO = '1234567890'
; 


DELETE FROM TB_SAL_HIS WHERE EMP_NO = '1234567890'; 
COMMIT;

ALTER TABLE SQLD.TB_SAL_HIS ADD CONSTRAINT FK_TB_SAL_HIS01 FOREIGN KEY (EMP_NO) REFERENCES SQLD.TB_EMP (EMP_NO) NOVALIDATE;