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