프로시저 예

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 ;