2019-05-05 PLSQL大批量更新表字段&replace使用

@TOC

确定要更新的表、记录数，编写SQL语句

--5260502    1
--select count(*) from  YCPHOTO.DB_FILE t where T.SAVE_PATH=1  and t.path  like '\\172.16.88.72\ycssupload%'
--3358981    2
--select count(*) from  YCPHOTO.DB_FILE t where T.SAVE_PATH=2  and t.path  like '\\172.16.0.103%'
--5525166    3
--select count(*) from  YCPHOTO.DB_FILE t where T.SAVE_PATH=3  and t.path  like '\\172.16.0.103%'

更新SQL1

DECLARE
  CURSOR cur IS
  SELECT
  T1.name,T1.path
  FROM    YCPHOTO.DB_FILE T1
  WHERE T1.SAVE_PATH=1 and T1.path  like '\\172.16.88.72\ycssupload%';
  
  V_COUNTER NUMBER;
BEGIN
  V_COUNTER := 0;
  FOR row IN cur LOOP
      update YCPHOTO.DB_FILE T  set T.path = replace(T.path,'\\172.16.88.72\ycssupload','\\172.16.89.40\ycssupload') 
      where T.path  like '\\172.16.88.72\ycssupload%' and T.SAVE_PATH=1  and T.path=row.path and T.name = row.name;
     V_COUNTER := V_COUNTER + 1;
    IF (V_COUNTER >= 1000) THEN
        commit;
        V_COUNTER := 0;
    END IF;
  END LOOP;
  commit;
END;
/

更新SQL2

DECLARE
  CURSOR cur IS
  SELECT
  T1.name,T1.path
  FROM    YCPHOTO.DB_FILE T1
  WHERE T1.SAVE_PATH=2 and T1.path  like '\\172.16.0.103%';
  
  V_COUNTER NUMBER;
BEGIN
  V_COUNTER := 0;
  FOR row IN cur LOOP
      update YCPHOTO.DB_FILE T  set T.path = replace(T.path,'\\172.16.0.103','\\172.16.89.40') 
      where T.path  like '\\172.16.0.103%' and T.SAVE_PATH=2  and T.path=row.path and T.name = row.name;
     V_COUNTER := V_COUNTER + 1;
    IF (V_COUNTER >= 1000) THEN
        commit;
        V_COUNTER := 0;
    END IF;
  END LOOP;
  commit;
END;
/

更新sql3

DECLARE
  CURSOR cur IS
  SELECT
  T1.name,T1.path
  FROM    YCPHOTO.DB_FILE T1
  WHERE T1.SAVE_PATH=3 and T1.path  like '\\172.16.0.103%';
  
  V_COUNTER NUMBER;
BEGIN
  V_COUNTER := 0;
  FOR row IN cur LOOP
      update YCPHOTO.DB_FILE T  set T.path = replace(T.path,'\\172.16.0.103','\\172.16.89.40') 
      where T.path  like '\\172.16.0.103%' and T.SAVE_PATH=3  and T.path=row.path and T.name = row.name;
     V_COUNTER := V_COUNTER + 1;
    IF (V_COUNTER >= 1000) THEN
        commit;
        V_COUNTER := 0;
    END IF;
  END LOOP;
  commit;
END;
/

将存储路径的/替换为\

--将存储路径的/替换为\
--update YCPHOTO.DB_FILE t  set t.path = replace(t.path,'/','\')

DECLARE
  CURSOR cur IS
  SELECT
  T1.name,T1.path
  FROM    YCPHOTO.DB_FILE T1;
  V_COUNTER NUMBER;
BEGIN
  V_COUNTER := 0;
  FOR row IN cur LOOP
      update YCPHOTO.DB_FILE T  set T.path = replace(t.path,'/','\')
      where T.path=row.path and T.name = row.name;
     V_COUNTER := V_COUNTER + 1;
    IF (V_COUNTER >= 1000) THEN
        commit;
        V_COUNTER := 0;
    END IF;
  END LOOP;
  commit;
END;
/

替换/为\