프로시저 공부
set @thistime = '';
select now() into @thistime;
select @thistime;
--------------------------------------------------------------------------------------------------------------
프리페어 스테이트먼트용 SQL 인터페이스
SELECT actor_id, first_name, last_name FROM sakila.actor WHERE first_name ='Penelope';
SET @SQL = 'select actor_id, first_name, last_name from sakila.actor where first_name = ?';
PREPARE stmt FROM @SQL;
SET @actor_name = 'Penelope';
EXECUTE stmt USING @actor_name;
DEALLOCATE PREPARE stmt;
------------------------------------------------------------------------------------------------------------------
DELIMITER $$
USE `tour`$$
DROP PROCEDURE IF EXISTS `manager_user_nouse`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `manager_user_nouse`(
IN p_id TEXT, -- 받는 변수값
IN p_delimiter VARCHAR(45), -- 받는 변수값
OUT o_status INT) -- 출력되는 값
BEGIN
DECLARE v_regexp_count INT; -- 변수선언
DECLARE v_id TEXT; -- 변수선언
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET o_status = -1;
SET o_status = 0; -- o_status 값 0으로 초기화 선언
SET v_regexp_count = regexp_count(p_id, p_delimiter); -- regexp_count 변수 안에 입력 변수값 넣음 (p_id, p_delimiter)
IF (v_regexp_count > 0) THEN -- 조건문
SET v_id = last_char_cut(p_id, p_delimiter); -- set 선언 하여 v_id 변수 안에 last_char_cut(p_id, p_delimiter) 넣음.
SET @v_query_string = CONCAT('UPDATE user SET is_status = \'N\' WHERE id IN (', v_id, ')');
-- @v_query_string 변수 선언 하여 그 안에 concat 함수 사용 및 update query 넣음 ( IN 안에 v_id 값 들갓음)
PREPARE stmt FROM @v_query_string; -- @v_query_string 변수 값을 stmt 에 담음.
EXECUTE stmt; -- stmt 변수값 실행
DEALLOCATE PREPARE stmt; -- 프리페어드 구문 종료 (닫음)
END IF;
END$$
DELIMITER ;
--------------------------------------------------------------------------------------------------------------------
DELIMITER $$
USE `tour`$$
DROP PROCEDURE IF EXISTS `optimize_tables`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `optimize_tables`(
IN db_name VARCHAR(30)
)
BEGIN
DECLARE t VARCHAR(64);
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_schema = db_name
AND table_type = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- SELECT 문의 실행 결과 레코드 건이 없거나, CURSOR의 결과 레코드를 모두 Fetch하고 나면 done 변수 값을 1로 설정하고
-- Stored routine의 마지막 실행 지점으로 돌아가서 계속 나머지 코드를 실행한다. ("02000"는 NOT FOUND를 의미하는 SQLSTATE값이다.)
OPEN c; -- 커서를 연다
tables_loop: LOOP
FETCH c INTO t; -- 커서 C 변수값을 T 변수에 삽입한다.
IF done THEN -- done 변수값이 0 이면 참. (실행)
CLOSE c; -- 커서를 닫는다.
LEAVE tables_loop; -- 반복문을 벗어난다. 이 문장이 없으면 무한 루프 ...
END IF; -- if 문을 끝낸다.
SET @stmt_text = CONCAT("optimize table ", db_name, ".", t); -- concat 함수를 사용하여 @stmt_text 변수에 담는다.
PREPARE stmt FROM @stmt_text; -- stmt 변수에 데이터 담음
EXECUTE stmt; -- stmt 실행.
DEALLOCATE PREPARE stmt;
END LOOP; -- loop 문 닫는다.
CLOSE c; -- 커서를 닫는다.
END$$
DELIMITER ;
[루프문 대응 가능]
-> REPEAT
UNTIL is_loop END REPEAT;
----------------------------------------------------------------------------------------
DELIMITER $$
USE `study`$$
DROP PROCEDURE IF EXISTS `study_cour`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `study_cour`()
BEGIN
DECLARE is_loop INT DEFAULT 0;
DECLARE a1 INT;
DECLARE a2 ENUM('o','s') DEFAULT 'o';
DECLARE a3 TINYINT;
DECLARE a4 VARCHAR(45);
DECLARE park CURSOR FOR
SELECT `idx`, `tyep`, `code`, `desc` FROM checkimall.naver_cancel_code;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET is_loop = 1;
OPEN park;
REPEAT
FETCH park INTO a1, a2, a3, a4;
IF NOT is_loop THEN
IF a1 > 2
AND a1 < 7
THEN
INSERT INTO test.xxx (`idx`, `type`, `code`, `desc`) VALUES (a1,a2,a3,a4);
END IF;
END IF;
UNTIL is_loop END REPEAT;
CLOSE park;
END$$
DELIMITER ;