批量更新表字段日期值的存储过程

堆码狂人

今天由于测试一个报表，必须要当天的数据，可我昨天刚测过，数据还在库里，于是想偷个懒，手工修改时间字段为今天的日期，改了不到1分钟，发现表太多了，而且记录时间的字段每个表还不止一个，不如用程序来做。于是写了下面的这个存储过程，哈哈，好用

1. /*
   
2. *搜索数据库当前用户的所有表，查出所有含date和time名称，
   
3. *并且是number(14)类型的字段的日期部分改为当前日期
   
4. */
   
5. declare
   
6.   type tcur is ref cursor; --定义游标类型
   
7.   columnsCur   tcur;       --定义表的字段游标
   
8.   sTName       varchar(30);--存储表名
   
9.   sCName       varchar(30);--存储字段名
   
10.   nCurrentTime number(14); --存储当前日期
    
11.   updstr       varchar(900);
    
12. begin
    
13.   --获取当前日期YYYYMMDD
    
14.   select to_number(to_char(sysdate, 'YYYYMMDD') || '000000')
    
15.     into nCurrentTime
    
16.     from dual;
    
17. 
    
18.   --从oracle系统表获取包含数字日期形式的字段名和对应的表名
    
19.   OPEN columnsCur for
    
20.     select p.TABLE_NAME, p.COLUMN_NAME
    
21.       from user_tab_columns p
    
22.      where p.TABLE_NAME IN ('OPENORDER',
    
23.                             'ORDERINSTRUCTION',
    
24.                             'INSTRUCTIONDETAIL',
    
25.                             'INTELLIGENTORDER',
    
26.                             'TRADINGRESULT')
    
27.        and p.DATA_TYPE = 'NUMBER' and p.DATA_PRECISION=14
    
28.        and (p.COLUMN_NAME like '%DATE%' or p.COLUMN_NAME like '%TIME%');
    
29. 
    
30.   --根据获得的表名和字段名把目前的时间更新为当前日期，时间保持不变
    
31.   loop
    
32.     fetch columnsCur
    
33.       into sTName, sCName;
    
34.     exit when columnsCur%notfound;
    
35.     begin
    
36.       --执行更新
    
37.       updstr := 'UPDATE ' || sTName || ' SET ' || sCName || '=' ||
    
38.                 nCurrentTime || '+ to_number(nvl(substr(' || sCName ||
    
39.                 ', 9), 0)) where ' || sCName || ' IS NOT NULL AND ' || sCName || '>0';
    
40.       --debug
    
41.       --dbms_output.put_line(updstr);
    
42.       execute immediate updstr;
    
43.     exception
    
44.       --如果发生错误，打印出执行的sql
    
45.       when others then
    
46.         dbms_output.put_line('Error:' || updstr);
    
47.     end;
    
48.     end loop;
    
49.   commit;
    
50. end;

复制代码