自己用的一个mysql存储过程

“`
BEGIN
DECLARE main_billno VARCHAR(45);
DECLARE main_id INT DEFAULT 0;
DECLARE main_fid VARCHAR(45);
DECLARE done INT DEFAULT 0;
DECLARE rs CURSOR FOR
SELECT id,fbillno FROM stockbill;
DECLARE rs2 CURSOR FOR
SELECT DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=’inventory’ AND table_name=’entry’ AND COLUMN_NAME=’fbillno’;
DECLARE rs_entry CURSOR FOR
SELECT fid,fwarehouse,fwarehouse2,fdeptid,fpricetype FROM entry;
DECLARE rs_icitem CURSOR FOR
SELECT id,fid FROM icitem;
DECLARE rs_warehouse CURSOR FOR
SELECT id,fid FROM warehouse;
DECLARE rs_dept CURSOR FOR
SELECT id,fid FROM department;
DECLARE rs_pricetype CURSOR FOR
SELECT id,fid FROM pricetype;
DECLARE rs_measureunits CURSOR FOR
SELECT id,fid FROM measureunits;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1;

set done=0;
open rs2;
FETCH rs2 INTO main_fid;
if main_fid=’varchar’ then
ALTER TABLE inventory.entry ADD COLUMN tempno INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER fpricetype,
ADD COLUMN tempfid INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER tempno,
ADD COLUMN tempfwarehouse INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER tempfid,
ADD COLUMN tempfdeptid INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER tempfwarehouse,
ADD COLUMN tempfwarehouse2 INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER tempfdeptid,
ADD COLUMN tempfpricetype INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER tempfwarehouse2;

    set done=0;

OPEN rs;
FETCH NEXT FROM rs INTO main_id,main_billno;
REPEAT
update entry set tempno=main_id where fbillno=main_billno;
FETCH NEXT FROM rs INTO main_id,main_billno;
UNTIL done END REPEAT;
CLOSE rs;

/end process stockbill/
set done=0;
OPEN rs_icitem;
FETCH NEXT FROM rs_icitem INTO main_id,main_fid;
REPEAT
update entry set tempfid=main_id where fid=main_fid;
FETCH NEXT FROM rs_icitem INTO main_id,main_fid;
UNTIL done END REPEAT;
close rs_icitem;
/end process entry/
set done=0;
OPEN rs_warehouse;
FETCH NEXT FROM rs_warehouse INTO main_id,main_fid;
REPEAT
update entry set tempfwarehouse=main_id where fwarehouse=main_fid;
update entry set tempfwarehouse2=main_id where fwarehouse2=main_fid;
FETCH NEXT FROM rs_warehouse INTO main_id,main_fid;
UNTIL done END REPEAT;
close rs_warehouse;
/end process entry/
set done=0;
OPEN rs_dept;
FETCH NEXT FROM rs_dept INTO main_id,main_fid;
REPEAT
update entry set tempfdeptid=main_id where fdeptid=main_fid;
FETCH NEXT FROM rs_dept INTO main_id,main_fid;
UNTIL done END REPEAT;
close rs_dept;
/end process entry/
set done=0;
OPEN rs_pricetype;
FETCH NEXT FROM rs_pricetype INTO main_id,main_fid;
REPEAT
update entry set tempfpricetype=main_id where fpricetype=main_fid;
FETCH NEXT FROM rs_pricetype INTO main_id,main_fid;
UNTIL done END REPEAT;
close rs_pricetype;
/end process entry/
ALTER TABLE inventory.entry DROP COLUMN fbillno,
DROP COLUMN fid,
DROP COLUMN fwarehouse,
DROP COLUMN fdeptid,
DROP COLUMN fwarehouse2,
DROP COLUMN fpricetype,
DROP INDEX Index_2,
ADD INDEX Index_2 USING BTREE(remark, fdate, fname),
DROP INDEX Index_4,
ADD INDEX Index_4 USING BTREE(fyear, fperiod);

ALTER TABLE inventory.entry ADD COLUMN fbillno INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER id,
ADD COLUMN fid INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER fbillno,
ADD COLUMN fdeptid INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER fid,
ADD COLUMN fwarehouse INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER fdeptid,
ADD COLUMN fwarehouse2 INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER fwarehouse,
ADD COLUMN fpricetype INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER fwarehouse2;

update entry set fbillno=tempno,fid=tempfid,fdeptid=tempfdeptid,fwarehouse=tempfwarehouse,fwarehouse2=tempfwarehouse2,fpricetype=tempfpricetype;

ALTER TABLE inventory.entry DROP COLUMN tempno,
DROP COLUMN tempfid,
DROP COLUMN tempfwarehouse,
DROP COLUMN tempfdeptid,
DROP COLUMN tempfwarehouse2,
DROP COLUMN tempfpricetype;

end if;
close rs2;
END