본문 바로가기
코딩 공부/web & Java

[SQL] Stored Program(프로시저, 사용자 지정 함수, 트리거, 트랜잭션)

by 현장 2022. 9. 23.

Stored Program

일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합으로 MySQL 안에서 프로그래밍 언어와 같은 기능을 제공하는 프로그램입니다. 자주 사용하는 복잡한 쿼리를 하나로 묶어서 이름으로 지정하여 이름을 호출하여 실행되도록 설정합니다.

 

특징

종류

개념

프로시저

특징

• 어떠한 동작을 일괄 처리하기 위한 용도

• 자주 사용되는 일반적인 쿼리를 모듈화 시켜 필요할 때만 호출

• MySQL 운영에 편리

 

단점

•유지 보수 복잡성 증가
 - 애플리케이션의 설치나 배포가 더 복잡해짐 (각 기능을 담당하는 프로그램 코드가
   자바와 MySQL 스토어드 프로그램으로 분산되어 관리하기 때문에)

형식

DELIMITER $$ 
-- 세미콜론을 사용하지만 종료가 아니라 끝나는 다시 나오기 전까지 실행
-- java의 {}와 비슷

CREATE PROCEDURE 스토어드 프로시저 이름(IN 또는 OUT 파라미터)
BEGIN
	
	SQL 프로그래밍 코딩…

END $$
DELIMITER;


CALL 스토어드 프로시저 이름(); -- 프로시저 실행

-- 예시

DROP PROCEDURE IF EXISTS memberProc;
DELIMITER $$
CREATE PROCEDURE memberProc(IN mName VARCHAR(50))
BEGIN
	SELECT
	   *
	FROM
	   tb_member AS m
	WHERE
	   m.m_name = mName;
END $$
DELIMITER;

out 파라미터

BEGIN
	SELECT
		m.m_email INTO result
	FROM
		tb_member AS m
	WHERE
		m.m_id = memberId
		AND
		m.m_pw = memberPw;
END

-- 쿼리
call sp_member_email ('id001', 'pw001', @result);
SELECT @result;

위와 같이 result변수에 값을 담는다.

 

IN 파라미터

CREATE DEFINER=`ksmartid`@`%` PROCEDURE `ksmart45db`.`sp_add_grade`(
	IN `lName` VARCHAR(50)
)
    COMMENT '회원 등급을 추가하는 프로시저'
BEGIN
	INSERT INTO tb_member_level
		(level_name, level_reg_date)
	VALUES
		(lName,CURDATE());
END

입력을 받을 경우 사용합니다.

 

IF 조건문

BEGIN
	/* 변수 선언 */
	DECLARE memberLevel INT;
	SET memberLevel := 0;
	/* 변수 선언 및 초기화 동시에 */
	-- DECLARE memberLevel INT DEFAULT 0;
	
	SELECT 
		m.level_num INTO memberLevel
	FROM
		tb_member AS m
	WHERE
		m.m_id = memberId;
		
	/* 조건문 */
	IF (memberLevel = 1) THEN
		SELECT '관리자' AS '권한';
	ELSEIF (memberLevel = 2) THEN
		SELECT '판매자' AS '권한';
	ELSEIF (memberLevel = 3) THEN
		SELECT '구매자' AS '권한';
	ELSE
		SELECT '회원' AS '권한';
	END IF;
END

주의할 점은 THEN의 입력과 IF문 끝에 END IF;를 해줘야 한다.

 

CASE 조건문

BEGIN
	/* 변수 선언 및 초기화 */
	DECLARE memberLeval INT DEFAULT 0;
	
	SELECT
		m.level_num INTO memberLeval
	FROM
		tb_member AS m
	WHERE
		m.m_id = memberId;
		
	/* 조건문 CASE */
	CASE
	WHEN (memberLeval = 1) THEN
		SELECT '관리자' AS '권한';
	WHEN (memberLeval = 2) THEN
		SELECT '판매자' AS '권한';
	WHEN (memberLeval = 3) THEN
		SELECT '구매자' AS '권한';
	ELSE
		SELECT '회원' AS '권한';
	END CASE;
END

 

WHILE 조건문

CREATE DEFINER=`ksmartid`@`%` PROCEDURE `ksmart45db`.`sp_sum_while`(
	IN `intValue` INT
)
    COMMENT '숫자를 입력받아 숫자의 범위만큼 누적 합계를 구하는 프로시저'
