oracle游标中date,在Oracle过程中使用游标

create or replace procedure SS_C002_tm14to13(p_errcode out number,

p_errtext out varchar2) is

/*

isbn转换条码,14位错误的转换成13位正确的

yc

2008-04-18

*/

cursor c_gckc is

select *

from t_gckc

where length(isbn)=14;

v_isbn     varchar2(20);  v_tm       varchar2(30); --条码  v_rowcount number(12, 0) := 0;  v_row      number(12, 0) := 0;  v_sysdate  date;  v_isbn2    varchar2(20); --原书号  v_rows     number(12, 0) := 0;begin  v_sysdate := sysdate;  --循环读取数据  for vc_gckc in c_gckc loop    v_rows  := v_rows + 1;    v_isbn2 := vc_gckc.isbn; --原书号        v_tm:=substr(v_isbn2,1,12)||'0';        --计算库存是否有记录        select count(*)          into v_row          from t_gckc         where dm = vc_gckc.dm           and xydm = vc_gckc.xydm           and isbn = v_tm;        --没有，进行修改        if v_row = 0 then          update t_gckc             set isbn = v_tm, gxrq = v_sysdate           where dm = vc_gckc.dm             and xydm = vc_gckc.xydm             and isbn = vc_gckc.isbn;             end if;      --记录数      v_rowcount := v_rowcount + 1;       if v_rows = 1000 then      commit;      v_rows := 0;    end if;  end loop;  --提交数据  commit;  p_errcode := 0;  p_errtext := '共' || v_rowcount || '条记录转换成功';exception  when others then    rollback;    p_errcode := -1;    p_errtext := '[' || v_isbn2 || ']错误信息:' || sqlerrm;end;