MySQL/Stored Procedure

[MySQL/Stored Procedure] 명명 규칙

르매 2018. 6. 1. 16:50

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 입니다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
-- -----------------------------------------------------
-- 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 ;
cs

  1. precision에 따른 각 데이터 유형의 크기 (byte) tm3 > 3byte [본문으로]
  2. precision에 따른 각 데이터 유형의 크기 (byte) dt8 > 8byte [본문으로]
저작자표시 비영리 변경금지