oracle游标获取的表数据变化,Oracle游标循环更新整表数据和产生随机数字及截取方法使用...

1.产生字符型整数

to_char(ABS(MOD(DBMS_RANDOM.RANDOM,100))) ;

to_char(trunc(dbms_random.value(0,100)));

to_char(round(dbms_random.value)); --(0-1)

2.截取拼接

substr(title_proper,0,instr(title_proper,'.',-1,1))||'pdf' --截取点前面的字符拼接pdf字符

substr(title_proper,instr(title_proper,'.',-1,1)) --截取点后面的字符(包含点自身)

3.查找

instr(title_proper,'.',-1,1))  --反向查找点第一次出现的位置

instr(title_proper,'.',1,1)) --正向查找点第一个出现的位置

4.游标

declare

cursor carea is select id from t_archive_area_code;

pid t_archive_area_code.id%type;

begin

open carea;

loop

fetch carea into pid;

exit when carea%notfound;

update t_archive_area_code set horizontal_positon = to_char(ABS(MOD(DBMS_RANDOM.RANDOM,100))),vertical_position = to_char(trunc(dbms_random.value(0,100))),status = to_char(round(dbms_random.value)),title_proper = substr(title_proper,0,instr(title_proper,'.',-1,1))||'pdf'  where id = pid;

end loop;

close carea;

commit;

end;