Shulou(Shulou.com)06/01 Report--

Create or replace procedure PROC_test is-- Description: delete the specified characters in the field (carriage return chr (13), line feed chr (10))-- By LiChao-- Date:2016-03-01 colname varchar (20);-- column name cnt number;-- number of rows of columns containing newline characters v_sql varchar (2000) -- dynamic SQL variable begin-- read the column for col in (select column_name from user_tab_columns where table_name = 'TEMP') loop colname: = col.column_name;-- replace the newline character chr (10) v_sql: =' select count (1) from temp where instr ('| | colname | |', chr (10)) > 0'; EXECUTE IMMEDIATE V_SQL into cnt If cnt > 0 then v_sql: = 'update temp set' | | colname |'= trim (replace ('| | colname | |', chr (10),'))'| 'where instr (' | | colname | |', chr (10)) > 0'; EXECUTE IMMEDIATE VSQL; commit; end if -replace the carriage return character chr (13) v_sql: = 'select count (1) from temp where instr (' | | colname | |', chr (13)) > 0'; EXECUTE IMMEDIATE V_SQL into cnt If cnt > 0 then v_sql: = 'update temp set' | | colname |'= trim (replace ('| | colname | |', chr (13),')'| 'where instr (' | | colname | |', chr (13)) > 0'; EXECUTE IMMEDIATE VSQL; commit; end if -- replace'| 'chr (124) is' * 'chr (42) v_sql: =' select count (1) from temp where instr ('| | colname | |', chr (124)) > 0'; EXECUTE IMMEDIATE V_SQL into cnt If cnt > 0 then v_sql: = 'update temp set' | | colname |'= replace ('| colname | |', chr (124), chr (42)'| | 'where instr (' | | colname | |', chr (124)) > 0'; EXECUTE IMMEDIATE VSQL; commit; end if; end loop;end PROC_test;/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.