[mysql] R stored procedure 기본형

CREATE DEFINER=`root`@`%` PROCEDURE `new procedure`(IN `idxParam` CHAR(6)
# 파라메터들
, IN `page` INT UNSIGNED
, IN `offset` INT UNSIGNED
, OUT `total` INT UNSIGNED
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    IF `offset` IS NULL THEN    
    	SET `offset` = 2;
    END IF;    
    
    IF `page` IS NULL THEN    
    	SET `page` = 0;
    ELSE
    	SET `page` = `page` * `offset`;
    END IF;        
    
    #상세보기
    IF `idxParam` IS NOT NULL THEN
    BEGIN
	  SET `total` = ( SELECT COUNT(*) AS total
							  FROM `DB`,`TABLE` );
								    
		SELECT #컬럼들
		  FROM `DB`.`table`
		 WHERE `idx` = `idxParam`
		 LIMIT `page`,`offset`;
    END;
    ELSE
    BEGIN
      #전체보기
	  SET `total` = ( SELECT COUNT(*) AS total
							  FROM `DB`.`TABLE` );
								    
		SELECT #컬럼들
		  FROM `DB`.`TABLE`
		 WHERE `use_yn` = `useYnParam`
		 LIMIT `page`,`offset`;
    END;
    END IF;   
END