【达梦数据库】存储过程统计模式下表信息-SQL改写

配景

在一次Oracle迁移Dm的过程中，源库&目的库大小写均敏感，实行客户提供的SQL脚本的过程中发现，表ip_address被系统默认成了表IP_ADDRESS。
颠末分析，客户提供的SQL没有使用双引号，来确保Oracle和Dm数据库按照指定的大小写来辨认表名，因此，做以下改写。
源SQL

需要用目的用户去实行

1. declare
2.     num number;
3. begin
4.     select count(1)
5.       into
6.            num
7.       from user_tables
8.      where table_name = 'TMP_GET_ACTURAL_TABLE_COUNT';
9.      
10.     if num > 0 then
11.         execute immediate 'drop table TMP_GET_ACTURAL_TABLE_COUNT';
12.     end if;
13. end;
14. /
15. create table TMP_GET_ACTURAL_TABLE_COUNT
16.              (
17.                           table_name varchar(50),
18.                           table_cnt  int
19.              );
21. /
22. CREATE OR REPLACE
23. PROCEDURE "GET_ACTURAL_TABLE_COUNT"(isrun integer) AUTHID CURRENT_USER
24. IS
25.     sqlstr varchar2(4000);
26. begin
27.     for cursor_sql in
28.     ( select 'insert into TMP_GET_ACTURAL_TABLE_COUNT(table_name,table_cnt) select '''||table_name||''' as table_name,count(1) as table_cnt from '||table_name as sqlstr1
29.        from user_tables
30.       where table_name not like 'SREF_CON_%'
31.     )
32.     loop
33.         execute immediate (cursor_sql.sqlstr1);
34.         commit;
35.     end loop;
36. end;
37. /
38. truncate table TMP_GET_ACTURAL_TABLE_COUNT;
40. /
41. call GET_ACTURAL_TABLE_COUNT(1);
42. /
43. commit;
44. /
45.   select *from TMP_GET_ACTURAL_TABLE_COUNT order by upper(table_name);
47. /

复制代码

改写后SQL

需要用目的用户去实行

1. DECLARE
2.     num NUMBER;
3. BEGIN
4.     SELECT COUNT(1)
5.       INTO
6.            num
7.       FROM user_tables
8.      WHERE table_name = 'TMP_GET_ACTURAL_TABLE_COUNT';
9.    
10.     IF num > 0 THEN
11.         EXECUTE IMMEDIATE 'DROP TABLE TMP_GET_ACTURAL_TABLE_COUNT';
12.     END IF;
13.     EXECUTE IMMEDIATE 'CREATE TABLE TMP_GET_ACTURAL_TABLE_COUNT(table_name VARCHAR2(50), table_cnt INT)';
14. END;
15. /
16. -- 创建存储过程
17. CREATE OR REPLACE
18. PROCEDURE "GET_ACTURAL_TABLE_COUNT"(isrun INTEGER) AUTHID CURRENT_USER
19. IS
20.     sqlstr VARCHAR2(4000);
21.     v_table_name USER_TABLES.TABLE_NAME%TYPE;
22. BEGIN
23.     -- 遍历所有用户表（排除以'SREF_CON_'开头的表）
24.     FOR rec IN (SELECT table_name FROM user_tables WHERE table_name NOT LIKE 'SREF_CON_%')
25.     LOOP
26.         -- 构建并执行SQL语句
27.         sqlstr := 'INSERT INTO TMP_GET_ACTURAL_TABLE_COUNT(table_name, table_cnt) SELECT ''' || rec.table_name || ''' AS table_name, COUNT(1) AS table_cnt FROM "' || rec.table_name || '"';
28.         EXECUTE IMMEDIATE sqlstr;
29.     END LOOP;
30.     -- 提交事务（在循环外提交）
31.     COMMIT;
32. END;
33. /
34. -- 调用存储过程
35. --BEGIN
36. --    GET_ACTURAL_TABLE_COUNT(1);
37. --END;
38. --/
39. -- 截断临时表（如果需要重新填充）
40. TRUNCATE TABLE TMP_GET_ACTURAL_TABLE_COUNT;
42. /
43. -- 调用存储过程（如果需要）
44. BEGIN
45.     GET_ACTURAL_TABLE_COUNT(1);
46. END;
47. /
48. -- 查询临时表
49.   SELECT *
50.     FROM TMP_GET_ACTURAL_TABLE_COUNT
51. ORDER BY UPPER(table_name);
53. /

复制代码

通用SQL-（暂时未乐成）

思路：使用DBA用户可以实行任何模式下的表信息的统计

1. -- 删除并重新创建临时表
2. DECLARE  
3.     num NUMBER;  
4.     target_schema VARCHAR2(50) := 'ECOLOGY_TARGET';  --填写目标模式，保证大小写正确
5. BEGIN  
6.     SELECT COUNT(1)   
7.     INTO num   
8.     FROM dba_tables   
9.     WHERE table_name = 'TMP_GET_ACTURAL_TABLE_COUNT'  
10.     AND owner = target_schema;  
11.       
12.     IF num > 0 THEN  
13.         EXECUTE IMMEDIATE 'DROP TABLE ' || target_schema || '.TMP_GET_ACTURAL_TABLE_COUNT';  
14.     END IF;  
15.     EXECUTE IMMEDIATE 'CREATE TABLE ' || target_schema || '.TMP_GET_ACTURAL_TABLE_COUNT(table_name VARCHAR2(50), table_cnt INT)';  
16. END;  
17. /
19. CREATE OR REPLACE PROCEDURE GET_ACTURAL_TABLE_COUNT(isrun INTEGER)
20. IS  
21.     sqlstr VARCHAR2(4000);  
22.     v_table_name USER_TABLES.TABLE_NAME%TYPE;  
23.     target_schema VARCHAR2(50) := 'ECOLOGY_TARGET';  --填写目标模式，保证大小写正确
24. BEGIN  
25.     -- 遍历用户表（排除以'SREF_CON_'开头的表）  
26.     FOR rec IN (SELECT table_name FROM dba_tables WHERE owner = 'target_schema' AND table_name NOT LIKE 'SREF_CON_%')   
27.     LOOP  
28.         -- 构建并执行SQL语句  
29.         sqlstr := 'INSERT INTO ' || target_schema || '.TMP_GET_ACTURAL_TABLE_COUNT(table_name, table_cnt) SELECT "' || rec.table_name || '" AS table_name, COUNT(1) AS table_cnt FROM "' || rec.table_name || '"';  
30.         EXECUTE IMMEDIATE sqlstr;  
31.     END LOOP;  
32.     -- 提交事务（在循环外提交）  
33.     COMMIT;  
34. END;  
35. /
37. -- 截断临时表（如果需要重新填充）
38. TRUNCATE TABLE TMP_GET_ACTURAL_TABLE_COUNT;
40. /
41. -- 调用存储过程（如果需要）
42. BEGIN
43.     GET_ACTURAL_TABLE_COUNT(1);
44. END;
45. /
46. -- 查询临时表
47.   SELECT *
48.     FROM TMP_GET_ACTURAL_TABLE_COUNT
49. ORDER BY UPPER(table_name);
51. /

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。