MYSQL將表名稱修改成大寫的存儲過程

 1 #call uppercase('庫名')
 2 DROP PROCEDURE IF EXISTS uppercase; 
 3 CREATE PROCEDURE uppercase(IN dbname VARCHAR(200))   
 4 BEGIN  
 5    
 6 DECLARE done INT DEFAULT 0;   
 7    
 8 DECLARE oldname VARCHAR(200);   
 9    
10 DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema = dbname;   
11    
12 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;   
13    
14 OPEN cur;    
15    
16 REPEAT   
17    
18 FETCH cur INTO oldname;   
19    
20 SET @newname = UPPER(oldname);   
21 
22 SET @isNotSame = @newname <> BINARY oldname;   
23    
24 IF NOT done && @isNotSame THEN  
25    
26 SET @SQL = CONCAT('rename table `',oldname,'` to `', LOWER(@newname), '_tmp` ');   
27    
28 PREPARE tmpstmt FROM @SQL;   
29    
30 EXECUTE tmpstmt;   
31 
32 
33 SET @SQL = CONCAT('rename table `',LOWER(@newname),'_tmp` to `',@newname, '`');   
34    
35 PREPARE tmpstmt FROM @SQL;   
36    
37 EXECUTE tmpstmt; 
38    
39 DEALLOCATE PREPARE tmpstmt;   
40    
41 END IF;   
42    
43 UNTIL done END REPEAT;   
44    
45 CLOSE cur;   
46    
47 END    

3. 執行一下語句

call uppercase('庫名');