1.
Drop PROCEDURE IF EXISTS Pro_InsertShopStorage; CREATE PROCEDURE Pro_InsertShopStorage() BEGIN DECLARE cnt int default 0; declare i int default 0; drop table if exists tmp; create TEMPORARY table tmp like waretype; select count(1) as total from (select count(1) from waretype where 1 GROUP BY TypeID) T into cnt; WHILE i < cnt DO set @stmt = concat("select TypeID from waretype GROUP BY TypeID order by TypeID asc LIMIT ", i, ",1 into @t_typeid"); PREPARE s1 from @stmt; EXECUTE s1; DEALLOCATE PREPARE s1; set @stmt = NULL; set @stmt2 = concat("insert into tmp select Id,TypeID, TypeName from waretype where TypeID = '", @t_typeid, "' order by TypeID asc"); prepare s2 from @stmt2; EXECUTE s2; DEALLOCATE PREPARE s2; set @stmt2 = NULL; set i = i + 1; END WHILE; select * from tmp; END call Pro_InsertShopStorage();
2.
Drop PROCEDURE IF EXISTS Pro_InsertShopStorage; CREATE PROCEDURE Pro_InsertShopStorage() BEGIN DECLARE cnt_shop int default 0; declare cnt_type int default 0; declare cnt_model int default 0; declare i_shop int default 0; declare i_type int default 0; declare i_model int default 0; select count(1) as total from (select count(1) from Shop where 1 GROUP BY ShopID) T into cnt_shop; select count(1) as total from (select count(1) from WareType where 1 group by TypeID) T into cnt_type; WHILE i_shop < cnt_shop DO set @stmt = concat("select ShopID from Shop GROUP BY ShopID order by ShopID asc LIMIT ", i_shop, ",1 into @t_shopid"); PREPARE s from @stmt; EXECUTE s; DEALLOCATE PREPARE s; set @stmt = NULL; while i_type < cnt_type DO set @stmt1 = concat("select TypeID from WareType group by TypeID order by TypeID asc limit ", i_type, ",1 into @t_typeid"); prepare s1 from @stmt1; execute s1; DEALLOCATE prepare s1; set @stmt1 = NULL; -- 注释，-- 后面有空格 -- if语句要有end if; /* if @t_shopid = '001' THEN select @t_typeid; end if; */ -- 用户变量赋值给变量的方法一 /* set @stmt2 = concat("select count(1) from WareModel where TypeID = '", @t_typeid, "' into @t_model"); prepare s2 from @stmt2; execute s2; DEALLOCATE PREPARE s2; set @stmt2 = NULL; set cnt_model = @t_model; */ -- 用户变量赋值给变量的方法二 /* select count(1) from WareModel where TypeID = @t_typeid into @t_model; set cnt_model = @t_model; */ -- 用户变量赋值给变量的方法三 select count(1) from waremodel where TypeID = @t_typeid into cnt_model; while i_model < cnt_model DO set @stmt3 = concat("select ModelID from WareModel where TypeID = '", @t_typeid, "' order by ModelID asc limit ", i_model, ", 1 into @t_modelid"); prepare s3 from @stmt3; execute s3; deallocate prepare s3; set @stmt3 = NULL; set @stmt4 = concat("insert into shopstorage (ShopID, TypeID, ModelID, StorNumber) values ('", @t_shopid, "','", @t_typeid, "','", @t_modelid, "', '", cast(rand() as char(32)), "')"); -- 最后记得加")"，否则提示near ''错误 prepare s4 from @stmt4; EXECUTE s4; DEALLOCATE PREPARE s4; set @stmt4 = NULL; set i_model = i_model + 1; end while; set i_type = i_type + 1; set cnt_model = 0; set i_model = 0; end while; set i_shop = i_shop + 1; set i_type = 0;-- 千万千万记得，i_type需要重置(:-…… END WHILE; END call Pro_InsertShopStorage(); truncate table shopstorage select * from shopstorage
3.
Drop procedure if exists Pro_Disp; create procedure Pro_Disp(in p_TypeID varchar(32), in p_ModelID varchar(32), in p_DispNum int, in p_DispDate varchar(32), in p_DispShop varchar(32)) BEGIN declare sum int default 0; declare gap int default 0; declare every_id int default 0; declare every_num int default 0; declare record varchar(64); set record = '';-- 不能置为NULL，否则后面concat就加不上内容 start TRANSACTION; while_label:WHILE TRUE DO set @stmt = concat("select Id, RemainNum from WareStock where TypeID = '", p_TypeID, "' and ModelID = '", p_ModelID, "' and DispState <> 'AllDisp' order by StockDate, Id asc limit 0, 1 into @t_Id, @t_num"); prepare s from @stmt; execute s; DEALLOCATE prepare s; set @stmt = NULL; set every_id = @t_Id; set every_num = @t_num; IF (sum + every_num) = p_DispNum THEN UPDATE WareStock set RemainNum = 0, DispState = 'AllDisp' where Id = every_id; set record = concat(record, cast(every_id as char(8)), "|", cast(every_num as char(8)), ","); leave while_label; elseif (sum + every_num > p_DispNum) THEN -- elseif 不是 else if set gap = p_DispNum - sum; update WareStock set RemainNum = every_num - gap, DispState = 'SomeDisp' where Id = every_id;-- 可以直接用运算 set record = concat(record, cast(every_id as char(8)), "|", cast(gap as char(8)), ","); leave while_label; else update WareStock set RemainNum = 0, DispState = 'AllDisp' where Id = every_id; set record = concat(record, cast(every_id as char(8)), "|", cast(every_num as char(8)), ","); set sum = sum + every_num; end if; end WHILE; set @stmt2 = concat("insert into waredispatch (TypeID, ModelID, DispNumber, ShopID, DispDate, SaleState, RemainNum, StockRecord) values ('", p_TypeID, "','", p_ModelID, "',", cast(p_DispNum as char(8)), ",'", p_DispShop, "','", p_DispDate, "','NoSale',", cast(p_DispNum as char(8)), ",'", record, "')"); prepare s2 from @stmt2; execute s2; deallocate prepare s2; set @stmt2 = NULL; update ShopStorage set StorNumber = StorNumber - p_DispNum where ShopID = '001' and TypeID = p_TypeID and ModelID = p_ModelID; insert into shopstorage (ShopID, TypeID, ModelID, StorNumber) values (p_DispShop, p_TypeID, p_ModelID, cast(p_DispNum as char(8))) on duplicate key update StorNumber = StorNumber + p_DispNum; COMMIT; end -- call Pro_Disp("001", "001001", 1, "2011-04-19", "002");
4.
drop procedure if exists Pro_RecoverDisp; create procedure Pro_RecoverDisp(in p_Record varchar(128), in p_Cnt int, in p_DelID int) BEGIN declare i int; declare rec int default 0; declare ori int default 0; declare stock int default 0; declare subRemain varchar(128); start TRANSACTION; set i = 0; set subRemain = p_Record; while i < p_Cnt DO set @every_sub = substring_index(SubRemain, ',', 1); set SubRemain = substring_index(SubRemain, ',', i - p_Cnt); set @t_id = substring_index(@every_sub, '|', 1); set @t_rec = substring_index(@every_sub, '|', -1); set rec = @t_rec; select RemainNum from warestock where Id = @t_id into @t_ori; select StockNumber from warestock where Id = @t_id into @t_stock; set ori = @t_ori; set stock = @t_stock; if ori + rec < stock THEN update warestock set RemainNum = ori + rec, DispState = 'SomeDisp' where Id = @t_id; elseif ori + rec = stock THEN update warestock set RemainNum = ori + rec, DispState = 'NoDisp' where Id = @t_id; end if; set i = i + 1; set rec = 0; set ori = 0; set stock = 0; end while; delete from waredispatch where Id = p_DelID; commit; END -- call Pro_RecoverDisp("4|4,1|3,2|1,", 3, 16);
5.
Drop procedure if exists Pro_Sale; create procedure Pro_Sale(in p_TypeID varchar(32), in p_ModelID varchar(32), in p_ShopID varchar(32), in p_SaleNum int, in p_SaleDate varchar(32)) BEGIN declare sum int default 0; declare gap int default 0; declare every_id int default 0; declare every_num int default 0; declare record varchar(64); set record = '';-- 不能置为NULL，否则后面concat就加不上内容 start TRANSACTION; while_label:WHILE TRUE DO set @stmt = concat("select Id, RemainNum from WareDispatch where TypeID = '", p_TypeID, "' and ModelID = '", p_ModelID, "' and SaleState <> 'AllSale' order by DispDate, Id asc limit 0, 1 into @t_Id, @t_num"); prepare s from @stmt; execute s; DEALLOCATE prepare s; set @stmt = NULL; set every_id = @t_Id; set every_num = @t_num; IF (sum + every_num) = p_SaleNum THEN UPDATE waredispatch set RemainNum = 0, SaleState = 'AllSale' where Id = every_id; set record = concat(record, cast(every_id as char(8)), "|", cast(every_num as char(8)), ","); leave while_label; elseif (sum + every_num > p_SaleNum) THEN -- elseif 不是 else if set gap = p_SaleNum - sum; update waredispatch set RemainNum = every_num - gap, SaleState = 'SomeSale' where Id = every_id;-- 可以直接用运算 set record = concat(record, cast(every_id as char(8)), "|", cast(gap as char(8)), ","); leave while_label; else update waredispatch set RemainNum = 0, DispState = 'AllSale' where Id = every_id; set record = concat(record, cast(every_id as char(8)), "|", cast(every_num as char(8)), ","); set sum = sum + every_num; end if; end WHILE; set @stmt2 = concat("insert into WareSale (TypeID, ModelID, ShopID, SaleNumber, SaleDate, DispRecord) values ('", p_TypeID, "','", p_ModelID, "','", p_ShopID, "',", cast(p_SaleNum as char(8)), ",'", p_SaleDate, "','", record, "')"); prepare s2 from @stmt2; execute s2; deallocate prepare s2; set @stmt2 = NULL; update ShopStorage set StorNumber = StorNumber - p_SaleNum where ShopID = p_ShopID and TypeID = p_TypeID and ModelID = p_ModelID; COMMIT; end -- call Pro_Sale("001", "001001", "002", 5, "2011-04-19");
6.
drop procedure if exists Pro_RecoverSale; create procedure Pro_RecoverSale(in p_Record varchar(128), in p_Cnt int, in p_DelID int) BEGIN declare i int; declare rec int default 0; declare ori int default 0; declare disp int default 0; declare subRemain varchar(128); start TRANSACTION; set i = 0; set subRemain = p_Record; while i < p_Cnt DO set @every_sub = substring_index(SubRemain, ',', 1); set SubRemain = substring_index(SubRemain, ',', i - p_Cnt); set @t_id = substring_index(@every_sub, '|', 1); set @t_rec = substring_index(@every_sub, '|', -1); set rec = @t_rec; select RemainNum from waredispatch where Id = @t_id into @t_ori; select DispNumber from waredispatch where Id = @t_id into @t_disp; set ori = @t_ori; set disp = @t_disp; if ori + rec < disp THEN update waredispatch set RemainNum = ori + rec, SaleState = 'SomeSale' where Id = @t_id; elseif ori + rec = disp THEN update waredispatch set RemainNum = ori + rec, SaleState = 'NoSale' where Id = @t_id; end if; set i = i + 1; set rec = 0; set ori = 0; set disp = 0; end while; select TypeID, ModelID, ShopID, SaleNumber from waresale where Id = p_DelID into @t_type, @t_model, @t_shop, @t_num; update ShopStorage set StorNumber = StorNumber + @t_num where ShopID = @t_shop and TypeID = @t_type and ModelID = @t_model; delete from waresale where Id = p_DelID; commit; END -- call Pro_RecoverSale("1|5,", 1, 1);
7.
DROP PROCEDURE IF EXISTS Pro_InnerNetUser; CREATE PROCEDURE Pro_InnerNetUser() BEGIN DECLARE i INT DEFAULT 0; DECLARE cntPc INT DEFAULT 0; DECLARE j INT DEFAULT 0; DROP TABLE IF EXISTS tmpTable; CREATE TEMPORARY TABLE tmpTable(pcName char(64)); INSERT INTO tmpTable(pcName) SELECT pcName FROM T_SC_UserManager; SELECT COUNT(*) FROM tmpTable INTO cntPc; WHILE i < 100 DO WHILE j < cntPc DO SET @stmt = concat("SELECT pcName FROM tmpTable LIMIT ", j, ",1 into @t_pc"); PREPARE s from @stmt; EXECUTE s; DEALLOCATE PREPARE s; SET @stmt = NULL; SET @stmt1 = concat("INSERT INTO T_Task_NWAQY_User (vTaskName, pcName, IP1, iState, bZiDingYi, isOnline, ndisFreshTime, bStatus, config) VALUES ('", i, "','", @t_pc, "', INET_NTOA(INET_ATON('192.168.1.1') + ", j, "), 1, 0, IF(", j, " > 300, 0, 1), 60, IF(", j, " > 350, 0, 1), CASE WHEN ", j, " >= 0 AND ", j, " < 100 THEN 0 WHEN ", j, " >= 100 AND ", j, " < 200 THEN 1 ELSE 2 END)"); PREPARE s1 FROM @stmt1; EXECUTE s1; DEALLOCATE PREPARE s1; SET @stmt1 = NULL; SET j = j + 1; END WHILE; SET i = i + 1; SET j = 0; END WHILE; SET i = 0; SET j = 0; WHILE i < 100 DO WHILE j < cntPc DO SET @stmt = concat("SELECT pcName FROM tmpTable LIMIT ", j, ",1 into @t_pc"); PREPARE s from @stmt; EXECUTE s; DEALLOCATE PREPARE s; SET @stmt = NULL; SET @stmt1 = concat("INSERT INTO T_Task_NWAQY_User (vTaskName, pcName, IP1, iState, bZiDingYi, isOnline, ndisFreshTime, bStatus, config) VALUES ('", i, "','", CONCAT(@t_pc, "_zdy"), "', INET_NTOA(INET_ATON('192.168.1.1') + ", j, "), 1, 1, IF(", j, " > 300, 0, 1), 60, IF(", j, " > 350, 0, 1), CASE WHEN ", j, " >= 0 AND ", j, " < 100 THEN 0 WHEN ", j, " >= 100 AND ", j, " < 200 THEN 1 ELSE 2 END)"); PREPARE s1 FROM @stmt1; EXECUTE s1; DEALLOCATE PREPARE s1; SET @stmt1 = NULL; SET j = j + 1; END WHILE; SET i = i + 1; SET j = 0; END WHILE; END; CALL Pro_InnerNetUser();
