MySQL存储过程示例

示例1

-- 创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `rename_file_name`()
begin
DECLARE flag INT;
DECLARE errfId BIGINT(20); -- e_relative_record_file id
DECLARE efrId BIGINT(20); -- e_file_record id
DECLARE renameFileName VARCHAR(200); -- 重新生成的图片文件名称
DECLARE target CURSOR FOR 
SELECT errf.id errfId,efr.id efrId,concat(substring_index(errf.file_name, '_', 1),'-',FLOOR(100+( RAND()*900)),'_cut0.jpg') renameFileName 
FROM e_relative_record_file errf INNER JOIN e_file_record efr ON errf.file_name = efr.file_name GROUP BY errf.file_name HAVING COUNT(errf.file_name)>1; -- 游标 查询重复的文件名称的数据
SET flag=0;
OPEN target;
REPEAT
     FETCH target into errfId,efrId,renameFileName; -- 变量赋值
     UPDATE e_relative_record_file SET file_name = renameFileName WHERE id= errfId;  -- 更新e_relative_record_file表重复的图片名称
     UPDATE e_file_record SET file_name = renameFileName WHERE id= efrId;     -- 更新e_file_record表重复的图片名称
UNTIL flag
END REPEAT;
CLOSE target;
end
-- 运行存储过程
call rename_file_name();
-- 删除存储过程
DROP PROCEDURE rename_file_name;

示例2

-- 创建存储过程
CREATE PROCEDURE `updatePersonId`()
begin
DECLARE cnt INT;
declare i int default 0 ;
DECLARE personId INT;
DECLARE personName VARCHAR(200);
DECLARE target CURSOR FOR SELECT p.name as personName,p.id as personId FROM e_person_info p GROUP BY p.name; -- 游标 查询重复的文件名称的数据
SELECT COUNT(*) into cnt FROM (SELECT id FROM e_person_info p GROUP BY p.name) t;
OPEN target;
REPEAT
         set i := i+1;
     FETCH target into personName,personId; -- 变量赋值
     UPDATE e_access_control_card SET person_id = personId WHERE card_owner = personName;
until i>=cnt end repeat;
CLOSE target;
end
-- 运行存储过程
call updatePersonId();
-- 删除存储过程
DROP PROCEDURE updatePersonId;

示例3

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `eauth`.`parkinglot_road`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
BEGIN
DECLARE flag INT; -- e_archives_facility id
DECLARE eafId BIGINT(20); -- e_archives_facility id
DECLARE facilityDid VARCHAR(100); -- e_archives_facility facility_did
DECLARE colFlag INT DEFAULT -1; -- 判断 e_parkinglot_road 是否有facility_id或facility_did字段
 DECLARE target CURSOR FOR 
 SELECT DISTINCT epr.facility_did facilityDid, eaf.id eafId  FROM e_parkinglot_road epr INNER JOIN e_archives_facility  eaf ON epr.facility_did = eaf.facility_did;
 SELECT COUNT(*) INTO flag FROM e_parkinglot_road epr INNER JOIN e_archives_facility  eaf ON epr.facility_did = eaf.facility_did;
 -- 查询 e_parkinglot_road 表是否有facility_id 字段，没有就新建
  SELECT COUNT(*) INTO colFlag FROM information_schema.columns WHERE TABLE_SCHEMA ='eauth' AND table_name = 'e_parkinglot_road' AND column_name = 'facility_id';
 IF colFlag = 0 
 THEN  
 SET @insert_data_sql = "ALTER TABLE e_parkinglot_road ADD COLUMN `facility_id` BIGINT(20) NOT NULL DEFAULT 0 COMMENT '设备唯一标识（设备主键）' AFTER `parkinglot_id`;";
  PREPARE add_sql FROM @insert_data_sql;
     EXECUTE add_sql;
END IF;

IF flag > 0 
  THEN 
   OPEN target;
    LOOP
    FETCH target INTO facilityDid,eafId;
     UPDATE e_parkinglot_road SET facility_id = eafId WHERE facility_did = facilityDid;  -- 对e_parkinglot_road表的facility_id字段赋值
    END LOOP;
     CLOSE target;
  END IF;
  
SET colFlag = -1;
 -- 查询 e_parkinglot_road 表是否有facility_did字段，存在就删除
 SELECT COUNT(*) INTO colFlag FROM information_schema.columns WHERE TABLE_SCHEMA ='eauth' AND table_name = 'e_parkinglot_road' AND column_name = 'facility_did';
 IF colFlag > 0
 THEN  SET @delete_data_sql = "ALTER TABLE e_parkinglot_road DROP COLUMN facility_did;";
   PREPARE del_sql FROM @delete_data_sql;
        EXECUTE del_sql;
 END IF;

    END$$
DELIMITER ;