ALBERT SITE

간단한 프로시저 만들기

=========================================================

GetAllProducts 란 프로시저를 생성하는 문법

=========================================================

DELIMITER //

CREATE PROCEDURE GetAllProducts()

  BEGIN

  SELECT *  FROM su_admin;

  END //

DELIMITER ;

=========================================================

만든 프로시저를 가져오는 문법

=========================================================

CALL GetAllProducts();

=========================================================

프로시져 내에서 함수생성하는 문법

=========================================================

DECLARE variable_name datatype(size) DEFAULT default_value;

예:  

DECLARE total_products INT DEFAULT 0
SELECT COUNT(*) INTO total_products
FROM products 

=========================================================

Mysql Stored Procedure Parameter 문법

=========================================================

1) 프로시져 생성 (파라미터값 countryName )

DELIMITER // 

	CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255)) 

    BEGIN 

	SELECT city, phone 

	FROM offices 

	WHERE country = countryName; 

    END // 

DELIMITER ;

2) 파라미터를 이용하여 프로시져 실행

CALL GetOfficeByCountry('3')  

=========================================================
영문문자 앞자리 대문자로 자동 변환하여 출력하는 프로시져
=========================================================
1) 프로시져 생성
DELIMITER $$
CREATE PROCEDURE `Capitalize`(INOUT str VARCHAR(1024))
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE myc, pc CHAR(1);
 	DECLARE outstr VARCHAR(1000) DEFAULT str;
 	WHILE i <= CHAR_LENGTH(str) DO
		SET myc = SUBSTRING(str, i, 1);
		SET pc = CASE WHEN i = 1 THEN ' ' 
			      ELSE SUBSTRING(str, i - 1, 1) 
			 END;
		IF pc IN (' ', '&', '''', '_', '?', ';', ':', '!', ',', '-', '/', '(', '.') THEN
		    SET outstr = INSERT(outstr, i, 1, UPPER(myc));
		END IF;
		SET i = i + 1;
	END WHILE;
	SET str = outstr;
END$$
DELIMITER ;  
2) 파라미터상 문자넣어 테스트 하기
SET @str = 'mysql stored procedure tutorial';
CALL Capitalize(@str);
SELECT @str;


=========================================================
Loop in Stored Procedures 예제
=========================================================
DELIMITER $$
DROP PROCEDURE IF EXISTS WhileLoopProc$$
CREATE PROCEDURE WhileLoopProc()
      BEGIN
              DECLARE x  INT;
              DECLARE str  VARCHAR(255);
              SET x = 1;
              SET str =  '';
              WHILE x  <= 5 DO
                          SET  str = CONCAT(str,x,',');
                          SET  x = x + 1; 
              END WHILE;
              SELECT str;
      END$$
DELIMITER ;

출력내용: 1,2,3,4,5,


=========================================================
Repeat Loop in Stored Procedures 예제
=========================================================
DELIMITER $$
DROP PROCEDURE IF EXISTS RepeatLoopProc$$
CREATE PROCEDURE RepeatLoopProc()
      BEGIN
              DECLARE x  INT;
              DECLARE str  VARCHAR(255);
              SET x = 1;
              SET str =  '';
              REPEAT
                          SET  str = CONCAT(str,x,',');
                          SET  x = x + 1; 
              UNTIL x  > 5
              END REPEAT;
              SELECT str;
      END$$
DELIMITER ;

결과내용은 똑같음



=========================================================
우수/기수 일정간격으로 loop 실행하는 예제
=========================================================
DELIMITER $$
DROP PROCEDURE IF EXISTS LOOPLoopProc$$
CREATE PROCEDURE LOOPLoopProc()
      BEGIN
              DECLARE X  INT;
              DECLARE str  VARCHAR(255);
              SET X = 1;
              SET str =  '';
              loop_label:  LOOP
                          IF  X > 10 THEN
                                      LEAVE  loop_label;
                          END  IF;
                          SET  X = X + 1;
                          IF  (X MOD 2) THEN
                                      ITERATE  loop_label;
                          ELSE
                                      SET  str = CONCAT(str,X,',');
                          END  IF;
                          
              END LOOP;    
              SELECT str;
      END$$
DELIMITER ;

결과: 2,4,6,8,10,