프로시저 처리

use CommonDB;

CREATE TABLE `log_error` (
  `partkey_month` int(11) NOT NULL COMMENT '파티션키',
  `log_error_idx` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '키 IDENTITY',
  `procedure_name` varchar(50) not null comment '프로시저명',
  `sql_state` varchar(10) not null comment 'sqlstate',
  `msg` varchar(500) not null comment '에러메시지',
  `reg_date` datetime NOT NULL COMMENT '등록일',
  PRIMARY KEY (`log_error_idx`,`partkey_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (partkey_month)
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (2) ENGINE = InnoDB,
 PARTITION p3 VALUES IN (3) ENGINE = InnoDB,
 PARTITION p4 VALUES IN (4) ENGINE = InnoDB,
 PARTITION p5 VALUES IN (5) ENGINE = InnoDB,
 PARTITION p6 VALUES IN (6) ENGINE = InnoDB,
 PARTITION p7 VALUES IN (7) ENGINE = InnoDB,
 PARTITION p8 VALUES IN (8) ENGINE = InnoDB,
 PARTITION p9 VALUES IN (9) ENGINE = InnoDB,
 PARTITION p10 VALUES IN (10) ENGINE = InnoDB,
 PARTITION p11 VALUES IN (11) ENGINE = InnoDB,
 PARTITION p12 VALUES IN (12) ENGINE = InnoDB) */;

create table test ( 
 i int 
);

[출처] MYSQL 프로시저 내 에러처리 그리고 에러 로깅 (MySQL Power Group) |작성자 더데빌

DROP PROCEDURE IF EXISTS usp_RegUser;

delimiter //

CREATE PROCEDURE usp_RegUser (

IN pi_site_user_id VARCHAR(30)

, IN pi_site_type INT

, OUT po_result bigint

, OUT po_date datetime

)

BEGIN

    DECLARE v_date DATETIME DEFAULT now();

    DECLARE v_gamedb_shard_cnt SMALLINT DEFAULT 16;

    DECLARE v_logdb_shard_cnt SMALLINT DEFAULT 16;

    DECLARE v_account_idx BIGINT DEFAULT 0;

    DECLARE v_gamedb_idx INT DEFAULT 0;

    DECLARE v_logdb_idx INT DEFAULT 0;

    

    DECLARE v_sqlstate VARCHAR(5) DEFAULT '00000';

    DECLARE v_msg                            VARCHAR(500);

    DECLARE v_err_no INT;

    

    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING

    BEGIN

GET DIAGNOSTICS CONDITION 1 v_sqlstate = RETURNED_SQLSTATE

, v_err_no = MYSQL_ERRNO

, v_msg = MESSAGE_TEXT;

ROLLBACK;

                ## 에러 처리 시 테이블에 저장

                insert into log_error ( partkey_month, procedure_name, sql_state, msg, reg_date ) values ( month(sysdate()), "usp_RegUser ", v_sqlstate, v_msg, sysdate());

[출처] MYSQL 프로시저 내 에러처리 그리고 에러 로깅 (MySQL Power Group) |작성자 더데빌

        SET po_result = v_err_no * -1;

        

    END;

    

    SET po_date = v_date;

    

    BEGIN

    

START TRANSACTION;

        

        INSERT INTO user2db (

    gamedb_idx

            ,logdb_idx

            ,user_status

            ,site_type

            ,site_user_id

            ,site_user_seq

            ,reg_date

            ,upd_date

        )

        VALUES (101, 301, 'R', pi_site_type, pi_site_user_id, 1, v_date, v_date);

        

        SET v_account_idx := LAST_INSERT_ID();

        

        SELECT gamedb_idx, logdb_idx

        INTO v_gamedb_idx, v_logdb_idx

        FROM shard2db

        WHERE shard2db_idx = mod(v_account_idx, 16) +1;

        

        UPDATE user2db a

        SET a.gamedb_idx = v_gamedb_idx

    , a.logdb_idx = v_logdb_idx

            , a.user_status = 'N'

WHERE a.site_user_id = pi_site_user_id

AND a.site_type = pi_site_type

              AND a.account_idx = v_account_idx;

    

COMMIT;

    

    END;

    

    SET po_result = v_account_idx;    

END //

delimiter ;

TRUNCATE TABLE user2db;

SET @po_result = -999;

CALL usp_RegUser('TestUser1', 1, @po_result, @po_date);

SELECT @po_date, @po_result;

SELECT * FROM user2db;

set @po_result = -999;

call usp_RegUser('TestUser2',1, @po_result, @po_date);

call usp_RegUser('TestUser3',1, @po_result, @po_date);

call usp_RegUser('TestUser4',1, @po_result, @po_date);

call usp_RegUser('TestUser5',1, @po_result, @po_date);

select @po_date, @po_result;

SELECT * FROM user2db;