java随记

坚持就是胜利!

 

oracle 嵌套游标以及java,oracle的时间处理

 

create or replace procedure test is
 
       ids VOD_CMS_OPERATION_REGION.id%type; //变量ids与VOD_CMS_OPERATION_REGION表的id字段的类型一致
       cursor cur_region is    select id from VOD_CMS_OPERATION_REGION; //定义游标
       phoneId VOD_CMS_OPERATION_REGION2PHONE.id%type;
       cursor cur_phone is select id from   VOD_CMS_OPERATION_REGION2PHONE  //第二个游标
               where VOD_CMS_OPERATION_REGION2PHONE.REGION_ID=ids ;
        
begin
       open cur_region;  //打开游标
       loop    //循环
         fetch cur_region  into ids;    //逐行处理游标把值放入变量 ids
         exit when cur_region%notfound; //没找到游标退出循环
        
         open cur_phone;
         
         loop                           
           fetch cur_phone into phoneId; 
           exit when cur_phone%notfound;
           update VOD_CMS_OPERATION_REGION2PHONE set creater=1 where VOD_CMS_OPERATION_REGION2PHONE.id=phoneId;
         end loop;
         close cur_phone;
        
       end loop;
       close cur_region; 关闭游标
       commit;
end test;


程序处理oracle时间

  Calendar ca = Calendar.getInstance(Locale.CHINA);
  ca.setTime(new Date());
  ca.set(Calendar.HOUR_OF_DAY, 0);
  ca.set(Calendar.MINUTE,0);
  ca.set(Calendar.SECOND, 0);
  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  StringBuffer buffer = new StringBuffer();
  buffer.append("select cmsAssetObject.regionIds from CmsAssetObject cmsAssetObject where cmsAssetObject.validdate >= " );
  buffer.append("to_date('"+sdf.format(ca.getTime())+ "','yyyy-MM-dd hh24:mi:ss') ");
  ca.set(Calendar.HOUR_OF_DAY, 23);
  ca.set(Calendar.MINUTE,59);
  ca.set(Calendar.SECOND,59);
  buffer.append(" and cmsAssetObject.expiredate <= ");
  buffer.append("to_date('"+sdf.format(ca.getTime())+ "','yyyy-MM-dd hh24:mi:ss') ");
  buffer.append(" and cmsAssetObject.isWeather = 1");

posted on 2009-03-09 10:50 傻 瓜 阅读(780) 评论(0)  编辑  收藏 所属分类: 杂项


只有注册用户登录后才能发表评论。


网站导航:
 

导航

统计

常用链接

留言簿(7)

我参与的团队

随笔分类

随笔档案

文章分类

友情链接

搜索

积分与排名

最新评论

阅读排行榜

评论排行榜