CREATE OR REPLACE FUNCTION removespace(inpa number)
RETURN NVARCHAR2
IS
v_tmp VARCHAR2 (100);
BEGIN
---循环当前用户下的表
for rs in (select table_name from tabs) loop
--循环当前表的各个字段
for subrs in (select column_name,data_type from user_tab_columns where table_name=rs.table_name and (data_type='NVARCHAR2' or data_type='VARCHAR2')) loop
---分别移除左右空格
v_tmp:='update '||rs.table_name||' set '||subrs.column_name||'=ltrim('||subrs.column_name||')';
execute immediate v_tmp;
v_tmp:='update '||rs.table_name||' set '||subrs.column_name||'=rtrim('||subrs.column_name||')';
execute immediate v_tmp;
end loop;
end loop;
commit;
RETURN 'ok';
END removespace;
批量处理数据表中的空格
最新推荐文章于 2020-12-03 14:46:19 发布