[MySQL/Stored Procedure] 명명 규칙

Stored Procedure 명명 규칙

1. 접두어 usp_ 를 사용한다.
2. SP의 이름을 구성하는 각각의 단어를 underscore 로 연결하는 snake case 를 사용한다.
3. 특정 테이블에 대한 단순 CRUD 작업인 경우, 각각 아래와 같은 이름 규칙을 사용한다.
1. CREATE
1. usp_add_{테이블 이름}
2. RETRIEVE
1. usp_get_{테이블 이름} / 단일 행을 반환하는 경우
2. usp_get_list_{테이블 이름} / 여러 행을 반환하는 경우
3. UPDATE
1. usp_mod_{테이블 이름}
4. DELETE
1. usp_del_{테이블 이름}
4. SP가 특정 비즈니스 로직을 처리하는 경우, 적절한 동사와 명사의 조합을 사용한다.
1. (예) usp_validate_applicant, usp_check_brand_user, ...
5. local variable, input parameter, output parameter
1. local variable
1. 접두어 v_ 를 사용한다.
2. input parameter
1. 접두어 pi_ 를 사용한다.
3. output parameter
1. 접두어 po_ 를 사용한다.
4. (공통) 데이터 유형에 따라 다음의 접두어를 사용한다.
   

   데이터 유형	접두어	예시
   bit	bit	v_bit_confirm_flag
   tinyint	iny	v_iny_gender_code
   smallint	ins	v_ins_score
   mediumint	inm	v_inm_item_count
   int	int	v_int_user_id
   bigint	inb	v_inb_bbs_id
   char	chr	v_chr_social_number
   varchar	vch	v_vch_user_name
   binary	bin	v_bin_password
   varbinary	vbn	v_vbn_encrypted_email
   time(p)	tm3 ~ tm6 [각주:1]	v_tm3_now
   date	dat	v_dat_basis_date
   timestamp	dt4	v_dt4_created_date
   datetime(p)	dt5 ~ dt8 [각주:2]	v_dt5_now
   decimal	dec	v_dec_cash_amount
   text	txt	v_txt_memo
   mediumtext	txm	v_txm_description
   longtext	txl	v_txl_content
   json	jsn	v_jsn_server_config

    

위의 Naming Convention을 사용해 작성한 Stored Procedure 입니다.

-- -----------------------------------------------------
-- PROCEDURE usp_add_shipping_address_for_campaign
-- -----------------------------------------------------
 
USE totoro;
 
DELIMITER $$
 
DROP PROCEDURE IF EXISTS `usp_add_shipping_address_for_campaign`$$
 
CREATE DEFINER=CURRENT_USER() PROCEDURE usp_add_shipping_address_for_campaign(
      IN pi_int_marketing_event_id int UNSIGNED -- 캠페인 ID
    , IN pi_int_social_user_id int UNSIGNED     -- 소셜 유저 ID
    , IN pi_vch_real_name varchar(50)           -- 성명
    , IN pi_vch_phone_number varchar(15)        -- 전화 번호
    , IN pi_vch_zip_code varchar(15)            -- 우편 번호
    , IN pi_vch_address_1 varchar(100)          -- 주소 1
    , IN pi_vch_address_2 varchar(50)           -- 주소 2
    , IN pi_vch_key_password varchar(64)        -- 암호화 키 생성에 사용한 패스워드
    , IN pi_dt5_now datetime(0)                 -- 현재 서버 시각
    , OUT po_int_return int                     -- 리턴 값
)
DETERMINISTIC
SQL SECURITY DEFINER
CONTAINS SQL
COMMENT '
author : doeyull.kim
e-mail : purumae@gmail.com
created date : 2018-05-25
description : 상품 배송을 위한 개인 정보를 입력합니다.
parameter : 
      IN pi_int_marketing_event_id int UNSIGNED -- 캠페인 ID
    , IN pi_int_social_user_id int UNSIGNED     -- 소셜 유저 ID
    , IN pi_vch_real_name varchar(50)           -- 성명
    , IN pi_vch_phone_number varchar(15)        -- 전화 번호
    , IN pi_vch_zip_code varchar(15)            -- 우편 번호
    , IN pi_vch_address_1 varchar(100)          -- 주소 1
    , IN pi_vch_address_2 varchar(50)           -- 주소 2
    , IN pi_vch_key_password varchar(64)        -- 암호화 키 생성에 사용한 패스워드
    , IN pi_dt5_now datetime(0)                 -- 현재 서버 시각
    , OUT po_int_return int                     -- 리턴 값
