运维开发网

mysql 的sql语句

运维开发网 https://www.qedev.com 2020-03-18 17:11 出处:51CTO 作者:小作为
1procdure存储过程用游标循环BEGINDECLAREdevsnvarchar(100)defaultnull;DECLAREserialNovarchar(40)defaultnull;DECLAREfcloorint(11)DEFAULTNULL;DECLAREnextIntimedateTimedefaultnull;DeclaremodelNamevarchar(200)defaul

1 procdure 存储过程 用游标循环

BEGIN

DECLARE devsn varchar(100) default null;

DECLARE serialNo varchar(40) default null;

DECLARE fcloor int(11) DEFAULT NULL ;

DECLARE nextIntime dateTime default null;

Declare modelName varchar(200) default null;

Declare maintainName varchar(200) default null;

Declare propertyName varchar(200) default null;

DECLARE flg boolean default true;

DECLARE name varchar(200) default null;

// 获取结果集游标

DECLARE cur CURSOR FOR

select * from ems.sheet3 where ems.sheet3.设备注册代码 not in (select ems.tbl_user_dev.user_dev_sn

from ems.tbl_user_dev );

// 当不能发现时设置为false

DECLARE CONTINUE HANDLER FOR NOT FOUND SET flg = false;

 open cur; 
                while(flg) do
                FETCH cur into devsn,name,serialNo,nextIntime,fcloor,propertyName,maintainName;
                    if(flg) then
                    insert into tbl_user_dev (tbl_user_dev.user_dev_sn,
               tbl_user_dev.factory_no,
                        tbl_user_dev.user_dev_model_id,
                        tbl_user_dev.maintenance_organization_id,
                        tbl_user_dev.using_organization_id)
                    select devsn,serialNo,
                (select tbl_user_dev_model.id from tbl_user_dev_model where tbl_user_dev_model.model_name=name),
                (select tbl_org.id from tbl_org where tbl_org.org_name=maintainName and tbl_org.org_type='01'),
                (select tbl_org.id from tbl_org where tbl_org.org_name=propertyName and tbl_org.org_type='02');                     

        insert into tbl_elevator ( 
            tbl_elevator.user_dev_id,
            tbl_elevator.nextin_spection_date,
            tbl_elevator.floor )
            select (select tbl_user_dev.id from tbl_user_dev where tbl_user_dev.user_dev_sn=devsn),
                        nextIntime,
                        fcloor;
                end if;
        end WHILE;
 close cur;

end

// 注意:当插入的值要去继续查找时要用select 而不是 values() 而且将select的结果用扩好括起来

另外一个例子:

BEGIN

DECLARE aSn VARCHAR(45) default null;

DECLARE aFloor VARCHAR(45) default null;

DECLARE aStation VARCHAR(45) default null;

DECLARE aDoor VARCHAR(45) default null;

DECLARE done Boolean default true;

declare cur cursor for select

tbl_elevator_input.电梯注册代码,

substring_index(tbl_elevator_input.层数/站数/门数 ,'/',1) as floor ,

substring_index(substring_index(tbl_elevator_input.层数/站数/门数 ,'/',2),'/',-1) as station ,

substring_index(tbl_elevator_input.层数/站数/门数 ,'/',-1) as door

FROM tbl_elevator_input

where

length(tbl_elevator_input.层数/站数/门数)-length(replace(tbl_elevator_input.层数/站数/门数,'/','')) = 2

union all

select

tbl_elevator_input.电梯注册代码,

substring_index(tbl_elevator_input.层数/站数/门数 ,'/',1),

substring_index(substring_index(tbl_elevator_input.层数/站数/门数 ,'/',2),'/',-1),

''

FROM tbl_elevator_input

where

length(tbl_elevator_input.层数/站数/门数)-length(replace(tbl_elevator_input.层数/站数/门数,'/','')) = 1

union all

select tbl_elevator_input.电梯注册代码,substring_index(tbl_elevator_input.层数/站数/门数 ,'/',1),'',''

FROM tbl_elevator_input

where

length(tbl_elevator_input.层数/站数/门数)-length(replace(tbl_elevator_input.层数/站数/门数,'/','')) = 0;

declare continue handler for not found set done=FALSE;

open cur;

while done DO

fetch cur into aSn,aFloor,aStation,aDoor;

if(done) then

update tbl_elevator

set tbl_elevator.floor=aFloor,

tbl_elevator.stand=aStation,

tbl_elevator.door=aDoor

where tbl_elevator.user_dev_id in

(select * from tbl_user_dev

where tbl_user_dev.user_dev_sn=aSn);

end if;

end while;

close cur;

END

3,如何给某个字段的值一次加1,并用固定的长度显示不足的用0代替

sql 语句:

         set @r=0;// 定义局部变量
        update tbl_district set code =  lpad(@r:=@r+1,6,'0') // 左侧补0,定长为6位
0

精彩评论

暂无评论...
验证码 换一张
取 消