[mariaDB] Procedure , Function create 실습 기록

 

 

1. 인사마스터와 1:1 mapping되는 사내대출원장 기초데이터를 삽입하는 프로시저를 생성해보자

 

USE OSS;

#지정 월 상환액 추가
ALTER TABLE TB_LOAN ADD COLUMN MONTH_PAY NUMERIC(10) NOT NULL;
#컬럼 위치 이동
ALTER TABLE TB_LOAN MODIFY MONTH_PAY NUMERIC(10) AFTER LOAN_AMOUNT;
ALTER TABLE TB_LOAN MODIFY BALANCE NUMERIC(10) AFTER TOTAL_PAY;

SELECT * FROM TB_LOAN;

DELIMITER $$
CREATE OR REPLACE PROCEDURE INSERT_LOAN(OUT v_result INT)
    /*
     @DESCRIPTION
        LOAN 데이터(대출 원장)를 사원 수 만큼 인서트 한다.
     @RETURN
        RESULT : 실패(-1) 성공(0)
     */
    BEGIN
        DECLARE v_emp_cnt INT; #사원 수
        DECLARE v_kor_nm NVARCHAR(10); #사원명
        DECLARE v_min_emp_no INT; #사원 번호(min)
        DECLARE v_loan_amount INT; #대출금액
        DECLARE v_total_pay INT; #총 상환액
        DECLARE v_interest INT; #월 적용이자
        DECLARE v_balance INT; #대출 잔액
        DECLARE v_month_pay INT; #월 상환액(매 월 고정)
        DECLARE i INT DEFAULT 0; #Loop i 변수

        DECLARE EXIT HANDLER FOR SQLEXCEPTION #SQL ERROR시 ROLLBACK하고 v_result -1 처리
            BEGIN
                ROLLBACK;
                SET v_result = -1;
            END;

        /** 트랜잭션 시작 */
        START TRANSACTION;
            SELECT COUNT(*) INTO v_emp_cnt FROM TB_EMP_MST;
            SELECT MIN(EMP_NO) INTO v_min_emp_no FROM TB_EMP_MST;

            /** 사원 수 만큼 Loop */
            WHILE i < v_emp_cnt DO
                BEGIN
                    BEGIN
                        SELECT KOR_NM INTO v_kor_nm FROM TB_EMP_MST WHERE EMP_NO = v_min_emp_no + i;
                    END;
                    BEGIN
                        SELECT RPAD(FLOOR(1 + RAND() * 9), '7', '0') INTO v_loan_amount;
                    END;
                    BEGIN
                        SELECT RPAD(FLOOR(1 + RAND() * 9), '5', '0') INTO v_month_pay; #월에 갚아야하는 돈
                    END;


                    SET v_total_pay = 0;
                    SET v_interest = v_loan_amount * 0.011; #이자는 1.1% 로 동일 적용
                    SET v_balance =  v_loan_amount - v_total_pay;

                    INSERT INTO TB_LOAN (LOAN_NO, EMP_NO, LOAN_DATE, LOAN_AMOUNT, TOTAL_PAY, REMARK, INTEREST_RATE, INTEREST, IS_REPAY, REPAY_PERIOD, BALANCE, MONTH_PAY)
                    VALUES (NEXTVAL(LOAN_NO_SEQ), (SELECT EMP_NO FROM TB_EMP_MST WHERE KOR_NM = v_kor_nm), '20220501', v_loan_amount, v_total_pay, '학자금 대출', '1.1', v_interest, 'N', '20220725', v_balance, v_month_pay);
                    SET i = i+1;
                    SET v_result = 0;
                END;
            END WHILE;
        /** 트랜잭션 종료 (커밋) */
        COMMIT;
    END;

CALL INSERT_LOAN(@v_result);

---

참고 테이블 생성문

CREATE DATABASE OSS;
USE OSS;

