mariaDB存储过程与触发器，mariadb存储过程

mariaDB存储过程与触发器，mariadb存储过程

根据网上例子，自己修改后，如下：

CREATE TABLE `ccs_customer` (
  `customer_code` varchar(20) COLLATE utf8_bin NOT NULL,
  `customer_name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `is_billing` int(11) DEFAULT NULL,
  `customer_tel` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `customer_mobile` varchar(15) COLLATE utf8_bin DEFAULT NULL,
  `customer_type` int(11) DEFAULT NULL,
  UNIQUE KEY `UK_CUSTOMERCODE` (`customer_code`)
)

CREATE TABLE `test1` (
  `cust_code` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `cust_name` varchar(45) COLLATE utf8_bin NOT NULL
)

存储过程：

delimiter //
drop procedure if exists new_procedure;
CREATE PROCEDURE `ccs5_test`.`new_procedure` ()
BEGIN
DECLARE a varchar(20) ;

-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT cust_code FROM ccs5_test.test1;

-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
OPEN cur;

-- 开始循环
read_loop: LOOP
-- 提取游标里的数据，这里只有一个，多个的话也一样；
FETCH cur INTO a;
-- 声明结束的时候
IF done THEN
LEAVE read_loop;
END IF;
-- 这里做你想做的循环的事件

INSERT INTO ccs5_test.ccs_customer(customer_code,customer_name) VALUES (a,'test');

END LOOP;
-- 关闭游标
CLOSE cur;

END

触发器：

delimiter //
drop trigger if exists t_trigger//
create trigger t_trigger after update on  ccs_customer for each row
begin
if (old.customer_code != new.customer_code) then
insert into test1 set cust_code=NEW.customer_code,cust_name=NEW.customer_code;
end if;
end