# ps 因工作需要删除垃圾数据,特此记录
# 实现功能:输入参数(表名)自动遍历所有字段名 实现自动删除:及:之前的内容
CREATE OR REPLACE PROCEDURE update_column_value(p_table_name IN VARCHAR2)
IS
l_sql VARCHAR2(1000);
BEGIN
-- 获取表中所有字段名
FOR column_rec IN (SELECT column_name FROM user_tab_columns WHERE table_name = upper(p_table_name))
LOOP
-- 构建动态SQL语句
l_sql := 'UPDATE ' || p_table_name || ' SET ' || column_rec.column_name || ' = SUBSTR(' || column_rec.column_name || ', INSTR(' || column_rec.column_name || ', '':'') + 1) WHERE INSTR(' || column_rec.column_name || ', '':'') > 0';
-- 执行动态SQL语句
EXECUTE IMMEDIATE l_sql;
END LOOP;
END;
/