BEGIN
	/* 변수 선언 및 초기화 */
	DECLARE i INT;
	DECLARE resultSum INT;
	SET i := 0;
	SET resultSum := 0;
	
	/* while 구문 */
	WHILE (i < intValue) DO
		SET i := i + 1;
		SET resultSum := resultSum + i;
END WHILE;
	
	SELECT resultSum AS '누적합계';
END

/* INERATE */
COMMENT '2를 제외한 누적합계를 구하는 프로시저_iterate'
BEGIN
	/* 변수 선언 및 초기화 */
	DECLARE i INT;
	DECLARE resultSum INT;
	SET i := 0;
	SET resultSum := 0;
	
	/* while 구문 */
	WHILE_label: 
        WHILE (i < intValue) DO
            SET i := i + 1;
            IF (i = 2) THEN
                ITERATE WHILE_label;
            END IF;
            SET resultSum := resultSum + i;
         END WHILE;
	
	/* 누적합계 조회 */
	SELECT resultSum AS '누적합계';
END

각각의 결과

LOOP 조건문

CREATE PROCEDURE ksmart45db.sp_sum_loop(
	IN inputVlaue INT
)
comment '숫자를 입력받아 숫자의 범위만큼 누적 합계를 구하는 프로시저'
BEGIN
	DECLARE i INT;
	DECLARE resultSum INT;
	SET i := 0;
	SET resultSum := 0;
	
	/* loop구문 */
	loop_label: 
	LOOP
		/* 반복문 제어를 위한 조건문 */
		IF(i = inputVlaue) THEN
			LEAVE loop_label;
		END IF;
		SET i := i + 1;
		SET resultSum := resultSum + i;
	END LOOP;
	
	SELECT resultSum AS '누적합계';
END

 

에러 처리

CREATE PROCEDURE ksmart45db.sp_overflow_exception()
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE total INT DEFAULT 0;
	DECLARE exceptionMsg TEXT DEFAULT ''; 

	DECLARE EXIT HANDLER FOR 1264
	BEGIN
		SELECT 
			CONCAT ('INT overflow직전 합계 --> ', exceptionMsg) AS '결과1',
			CONCAT('1+2+3+4+...+',i ,'=오버플로') AS '결과2';
	END;
	
	WHILE(TRUE) DO
		SET exceptionMsg := total;
		SET total := total + i;
		SET i := i + 1;
	END WHILE;
	
END

출력

사용자 정의 함수

내장함수가 사용자를 만족하는 모든 함수를 제공하지 않아 필요에 의해 사용자가 직접 함수를 만들어서 사용합니다. 형태와 사용 용도에 프로시저와 차이가 있습니다.

DELIMITER $$

CREATE FUNCTION 스토어드 함수 이름(파라미터)
	RETURNS 반환형식
BEGIN
	
	프로그래밍 코딩…
	RETURN 반환값 ;

END $$
DELIMITER;

SELECT 스토어드 함수 이름();

 

단점

• 유지 보수 복잡성 증가
→ 애플리케이션의 설치나 배포가 더 복잡해짐 (각 기능을 담당하는 프로그램 코드가
   자바와 MySQL 스토어드 프로그램으로 분산되어 관리하기 때문에)

 

사용자 정의 함수 vs 프로시저

예시

CREATE FUNCTION ksmart45db.sf_member_email(
	member_id VARCHAR(20)
)
RETURNS VARCHAR(50)
comment '회원 아이디를 입력받아 회원의 이메일을 반환하는 함수'
BEGIN
	DECLARE memberEmail VARCHAR(50);
	
	SELECT 
		m_email INTO memberEmail
	FROM 
		tb_member
	WHERE
		m_id = member_id;

	RETURN memberEmail;
END

결과

트리거

테이블에 삽입, 수정, 삭제 등의 작업(이벤트)이 발생할 때에 자동으로 작동하는 데이터 베이스 개체입니다.

DELIMITER $$

CREATE TRIGGER 트리거 이름 트리거타임 트리거이벤트 ON 테이블이름 FOR EACH ROW 
BEGIN
	
	트리거 관련 명령문

END $$
DELIMITER;

트리거 타임: BEFORE / AFTER
트리거 이벤트: INSERT / UPDATE / DELETE

특징

  • 프로시저와 달리 직접 실행시킬 수 없고 오직 해당 테이블에 이벤트가 발생할 경우에만 실행한다. (매개변수는 사용불가)
  • 이벤트발생 한 후 ROLLBACK 할 수 없다.

