[SP] 공부

프로시저 공부

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 ;