icuba

-- 전체발송
INSERT INTO T_NOTE (SEND_USER_NO, SEND_USER_NAME, SEND_DATE, SEND_SW, RECEIVE_USER_NO, RECEIVE_USER_NAME, RECEIVE_DATE, RECEIVE_SW, NOTE_SUBJECT, NOTE_MEMO, USE_SW)
SELECT 0, '관리자', NOW(), 'Y', USER_NO, USER_NAME, NOW(), 'R', '제목', '내용', 'Y'
FROM T_USER
WHERE USE_SW='Y' 
AND USER_TYPE='P'
AND USER_TYPE='N'
AND USER_NO IN ()

-- T_NOTE
CREATE TABLE T_NOTE (
  IDX int(11) NOT NULL AUTO_INCREMENT,
  SEND_USER_NO int(11) NOT NULL DEFAULT '0',
  SEND_USER_NAME varchar(50) NOT NULL,
  SEND_DATE datetime DEFAULT NULL,
  SEND_SW char(1) NOT NULL DEFAULT 'N',
  RECEIVE_USER_NO int(11) NOT NULL DEFAULT '0',
  RECEIVE_USER_NAME varchar(50) NOT NULL,
  RECEIVE_DATE datetime DEFAULT NULL,
  RECEIVE_SW char(1) NOT NULL DEFAULT 'N',
  NOTE_SUBJECT varchar(500) DEFAULT NULL,
  NOTE_MEMO text,
  USE_SW char(1) NOT NULL DEFAULT 'N',
  PRIMARY KEY (IDX)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


-- T_NOTE_ALARM
CREATE TABLE T_NOTE_ALARM (
  USER_NO int(11) NOT NULL,
  NOTE_COUNT int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (USER_NO),
  UNIQUE KEY USER_NO (USER_NO)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


-- SP_NOTE_ALARM_RESET
DELIMITER //
CREATE PROCEDURE SP_NOTE_ALARM_RESET()
BEGIN
    TRUNCATE TABLE T_NOTE_ALARM;

    INSERT T_NOTE_ALARM (USER_NO, NOTE_COUNT)
    SELECT RECEIVE_USER_NO, COUNT(1) AS NOTE_COUNT
    FROM T_NOTE
    WHERE USE_SW='Y' AND RECEIVE_SW='R'
    GROUP BY RECEIVE_USER_NO;
END//
DELIMITER ;


-- SP_NOTE_ALARM_USER
DELIMITER //
CREATE PROCEDURE SP_NOTE_ALARM_USER(
    IN _USER_NO INT
)
BEGIN
    DECLARE _NOTE_COUNT INT DEFAULT 0;

    SELECT IFNULL(COUNT(1), 0) INTO _NOTE_COUNT FROM T_NOTE WHERE USE_SW='Y' AND RECEIVE_SW='R' AND RECEIVE_USER_NO=_USER_NO;

    INSERT INTO T_NOTE_ALARM (USER_NO, NOTE_COUNT)
    VALUES (_USER_NO, _NOTE_COUNT)
    ON DUPLICATE KEY UPDATE NOTE_COUNT=_NOTE_COUNT;
END//
DELIMITER ;

• #테이블
• #프로시져