[Mysql] Procedure

CREATE PROCEDURE `PROC_TEST` (
    PARAM_NAME VARCHAR(20) -- 파라미터 선언
    , PARAM_AGE INTEGER
)
BEGIN
    DECLARE PARAM_NUM INTEGER; -- 변수 선언
    
    SELECT COUNT(*) + 1
      INTO PARAM_NUM -- 변수에 값 할당
      FROM TB1;
    
    -- INSERT 문 수행
    INSERT INTO TB1(NUM, NAME, AGE) VALUES(PARAM_NUM, PARAM_NAME, PARAM_AGE);
END

Sample

CREATE DEFINER=`master`@`%` PROCEDURE `job_mass_process_category_change`()
BEGIN
	DECLARE PV_JOB_SEQ  BIGINT default null;
	DECLARE PV_SRC_CATE VARCHAR(16) default null;
    DECLARE PV_DST_CATE VARCHAR(16) default null;
    DECLARE PV_LOOP_CTRL BIGINT default 1;
    DECLARE PV_EFFECTED_ROWS BIGINT default 0;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		GET DIAGNOSTICS CONDITION 1
			  @SQL_STATE = RETURNED_SQLSTATE
			, @ERR_NO = MYSQL_ERRNO
			, @ERR_MSG = MESSAGE_TEXT;
		INSERT INTO db_process_err_log
		(proc_owner, ref_key, error_msg)
		VALUES('job_mass_process_category_change', CONCAT(PV_SRC_CATE, ':', PV_DST_CATE), @ERROR_INFO);
	END;
    
    SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    
    SELECT idx, job_param1, job_param2 INTO PV_JOB_SEQ, PV_SRC_CATE, PV_DST_CATE
    FROM mass_process_job_list
    WHERE process_type = 'CATEGORY_CHANGE'
      AND job_state <> 'COMPLETE'
	ORDER BY reg_dtm ASC
    LIMIT 1;
    
    IF PV_JOB_SEQ IS NOT NULL AND PV_SRC_CATE IS NOT NULL AND PV_DST_CATE IS NOT NULL THEN
		-- Run Step 1
        UPDATE mass_process_job_list
		SET run_step = 1
		  , effected_rows = PV_EFFECTED_ROWS
		WHERE idx = PV_JOB_SEQ;
		
		WHILE PV_LOOP_CTRL > 0 
        DO
			SET PV_LOOP_CTRL = 0;
            
			UPDATE ws_product_item2item_category_map
			SET cate_mst_cd = PV_DST_CATE
			WHERE cate_mst_cd = PV_SRC_CATE
            LIMIT 1000;
            
			SET PV_LOOP_CTRL = ROW_COUNT();
			SET PV_EFFECTED_ROWS = PV_EFFECTED_ROWS + PV_LOOP_CTRL;
            
            UPDATE mass_process_job_list
			SET effected_rows = PV_EFFECTED_ROWS
			WHERE idx = PV_JOB_SEQ;
            
            COMMIT;
		END WHILE;
        
        -- Run Step 2
        UPDATE mass_process_job_list
		SET run_step = 2
		  , effected_rows = PV_EFFECTED_ROWS
		WHERE idx = PV_JOB_SEQ;
        
        SET PV_LOOP_CTRL = 1;
        WHILE PV_LOOP_CTRL > 0
        DO
			UPDATE ws_vendor_category_map
			SET cate_mst_cd = PV_DST_CATE
			WHERE cate_mst_cd = PV_SRC_CATE
            LIMIT 1000;
            
            SET PV_LOOP_CTRL = ROW_COUNT();
            SET PV_EFFECTED_ROWS = PV_EFFECTED_ROWS + PV_LOOP_CTRL;
            
            UPDATE mass_process_job_list
			SET effected_rows = PV_EFFECTED_ROWS
			WHERE idx = PV_JOB_SEQ;
            
            COMMIT;
		END WHILE;
        
		-- Run Step 3
        UPDATE mass_process_job_list
		SET run_step = 3
		  , effected_rows = PV_EFFECTED_ROWS
		WHERE idx = PV_JOB_SEQ;
        
		SET PV_LOOP_CTRL = 1;
        WHILE PV_LOOP_CTRL > 0
        DO
			UPDATE ws_product_category_map
			SET cate_mst_cd = PV_DST_CATE
			WHERE cate_mst_cd = PV_SRC_CATE
			LIMIT 1000;
            
			SET PV_LOOP_CTRL = ROW_COUNT();
            SET PV_EFFECTED_ROWS = PV_EFFECTED_ROWS + PV_LOOP_CTRL;
            
            UPDATE mass_process_job_list
			SET effected_rows = PV_EFFECTED_ROWS
			WHERE idx = PV_JOB_SEQ;
            
            COMMIT;
		END WHILE;
        
        -- Run Step 4
        UPDATE mass_process_job_list
		SET run_step = 4
		  , effected_rows = PV_EFFECTED_ROWS
		WHERE idx = PV_JOB_SEQ;
        
		SET PV_LOOP_CTRL = 1;
        WHILE PV_LOOP_CTRL > 0
        DO
			UPDATE ws_product_category_map
			SET auto_cate_mst_cd = PV_SRC_CATE
			WHERE auto_cate_mst_cd = PV_SRC_CATE
			LIMIT 1000;
            
			SET PV_LOOP_CTRL = ROW_COUNT();
            SET PV_EFFECTED_ROWS = PV_EFFECTED_ROWS + PV_LOOP_CTRL;
            
            UPDATE mass_process_job_list
			SET effected_rows = PV_EFFECTED_ROWS
			WHERE idx = PV_JOB_SEQ;
            
            COMMIT;
		END WHILE;
        
        
        UPDATE mass_process_job_list
        SET job_state = 'COMPLETE'
          , effected_rows = PV_EFFECTED_ROWS
        WHERE idx = PV_JOB_SEQ;
	END IF;
END