3_1_Read예제

CREATE TABLE `USER_0` (
  `user_no` int(11) NOT NULL,
  `pass_wd` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`user_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `USER_0` (`user_no`, `pass_wd`) VALUES ('10', '123');

DELIMITER $$

-----------------------------------------------------------

DROP PROCEDURE IF EXISTS `WebProtocal_USER_R`$$

CREATE PROCEDURE `WebProtocal_USER_R`(
 IN   i_user_no   BIGINT UNSIGNED
, OUT  o_sp_rtn    INT
, OUT  o_rtn_message VARCHAR(100)
)
BEGIN
/******************************************************************************
Name        : WebProtocal_USER_R
Description  : 유저 기본정보 가져오기
Return     : 0 = 조회성공
      -1 = 해당 유저정보가 존재하지 않습니다.
    
     
Example     :
 
SET  @i_user_no     := 1
,    @o_sp_rtn             := -1
,    @o_rtn_message   := ''
;


CALL WebProtocal_USER_R(@i_user_no,@o_sp_rtn,@o_rtn_message);

SELECT  @o_sp_rtn, @o_rtn_message;

SELECT * FROM USER_0;



Reference    :
    1. USER_0~9
History   :
Ver        Date        Author           Description
---------  ----------  ---------------  ------------------------------------
1.0        2013-12-30 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 v_group_no    TINYINT      DEFAULT 0;


 FLOW_START:
 BEGIN
   
  SET v_group_no = RIGHT(i_user_no,1);
   
  -- STEP 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  :=  '
           SELECT
       user_no
       , pass_wd
           FROM @p_table_name
           WHERE user_no  = ?
     ';

  SET   @v_sql  :=  REPLACE(@v_sql,'@p_table_name',@v_table_name);
  SET   @v_user_no :=  i_user_no;
   
  SET   @v_effect_row :=  -1;
  SET   @v_error_number :=  -1;


  PREPARE stmt FROM @v_sql;   
  EXECUTE stmt USING @v_user_no;
  SELECT FOUND_ROWS() INTO v_row_count ;  
  DEALLOCATE PREPARE stmt;
  
  IF (v_row_count<>1) THEN
   ROLLBACK; 
   SELECT -1      INTO o_sp_rtn;
   SELECT '해당 유저정보가 존재하지 않습니다.' INTO o_rtn_message;
   LEAVE FLOW_START;
  END IF;
  
  SELECT 0   INTO o_sp_rtn;
  SELECT '조회성공' INTO o_rtn_message;

 END;  
END$$

DELIMITER ;