oracle 如何修剪所有字符串类型表中所有行中的所有列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2835893/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to trim all columns in all rows in all tables of type string?
提问by daveslab
In Oracle 10g, is there a way to do the following in PL/SQL?
在 Oracle 10g 中，有没有办法在 PL/SQL 中执行以下操作？
for each table in database
for each row in table
for each column in row
if column is of type 'varchar2'
column = trim(column)
Thanks!
谢谢！
回答by Dave Costa
Of course, doing large-scale dynamic updates is potentially dangerous and time-consuming. But here's how you can generate the commands you want. This is for a single schema, and will just build the commands and output them. You could copy them into a script and review them before running. Or, you could change dbms_output.put_line( ... )to EXECUTE IMMEDIATE ...to have this script execute all the statements as they are generated.
当然，进行大规模的动态更新具有潜在的危险性和耗时性。但是，您可以通过以下方式生成所需的命令。这是针对单个模式的，只会构建命令并输出它们。您可以将它们复制到脚本中并在运行前查看它们。或者，您可以更改dbms_output.put_line( ... )为EXECUTE IMMEDIATE ...让此脚本在生成所有语句时执行它们。
SET SERVEROUTPUT ON
BEGIN
FOR c IN
(SELECT t.table_name, c.column_name
FROM user_tables t, user_tab_columns c
WHERE c.table_name = t.table_name
AND data_type='VARCHAR2')
LOOP
dbms_output.put_line(
'UPDATE '||c.table_name||
' SET '||c.column_name||' = TRIM('||c.column_name||') WHERE '||
c.column_name||' <> TRIM('||c.column_name||') OR ('||
c.column_name||' IS NOT NULL AND TRIM('||c.column_name||') IS NULL)'
);
END LOOP;
END;
回答by Allan
Presumably you want to do this for every column in a schema, not in the database. Trying to do this to the dictionary tables would be a bad idea...
大概您想对模式中的每一列执行此操作，而不是在数据库中。尝试对字典表执行此操作将是一个坏主意......
declare
v_schema varchar2(30) := 'YOUR_SCHEMA_NAME';
cursor cur_tables (p_schema_name varchar2) is
select owner, table_name, column_name
from all_tables at,
inner join all_tab_columns atc
on at.owner = atc.owner
and at.table_name = atc.table_name
where atc.data_type = 'VARCHAR2'
and at.owner = p_schema;
begin
for r_table in cur_tables loop
execute immediate 'update ' || r.owner || '.' || r.table_name
|| ' set ' || r.column_name || ' = trim(' || r.column_name ||');';
end loop;
end;
This will only work for fields that are VARCHAR2s in the first place. If your database contains CHAR fields, then you're out of luck, because CHAR fields are always padded to their maximum length.
这仅适用于首先是 VARCHAR2 的字段。如果您的数据库包含 CHAR 字段，那么您就不走运了，因为 CHAR 字段总是被填充到它们的最大长度。
