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");