存储过程学习笔记1

修改mysql的结束符号,避免遇到；停止
DELIMITER
[DEFINER = { user | CURRENT_USER }]
CREATE PROCEDURE zswms.UpdateCaseVolumeWeight()
BEGIN
/声明变量/
DECLARE row_sku_no VARCHAR(90);
DECLARE i INT DEFAULT 0;
DECLARE cnt INT DEFAULT 0;
/* 定义游标*/
DECLARE getcategory CURSOR FOR SELECT sku_no FROM inventory_part_tab;
/给变量赋值/
SELECT COUNT(*) INTO cnt FROM inventory_part_tab WHERE contract=’AM’;
/打开游标/
OPEN getcategory;
/开始循环/
REPEAT
/设置循环条件/
SET i:=i+1;
/将游标的一行值赋值给变量 row_sku_no/
FETCH getcategory INTO row_sku_no;
/显示变量值/
SELECT row_sku_no,cnt;
/逐行Update/
UPDATE inventory_part_tab t
SET t.case_volume_weight=
(SELECT b.a FROM
(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’;
/终止循环的条件/
UNTIL i>=cnt END REPEAT;
/关闭游标/
CLOSE getcategory;
/结束/
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.写一个存储过程给这个表赋值（遍历的存储过程）