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 |
---|