--查看表中是否有中文字符
DECLARE
V_SQL CLOB;
TYPE T_TABLE_ARRAY IS VARRAY(50) OF VARCHAR(50);
TABLES T_TABLE_ARRAY := T_TABLE_ARRAY('table_name');
V_OWNER VARCHAR2(30) := 'schema'; --
V_RESULT INTEGER;
BEGIN
FOR I IN 1 .. TABLES.COUNT LOOP
SELECT 'SELECT COUNT(*) FROM ' || V_OWNER || '.' || UPPER(TABLES(I)) ||
CHR(10) || ' WHERE ROWNUM<10 AND (' || TRIM_COLUMN_NAME || ')'
INTO V_SQL
FROM (SELECT REPLACE(APP_COMPASS.F_LINK(TRIM_COLUMN_NAME), ',', ' OR ') TRIM_COLUMN_NAME
FROM (SELECT 'ASCIISTR(' || COL.COLUMN_NAME ||
') LIKE ''%\%'' ' TRIM_COLUMN_NAME
FROM ALL_TAB_COLUMNS COL
WHERE COL.OWNER = V_OWNER
AND COL.TABLE_NAME = UPPER(TABLES(I))
AND (COL.DATA_TYPE LIKE '%CHAR%' OR
COL.DATA_TYPE LIKE '%CLOB%')
ORDER BY COL.COLUMN_ID
));
EXECUTE IMMEDIATE V_SQL
INTO V_RESULT;
DBMS_OUTPUT.PUT_LINE(TABLES(I)||' chinese count result is: '|| V_RESULT);
IF V_RESULT > 0 THEN
SELECT REPLACE(REPLACE(V_SQL, 'COUNT(*)', '*'),'ROWNUM<10 AND','') INTO V_SQL FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_SQL || ';');
END IF;
END LOOP;
END;
08-01
3030
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
“相关推荐”对你有帮助么?
-
非常没帮助
-
没帮助
-
一般
-
有帮助
-
非常有帮助
提交