구 시스템에서 새 시스템으로 옮기는 대장

CREATE PROCEDURE sp_GetBookList(IN p_site VARCHAR(10), IN p_keyword VARCHAR(100))
BEGIN
  SET @q = CONCAT('SELECT id, title, author_name FROM tb_book WHERE site = ''', p_site, '''');
  IF p_keyword <> '' THEN
    SET @q = CONCAT(@q, ' AND title LIKE ''%', p_keyword, '%''');
  END IF;
  PREPARE s FROM @q; EXECUTE s; DEALLOCATE PREPARE s;
END

CREATE PROCEDURE sp_GetBookDetail(IN p_id INT)
BEGIN
  SELECT b.*, d.id AS data_id, d.title AS data_title
    FROM tb_book b LEFT JOIN tb_data d ON d.book_id = b.id
   WHERE b.id = p_id;
END

CREATE PROCEDURE sp_InsertData(IN p_book INT, IN p_title VARCHAR(200), IN p_file VARCHAR(100))
BEGIN
  INSERT INTO tb_data (book_id, title, file_name, reg_date) VALUES (p_book, p_title, p_file, CURDATE());
END

CREATE PROCEDURE sp_CountDownload(IN p_from DATE, IN p_to DATE)
BEGIN
  SELECT data_id, COUNT(*) FROM tb_download_log WHERE log_date BETWEEN p_from AND p_to GROUP BY data_id;
END

CREATE TRIGGER trg_tb_book_upd BEFORE UPDATE ON tb_book
FOR EACH ROW SET NEW.upd_date = NOW();

CREATE TRIGGER trg_tb_data_log AFTER UPDATE ON tb_data
FOR EACH ROW INSERT INTO tb_data_history (data_id, changed_at, old_title) VALUES (OLD.id, NOW(), OLD.title);

CREATE PROCEDURE sp_MainBanner_Rotate()
BEGIN
  UPDATE tb_banner SET sort_no = FLOOR(RAND() * 100);
END

CREATE PROCEDURE sp_SyncTextbookSite()
BEGIN
  INSERT INTO trade_db.tb_book_copy SELECT * FROM edu_db.tb_book
  ON DUPLICATE KEY UPDATE title = VALUES(title);
END