oracle 存储过程

create or replace package body AutoLocateUtil is
  procedure AutoLocate is
    TYPE RefCurTyp IS REF CURSOR;
    cv       RefCurTyp;
    nLib     number;
    nNewLib  number;
    nShelf   number;
    nBook    number;
    oCurDate date;
    nCurCode number;
    sCurNum  varchar2(40);
  begin
    --(1) 检查当前定位位置
    CheckCurrentLocation(oCurDate, nLib, nShelf, nBook);
 
    --(2) 获取所有符合条件未定位的照片
    open cv for 'select photo_plate_num,photo_plate_code
       from dom_plate_record
       where photo_plate_code = 99
       and ARRIVAL_SIGNDATE >= :1
       and ARRIVAL_SIGNDATE < :2
       and photo_sourcetype=3
       and photo_libid is null order by photo_plate_num'
      using oCurDate, oCurDate + 1;
    loop
      fetch cv
        into sCurNum, nCurCode;
      exit when cv%NOTFOUND;
      nNewLib := CheckLibName(nLib, oCurDate);
      if (nNewLib = nLib) then
        if (nBook >= 100) then
          nShelf := nShelf + 1;
          nBook  := 1;
        else
          nBook := nBook + 1;
        end if;
      else
        nLib   := nNewLib;
        nShelf := 1;
        nBook  := 1;
      end if;
      execute immediate 'update DOM_PLATE_RECORD set PHOTO_LIBID=:1,PHOTO_SHELF=:2,PHOTO_BOOK=:3 where PHOTO_PLATE_CODE=:4 and PHOTO_PLATE_NUM=:5 and PHOTO_SOURCETYPE=3'
        using nLib, nShelf, nBook, nCurCode, sCurNum;
      --满一百就要在定位表中添加新本记录
      if (nBook = 100) then
        execute immediate 'update PHOTO_PLATE_LOCATION set page=:1 where LIB=:2 and BOOK=:3'
          using nBook, nLib, nShelf;
      elsif (nBook = 1) then
        execute immediate 'insert into PHOTO_PLATE_LOCATION values(:1,:2,1)'
          using nLib, nShelf;
      end if;
      --commit;
    end loop;
    --最后一次循环结束后，如果不是100或1，则这里执行最后的更新，如果是100或1，则本次更新无效
    execute immediate 'update PHOTO_PLATE_LOCATION set page=:1 where LIB=:2 and BOOK=:3'
      using nBook, nLib, nShelf;
     
      -- 必须重新计算架和本
      -- 因为 Arrival_date的序和Photo_plate_num的序不一定一致，所以只有重新计算才可信
      select max(photo_libid) into nLib from dom_plate_record ;
      select max(photo_shelf) into nShelf from dom_plate_record where photo_libid = nLib;
      select max(photo_book) into nBook from dom_plate_record where photo_libid = nLib and photo_shelf = nShelf;
     
    execute immediate 'update PLATE_CURR_LOCATION set PLATE_LIB_ID=:1,PLATE_BOOK_ID=:2,PLATE_PAGE_ID=:3,PLATE_IMPORT_DATE=:4'   
      using nLib, nShelf, nBook, oCurDate + 1;
    close cv;
    commit;
  end;

  procedure AutoCleanLocate(sDate in varchar2) is
    nPlateCode number;
    sPlateNum  varchar2(40);
    clearDate  Date;
    currDate   Date;
    lib        number;
    shelf      number;
    book       number;
    TYPE RefCurTyp IS REF CURSOR;
    cv RefCurTyp;
  begin
    select to_date(sDate, 'yyyy-mm-dd') into clearDate from dual;
    open cv for 'select plate_import_date from plate_curr_location';
    fetch cv
      into currDate;
    --如果没有找到定位指针，退出
    if (cv%NOTFOUND) then
      return;
    end if;
    close cv;
    --还没有定位到的时间，马上结束
    if (clearDate >= currDate) then
      return;
    end if;
    --
    open cv for 'select PHOTO_PLATE_CODE,
       PHOTO_PLATE_NUM,
       PHOTO_LIBID,
       PHOTO_SHELF,
       PHOTO_BOOK
        from DOM_PLATE_RECORD
       where PHOTO_PLATE_CODE = 99
         and photo_libid > 0
         AND arrival_signdate >= :1
         and photo_sourcetype=3
       group by PHOTO_PLATE_CODE,
          PHOTO_PLATE_NUM,
          PHOTO_LIBID,
          PHOTO_SHELF,
          PHOTO_BOOK'
      using clearDate;
    loop
      fetch cv
        into nPlateCode, sPlateNum, lib, shelf, book;
      exit when cv%NOTFOUND;
      execute immediate 'update DOM_PLATE_RECORD set PHOTO_LIBID=NULL,PHOTO_SHELF=NULL,PHOTO_BOOK=NULL where PHOTO_PLATE_CODE=:1 and PHOTO_PLATE_NUM=:2'
        using nPlateCode, sPlateNum;
      execute immediate 'update PHOTO_PLATE_LOCATION set PAGE=PAGE-1 where LIB=:1 and BOOK=:2'
        using lib, shelf;
      commit;
    end loop;
    close cv;
    open cv for 'select PHOTO_LIBID, PHOTO_SHELF, PHOTO_BOOK from dom_plate_record where ARRIVAL_SIGNDATE in (select max(arrival_signdate) from dom_plate_record where PHOTO_PLATE_CODE = 99 and PHOTO_LIBID > 0 and ARRIVAL_SIGNDATE<:1) order by photo_shelf desc, photo_book desc'
      using clearDate + 1;
    fetch cv
      into lib, shelf, book;
    if (cv%NOTFOUND) then
      execute immediate 'update plate_curr_location set plate_lib_id=null,plate_book_id=null,plate_page_id=null,PLATE_IMPORT_DATE=null';
    else
      -- 必须重新计算架和本
      -- 因为 Arrival_date的序和Photo_plate_num的序不一定一致，所以只有重新计算才可信
      select max(photo_libid) into lib from dom_plate_record ;
      select max(photo_shelf) into shelf from dom_plate_record where photo_libid = lib;
      select max(photo_book) into book from dom_plate_record where photo_libid = lib and photo_shelf = shelf;

      execute immediate 'update plate_curr_location set plate_lib_id=:1,plate_book_id=:2,plate_page_id=:3,PLATE_IMPORT_DATE=:4'
        using lib, shelf, book, clearDate;
    end if;
    close cv;
    execute immediate 'delete from PHOTO_PLATE_LOCATION where PAGE<=0';
  end;

  procedure CheckCurrentLocation(oDate  out date,
                                 nLib   out number,
                                 nShelf out number,
                                 nBook  out number) is
    TYPE RefCurTyp IS REF CURSOR;
    cv     RefCurTyp;
    bExist number;
  begin
    nLib   := 0;
    nShelf := 0;
    nBook  := 0;
    bExist := 1;
    open cv for 'select PLATE_LIB_ID,PLATE_BOOK_ID,PLATE_PAGE_ID,PLATE_IMPORT_DATE from PLATE_CURR_LOCATION where rownum=1';
    fetch cv
      into nLib, nShelf, nBook, oDate;
    if (cv%NOTFOUND) then
      bExist := 0;
    end if;
    close cv;
    if (bExist = 0) then
      open cv for 'select arrival_signdate
        from DOM_PLATE_RECORD
       where PHOTO_PLATE_CODE = 99
         and PHOTO_LIBID is null
       order by arrival_signdate asc';
      fetch cv
        into oDate;
      close cv;
    end if;
  end;

  function CheckLibName(nLib in number, oDate in date) return number as
    sYear    varchar2(4);
    sLibName varchar2(4);
    nNewLib  number;
  begin
    --select to_char(oDate + 1, 'yyyy') into sYear from dual;
    select to_char(oDate, 'yyyy') into sYear from dual;
    sYear    := substr(sYear, 3);
    sLibName := '';
    execute immediate 'select entry_name from category_other where wt_type = :1 and entry_code = :2'
      into sLibName
      using 4, nLib;
    if (sLibName <> sYear) then
      execute immediate 'select entry_code from category_other where wt_type = :1 and entry_name = :2'
        into nNewLib
        using 4, sYear;
      return nNewLib;
    else
      return nLib;
    end if;
  end;
end AutoLocateUtil;