• 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

  • 相关阅读:
    Linux bash script block comment All In One
    一道有疑问的小学二年级数学题 All In One
    2022 微软飞行模拟器 中文版 All In One
    HTML5 video controlslist All In One
    Oracle 查询出来的数据取第一条
    js 获取select的值 / js动态给select赋值 jQuery获取
    Repeater隔行变色,两个方式
    layui之动态添加下拉菜单
    myeclipse部署多个应用到不同的weblogic domain中
    .net中LAMBDA表达式常用写法
  • 原文地址:https://www.cnblogs.com/zhlblogs/p/9754903.html
Copyright © 2020-2023  润新知