Mysql存储过程Demo

基础结构

DROP PROCEDURE IF EXISTS temp;
DELIMITER //
CREATE PROCEDURE temp()
BEGIN
  DECLARE a INT DEFAULT 1;
 
  SET a=a+1;
  SET @b=@b+1;
  SELECT a,@b;
 
END
//
DELIMITER ;

mysql执行动态sql

delimiter //  
create procedure proce2(in old varchar(100), in newT varchar(100))  
begin  
        declare my_sql varchar(500);  
        set my_sql = concat('create table ',newT,' like ',old);  
        set @ms = my_sql;  
        prepare s1 from @ms;  
        execute s1;  
        deallocate prepare s1;  
end
// 
DELIMITER ; 

存储过程的事务控制（提交和回滚）以及异常处理

create table hppluginsetting(
pid varchar(50),
plugindesc varchar(1000),
filepath varchar(1000),
isuse int,
ordernum int
)
;
drop procedure if exists p_addplugintoportal; 
DELIMITER //
create  procedure p_addplugintoportal(newid varchar(4000),plugindesc varchar(4000),filepath varchar(4000),existid varchar(4000))
begin

DECLARE temp_order int DEFAULT 0;
DECLARE strsql varchar(4000);
DECLARE t_error INTEGER DEFAULT 0;  
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;  

select  max(ordernum) into temp_order from hppluginsetting where pid = existid;

IF temp_order is null or temp_order ='' THEN 
   begin
   select max(ordernum) into temp_order from hppluginsetting;
   end;
select "123"+temp_order;
ELSEIF temp_order is not null THEN 
-- 事务控制
 START TRANSACTION; 
  begin  
        set strsql:= concat('UPDATE hppluginsetting set ordernum = ordernum +1 where ordernum >',temp_order);  
        set @strsql = strsql;  
        prepare s1 from @strsql;  
        execute s1;  
        deallocate prepare s1;  
select strsql;
  end; 
ELSE 
  set temp_order:=0;
END IF;
begin 
insert into hppluginsetting(pid,plugindesc,filepath,isuse,ordernum) values (newid,plugindesc,filepath,1,temp_order+1);
end;
 IF t_error = 1 THEN  
    ROLLBACK;  
 ELSE  
    COMMIT;  
 END IF; 
end
//
DELIMITER ;

call p_addplugintoportal ('checkPlugin','插件下载检查','/js/init_wev8.js,/js/activex/ActiveX_wev8.js,/wui/common/js/plugin/checkPlugin/checkPlugin.js','0') 

疑问：

以下用两个prepare预处理第二条insert并没有执行

drop procedure if exists p_addplugintoportal; 
DELIMITER //
create  procedure p_addplugintoportal(newid varchar(4000),plugindesc varchar(4000),filepath varchar(4000),existid varchar(4000))
begin

DECLARE temp_order int DEFAULT 0;
DECLARE strsql varchar(4000);
DECLARE t_error INTEGER DEFAULT 0;  
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;  

select  max(ordernum) into temp_order from hppluginsetting where pid = existid;

IF temp_order is null or temp_order ='' THEN 
   begin
   select max(ordernum) into temp_order from hppluginsetting;
   end;
ELSEIF temp_order is not null THEN 
-- 事务控制
 START TRANSACTION; 
  begin  
        set strsql:= concat('UPDATE hppluginsetting set ordernum = ordernum +1 where ordernum >',temp_order);  
        set @strsql = strsql;  
        prepare s1 from @strsql;  
        execute s1;  
        deallocate prepare s1;  
  end; 
ELSE 
  set temp_order:=0;
END IF;
  set strsql:=concat('insert into hppluginsetting(pid,plugindesc,filepath,isuse,ordernum) values (',newid,',',plugindesc,',',filepath,',',1,',',temp_order+1,')');
   set @strsql2 = strsql;  
        prepare s2 from @strsql2;  
        execute s2;  
        deallocate prepare s2;
 IF t_error = 1 THEN  
    ROLLBACK;  
 ELSE  
    COMMIT;  
 END IF; 
end
//
DELIMITER ;