sql 存储过程和触发器

mysql-----------------------------------------------------------------------------------------------------------------------------------------

BEGIN

DECLARE i INT4;
DECLARE n INT4;

DELETE FROM article_keywords WHERE article_id = NEW.id;
set i = 0;
select func_get_split_string_total(NEW.keywords,';') INTO i;
set n = 1;
while n<=i DO
INSERT INTO article_keywords (article_id,keyword,sort_id) VALUES (NEW.id, func_get_split_string(NEW.keywords,';', n),n);
set n = n+1;
end WHILE;
end

 

BEGIN

DECLARE i INT4;
DECLARE n INT4;

DELETE FROM article_keywords WHERE article_id = NEW.id;
set i = 0;
select func_get_split_string_total(NEW.keywords,';') INTO i;
set n = 1;
while n<=i DO
INSERT INTO article_keywords (article_id,keyword,sort_id) VALUES (NEW.id, func_get_split_string(NEW.keywords,';', n),n);
set n = n+1;
end WHILE;
end

 

BEGIN
DELETE FROM article_keywords WHERE article_id = OLD.id;
end

 

postgresql-----------------------------------------------------------------------------------------------------------------------------------------

CREATE or Replace FUNCTION func_classification_updateTime() RETURNS trigger AS $func_classification_updateTime$ 
BEGIN 
update user_classification set updated_date = NOW() where user_id = NEW.user_id and name = NEW.classification_system_name; 
return null; 
END; 
$func_classification_updateTime$ LANGUAGE plpgsql;


CREATE TRIGGER t_classification_updateTime AFTER INSERT OR UPDATE OR DELETE ON classification_system 
FOR EACH ROW EXECUTE PROCEDURE func_classification_updateTime();

 

mysql存储过程-------------------------------------------------------------------------------------------------------------------------------

create procedure pro14()
begin
DECLARE stop1 INT DEFAULT 0; -- 定义终止符stop1
DECLARE city_adcode CHAR(225); -- 游标当前的城市adcode
DECLARE province_temp CHAR(225); -- 临时存储省份字段
DECLARE cur CURSOR FOR (select adcode from china_address where adcode not in (select adcode from china_address where adcode like '%0000')); -- 定义游标，查询所有市的adcode

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stop1=1; -- 当游标遍历结束时，终止符stop1置为1
OPEN cur; -- 开启游标
WHILE (stop1 <> 1) DO -- 循环
FETCH cur INTO city_adcode;-- 这个语句用指定的打开光标读取下一行（如果有下一行的话），并且前进光标指针，将当前游标的城市adcode赋给city_adcode参数
select province into province_temp from china_address where adcode=CONCAT(left(city_adcode, 2),'0000'); -- 根据city_adcode参数设置province_temp为当前城市的省份名称
update china_address set province= province_temp where adcode=city_adcode; -- 更新当前city_adcode对应的记录的省份为province_temp
END WHILE; -- 结束循环

CLOSE cur;-- 这个语句关闭先前打开的光标。
end;