MySQL存储过程学习之一

`/修改mysql的结束符号,避免遇到；停止 /
DELIMITER

/∗[DEFINER=user|CURRENTUSER]∗/CREATEPROCEDURE‘zswms‘.‘UpdateCaseVolumeWeight‘()/∗LANGUAGESQL|[NOT]DETERMINISTIC|CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA|SQLSECURITYDEFINER|INVOKER|COMMENT′string′∗/BEGIN/∗声明变量∗/DECLARErowskunoVARCHAR(90);DECLAREiINTDEFAULT0;DECLAREcntINTDEFAULT0;/∗定义游标∗/DECLAREgetcategoryCURSORFORSELECTskunoFROMinventoryparttab;/∗给变量赋值∗/SELECTCOUNT(∗)INTOcntFROMinventoryparttabWHEREcontract=′AM′;/∗打开游标∗/OPENgetcategory;/∗开始循环∗/REPEAT/∗设置循环条件∗/SETi:=i+1;/∗将游标的一行值赋值给变量rowskuno∗/FETCHgetcategoryINTOrowskuno;/∗显示变量值∗/SELECTrowskuno,cnt;/∗逐行Update∗/UPDATEinventoryparttabtSETt.casevolumeweight=(SELECTb.aFROM(SELECTIF(CAST((p.‘caseofhigh‘∗p.‘caseoflength‘∗p.‘caseofwidht‘)/6000ASDECIMAL(8,3))>CAST(p.casegrossweight/1000ASDECIMAL(8,3)),CAST((p.‘caseofhigh‘∗p.‘caseoflength‘∗p.‘caseofwidht‘)/6000ASDECIMAL(8,3)),CAST(p.casegrossweight/1000ASDECIMAL(8,3)))ASaFROMinventoryparttabpWHEREp.skuno=rowskunoANDp.contract=′AM′)ASb)WHEREt.skuno=rowskunoANDt.contract=′AM′;/∗终止循环的条件∗/UNTILi>=cntENDREPEAT;/∗关闭游标∗/CLOSEgetcategory;/∗结束∗/END

DELIMITER ;
如果用：
UPDATE inventory_part_tab t
SET t.case_volume_weight=
(SELECT IF (CAST((p.case_of_high * p.case_of_length * p.case_of_widht)/6000 AS DECIMAL(8,3)) > CAST(p.case_gross_weight/1000 AS DECIMAL(8,3)),
CAST((p.case_of_high * p.case_of_length * p.case_of_widht)/6000 AS DECIMAL(8,3)) , CAST(p.case_gross_weight/1000 AS DECIMAL(8,3)) ) AS a
FROM inventory_part_tab p WHERE p.sku_no=row_sku_no AND p.contract=’AM’) AS b)
WHERE t.sku_no=row_sku_no AND t.contract=’AM’;
mysql会报错：You can’t specify target table for update in FROM clause
错误的意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)。
例如下面这个sql：
delete from tbl where id in
(
select max(id) from tbl a where EXISTS
(
select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1
)
group by tac
)

改写成下面就行了：
delete from tbl where id in
(
select a.id from
(
select max(id) id from tbl a where EXISTS
(
select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1
)
group by tac
) a
)

也就是说将select出的结果再通过中间表select一遍，这样就规避了错误。注意，这个问题只出现于mysql，mssql和oracle不会出现此问题。

此外，如果要给一个表插入一个字段，然后通过一些列的计算再给这个字段赋值有以下流程：
1.在原表格新增一个字段
2.写一个存储过程给这个表赋值（遍历的存储过程）