return value :
    0 = 에러가 없습니다.
    -1 = 예상하지 않은 런 타임 오류가 발생하였습니다.
    10061 = 캠페인 참가자가 아닙니다.
usage :
    SET @pi_int_marketing_event_id = ;
    SET @pi_int_social_user_id = ;
    SET @pi_vch_real_name = ;
    SET @pi_vch_phone_number = ;
    SET @pi_vch_zip_code = ;
    SET @pi_vch_address_1 = ;
    SET @pi_vch_address_2 = ;
    SET @pi_vch_key_password = ;
    SET @pi_dt5_now = ;

    CALL usp_add_shipping_address_for_campaign (@pi_int_marketing_event_id, @pi_int_social_user_id, @pi_vch_real_name, @pi_vch_phone_number, @pi_vch_zip_code, @pi_vch_address_1, @pi_vch_address_2, @pi_vch_key_password, @pi_dt5_now, @po_int_return);

    SELECT @po_int_return;
'
proc_body: BEGIN
    DECLARE v_vch_proc_name varchar(100) DEFAULT 'usp_add_shipping_address_for_campaign';
    DECLARE v_iny_proc_step tinyint UNSIGNED DEFAULT 0;
    DECLARE v_txt_call_stack text;
    DECLARE v_bit_write_exception_log boolean DEFAULT FALSE;
    DECLARE v_vch_sql_state varchar(5);
    DECLARE v_int_error_no int;
    DECLARE v_txt_error_msg text;
 
    DECLARE v_vbn_real_name varbinary(160) DEFAULT AES_ENCRYPT(pi_vch_real_name, UNHEX(SHA2(pi_vch_key_password, 512))); 
    DECLARE v_vbn_phone_number varbinary(16) DEFAULT IF(pi_vch_phone_number IS NULL, NULL, AES_ENCRYPT(pi_vch_phone_number, UNHEX(SHA2(pi_vch_key_password, 512))));
    DECLARE v_vbn_zip_code varbinary(16) DEFAULT IF(pi_vch_zip_code IS NULL, NULL, AES_ENCRYPT(pi_vch_zip_code, UNHEX(SHA2(pi_vch_key_password, 512))));
    DECLARE v_vbn_address_1 varbinary(304) DEFAULT IF(pi_vch_address_1 IS NULL, NULL, AES_ENCRYPT(pi_vch_address_1, UNHEX(SHA2(pi_vch_key_password, 512))));
    DECLARE v_vbn_address_2 varbinary(160) DEFAULT IF(pi_vch_address_2 IS NULL, NULL, AES_ENCRYPT(pi_vch_address_2, UNHEX(SHA2(pi_vch_key_password, 512))));
 
    DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;
    DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;
 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 v_vch_sql_state = RETURNED_SQLSTATE
            , v_int_error_no = MYSQL_ERRNO
            , v_txt_error_msg = MESSAGE_TEXT;
 
        ROLLBACK;
 
        SET v_txt_call_stack = CONCAT('{"pi_int_marketing_event_id":', IF(pi_int_marketing_event_id IS NULL, 'null', pi_int_marketing_event_id)
            , ',"pi_int_social_user_id":', IF(pi_int_social_user_id IS NULL, 'null', pi_int_social_user_id)
            , ',"pi_vch_real_name":', IF(pi_vch_real_name IS NULL, 'null', CONCAT('"', REPLACE(pi_vch_real_name, '"', '\\\"'), '"'))
            , ',"pi_vch_phone_number":', IF(pi_vch_phone_number IS NULL, 'null', CONCAT('"', REPLACE(pi_vch_phone_number, '"', '\\\"'), '"'))
            , ',"pi_vch_zip_code":', IF(pi_vch_zip_code IS NULL, 'null', CONCAT('"', REPLACE(pi_vch_zip_code, '"', '\\\"'), '"'))
            , ',"pi_vch_address_1":', IF(pi_vch_address_1 IS NULL, 'null', CONCAT('"', REPLACE(pi_vch_address_1, '"', '\\\"'), '"'))
            , ',"pi_vch_address_2":', IF(pi_vch_address_2 IS NULL, 'null', CONCAT('"', REPLACE(pi_vch_address_2, '"', '\\\"'), '"'))
            , ',"pi_vch_key_password":', IF(pi_vch_key_password IS NULL, 'null', CONCAT('"', REPLACE(pi_vch_key_password, '"', '\\\"'), '"'))
            , ',"pi_dt5_now":', IF(pi_dt5_now IS NULL, 'null', CONCAT('"', pi_dt5_now, '"'))
            , '}'
        );
 
        -- 런 타임 에러 중 논리 에러로 분류할 항목을 필터링
        IF v_int_error_no = 1452 AND v_txt_error_msg LIKE '%fk-applicant-shipping_address_for_campaign%' THEN -- child row
            SET po_int_return = 10061;
            SET v_bit_write_exception_log = TRUE;
        END IF;
 
        IF v_bit_write_exception_log THEN  -- exception_log table에 기록하는 논리 에러인 경우
            INSERT exception_log (proc_name, proc_step, exception_no, call_stack, proc_call_date, log_date)
            VALUES (v_vch_proc_name, v_iny_proc_step, po_int_return, v_txt_call_stack, pi_dt5_now, NOW(0));
 
        ELSEIF po_int_return = 0 OR po_int_return IS NULL THEN  -- exception으로 분류하지 않는 그 밖의 런 타임 에러인 경우
            INSERT error_log (proc_name, proc_step, sql_state, error_no, error_msg, call_stack, proc_call_date, log_date)
            VALUES (v_vch_proc_name, v_iny_proc_step, v_vch_sql_state, v_int_error_no, v_txt_error_msg, v_txt_call_stack, pi_dt5_now, NOW(0));
 
            SET po_int_return = -1;
            RESIGNAL;
        END IF;
    END;
 
    SET v_txt_call_stack = CONCAT('{"pi_int_marketing_event_id":', IF(pi_int_marketing_event_id IS NULL, 'null', pi_int_marketing_event_id)
      , ',"pi_int_social_user_id":', IF(pi_int_social_user_id IS NULL, 'null', pi_int_social_user_id)
      , ',"pi_vch_real_name":', IF(pi_vch_real_name IS NULL, 'null', CONCAT('"', REPLACE(pi_vch_real_name, '"', '\\\"'), '"'))
      , ',"pi_vch_phone_number":', IF(pi_vch_phone_number IS NULL, 'null', CONCAT('"', REPLACE(pi_vch_phone_number, '"', '\\\"'), '"'))
      , ',"pi_vch_zip_code":', IF(pi_vch_zip_code IS NULL, 'null', CONCAT('"', REPLACE(pi_vch_zip_code, '"', '\\\"'), '"'))
      , ',"pi_vch_address_1":', IF(pi_vch_address_1 IS NULL, 'null', CONCAT('"', REPLACE(pi_vch_address_1, '"', '\\\"'), '"'))
      , ',"pi_vch_address_2":', IF(pi_vch_address_2 IS NULL, 'null', CONCAT('"', REPLACE(pi_vch_address_2, '"', '\\\"'), '"'))
      , ',"pi_vch_key_password":', IF(pi_vch_key_password IS NULL, 'null', CONCAT('"', REPLACE(pi_vch_key_password, '"', '\\\"'), '"'))
      , ',"pi_dt5_now":', IF(pi_dt5_now IS NULL, 'null', CONCAT('"', pi_dt5_now, '"'))
      , '}'
    );
 
    CALL usp_sub_add_proc_call_log(v_vch_proc_name, v_txt_call_stack, pi_dt5_now, po_int_return);
 
    SET v_iny_proc_step = 1;
 
    INSERT shipping_address_for_campaign (marketing_event_id, social_user_id, real_name, phone_number, zip_code, address_1, address_2, register_date, expire_date)
    VALUES (pi_int_marketing_event_id, pi_int_social_user_id, v_vbn_real_name, v_vbn_phone_number, v_vbn_zip_code, v_vbn_address_1, v_vbn_address_2, pi_dt5_now, TIMESTAMPADD(DAY, 90, pi_dt5_now))
    ON DUPLICATE KEY UPDATE
          real_name = v_vbn_real_name
        , phone_number = v_vbn_phone_number
        , zip_code = v_vbn_zip_code
        , address_1 = v_vbn_address_1
        , address_2 = v_vbn_address_2;
 
    SET po_int_return = 0;
END proc_body$$
 
DELIMITER ;

1. precision에 따른 각 데이터 유형의 크기 (byte) tm3 > 3byte [본문으로]
2. precision에 따른 각 데이터 유형의 크기 (byte) dt8 > 8byte [본문으로]