当前位置：首页 > news >正文

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

news 来源：原创 2024/9/27 1:16:45

背景

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

源SQL

需要用目的用户去执行

declare num number;
beginselect count(1) into num from user_tables where table_name = 'TMP_GET_ACTURAL_TABLE_COUNT';if num > 0 thenexecute 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 
ISsqlstr varchar2(4000);
beginfor 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_%') loopexecute 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

需要用目的用户去执行

DECLAREnum NUMBER;
BEGINSELECT COUNT(1)INTOnumFROM user_tablesWHERE table_name = 'TMP_GET_ACTURAL_TABLE_COUNT';IF num > 0 THENEXECUTE 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
ISsqlstr 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;/
-- 调用存储过程（如果需要）
BEGINGET_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;/
-- 调用存储过程（如果需要）
BEGINGET_ACTURAL_TABLE_COUNT(1);
END;
/
-- 查询临时表SELECT *FROM TMP_GET_ACTURAL_TABLE_COUNT
ORDER BY UPPER(table_name);/