MySQL 存储过程例子

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();