当前位置:Gxlcms > mysql > mysql的第一个存储过程_MySQL

mysql的第一个存储过程_MySQL

时间:2021-07-01 10:21:17 帮助过:5人阅读

DELIMITER //  
DROP PROCEDURE IF EXISTS procedure_ngg_pic_meta//
CREATE PROCEDURE procedure_ngg_pic_meta()
BEGIN
declare filename varchar(250) default '' ; /*文件名*/
declare style_type varchar(250) default '' ;/*风格*/
declare brand varchar(100) default '' ; /*品牌*/
declare review mediumtext default '' ;/*编辑评论*/
declare pid bigint(19) DEFAULT '0' ;/*定义pid*/
DECLARE rs CURSOR FOR select a.filename from tb_pictures a;/*申明游标*/
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET filename = null; /*当文件名为null时跳出循环*/
OPEN rs;/*打开游标*/
FETCH rs INTO filename; /*取得文件名*/
WHILE ( filename is not null) DO
/*开始遍历*/
/*主代码开始*/
select a.pid into pid from wp_ngg_pictures a where a.filename=filename;/*找到pid*/
select b.style_type,b.brand,b.review into style_type,brand,review from tb_pictures b where b.filename=filename;
insert into wp_nggcf_field_values (pid,fid,field_value,ngg_type,dateadded)
values( pid,6,style_type,1,now());
insert into wp_nggcf_field_values(pid,fid,field_value,ngg_type,dateadded)
values(pid,5,brand,1,now());
insert into wp_nggcf_field_values(pid,fid,field_value,ngg_type,dateadded)
values(pid,7,review,1,now());
/*主代码结束*/
FETCH rs INTO filename ;
END WHILE; /* 遍历结束*/
CLOSE rs;/*关闭游标 */
END //
DELIMITER ;

人气教程排行