ToB企服应用市场:ToB评测及商务社交产业平台

标题: 【达梦数据库】存储过程统计模式下表信息-SQL改写 [打印本页]
作者: tsx81429    时间: 2024-9-30 18:06
标题: 【达梦数据库】存储过程统计模式下表信息-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企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4