본문 바로가기

Databases/MySQL

프로시저 예

CREATE TABLE `td_bill_club_table` (
  `CLB_SEQ` int(11) NOT NULL COMMENT '당구 클럽 테이블수 - 당구 클럽 일련번호',
  `CLB_TABLE_TYPE` enum('LT','MT','PT','ST') NOT NULL COMMENT '당구 클럽 테이블수 - 테이블 구분',
  `CLB_TABLE_COST` decimal(10,0) NOT NULL DEFAULT '0' COMMENT '당구 클럽 테이블수 - 테이블 요금',
  `CLB_TABLES` int(11) NOT NULL DEFAULT '0' COMMENT '당구 클럽 테이블수 - 테이블수',
  PRIMARY KEY (`CLB_SEQ`,`CLB_TABLE_TYPE`),
  CONSTRAINT `fk_TD_BILL_CLUB_TABLE_TD_BILL_CLUB1` FOREIGN KEY (`CLB_SEQ`) REFERENCES `td_bill_club` (`CLB_SEQ`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='당구 클럽 테이블수';
-------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`gxsports`@`%` PROCEDURE `PROC_BILL_CLUB_TABLE.ADD_ROW`(P_CLB_SEQ INT, P_CLB_TABLE_TYPE INT, P_CLB_TABLE_COST INT, P_CLB_TABLES INT)
BEGIN
	INSERT INTO TD_BILL_CLUB_TABLE
		(
			CLB_SEQ, CLB_TABLE_TYPE,
			CLB_TABLE_COST, CLB_TABLES
		)
	VALUES
		(
			P_CLB_SEQ, P_CLB_TABLE_TYPE,
            P_CLB_TABLE_COST, P_CLB_TABLES
		)
	ON DUPLICATE KEY UPDATE
		CLB_TABLE_COST = P_CLB_TABLE_COST,
		CLB_TABLES = P_CLB_TABLES;
END$$
DELIMITER ;
-------------------------------------------------------------------------

DELIMITER $$ CREATE DEFINER=`gxsports`@`%` PROCEDURE `PROC_BILL_CLUB_TABLE.DEL_ROW`(P_CLB_SEQ INT(11), P_CLB_TABLE_TYPE enum('LT','MT','PT','ST')) BEGIN DELETE FROM TD_BILL_CLUB_TABLE WHERE CLB_SEQ = P_CLB_SEQ AND CLB_TABLE_TYPE = P_CLB_TABLE_TYPE; END$$ DELIMITER ;

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

DELIMITER $$ CREATE DEFINER=`gxsports`@`%` PROCEDURE `PROC_BILL_CLUB_TABLE.GET_ROW`(P_CLB_SEQ INT(11), P_CLB_TABLE_TYPE enum('LT','MT','PT','ST')) BEGIN SELECT CLB_SEQ, CLB_TABLE_TYPE, CLB_TABLE_COST, CLB_TABLES FROM TD_BILL_CLUB_TABLE WHERE CLB_SEQ = P_CLB_SEQ AND CLB_TABLE_TYPE = P_CLB_TABLE_TYPE; END$$ DELIMITER ;

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

DELIMITER $$ CREATE DEFINER=`gxsports`@`%` PROCEDURE `PROC_BILL_CLUB_TABLE.GET_ROWS`(P_PAGE_NO INT, P_PAGE_SIZE INT) BEGIN DECLARE LIMIT_START INT DEFAULT 0; DECLARE LIMIT_COUNT INT DEFAULT 10; SET LIMIT_START = (P_PAGE_NO - 1) * P_PAGE_SIZE; SET LIMIT_COUNT = P_PAGE_SIZE; SELECT CLB_SEQ, CLB_TABLE_TYPE, CLB_TABLE_COST, CLB_TABLES FROM TD_BILL_CLUB_TABLE ORDER BY CLB_SEQ LIMIT LIMIT_START, LIMIT_COUNT; END$$ DELIMITER ;

-------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`gxsports`@`%` PROCEDURE `PROC_SYSTEM_STATE.ADD_ROW`(
	P_SYS_STATE INT,
    P_SYS_CHK_SDATE DATETIME,
    P_SYS_CHK_EDATE DATETIME,
    P_SYS_MEM_SEQ INT
)
BEGIN

    # 관리자만 등록할 수 있다!
#	IF FN_IS_ADMINISTRATOR(P_SYS_MEM_SEQ) = FALSE THEN
	#	SELECT ERR
  #  END IF;

	
	INSERT INTO TD_SYSTEM_STATE
		(
			SYS_STATE,
			SYS_CHK_SDATE, SYS_CHK_EDATE
		)
	VALUES
		(
			P_SYS_STATE,
            P_SYS_CHK_SDATE, P_SYS_CHK_EDATE
		);
        
END$$
DELIMITER ;
-------------------------------------------------------------------------

DELIMITER $$ CREATE DEFINER=`gxsports`@`%` PROCEDURE `PROC_SYSTEM_STATE.DEL_ROW`(P_SYS_SEQ INT(11)) BEGIN DELETE FROM TD_SYSTEM_STATE WHERE SYS_SEQ = P_SYS_SEQ; END$$ DELIMITER ;

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

DELIMITER $$ CREATE DEFINER=`gxsports`@`%` PROCEDURE `PROC_SYSTEM_STATE.GET_ROW`(P_SYS_SEQ INT(11)) BEGIN SELECT SYS_SEQ, SYS_STATE, SYS_CHK_SDATE, SYS_CHK_EDATE, SYS_MEM_SEQ, RELEASE_DATE, FN_GET_SYSTEM_MESSAGE(SYS_STATE, P_SYS_LNG_CD) SYS_MESSAGE FROM TD_SYSTEM_STATE WHERE SYS_SEQ = P_SYS_SEQ ORDER BY SYS_SEQ DESC LIMIT 1; END$$ DELIMITER ;

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

DELIMITER $$ CREATE DEFINER=`gxsports`@`%` PROCEDURE `PROC_SYSTEM_STATE.GET_ROWS`(P_PAGE_NO INT, P_PAGE_SIZE INT) BEGIN DECLARE LIMIT_START INT DEFAULT 0; DECLARE LIMIT_COUNT INT DEFAULT 10; SET LIMIT_START = (P_PAGE_NO - 1) * P_PAGE_SIZE; SET LIMIT_COUNT = P_PAGE_SIZE; SELECT SYS_SEQ, SYS_STATE, SYS_CHK_SDATE, SYS_CHK_EDATE, SYS_MEM_SEQ, RELEASE_DATE FROM TD_SYSTEM_STATE ORDER BY SYS_SEQ LIMIT LIMIT_START, LIMIT_COUNT; END$$ DELIMITER ;


'Databases > MySQL' 카테고리의 다른 글

데이터 암호화 기법  (0) 2015.04.02