mysql create trigger and procedure demo

来源：互联网发布：apache用户访问认证编辑：程序博客网时间：2024/04/29 02:55

DROP TABLE IF EXISTS prAddVote;
DELIMITER

C R E A T E / * [D E F I N E R = u s e r | C U R R E N T U S E R] * / P R O C E D U R E ‘ i t e m ‘ . ‘ p r A d d V o t e ‘ (v o t e T i t l e V A R C H A R (255), v o t e S u m I N T, I m a g e V A R C H A R (255), e n d T i m e D A T E T I M E, m o d e l s B I T (1)) / * L A N G U A G E S Q L | [N O T] D E T E R M I N I S T I C | C O N T A I N S S Q L | N O S Q L | R E A D S S Q L D A T A | M O D I F I E S S Q L D A T A | S Q L S E C U R I T Y D E F I N E R | I N V O K E R | C O M M E N T' s t r i n g' * / B E G I N S E T @ v o t e T i t l e = v o t e T i t l e; S E T @ v o t e S u m = v o t e S u m; S E T @ I m a g e = I m a g e; S E T @ e n d T i m e = e n d T i m e; S E T @ m o d e l s = m o d e l s; S E T @ i n s e r t S q l = C O N C A T (' i n s e r t i n t o v o t e M a s t e r (v o t e T i t l e, v o t e S u m, I m a g e, e n d T i m e, m o d e l s) v a l u e s (?, ?, ?, ?, ?)'); P R E P A R E s t m t i n s e r t F R O M @ i n s e r t S q l; E X E C U T E s t m t i n s e r t U S I N G @ v o t e T i t l e, @ v o t e S u m, @ I m a g e, @ e n d T i m e, @ m o d e l s; D E A L L O C A T E P R E P A R E s t m t i n s e r t; E N D

DELIMITER ;

DROP TRIGGER IF EXISTS updateVoteMasterVotSum;
DELIMITER

C R E A T E T R I G G E R u p d a t e V o t e M a s t e r V o t S u m A F T E R U P D A T E O N v o t e D e t a i l s F O R E A C H R O W B E G I N I F n e w . v o t e N u m = o l d . v o t e N u m + 1 T H E N U P D A T E v o t e M a s t e r S E T v o t e S u m = v o t e S u m + 1 W H E R E v o t e M a s t e r . v o t e M a s t e r I d = o l d . v o t e M a s t e r I d; E N D I F; E N D

;

DROP TABLE IF EXISTS DateLookup;
CREATE TABLE DateLookup
(
DateKey INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
DateFull DATETIME,
CharacterDate VARCHAR(10),
FullYear CHAR(4),
QuarterNumber TINYINT,
WeekNumber TINYINT,
WeekDayName VARCHAR(10),
MonthDay TINYINT,
MONTHNAME VARCHAR(12),
YearDay SMALLINT,
DateDefinition VARCHAR(30),
WEEKDAY TINYINT,
MonthNumber TINYINT
);

SET @Date = DATE_FORMAT(‘2019-12-01’,’%Y-%c-%d %h:%i:%s’);
DROP PROCEDURE IF EXISTS update_datelookup;
DELIMITER ##
CREATE PROCEDURE update_datelookup()
BEGIN
loop1: WHILE @Date < ‘2020-01-01 00:00:00’ DO
INSERT INTO DateLookup
(
DateKey, DateFull, FullYear,
QuarterNumber, WeekNumber, WeekDayName,
MonthDay, MONTHNAME, YearDay,
DateDefinition, CharacterDate,
WEEKDAY,MonthNumber
)
SELECT
UNIX_TIMESTAMP(@Date), @Date, YEAR(@Date),
QUARTER(@Date), WEEK(@Date), DAYOFWEEK(@Date),
DAYOFMONTH(@Date), MONTHNAME(@Date), DAYOFYEAR(@Date),
DATE_FORMAT(@Date,’%Y-%c-%d %h:%i:%s’),DATE_FORMAT(@Date,’%Y-%m-%d’),
WEEKDAY(@Date),
MONTH(@Date);
SET @Date = DATE_ADD(@Date,INTERVAL 1 DAY) ;
END WHILE loop1;
END ##

CALL update_datelookup();

0 0