스토어드 프로시저, 스토어드 함수, 커서, 트리거

 

1. Srored Procedure -> 자바의 메서드 기능과 같이 자주 사용되는 기능을 빠르게 처리하기 위한 용도로 사용된다.

-> MariaDB의 성능이 향상될 수 있음

 

CALL userProc1('조관우'); 위와 같이 호출함. (CALL 프로시저_이름())

 

 

2. Stored Function -> Maria DB에서 모든 필요한 함수를 제공해줄 수는 없기에 사용자가 직접 만들어서 사용하는 함수 

 

2.1 스토어드 함수와 스토어드 프로시저의 차이점 :

1. 스토어드 함수는 IN, OUT 등을 사용할 수 없음 

2.RETRUNS 문으로 반환할 값의 데이터 형식 지정 

3. Stored Procedure는 CALL로 호출한다. 

 

3. 커서란 ? -> 테이블에서 여러 개 행을 쿼리한 후, 쿼리의 결과인 행의 집합을 한 행씩 돌면서 처리하기 위한 방식이다. 

 

커서는 선언 -> 반복 조건 선언 -> 커서 열기 (OPEN) -> 커서에서 데이터를 가져오기 (FETCH) -> 원하는 작업 -> 커서 닫기 (CLOSE) 순으로 처리가 된다. 

---

delimiter $$
CREATE PROCEDURE userPrco1(IN userName VARCHAR(10))
BEGIN 
    SELECT * FROM usertbl WHERE NAME = '바비킴';
END $$
delimiter ; 
 
 
CALL userPrco1('조관우');
 

프로시저의 간단한 예제이다. delimiter $$는 ;로 끝나는 sql문의 규칙을 $$로 끝나게 바꿔두는 것이다. 혼동을 피하기 위해 이와 같이 사용한다. BEGIN과 END 사이에 실행할 쿼리를 작성해주면 된다. 그리고 CALL userPrco1('조관우');와 같이 호출해주면 된다. 

 

delimiter $$
CREATE PROCEDURE userProc2(IN userBirth INT,
                                    IN userHeight INT)
BEGIN 
    SELECT * FROM usertbl
    WHERE birthyear > userBirth AND height > userHeight;
END $$
delimiter ; 
 
CALL userProc2(1970,178);

매개변수로 들어간 IN의 뜻은 외부에서 입력을 받겠다는 뜻이다. userBIrth는 변수이름, INT는 자료형이다.

 

 

delimiter $$
CREATE PROCEDURE whileProc()
BEGIN 
    DECLARE str VARCHAR(100); -- 결과 저장할 변수
    DECLARE i INT ; -- 앞자리
    DECLARE k INT ; -- 뒷자리
    SET i  = 2;
    
    while (i < 10) DO
        SET str = ''; -- 초기화'
        SET k = 1; -- 끝나면 계속 1로 초기화 해야 하기 떄문에 while 문 안쪽에 설정
        while(K < 10) DO
            SET str = CONCAT(str, ' ', i, 'x', k, '=', i*k);
            SET k = k +1;
            
        END while;
        SET i = i +1;
        INSERT INTO guguTBL VALUES(str);
        
    END while; 
        
END $$
delimiter ; 
 
 
CALL whileProc();  

 

프로시저를 이용해 구구단을 만들어볼 수도 있다. 

SHOW CREATE PROCEDURE sqldb.userProc3; 
 

위 코드로 userProc3이라는 프로시저의 상태를 볼 수도 있다. 

 

CREATE FUNCTION userFunc(VALUE1 INT, VALUE2 INT)
 
    RETURNS INT
    
BEGIN 
    RETURN VALUE1 + VALUE2;
END $$
delimiter ; 
 
SELECT userFunc(100,200);
 
 
 

Stored Function의 기본적인 형태이다. Proceduer과 마찬가지로 BEGIN과 END안에 원하는 내용을 써주면 된다.

 

delimiter $$
CREATE FUNCTION getAgeFunc(bYear INT)
 
    RETURNS INT
    
BEGIN 
    DECLARE age INT;
    SET age = YEAR(CURDATE()) - bYear; -- curdate -현재 날짜 출력 , year - 연도만 출력
    RETURN age;
END $$
delimiter ; 
 
 
SELECT getAgeFunc(1979);
 
SELECT getAgeFunc(1979) INTO @age1979;
SELECT getAgeFunc(1997) INTO @age1997;
SELECT CONCAT('1997과 1979의 나이차 -->', @age1979-@age1997;
 
SELECT userID, NAME, getAgeFunc(birthYear) AS '만 나이'
FROM usertbl;
 
SHOW CREATE FUNCTION getAgeFunc;
 

Stored Function으로 나이를 계산할 수 있는 간단한 기능도 구현을 해볼 수가 있다. 

 

-- 커서 사용하기
 
delimiter $$
CREATE PROCEDURE cursorProc()
 
BEGIN 
    DECLARE userHeight INT;
    DECLARE cnt INT DEFAULT 0; -- 고객의 인원수 
    DECLARE totalHeight INT DEFAULT 0; -- 키의 총합
    
    DECLARE endofRows BOOLEAN DEFAULT FALSE;
    
    DECLARE userCursor CURSOR FOR
        SELECT height FROM usertbl;
        
    DECLARE CONTINUE handler
        FOR NOT FOUND SET endofRows = TRUE;
        
        
    OPEN userCursor;
    
    cursor_loop:Loop
        fetch userCursor INTO userHeight;
        if endOfRows
         then leave cursor_loop;
         
         END if;
        
        SET cnt = cnt + 1;
        SET totalHeight = totalHeight + userHeight;
        
    END loop cursor_loop;
    CLOSE userCursor;
        
        
    END $$
delimiter ; 

커서를 사용해서 고객의 평균키를 구할 수 있는 procedure이다. 

 

DECLARE CONTINUE handler
FOR NOT FOUND SET endofRows = TRUE; 이 부분은 커서가 데이터를 다 돌았을 때 endofRows를 true로 만들어주어 루프를 나올 수 있게 하기 위함이다.  

 

선언 -> 반복 조건 선언 -> 커서 열기 (OPEN) -> 커서에서 데이터를 가져오기 (FETCH) -> 원하는 작업 -> 커서 닫기 (CLOSE) 순으로 처리가 되는 것을 볼 수 있다. 

 

 

delimiter //
CREATE TRIGGER backUserTbl_DeleteTrg
    AFTER DELETE
    ON userTbl
    FOR EACH ROW
    
BEGIN
    INSERT INTO backup_userTBL
        VALUES(OLD.userid, OLD.name, OLD.birthyear,
                OLD.addr, OLD.mobile1, OLD.mobile2,
                OLD.height, OLD.mdate, '삭제', CURDATE(),
                CURRENT_USER());
 
END //
delimiter ;
 

AFTER ~ ROW 부분은 트리거의 발동 조건이라고 생각하면 된다 . 각각의 행이 삭제가 된 후에 트리거가 발동하는 것이다. OLD. ~ 부분은 예전의 데이터를 말하는 것인데 변경 전의, 즉 이 트리거에서는 삭제 전의 데이터를 말하는 것이다. 이 데이터를 backup_userTBL라는 테이블에 백업을 해두는 것이다. 트리거는 업데이트, 삭제, 삽입 등의 DML 문에서만 사용을 할 수 있다. 이 부분에서는 DELETE를 사용해 예시를 들었다.