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

背景

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

源SQL

需要用目的用户去执行

declare 
    num number;
begin
    select count(1) 
      into 
           num 
      from user_tables 
     where table_name = 'TMP_GET_ACTURAL_TABLE_COUNT';
     
    if num > 0 then
        execute immediate 'drop table TMP_GET_ACTURAL_TABLE_COUNT';
    end if;
end;
/
create table TMP_GET_ACTURAL_TABLE_COUNT 
             ( 
                          table_name varchar(50), 
                          table_cnt  int 
             );
 
/
CREATE OR REPLACE 
PROCEDURE "GET_ACTURAL_TABLE_COUNT"(isrun integer) AUTHID CURRENT_USER 
IS
    sqlstr varchar2(4000);
begin
    for cursor_sql in 
    ( 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 
       from user_tables 
      where table_name not like 'SREF_CON_%'
    ) 
    loop
        execute immediate (cursor_sql.sqlstr1);
        commit;
    end loop;
end;
/
truncate table TMP_GET_ACTURAL_TABLE_COUNT;
 
/
call GET_ACTURAL_TABLE_COUNT(1);
/
commit;
/
  select *from TMP_GET_ACTURAL_TABLE_COUNT order by upper(table_name);
 
/

改写后SQL

需要用目的用户去执行

DECLARE
    num NUMBER;
BEGIN
    SELECT COUNT(1)
      INTO
           num
      FROM user_tables
     WHERE table_name = 'TMP_GET_ACTURAL_TABLE_COUNT';
    
    IF num > 0 THEN
        EXECUTE IMMEDIATE 'DROP TABLE TMP_GET_ACTURAL_TABLE_COUNT';
    END IF;
    EXECUTE IMMEDIATE 'CREATE TABLE TMP_GET_ACTURAL_TABLE_COUNT(table_name VARCHAR2(50), table_cnt INT)';
END;
/
-- 创建存储过程
CREATE OR REPLACE
PROCEDURE "GET_ACTURAL_TABLE_COUNT"(isrun INTEGER) AUTHID CURRENT_USER
IS
    sqlstr VARCHAR2(4000);
    v_table_name USER_TABLES.TABLE_NAME%TYPE;
BEGIN
    -- 遍历所有用户表（排除以'SREF_CON_'开头的表）
    FOR rec IN (SELECT table_name FROM user_tables WHERE table_name NOT LIKE 'SREF_CON_%')
    LOOP
        -- 构建并执行SQL语句
        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 || '"';
        EXECUTE IMMEDIATE sqlstr;
    END LOOP;
    -- 提交事务（在循环外提交）
    COMMIT;
END;
/
-- 调用存储过程
--BEGIN
--    GET_ACTURAL_TABLE_COUNT(1);
--END;
--/
-- 截断临时表（如果需要重新填充）
TRUNCATE TABLE TMP_GET_ACTURAL_TABLE_COUNT;

/
-- 调用存储过程（如果需要）
BEGIN
    GET_ACTURAL_TABLE_COUNT(1);
END;
/
-- 查询临时表
  SELECT * 
    FROM TMP_GET_ACTURAL_TABLE_COUNT 
ORDER BY UPPER(table_name);

/

通用SQL-（暂时未成功）

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

-- 删除并重新创建临时表
DECLARE  
    num NUMBER;  
    target_schema VARCHAR2(50) := 'ECOLOGY_TARGET';  --填写目标模式，保证大小写正确
BEGIN  
    SELECT COUNT(1)   
    INTO num   
    FROM dba_tables   
    WHERE table_name = 'TMP_GET_ACTURAL_TABLE_COUNT'  
    AND owner = target_schema;  
      
    IF num > 0 THEN  
        EXECUTE IMMEDIATE 'DROP TABLE ' || target_schema || '.TMP_GET_ACTURAL_TABLE_COUNT';  
    END IF;  
    EXECUTE IMMEDIATE 'CREATE TABLE ' || target_schema || '.TMP_GET_ACTURAL_TABLE_COUNT(table_name VARCHAR2(50), table_cnt INT)';  
END;  
/

CREATE OR REPLACE PROCEDURE GET_ACTURAL_TABLE_COUNT(isrun INTEGER) 
IS  
    sqlstr VARCHAR2(4000);  
    v_table_name USER_TABLES.TABLE_NAME%TYPE;  
    target_schema VARCHAR2(50) := 'ECOLOGY_TARGET';  --填写目标模式，保证大小写正确
BEGIN  
    -- 遍历用户表（排除以'SREF_CON_'开头的表）  
    FOR rec IN (SELECT table_name FROM dba_tables WHERE owner = 'target_schema' AND table_name NOT LIKE 'SREF_CON_%')   
    LOOP  
        -- 构建并执行SQL语句  
        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 || '"';  
        EXECUTE IMMEDIATE sqlstr;  
    END LOOP;  
    -- 提交事务（在循环外提交）  
    COMMIT;  
END;  
/

-- 截断临时表（如果需要重新填充）
TRUNCATE TABLE TMP_GET_ACTURAL_TABLE_COUNT;

/
-- 调用存储过程（如果需要）
BEGIN
    GET_ACTURAL_TABLE_COUNT(1);
END;
/
-- 查询临时表
  SELECT *
    FROM TMP_GET_ACTURAL_TABLE_COUNT
ORDER BY UPPER(table_name);

/