SELECT CASE (SELECT redtype FROM t_stock_redressal WHERE redid = 1)
WHEN 1 THEN
update t_stock_collocate_details as t1,t_stock_redressal_details as t2 set t1.factamount = t2.redcount
where t1.cdid = t2.cdid and t2.redid = 1;
WHEN 2 THEN
update t_stock_collocate_details as t1,t_stock_redressal_details as t2 set t1.inceptamount = t2.inceptcount
where t1.cdid = t2.cdid and t2.redid = 1;
END;
作用就是判断t_stock_redressal的redtype 是1还是2
是1的话就修改t_stock_collocate_details的factamount = t_stock_redressal_details的redcount ;
是2的话就修改t_stock_collocate_details的inceptcount = t_stock_redressal_details的inceptcount ;谢谢。..
WHEN 1 THEN
update t_stock_collocate_details as t1,t_stock_redressal_details as t2 set t1.factamount = t2.redcount
where t1.cdid = t2.cdid and t2.redid = 1;
WHEN 2 THEN
update t_stock_collocate_details as t1,t_stock_redressal_details as t2 set t1.inceptamount = t2.inceptcount
where t1.cdid = t2.cdid and t2.redid = 1;
END;
作用就是判断t_stock_redressal的redtype 是1还是2
是1的话就修改t_stock_collocate_details的factamount = t_stock_redressal_details的redcount ;
是2的话就修改t_stock_collocate_details的inceptcount = t_stock_redressal_details的inceptcount ;谢谢。..
try:
ELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `zz`.`ff`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
if (SELECT redtype FROM t_stock_redressal WHERE redid = 1)=1 then
update t_stock_collocate_details as t1,t_stock_redressal_details as t2 set t1.factamount = t2.redcount where t1.cdid = t2.cdid and t2.redid = 1;
else
update t_stock_collocate_details as t1,t_stock_redressal_details as t2 set t1.inceptamount = t2.inceptcount where t1.cdid = t2.cdid and t2.redid = 1;
end if;
END$$DELIMITER ;
DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `zz`.`ff`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
SELECT redtype into @dd FROM t_stock_redressal WHERE redid = 1;
case @dd
when 1 then
update t_stock_collocate_details as t1,t_stock_redressal_details as t2 set t1.factamount = t2.redcount where t1.cdid = t2.cdid and t2.redid = 1;
when 2 then
update t_stock_collocate_details as t1,t_stock_redressal_details as t2 set t1.inceptamount = t2.inceptcount where t1.cdid = t2.cdid and t2.redid = 1;
end case;
END$$DELIMITER ;
SELECT CASE (SELECT redtype FROM t_stock_redressal WHERE redid = ?)
WHEN 1 THEN
update t_stock_collocate_details as t1,t_stock_redressal_details as t2 set t1.factamount = t2.redcount
where t1.cdid = t2.cdid and t2.redid = ?;
WHEN 2 THEN
update t_stock_collocate_details as t1,t_stock_redressal_details as t2 set t1.inceptamount = t2.inceptcount
where t1.cdid = t2.cdid and t2.redid = ?;
END;不行吧?
我要用PreparedStatement 来设置 t2.redid = ?的.
DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `zz`.`ff`(in dd integer)
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
case dd
when 1 then
select 1;
when 2 then
select 2;
end case;
END$$DELIMITER ;