· 8 years ago · Feb 05, 2018, 06:24 PM
1use cvtv5wrkpda;
2
3
4
5
6 -- DEBUG
7 -- select * from TableInformation;
8
9DELIMITER $$
10
11
12
13DROP PROCEDURE IF EXISTS proc_loop_test$$
14CREATE PROCEDURE proc_loop_test()
15BEGIN
16
17-- ----------------------------------------------------------------------
18-- STEP 3 -- VARIABLE
19-- ----------------------------------------------------------------------
20declare Schema10 , Name10 , IdentityColumn10 VARCHAR(128);
21
22declare SelectRt varchar(128);
23 declare ParmDefinitionRt varchar(500);
24 declare ResultRt bigint;
25
26DECLARE done INT DEFAULT FALSE;
27declare TablesCursor cursor for
28 select Schema1, Name1, IdentityColumn1
29 from TableInformation;
30DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
31
32-- ----------------------------------------------------------------------
33-- STEP 1
34-- ----------------------------------------------------------------------
35drop table if exists TablesToImport
36;
37
38Create table TablesToImport
39(
40 Name varchar(128)
41)
42;
43
44insert into TablesToImport (Name) values
45('ao'),
46('campagnes'),
47('contacts'),
48('cv'),
49('cvao'),
50('documents'),
51('documentsao'),
52('evenements'),
53('finance'),
54('lexique'),
55('liens'),
56('messages'),
57('modeles'),
58('pjmails'),
59('processus'),
60('referentiel'),
61('societes'),
62('utilisateurs')
63;
64
65-- ----------------------------------------------------------------------
66-- STEP 2
67-- ----------------------------------------------------------------------
68
69drop table if exists TableInformation
70;
71
72Create table TableInformation
73(
74 Schema1 varchar(128),
75 Name1 varchar(128),
76 IdentityColumn1 varchar(128),
77 MaxIdentity int
78)
79;
80
81truncate table tableinformation;
82
83insert into TableInformation
84(
85 Schema1,
86 Name1,
87 IdentityColumn1
88) select
89 TABLE_SCHEMA,
90 TABLE_NAME,
91 COLUMN_NAME
92 from INFORMATION_SCHEMA.COLUMNS
93 where EXTRA='auto_increment'
94 and TABLE_SCHEMA='cvtrackerv5'
95 and TABLE_NAME in
96 (
97 select Name
98 from TablesToImport
99 )
100 ;
101
102-- ----------------------------------------------------------------------
103-- STEP 3
104-- ----------------------------------------------------------------------
105 -- select * from TableInformation;
106
107
108open TablesCursor ;
109
110
111read_loop: LOOP
112
113 fetch TablesCursor into Schema10, Name10, IdentityColumn10;
114
115
116 IF done THEN
117 LEAVE read_loop;
118 END IF;
119
120 -- GENERATE DYNAMIC SQL STATEMENT
121 set @spl_prepare = concat('select @ResultRt := max(?) from ' , Schema10 , '.' , Name10);
122 select @spl_prepare ;
123
124 PREPARE SelectRt FROM @spl_prepare;
125 -- max(' + @IdentityColumn + ') from ' + @Schema + '.' + @Name
126 -- ParmDefinitionRt = N'@Result bigint output';
127
128 -- EXECUTE DYNAMIC SQL STATEMENT
129 -- EXECUTE SelectRt USING @IdentityColumn1 , @Schema1, @Name1;
130 EXECUTE SelectRt USING @IdentityColumn10 ;
131
132 -- UPDATE MaxIdentity for Identity Column
133 update TableInformation
134 set MaxIdentity = @ResultRt
135 where Schema1 = @Schema10
136 and Name1 = @Name10
137 and IdentityColumn1 = @IdentityColumn10;
138
139
140END LOOP;
141
142close TablesCursor;
143
144
145-- DISPLAY TableInformation
146 select * from TableInformation;
147
148-- DISPLAY MaxIdentity
149 select MAX(MaxIdentity) from TableInformation;
150
151
152END$$
153
154DELIMITER ;
155
156CALL proc_loop_test