mysql 的sql語句

標籤：.sh   null   false   and   close   mode   while   OLE   proc   

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的結果用擴好括起來

mysql 的sql語句