[mysql] C/U/D stored procedure 기본형

CREATE DEFINER=`root`@`%` PROCEDURE `new procedure`(IN `procTypeParam` VARCHAR(20)
, IN `idxParam` 			INT
, OUT `identity_idx` 		INT
, OUT `error_code`			INT	
, OUT `error_message`		VARCHAR(1000)
)
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @error_code = MYSQL_ERRNO, @text = MESSAGE_TEXT;
        SET `error_code` = @error_code;
		SET `error_message` = CONCAT("ERROR ", @error_code, " (", @sqlstate, "): ", @text);
		#SELECT @full_error;
	END;	
    
	SET `error_code` = 0;
	SET `error_message` = 'OK';        
	
	START TRANSACTION;
    
	IF ( procTypeParam = 'insert' ) THEN
		BEGIN
			#insert query

			SET `identity_idx` = LAST_INSERT_ID();
		END;
    ELSEIF ( procTypeParam = 'update' ) THEN
		BEGIN
            #update query
            
            SET `identity_idx` = `idxParam`;
		END;
    ELSEIF ( procTypeParam = 'delete' ) THEN
		BEGIN
            #delete query
            
            SET `identity_idx` = `idxParam`;            
		END;    
    ELSE
		BEGIN
            SET `error_code` = 9999;
            SET `error_message` = '유효하지 않은 proctype 입니다.';                 
		END;
    END IF;
    
    COMMIT;
END