3_2_Create예제

DELIMITER $$

DROP PROCEDURE IF EXISTS `WebProtocol_User_C`$$

CREATE PROCEDURE `WebProtocol_User_C`(
  IN  i_user_no    BIGINT  UNSIGNED
,     IN  i_pass_wd    VARCHAR(100)
,  OUT o_sp_rtn    INT
,  OUT o_rtn_message VARCHAR(100)
)
BEGIN
/******************************************************************************
Name        : WebProtocol_User_C
Description  : 유저 생성하기   Ver 1.0
Return     : 0 = 등록성공
    -1 = 유저생성에 실패했습니다.
    
    -998 = 중복키 오류가 발생 했습니다.

     
Example     :
 
SET @i_user_no     := 30  -- 유저번호
,    @i_pass_wd    := 'asds'  -- 패스워드

,    @o_sp_rtn              := -1
,    @o_rtn_message    := ''
;

CALL WebProtocol_User_C(
      @i_user_no
     , @i_pass_wd
     , @o_sp_rtn
     ,    @o_rtn_message   
     );
SELECT  @o_sp_rtn ,    @o_rtn_message

SELECT * FROM User_0  WHERE user_no = 30;


Reference   : 
History   :
Ver        Date        Author           Description
---------  ----------  ---------------  ------------------------------------
1.0        2013-12-31 LEE yh 1. Create.
******************************************************************************/
DECLARE v_effect_row   INT           DEFAULT 0;
DECLARE v_error_number   INT           DEFAULT 0;
DECLARE v_row_count       INT       DEFAULT 0;


 DECLARE EXIT HANDLER FOR SQLEXCEPTION
 BEGIN
  ROLLBACK;
  SELECT -999     INTO o_sp_rtn;
  SELECT 'SQL 내부 오류'   INTO o_rtn_message;
 END;
 
 DECLARE EXIT HANDLER FOR 1062 /* Duplicate key*/
 BEGIN
  ROLLBACK;
  SELECT -998      INTO o_sp_rtn;
  SELECT '중복키 오류가 발생 했습니다.'  INTO o_rtn_message; 
 END;
 
 FLOW_START:
 BEGIN
  DECLARE v_group_no    TINYINT      DEFAULT 0;
  -- STEP 1. 필요한 정보를 가져와 변수에 담는다
  -- SELECT
  -- 칼럼
  -- INTO
  --  v_변수
  -- FROM 테이블
  -- WHERE 조건

 -- STEP 2. 유저정보 등록
 
  SET v_group_no = RIGHT(i_user_no,1);
  SET @v_sql  :=  '';
  SET @v_table_name :=  ''; 

  SET @v_table_name :=  'USER_';
  SET @v_table_name :=  CONCAT(@v_table_name,v_group_no);
  SET @v_sql  :=  '
           INSERT INTO @p_table_name
      (
       user_no
      , pass_wd
      ) 
      VALUES 
      (
       ?
      , ?
      )
     ';

  SET   @v_sql  :=  REPLACE(@v_sql,'@p_table_name',@v_table_name);

  SET   @v_user_no :=  i_user_no;
  SET   @v_pass_wd :=  i_pass_wd;
    
  START TRANSACTION;
  
  PREPARE stmt FROM @v_sql;   
  EXECUTE stmt USING @v_user_no,@v_pass_wd;
  SELECT ROW_COUNT() INTO v_effect_row;
  DEALLOCATE PREPARE stmt; 
  
  IF (v_effect_row=0) THEN
   ROLLBACK; 
   SELECT -1        INTO o_sp_rtn;
   SELECT '유저 생성에 실패했습니다.'  INTO o_rtn_message;
   LEAVE FLOW_START;
  END IF;
  
  COMMIT;
  SELECT 0     INTO o_sp_rtn;
  SELECT '등록성공'   INTO o_rtn_message;

 END;  -- FLOW_START:

END$$

DELIMITER ;