예시(OLD)

CREATE DEFINER=`root`@`localhost` TRIGGER `tb_member_trigger_AFTER_DELETE` AFTER DELETE ON `tb_member_trigger` FOR EACH ROW BEGIN
INSERT INTO tb_member_trigger_backup
(  
    m_id,
    m_pw,
    m_name,
    m_level,
    m_email,
    m_addr,
    m_reg_date,
    m_modify_type,
    m_modify_date,
    m_modify_user,
) VALUES (
-- OLD는 이미 데이터에 있는 내용을 의미
    OLD.m_id,
    OLD.m_pw,
    OLD.m_name,
    OLD.m_level,
    OLD.m_email,
    OLD.m_addr,
    OLD.m_reg_date,
    '삭제',
    CURDATE(),
    CURRENT_USER()
);

END

결과1
결과2

예시(NEW)

CREATE DEFINER=`root`@`localhost` TRIGGER `tb_member_trigger_BEFORE_INSERT` BEFORE INSERT ON `tb_member_trigger` FOR EACH ROW BEGIN
    CASE
    WHEN (NEW.m_level = '관리자') THEN SET NEW.m_level = '1';
    WHEN (NEW.m_level = '판매자') THEN SET NEW.m_level = '2';
    WHEN (NEW.m_level = '구매자') THEN SET NEW.m_level = '3';
    ELSE
		SET NEW.m_level = '4';
    END CASE;
END

트랜잭션

하나의 작업을 수행하기 위해 필요한 데이터베이스 연산들을 모아 놓은 것으로 작업 수행에 필요한 SQL 문들의 모임입니다. (논리적인 작업의 단위)
장애 발생 시 복구 작업이나 병행 제어 작업을 위한 중요한 단위로 사용되며 데이터베이스의 무결성과 일관성을 보장하기 위해 작업 수행에 필요한 연산들을 하나의 트랜잭션으로 제대로 정의하고 관리해야 합니다.

특징

트랜잭션 상태

• 활동상태 :  트랜잭션이 수행을 시작하여 현재 수행 중인 상태
• 부분완료상태 : 트랜잭션의 마지막 연산이 실행을 끝낸 직후의 상태
• 완료상태

    - 트랜잭션이 성공적으로 완료되어 commi연산을 실행한 상태

    - 트랜잭션이 수행한 최종 결과를 데이터베이스에 반영하고 데이터베이스가 새로운 일관된 상태가 되면서 트랜잭션이 종료됨.

• 실패 상태:  장애가 발생하여 트랜잭션의 수행이 중단된 상태
• 철회상태

    - 트랜잭션의 수행 실패로 rollback 연산을 실행한 상태

    - 지금까지 실행한 트랜잭션의 연산을 모둔 취소하고 트랜잭션이 수행되기 전의 데이터베이스 상태로 되돌리면서 트랜잭션이 종료됨.

    - 철회 상태로 종료된 트랜잭션은 상황에 따라 다시 수행되거나 폐기됨.

 

예시

SELECT @@autocommit;
SET @@autocommit:=0; /* 다음 내용들을 보장한다. */
SELECT @@autocommit;
START TRANSACTION;
SELECT * FROM tb_member_trigger;
DELETE FROM tb_member_trigger;
SELECT * FROM tb_member_trigger;
ROLLBACK; /* 이전 상태도 되돌린다. */
UPDATE tb_member_trigger
   SET
     m_name='홍길동'
   WHERE
     m_id='id014';
COMMIT;
SELECT * FROM tb_member_trigger;

/* save point */

SELECT @@autocommit;
SET @@autocommit:=0;
SELECT @@autocommit;
START TRANSACTION;
SELECT * FROM tb_member_backup;
SAVEPOINT a;
UPDATE tb_member_backup SET m_name='홍003' WHERE m_id='id003';
SAVEPOINT b;
DELETE FROM tb_member_backup;
ROLLBACK TO SAVEPOINT a; /* save point a가 있는 곳으로 돌아간다. */
COMMIT;

'코딩 공부 > web & Java' 카테고리의 다른 글

[JSP] 500 Error  (0) 2022.09.26
[JSP, MYSQL] MySQL 연동 오류(java.lang.ClassNotFoundException: com.mysql.jdbc.Driver)  (1) 2022.09.26
[SQL] View  (0) 2022.09.23
[SQL] Union  (0) 2022.09.22
[SQL] Sub Query  (0) 2022.09.22

관련글

티스토리툴바