mysql创建存储 游标_mysql中创建自定义存储过程，并使用游标案例

DELIMITER //

DROP PROCEDURE IF EXISTS `test` //

CREATE PROCEDURE `test` ()

BEGIN

DECLARE no_results,le_numbers,med_numbers,fee_numbers INT DEFAULT 0; /*定义变量*/

DECLARE r_hid LONG;

DECLARE cur_hid CURSOR FOR SELECT hid from hospital where `status` = 0; /*First: Delcare a cursor,首先这里对游标进行定义*/

#条件处理必须在游标声明之后声明

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_results = 1; /*when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/

/* for loggging information 创建个临时表格来保持*/

/* CREATE TEMPORARY TABLE infologs (

plid INT (11) NOT NULL AUTO_INCREMENT COMMENT '主键',

hid INT (11) COMMENT '医院id',

level VARCHAR (20) COMMENT '会员类型名',

created datetime COMMENT '创建时间',

modified datetime COMMENT '修改时间',

`status` TINYINT (2) COMMENT '状态 0.正常 1.删除',

discount decimal(3,1) default 10.0 comment '折扣',

PRIMARY KEY (`plid`)

);

*/

OPEN cur_hid; /*Second: Open the cursor 接着使用OPEN打开游标*/

FETCH cur_hid INTO r_hid; /*Third: now you can Fetch the row 把第一行数据写入变量中,游标也随之指向了记录的第一行*/

REPEAT

SELECT count(*) INTO le_numbers

FROM patient_level

WHERE hid = r_hid;

IF le_numbers = 0 THEN

/*INSERT INTO patient_level(hid,level,discount,status,created) */

INSERT INTO patient_level(hid,level,discount,status,created)

VALUES (r_hid,'普通会员',10,0,NOW()),(r_hid,'银牌会员',10,0,NOW()),(r_hid,'金牌会员',10,0,NOW()),(r_hid,'钻石会员',10,0,NOW());

END IF;

SELECT count(*) INTO med_numbers FROM med_item WHERE hid = r_hid;

IF med_numbers = 0 THEN

INSERT INTO med_item(hid,name,duration,status,created) VALUES

(r_hid,'医学验光',30,0,NOW()),(r_hid,'视功能检查',30,0,NOW()),(r_hid,'常规复查',30,0,NOW()),(r_hid,'OK镜验配',60,0,NOW()),(r_hid,'视觉训练',60,0,NOW());

END IF;

SELECT count(*) INTO fee_numbers FROM fee_category WHERE hid = r_hid;

IF fee_numbers = 0 THEN

insert into fee_category(superior,hid,name,type,status,created) values (1,r_hid,'挂号费',1,0,now()),(1,r_hid,'检查费',1,0,now()),(1,r_hid,'手术费',1,0,now())

,(3,r_hid,'镜片类',1,0,now()),(3,r_hid,'镜架类',1,0,now()),(3,r_hid,'隐形眼镜类',1,0,now()),(3,r_hid,'太阳镜类',1,0,now()),(3,r_hid,'配件类',1,0,now()),

(2,r_hid,'中药类',1,0,now()),(2,r_hid,'西药类',1,0,now()),(2,r_hid,'器械类',1,0,now()),

(4,r_hid,'塑形镜类',1,0,now()),(4,r_hid,'RGP类',1,0,now()),(4,r_hid,'离焦软镜类',1,0,now()),

(5,r_hid,'到店训练类',1,0,now()),(5,r_hid,'家庭训练类',1,0,now()),

(6,r_hid,'护理液类',1,0,now()),(6,r_hid,'润眼液类',1,0,now()),(6,r_hid,'清洗仪类',1,0,now());

END IF;

FETCH cur_hid INTO r_hid;

UNTIL no_results = 1

END REPEAT;

CLOSE cur_hid; /*Finally: cursor need be closed 用完后记得用CLOSE把资源释放掉*/

/* SELECT * FROM infologs;

DROP TABLE infologs;

SELECT * FROM infologs1;

DROP TABLE infologs1;

SELECT * FROM infologs2;

DROP TABLE infologs2; */

END //

DELIMITER ;

call `test` (); /*执行存储过程*/

这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

例子2：

DROP PROCEDURE if EXISTS updateExpireDate;

CREATE PROCEDURE updateExpireDate ()

BEGIN

DECLARE r_hid INT (11);

DECLARE expireDate date;

#声明终止变量

DECLARE

not_result INT DEFAULT 0;

#创建游标

DECLARE

cur_data CURSOR FOR SELECT h.hid

,u.expire_date

FROM

hospital h

LEFT JOIN `user` u ON h.hid = u.hid

AND u.role = 'SALER'

AND DATE_FORMAT(h.expire_date, '%Y-%m-%d') != DATE_FORMAT(u.expire_date, '%Y-%m-%d')

WHERE

h.`status` = 0;

#必须在声明游标之后声明

DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_result = 1;

#打开游标

OPEN cur_data;

FETCH cur_data INTO r_hid,expireDate;

#遍历游标

REPEAT

IF r_hid IS NOT NULL THEN

UPDATE hospital SET expire_date = expireDate WHERE hid = r_hid;

END

IF;

FETCH cur_data INTO r_hid,expireDate;

UNTIL not_result = 1

END

REPEAT;

CLOSE cur_data;

END;

例子3：使用loop遍历游标

drop PROCEDURE if EXISTS test;

create PROCEDURE test()

BEGIN

DECLARE oid1 varchar(11);

DECLARE age1 varchar(32);

DECLARE done INT DEFAULT FALSE;

DECLARE cur_birth CURSOR FOR select oid,age from okjing_prod.order where birth is null;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur_birth;

read_loop: LOOP

FETCH cur_birth INTO oid1,age1;

IF done THEN

LEAVE read_loop;

END IF;

update okjing_prod.order set birth=date_sub(CURDATE(),INTERVAL age1 YEAR) where oid = oid1;

END LOOP;

CLOSE cur_birth;

END

注意：自定义的变量不要和数据库中的字段名一样，不然使用navicat运行时无效果

详解请参考：https://blog.csdn.net/pang_da_xing/article/details/53836235