“`
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