维护集锦替换回车符版

维护集锦替换回车符版 --修改UserId用户名和TableName表名，即可自动将该表中所有字符型字段中的回车 符去掉（单表） decLare VarField VARCHAR2(50); UserId VARCHAR2(10) :='GADATA0001'; TableName VARCHAR2(20) :='ACCOUNT'; cursor CurField is select column_name from all_tab_columns where owner=UserId AND TABLE_NAME=TableName and data_type='VARCHAR2'; begin open CurField; loop fetch CurField into VarField; exit when CurField%notfound; execute immediate 'update ' || UserId || '.' || TableName || ' set ' || VarField || '=replace(replace(' || VarField || ',chr(10),null),chr(13),null)'; end loop; close CurField; commit; end; --修改UserId用户名，即可自动将该用户所有表的所有字符型字段中的回车符去掉（单用 户） decLare VarTable ALL_TAB_COLUMNS.TABLE_NAME%TYPE; VarField ALL_TAB_COLUMNS.COLUMN_NAME%TYPE; UserId VARCHAR2(10) := 'GADATA0001'; -- TableName VARCHAR2(20) := 'ACCOUNT'; cursor CurField is select c.table_name,c.column_name from all_tab_columns c,dba_tables t where c.owner=t.owner and c.table_name=t.table_name and c.owner = UserId -- AND c.TABLE_NAME = TableName and c.data_type = 'VARCHAR2'; begin open CurField; loop fetch CurField into VarTable,VarField; exit when CurField%notfound; execute immediate 'update ' || UserId || '.' || VarTable || ' set ' || VarField || '=replace(replace(' || VarField || ',chr(10),null),chr(13),null)'; end loop; close CurField; commit; end;