Mysql 儲存程式

標籤：

#1預存程序
create procedure greeting()
BEGIN
# 77 = 16 FOR username + 60 for hostname + 1 for ‘@‘
DECLARE user CHAR(77) CHARACTER SET utf8;
SET user = (SELECT CURRENT_USER());
IF INSTER(user, ‘@‘) > 0 THEN
SET user = SUBSTRING_INDEX(user, ‘@‘,1);
END IF;
IF user = ‘‘ THEN
SET = ‘earthling‘;
END IF;
SELECT CONCAT(‘Greetings,‘, user, ‘!‘) AS greetig;
END;

#時間儲存函數
delimiter $$
CREATE PROCEDURE show_time3()
BEGIN
SELECT ‘Local time is :‘, CURRENT_TIMESTAMP;
SELECT ‘UTC time is :‘ , UTC_TIMESTAMP;
END $$
DELIMITER ;

delimiter EOF
CREATE PROCEDURE show_time3()
SELECT ‘Local time is:‘, CURRENT_TIMESTAMP;
SELECT ‘UTC time is:‘, UTC_TIMESTAMP;
END EOF
DELIMITER ;

#2儲存函數

DELIMITER $
CREATE FUNCTION show_id_ord(id INT)
RETURNS INT
READS SQL DATA
BEGIN
RETURN (SELECT count(*) FROM test where id = id)
END$
DELIMITER ;

delimiter $
CREATE PROCEDURE update_test_test_id(p_id INT, p_date INT)
BEGIN
update test set test_id = p_date where id = p_id;
END$
delimiter ;

DELIMITER $
CREATE PROCEDURE select_test_id (OUT p_id int)
BEGIN
SELECT COUNT(*) FROM test where id = p_id INTO p_id;
END $
DELIMITER ;

DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘使用者ID‘,
`user_name` varchar(50) NOT NULL DEFAULT ‘‘ COMMENT ‘使用者名稱稱‘,
`gender` tinyint(1) NOT NULL DEFAULT ‘0‘ COMMENT ‘使用者性別;1:男,2:女‘,
`add_time` int(10) unsigned NOT NULL DEFAULT ‘0‘ COMMENT ‘使用者註冊時間‘,
`update_timne` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘使用者最後更新時間‘,
PRIMARY KEY (`id`) USING BTREE,
KEY `user_name` (`user_name`),
KEY `gender` (`gender`),
KEY `add_time` (`add_time`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT=‘使用者資訊表‘

#3觸發器

delimiter $
CREATE TRIGGER ai_t AFTER INSERT ON user_info
FOR EACH ROW BEGIN
INSTER INTO test(`test_id`) values((SELECT test_id FROM test_id order by id desc limit 1 )+1)
END $
DELIMITER ;

delimiter $
CREATE TRIGGER bi_t BEFORE INSERT ON t
FOR EACH ROW BEGIN
SET new.dt = CURRENT_TIMESTAMP;
IF NEW.parent <0 THEN
SET new.parent = 0;
ELSEIF new.parent >100 THEN
SET new.parent = 100;
END IF;
END$
DELIMITER ;

#4建立事件

CREATE EVENT id_defined_min
ON SCHEDULE EVERY 0.02 HOUR
DO
DELETE FROM test where id <9 ;

Mysql 儲存程式