DROP TABLE TB_EMP_MST;
CREATE TABLE IF NOT EXISTS TB_EMP_MST (
    EMP_NO INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT
    , KOR_NM NVARCHAR(30) NOT NULL
    , ENG_NM NVARCHAR(50)
    , MPHONE NVARCHAR(30)
    , ADDR NVARCHAR(150)
    , DIVISION NVARCHAR(150) NOT NULL
    , POSITION NVARCHAR(100) NOT NULL
    , JOB NVARCHAR(50) NOT NULL
    , DUTY NVARCHAR(50) NOT NULL
    , EDUCATION NVARCHAR(100)
    , WORK_TYPE NVARCHAR(10) NOT NULL
    , EMP_STATUS NVARCHAR(10) NOT NULL
    , JOIN_TYPE NVARCHAR(10) NOT NULL
    , JOIN_DATE DATE NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
ALTER TABLE TB_EMP_MST ADD BIRTH_DATE DATE;

CREATE SEQUENCE EMP_NO_SEQ
INCREMENT BY 1
START WITH 10000
MAXVALUE 999999
CYCLE;

DROP TABLE TB_LOAN;
CREATE TABLE IF NOT EXISTS TB_LOAN (
    LOAN_NO INT(10) NOT NULL AUTO_INCREMENT
    , EMP_NO INT(10) NOT NULL
    , LOAN_DATE DATE NOT NULL
    , LOAN_AMOUNT NVARCHAR(30) NOT NULL
    , TOTAL_PAY NUMERIC(10) NOT NULL
    , REMARK NVARCHAR(50)
    , INTEREST NUMERIC(10)
    , INTEREST_RATE NUMERIC(10)
    , IS_REPAY NVARCHAR(5) NOT NULL
    , REPAY_PERIOD DATE
    , BALANCE NUMERIC(10) NOT NULL
    , PRIMARY KEY (LOAN_NO, EMP_NO)
    , FOREIGN KEY (EMP_NO) REFERENCES TB_EMP_MST (EMP_NO)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
ALTER TABLE TB_LOAN MODIFY INTEREST_RATE DECIMAL(5, 2);

CREATE SEQUENCE LOAN_NO_SEQ
INCREMENT BY 1
START WITH 1
MAXVALUE 999999
CYCLE;

DROP TABLE TB_LOAN_HIST;
CREATE TABLE IF NOT EXISTS TB_LOAN_HIST (
    EMP_NO INT(10) NOT NULL
    , REPAY_DATE DATE NOT NULL
    , LOAN_DATE DATE NOT NULL
    , MONTH_PAY NUMERIC(10) NOT NULL
    , MONTH_INTERREST NUMERIC(10) NOT NULL
    , IS_REPAY NVARCHAR(5) NOT NULL
    , PRIMARY KEY(EMP_NO, REPAY_DATE, LOAN_DATE)
    , FOREIGN KEY (EMP_NO) REFERENCES TB_EMP_MST (EMP_NO)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
ALTER TABLE TB_LOAN_HIST MODIFY LOAN_DATE DATE AFTER EMP_NO;

---

2. 사내대출원장과 1:N mapping되는 월별 대출상환내역 데이터를 만들기 위한 프로시저를 생성해보자

매달 프로시져를 돌려, 월별 대출상환 내역 데이터를 삽입하고

월별 대출 상환 내역과 연계된 사내대출원장 데이터를 갱신한다.

USE OSS;

DELIMITER $$
CREATE OR REPLACE PROCEDURE INSERT_LOAN_HIST(IN v_repay_date DATE, OUT v_result INT)
    /*
     @DESCRIPTION
        대출 원장(TB_LOAN)에 대한 월 별 상환 데이터(TB_LOAN_HIST)를 상환 월를 매개변수로 하여 자동 생성하고
        대출 원장(TA_LOAN) 데이터의 대출 상환여부가 Y가 될 때 까지, 최신 상환액과 잔액 등을 갱신한다.
     @RETURN
        RESULT : 실패(-1) 성공(0)
     */
    BEGIN
        DECLARE v_loan_cnt INT; #대출원장 마스터 length
        DECLARE v_month_pay INT; #월 상환액
        DECLARE v_min_loan_no INT; #대출원장 번호(min)
        DECLARE v_balance INT; #대출 잔액
        DECLARE v_total_pay INT; #총 상환액
        DECLARE v_loan_amount INT; #대출 금액
        DECLARE v_is_repay NVARCHAR(3); #최종 상환여부
        DECLARE v_is_loan_hist INT; #월 상환내역 존재여부
        DECLARE i INT DEFAULT 0; #Loop 변수 i


        /** 트랜잭션 시작 */
        START TRANSACTION;
            #대출원장 마스터 length 만큼, loop
            SELECT COUNT(*) INTO v_loan_cnt FROM TB_LOAN;
            SELECT MIN(LOAN_NO) INTO v_min_loan_no FROM TB_LOAN;

            WHILE i < v_loan_cnt DO
                BEGIN
                    #상환 여부
                    BEGIN
                        SELECT IS_REPAY INTO v_is_repay FROM TB_LOAN WHERE LOAN_NO = v_min_loan_no + i;
                    END;
                    #월 상환 금액
                    BEGIN
                        SELECT MONTH_PAY INTO v_month_pay FROM TB_LOAN WHERE LOAN_NO = v_min_loan_no + i;
                    END;
                    #대출 잔액
                    BEGIN
                        SELECT BALANCE INTO v_balance FROM TB_LOAN WHERE LOAN_NO = v_min_loan_no + i;
                    END;
                    BEGIN
                        SELECT COUNT(1) INTO v_is_loan_hist FROM TB_LOAN_HIST WHERE REPAY_DATE = v_repay_date AND LOAN_NO = v_min_loan_no + i;
                    END;

                    #대출원장 마스터의 상환여부가 N이고, 그 달에 이미 돌리지 않았을 때에만
                    IF v_is_repay = 'N' && v_is_loan_hist = 0
                    THEN
                        #단, 잔액이 월 상환액보다 미만인 경우, 월 상환액은 잔액이 된다.
                        IF v_month_pay > v_balance
                            THEN SET v_month_pay = v_balance;
                        END IF;

                        BEGIN
                            INSERT INTO TB_LOAN_HIST (EMP_NO, LOAN_NO, REPAY_DATE, LOAN_DATE, MONTH_PAY, MONTH_INTERREST, IS_REPAY)
                            VALUES ((SELECT EMP_NO FROM TB_LOAN WHERE LOAN_NO = v_min_loan_no + i), (SELECT LOAN_NO FROM TB_LOAN WHERE LOAN_NO = v_min_loan_no + i), v_repay_date, (SELECT LOAN_DATE FROM TB_LOAN WHERE LOAN_NO = v_min_loan_no + i), v_month_pay, (SELECT INTEREST FROM TB_LOAN WHERE LOAN_NO = v_min_loan_no + i), 'Y');
                        END;

                        BEGIN
                            #월상환내역 생성 후, 대출원장을 갱신
                                #1. 해당 월 별 상환금액을 총 상환금액에 더 함
                                #2. (총 대출금액 - 총 상환금액) 계산하여 잔액을 갱신
                                #3. 잔액이 0이면 상환여부 Y처리
                            START TRANSACTION;
                                BEGIN
                                    UPDATE TB_LOAN
                                    SET TOTAL_PAY = TOTAL_PAY + v_month_pay
                                    WHERE LOAN_NO = v_min_loan_no + i;
                                END;
                            COMMIT;
                            BEGIN
                                SELECT TOTAL_PAY INTO v_total_pay FROM TB_LOAN WHERE LOAN_NO = v_min_loan_no + i;
                            END;
                            BEGIN
                                SELECT LOAN_AMOUNT INTO v_loan_amount FROM TB_LOAN WHERE LOAN_NO = v_min_loan_no + i;
                            END;
                            BEGIN
                                IF v_loan_amount - v_total_pay = 0
                                    THEN SET v_is_repay = 'Y';
                                ELSE
                                    SET v_is_repay = 'N';
                                END IF;
                            END;
                            BEGIN
                                UPDATE TB_LOAN
                                SET BALANCE = v_loan_amount - v_total_pay
                                    , IS_REPAY = v_is_repay
                                WHERE LOAN_NO = v_min_loan_no + i;
                            END;
                        END;
                    END IF;
                END;

                SET i = i + 1;
                SET v_result = 0;

            END WHILE;

        /** 트랜잭션 종료 (커밋) */
        COMMIT;
    END;

CALL INSERT_LOAN(@v_result);
CALL INSERT_LOAN_HIST('20220725', @v_result);
CALL INSERT_LOAN_HIST('20220825', @v_result);
CALL INSERT_LOAN_HIST('20220925', @v_result);
CALL INSERT_LOAN_HIST('20221025', @v_result);
CALL INSERT_LOAN_HIST('20221125', @v_result);
CALL INSERT_LOAN_HIST('20221225', @v_result);

DELETE FROM TB_LOAN;
SELECT * FROM TB_LOAN;
DELETE FROM TB_LOAN_HIST;
SELECT * FROM TB_LOAN_HIST;

---

3. 부서와 직급을 무작위로 지정하여 사원 마스터 100개를 생성해주는 프로시저를 만들어보자 

USE OSS;

DELIMITER $$
CREATE OR REPLACE PROCEDURE INSERT_EMP(OUT v_result INT)
    /*
     @DESCRIPTION
        직급, 소속을 랜덤으로 하는 인사마스터 100개 INERT
     @RETURN
        RESULT : 실패(-1) 성공(0)
     */
    BEGIN
        DECLARE v_division INT; #부서
        DECLARE v_kor_nm NVARCHAR(10); #사원명
        DECLARE v_position INT; #직급
        DECLARE i INT DEFAULT 0; #Loop i 변수

        DECLARE EXIT HANDLER FOR SQLEXCEPTION #SQL ERROR시 ROLLBACK하고 v_result -1 처리
        BEGIN
            ROLLBACK;
            SET v_result = -1;
        END;

        /** 트랜잭션 시작 */
        START TRANSACTION;

            /** Loop */
            WHILE i <= 100 DO
                BEGIN
                    BEGIN
                        SELECT RPAD(FLOOR(1 + RAND() * 4), '3', '0') INTO v_division;
                    END;
                    BEGIN
                        SELECT RPAD(FLOOR(1 + RAND() * 5), '2', '0') INTO v_position;
                    END;
                    BEGIN
                        SELECT GET_KORNM() INTO v_kor_nm;
                    END;

                    BEGIN
                        INSERT INTO TB_EMP_MST (EMP_NO, KOR_NM, ENG_NM, MPHONE, ADDR, DIVISION, POSITION, JOB, DUTY, EDUCATION, WORK_TYPE, EMP_STATUS, JOIN_TYPE, JOIN_DATE, BIRTH_DATE)
                        VALUES (NEXTVAL(EMP_NO_SEQ), v_kor_nm, '', '', '', v_division, v_position, '팀원', '웹개발', '대졸', '정규직', '재직', '경력', '2022-04-25', CURDATE());
                        SET i = i+1;
                    END;
                    SET v_result = 0;
                END;
            END WHILE;

        /** 트랜잭션 종료 (커밋) */
        COMMIT;
    END;

CALL INSERT_EMP(@v_result);
SELECT @v_result;

 

TB_LOAN

 

TB_LOAN_HIST

---

3. 무작위 한글 이름을 return 해주는 function 생성

 - return 값을 SET으로 초기화 (SET v_real_nm = "";) 해주지 않으니까, 오류가 났다. 

DELIMITER $$
CREATE OR REPLACE FUNCTION GET_KORNM() RETURNS NVARCHAR(20)

    BEGIN
        DECLARE v_first_nm NVARCHAR(10);
        DECLARE v_middle_nm NVARCHAR(10);
        DECLARE v_last_nm NVARCHAR(10);
        DECLARE v_real_nm NVARCHAR(10);

        SET v_real_nm = "";

        BEGIN
            SELECT SUBSTRING('김이박최정강조윤장임오한신서권황안송유홍전고문소양배조백허남', rand() * 36 + 1, 1) INTO v_first_nm FROM dual;
        END;
        BEGIN
            SELECT SUBSTRING('민현동인지현재우건준승영성진준정수광영호중훈후우상연철아윤은', rand() * 36 + 1, 1) INTO v_middle_nm FROM dual;
        END;
        BEGIN
            SELECT SUBSTRING('유자도성상남식일철병혜영미환식숙자희순진서빈정지하연성공안원', rand() * 36 + 1, 1) INTO v_last_nm FROM dual;
        END;
        BEGIN
        SELECT CONCAT(v_first_nm, v_middle_nm, v_last_nm) INTO v_real_nm;
        END;

    RETURN v_real_nm;
    END;

---

4. 대출 상환 내역을 생성한 후, 대출 상환 마스터 데이터를 갱신하는 프로시저를 단순 쿼리문으로 변경해보자 !

USE OSS;

#4월 데이터 삭제
DELETE FROM TB_LOAN_HIST
WHERE REPAY_DATE = '2023-04-25';

#4월 HIST 데이터 생성
INSERT INTO TB_LOAN_HIST (EMP_NO, LOAN_NO, LOAN_DATE, REPAY_DATE, MONTH_PAY, MONTH_INTERREST, IS_REPAY)
SELECT
    A.EMP_NO AS EMP_NO
    , B.LOAN_NO AS LOAN_NO
    , B.LOAN_DATE AS LOAN_DATE
    , '2023-04-25' AS REPAY_DATE
    , B.MONTH_PAY AS MONTH_PAY
    , B.INTEREST AS MONTH_INTERREST
    , 'Y' AS IS_REPAY
FROM TB_EMP_MST A
INNER JOIN TB_LOAN B
ON A.EMP_NO = B.EMP_NO;

#4월 HIST 데이터를 긁어와서 마스터 데이터 최신 버전으로 갱신
UPDATE TB_LOAN AA
        , (SELECT
                 A.LOAN_NO AS LOAN_NO
                 , CASE
                        WHEN A.BALANCE = '0' THEN A.BALANCE
                        WHEN A.BALANCE < (A.LOAN_AMOUNT - B.MONTH_PAY) THEN 0
                        ELSE A.LOAN_AMOUNT - B.MONTH_PAY
                    END             AS AFTER_BALANCE   #업데이트 할 잔액
                  , B.MONTH_PAY AS AFTER_TOTAL_PAY #업데이트 할 이제까지 갚은 돈
             FROM TB_LOAN A
             INNER JOIN (SELECT LOAN_NO     AS LOAN_NO
                           , SUM(MONTH_PAY) AS MONTH_PAY
                        FROM TB_LOAN_HIST
                        GROUP BY LOAN_NO) B
                        ON A.LOAN_NO = B.LOAN_NO) BB
SET AA.BALANCE = BB.AFTER_BALANCE
    , AA.TOTAL_PAY = BB.AFTER_TOTAL_PAY
    , AA.IS_REPAY = (CASE WHEN BB.AFTER_BALANCE = 0 THEN 'Y' ELSE 'N' END) #IS_REPAY는 잔액으로 결정
WHERE AA.LOAN_NO = BB.LOAN_NO;

#결과
SELECT * FROM TB_LOAN;