存储过程实现查询后更新&&与只用sql一句话的对比

存储过程实例

• 存储过程–以基本信息表为基准，使任务表的规划基站名与其一致

DELIMITER $$

DROP PROCEDURE IF EXISTS copySur_station_name $$

CREATE PROCEDURE copySur_station_name () 
BEGIN
/*局部变量的定义 declare*/
DECLARE pn VARCHAR (100) ;
DECLARE sn VARCHAR (100) ;
DECLARE tid VARCHAR (200) ;
declare stop int default 0;
declare cur cursor for(
# 查询出两张表中规划站点名不同的项
SELECT planbsname, sur_station_name, taskid FROM t_task_info t LEFT JOIN mdi_basestation m ON m.taskid = t.id 
WHERE m.sur_station_name <> t.planbsname
);
/*这把 游标 异常后 捕捉并设置 循环使用 变量 stop 为 null 跳出循环。*/
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop = null;
/*开游标*/
OPEN cur;
/*游标向下走一步，将查询出来的值付给定义的变量*/
    FETCH cur INTO pn,sn,tid;
    WHILE ( stop is not null) DO
    update t_task_info  set  planbsname=sn  where id = tid ;
    FETCH cur INTO pn,sn,tid;
    END WHILE;
/*游标向下走一步*/
CLOSE cur;
END

• SELECT INTO 用法 试验

DELIMITER $$

DROP PROCEDURE IF EXISTS copySur_station_name $$

CREATE PROCEDURE copySur_station_name () 
BEGIN
/*局部变量的定义 declare*/
DECLARE pn VARCHAR (100) ;
DECLARE sn VARCHAR (100) ;
DECLARE tid VARCHAR (200) ;
declare stop int default 0;
declare cur cursor for(
/* 查询出两张表中规划站点名不同的项*/
SELECT planbsname, taskid FROM t_task_info t LEFT JOIN mdi_basestation m ON m.taskid = t.id 
WHERE m.sur_station_name <> t.planbsname
);
/*这把 游标 异常后 捕捉并设置 循环使用 变量 stop 为 null 跳出循环。*/
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop = null;
/*开游标*/
OPEN cur;
/*游标向下走一步，将查询出来的值付给定义的变量*/
    FETCH cur INTO pn,tid;
    WHILE ( stop is not null) DO
    SELECT sur_station_name INTO sn FROM t_task_info t LEFT JOIN mdi_basestation m ON
    m.taskid = tid WHERE m.sur_station_name <> t.planbsname;
    /*！！！！需要注意的是这里如果更新两条记录会报错（Result consisted of more than one row）*/
    update t_task_info  set  planbsname=sn  where id = tid ;
    FETCH cur INTO pn,tid;
    END WHILE;
/*游标向下走一步*/
CLOSE cur;
END

• 父子级关系–存储过程

DELIMITER $$

DROP PROCEDURE IF EXISTS parent_son_relation $$

CREATE PROCEDURE parent_son_relation () 
BEGIN  
    -- 创建接收游标数据的变量  
    declare tid  varchar(200);  
    declare p_attr VARCHAR (100); 
    declare p_attr1 VARCHAR (100); 
    declare p_columnname VARCHAR (100);  
    declare resc VARCHAR (100) ;  
    -- 创建结束标志变量  
    declare done int default false;  
    -- 创建游标  
    declare cur cursor for select id,substring_index(sourceexpression, ',', -1) as p_attributecnname,substring_index(sourceexpression, ',', 1) as p_attributecnname1,resclassenname FROM m_resattribute WHERE ifchange = '1' AND sourceexpression <> '' AND sourceexpression IS NOT NULL AND locate(',',sourceexpression)>0 AND inputtype <> 'spinner_counts';  
    -- 指定游标循环结束时的返回值  
    declare continue HANDLER for not found set done = true;  

    -- 打开游标  
    open cur;  
    -- 开始循环游标里的数据  
    read_loop:loop  
    -- 根据游标当前指向的一条数据  
    fetch cur into tid,p_attr,p_attr1,resc; 
    -- 判断游标的循环是否结束  
    if done then  
        leave read_loop;    -- 跳出游标循环  
    end if;  

    SELECT m.attributecolumnname into p_columnname FROM m_resattribute m WHERE attributecnname = p_attr AND resclassenname=resc;

    IF(p_columnname IS NULL OR p_columnname = '') THEN
         SELECT m.attributecolumnname into p_columnname FROM m_resattribute m WHERE sourceexpression = p_attr1 AND resclassenname=resc;  
    END IF;

    update m_resattribute res  set  res.parentcolumn = p_columnname  where res.id = tid ; 
    -- 结束游标循环  
    end loop;  
    -- 关闭游标  
    close cur;  

END 

• 土建图纸提交时间–存储过程与一句话sql实现先查询后更新功能的对比

法一：

DELIMITER $$

DROP PROCEDURE IF EXISTS process_tujian_tuzhiDate $$

CREATE PROCEDURE process_tujian_tuzhiDate () 
BEGIN
/*局部变量的定义 declare*/
DECLARE cdate VARCHAR (100) ;
DECLARE tid VARCHAR (200) ;
declare stop int default 0;
declare cur cursor for(
#查找提交了土建图纸的taskid；然后获取最新的土建图纸提交时间
SELECT 
d.taskid
,MAX(a.createdate) as createdate
FROM t_s_document d LEFT JOIN t_s_attachment a ON d.id = a.ID
WHERE  a.extend='dwg' AND d.taskid IS NOT NULL AND d.taskid <> '' and d.typetz ='1'-- 土建
GROUP BY d.taskid
);
/*这把 游标 异常后 捕捉并设置 循环使用 变量 stop 为 null 跳出循环。*/
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop = null;
/*开游标*/
OPEN cur;
/*游标向下走一步，将查询出来的值付给定义的变量*/
    FETCH cur INTO cdate,tid;
    WHILE ( stop is not null) DO
    update t_task_info  set  tjtzdate = cdate  where id = tid ;
    FETCH cur INTO cdate,tid;
    END WHILE;
/*游标向下走一步*/
CLOSE cur;
END

法二：不用存储过程，一句sql搞定
#用其他两表的数据更新第三张表
UPDATE t_task_info t
LEFT JOIN
(  
SELECT 
d.taskid,MAX(a.createdate) as createdate
FROM t_s_document d LEFT JOIN t_s_attachment a ON d.id = a.ID
WHERE  a.extend='dwg' AND d.taskid IS NOT NULL AND d.taskid <> '' and d.typetz ='1'-- 土建
GROUP BY d.taskid
) r
ON r.taskid = t.id SET t.tjtzdate = r.createdate