Procedure

조회

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE';

SHOW PROCEDURE STATUS;

# DDL 확인
SHOW CREATE PROCEDURE schema_name.prcedure_name;

기본 형식

DELIMITER $$
CREATE DEFINER=`user_name`%`host_name` PROCEDURE procedure_name(
	IN  pi_var_name VARCHAR2(2);             # 파라미터 선언 (IN)
	OUT po_var_name VARCHAR2(4);             # 파라미터 선언 (OUT)
)
BEGIN
	DECLARE eor BOOLEAN DEFAULT FALSE;                # 행 끝 여부

	# 변수 선언
	DECLARE var_name VARCHAR(2) DEFAULT pi_var_name;

	# 할당
	SET po_var_name = varname + 'A';

	# 커서
	DECLARE cursor_name CURSOR FOR
		SELECT column_name FROM schema_name.table_name; # 해당 쿼리 결과를 사용

	# 핸들러
	DECLARE CONTINUE HANDLER                          # 더 이상 행이 없다면 eor = TRUE
		FOR NOT FOUND SET eor = TRUE;

	# 커서 Open
	OPEN cursor_name;
		# 루프
		loop_name : LOOP
			# Fetch
			FETCH cursor_name INTO var_name;              # 데이터 가져오기
			IF eor THEN
				LEAVE loop_name;                            # 더 이상 데이터가 없다면 루프 해제
			END IF;

			# 가져온 데이터 처리 (Example)
			/*
			IF NOT EXISTS (SELECT column_name FROM schema_name.table_name WHERE column_name = var_name)
				THEN DELETE FROM schema_name.table_name WHERE column_name = var_name;
			END IF;

			SET v_partition_name = DATE_FORMAT(DATE_ADD(DATE(v_partition_name_before), INTERVAL 1 DAY), '%Y-%m-%d');

			ALTER TABLE v_schema_name.v_table_name ADD PARTITION(
				PARTITION v_partition_name VALUES LESS THAN ( v_partition_date )
			);
			*/

		# 루프 끝
		END LOOP loop_name;

	# 커서 Close
	CLOSE cursor_name;
END;

특징

• 프로시저 정의 할때는 DELIMITER $$ 을 사용 (문장 구분자가 ; 인데 이는 프로시저 정의 부분에 사용해야하므로 ; 를 인식하지 못하게 하기 위한 장치)
• 커서는 루프를 이용해 연속적인 데이터 처리를 할 수 있게 함
• FETCH는 오픈된 커서의 데이터를 한 행씩만 가져올 수 있음

핸들러

# 에러 발생 시 변수 값을 1로 설정하고 다음 내용을 실행
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET variable_name = 1;

# 에러 발생 시 롤백 후 에러 메시지를 표시하고 현재 코드 단락을 나감
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
	ROLLBACK;
	SELECT 'Error Message!'; # An error has occurred, operation rollbacked and the stored procedure was terminated
END;

# SELECT INTO나 CURSOR일 때 더 이상 가져올 데이터가 없다면 변수 값을 1로 설정하고 계속 실행
DECLARE CONTINUE HANDLER FOR NOT FOUND SET variable_name = 1;

# 해당 코드에 해당하는 에러가 발생하면 에러 메시지를 표시하고 계속 실행
# 1062는 중복 키 에러
DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Error Message!'; # Duplicate entry '1' for key 'PRIMARY'

핸들러의 우선순위

|우선순위|핸들러| |-|-| |1|MySQL 에러 코드 핸들러| |2|SQLSTATE 핸들러| |3|SQLEXCEPTION|

IF

IF 조건식 THEN
	실행문;
ELSEIF 조건식 THEN
	실행문;
ELSE
	실행문;
END IF;