plsql实战之综合应用

create or replace package pack_materialUtils
is
   --type materialRecordsType is table of eachMaterialRecord index by binary_integer;
  --获得指定出库单包含的物资的名称
  --molId：出库单id
  --strlink:物资名称的连接符
  function getNamesByMol(molId number) return varchar2;
  function getNamesByMil(milId number) return varchar2;
  function getMaterialSerialNumber(deptId varchar2) return varchar2;
  function getPiciByMilSn(milsn varchar2) return varchar2;
  function getPiciByMolSn(molsn varchar2) return varchar2;
  function searchMaterial(materialName varchar2,
                         deptId varchar2,
                         storeType varchar2,
                         gridRibboncode varchar2,
                         materialType varchar2,
                         storeId varchar2)
                         return materialRecordsType;
end  pack_materialUtils;

create or replace package body pack_materialUtils
is
   --*******函数1***********
   --取出库单物资的名称（拼接成字符串）
   --Chency
   --20130927
   function getNamesByMol(molId number) return varchar2
   is
      resultNames varchar2(2000) := '';
      tempName    material.name%type;
      cursor c_mo is  select m.name from material m right join material_out mo on mo.material_id = m.id where mo.material_out_list_id = molId;
   begin
     open c_mo;
     fetch c_mo into  tempName;
      while c_mo%found loop
           resultNames := tempName || ',' || resultNames;
           fetch c_mo into tempName;
       end loop;
         return resultNames;
       close c_mo;
    end;
   --*******函数1***********
   --取入库单物资的名称（拼接成字符串）
   --Chency
   --20130927
   function getNamesByMil(milId number) return varchar2
   is
      resultNames varchar2(20000) := '';
      tempName    material.name%type;
      cursor c_mi is  select m.name from material m right join material_in mi on mi.material_id = m.id where mi.material_in_list_id = milId;
   begin
     open c_mi;
     fetch c_mi into  tempName;
      while c_mi%found loop
           resultNames := tempName || ',' || resultNames;
           fetch c_mi into tempName;
       end loop;
         return resultNames;
       close c_mi;
    end;

     /*
      --/取得单位的出库单、入库单的流水号
      说明：
      1，流水号：形式20130912xxxxxMMMMMM，前面是年月日，中间xxxxx是部门的id后边是本田的第几个流水号5位数字组成
      2，该函数每次调用都会返回当日最大的流水号，调用完之后更新最大流水号加一。
      3，出库单，入库单的流水号是全市唯一的
    */
    function getMaterialSerialNumber(deptId varchar2) return varchar2
    is
      v_realToday varchar2(20);
      v_tempToday varchar2(20);
      v_realMaxNumber int;
      v_tempMaxNumber int;
      v_tempId    material_serial_number.id%type;
      v_result  varchar2(20);
      cursor c is select today  from material_serial_number where dept_id = deptId for update;
    begin
        select hibernate_sequence.nextval into v_tempId from dual;
        select to_char(sysdate,'yyyymmdd') into v_realToday from dual;
 --       select today  from material_serial_number where dept_id = deptId;
        open c;
        fetch c into v_tempToday;
        if c%FOUND THEN
            select today into v_tempToday from material_serial_number where dept_id = deptId;
            dbms_output.put_line('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
            if v_realToday = v_tempToday then   --如果序列表里有当前的记录
               select maxNumber into v_realMaxNumber from material_serial_number where dept_id = deptId and today = v_realToday;
               --生成流水号最后的序号
               dbms_output.put_line('长度：' || lengthb(to_char(v_realMaxNumber)));
               case lengthb(to_char(v_realMaxNumber))
               when 1   then
                   v_result := v_realToday ||  to_char(deptId) || '0000' || to_char(v_realMaxNumber);
               when 2   then
                   v_result := v_realToday ||  to_char(deptId) || '000'  || to_char(v_realMaxNumber);
               when 3   then
                   v_result := v_realToday  || to_char(deptId) || '00'   || to_char(v_realMaxNumber);
               when 4   then
                   v_result := v_realToday  || to_char(deptId)|| '0'     || to_char(v_realMaxNumber);
               else
                   v_result :=  v_realToday || to_char(deptId) || to_char(v_realMaxNumber);
               end case;
               --更新序列表
             else
               delete from material_serial_number where dept_id = deptId;
               insert into material_serial_number values(v_tempId,0,v_realToday,1,deptId);
               dbms_output.put_line('ccccccccccccccccccccccccccccccccccccccccccc');
               v_result := v_realToday || to_char(deptId) ||  '00001';
             end if;
        else
            insert into material_serial_number values(v_tempId,0,v_realToday,1,deptId);
            dbms_output.put_line('ddddddddddddddddddddddddddddddddddddddddddddddddd');
            v_result := v_realToday || to_char(deptId) ||  '00001';
        end if;
        update material_serial_number set maxnumber = maxnumber + 1 where dept_id = deptId;
        commit;
        return v_result;
    end;
    /*
    根据入库单的流水号
    */
     function getPiciByMilSn(milsn varchar2) return varchar2
     is
       v_count integer;
       v_result varchar2(10);
     begin
         select count(*)+1 into v_count from material_in where mil_serial_number = milsn;
         case lengthb(v_count)
         when 1  then
             v_result := '00'|| to_char(v_count);
         when 2 then
             v_result := '0'|| to_char(v_count);
         when 3 then
             v_result :=  to_char(v_count);
         else
             v_result :=  '批次达到最大数';
         end case;
     return v_result;
     end;

     --取得出库记录的批次
    function getPiciByMolSn(molsn varchar2) return varchar2
      is
       v_count integer;
       v_result varchar2(10);
     begin
         select count(*)+1 into v_count from material_out where mol_serial_number = molsn;
         case lengthb(v_count)
         when 1  then
             v_result := '00'|| to_char(v_count);
         when 2 then
             v_result := '0'|| to_char(v_count);
         when 3 then
             v_result :=  to_char(v_count);
         else
             v_result :=  '批次达到最大数';
         end case;
     return v_result;
     end;
   --******************根据传递的参数查询库里的物资  **************************************
   function searchMaterial(
                           materialName varchar2,
                           deptId varchar2,
                           storeType varchar2,
                           gridRibboncode varchar2,
                           materialType varchar2,
                           storeId varchar2)
                           return materialRecordsType
   is
   type ref_eachMaterialTotal is ref cursor;   --声明游标变量类型
   v_materialRecords materialRecordsType :=  materialRecordsType();     --定义存放结果集 表
   v_cursor ref_eachMaterialTotal;            --定义游标变量
   v_cursorTemp ref_eachMaterialTotal;        --定义暂时的游标变量
   v_temp  eachMaterialRecord;                --测试变量
   v_i   int := 1;
   begin
       FOR emp_record IN ( select m.id,m.name materialName ,mi.totalnumber inputMaterialNumber,totalrealnumber remaindNumber,mi.totalPrice totalPrice from material m
              join (select material_id, sum(material_mumber) totalnumber,sum(real_mumber) totalrealnumber, sum(price * real_mumber) totalPrice from material_in
              where 1=1
              and instr(decode(deptId, '', 'yes', dept_id,'yes'), 'y') > 0
              and instr(decode(storeId, '', 'yes', store_id,'yes'), 'y') > 0
              group by material_id)mi
              on m.id = mi.material_id
              where 1=1
              and instr(decode(materialName, '', 'yes', m.name,'yes'), 'y') > 0
              )

              LOOP
              v_materialRecords.extend();
              v_materialRecords(v_materialRecords.count) := eachMaterialRecord(
                                                          emp_record.id,
                                                          emp_record.materialname,
                                                          emp_record.inputMaterialNumber,
                                                          emp_record.remaindNumber,
                                                          emp_record.totalPrice,
                                                          0,
                                                          0,
                                                          0,
                                                          0,
                                                          0,
                                                          0,
                                                          0,
                                                          0,
                                                          0);

            BEGIN               --获取物资的实物储备总数量、总库存、总金额
                select mi.totalnumber inputMaterialNumberSw,totalrealnumber remaindNumberSw,mi.totalPrice totalPriceSw  into
                 v_materialRecords(v_materialRecords.count).inputMaterialNumberSw, v_materialRecords(v_materialRecords.count).remaindNumberSw, v_materialRecords(v_materialRecords.count).totalPriceSw from material m
                join (select material_id, sum(material_mumber) totalnumber,sum(real_mumber) totalrealnumber, sum(price * real_mumber) totalPrice from material_in
                where material_id = emp_record.id
                and store_type = '实物储备'
                and instr(decode(deptId, '', 'yes', dept_id,'yes'), 'y') > 0
                and instr(decode(storeId, '', 'yes', store_id,'yes'), 'y') > 0
                group by material_id)mi
                on m.id = mi.material_id
                where 1=1
                and instr(decode(materialName, '', 'yes', m.name,'yes'), 'y') > 0;
           EXCEPTION
           WHEN no_data_found THEN
             null;
           END;

           BEGIN               --获取物资的协议储备总数量、总库存、总金额
                select mi.totalnumber inputMaterialNumberSw,totalrealnumber remaindNumberSw,mi.totalPrice totalPriceSw  into
                 v_materialRecords(v_materialRecords.count).inputMaterialNumberXy, v_materialRecords(v_materialRecords.count).remaindNumberXy, v_materialRecords(v_materialRecords.count).totalPriceXy from material m
                join (select material_id, sum(material_mumber) totalnumber,sum(real_mumber) totalrealnumber, sum(price * real_mumber) totalPrice from material_in
                where material_id = emp_record.id
                and store_type = '协议储备'
                and instr(decode(deptId, '', 'yes', dept_id,'yes'), 'y') > 0
                and instr(decode(storeId, '', 'yes', store_id,'yes'), 'y') > 0
                group by material_id)mi
                on m.id = mi.material_id
                where 1=1
                and instr(decode(materialName, '', 'yes', m.name,'yes'), 'y') > 0;
           EXCEPTION
           WHEN no_data_found THEN
             null;
           END;
           BEGIN               --获取物资的产能储备总数量、总库存、总金额
                select mi.totalnumber inputMaterialNumberSw,totalrealnumber remaindNumberSw,mi.totalPrice totalPriceSw  into
                 v_materialRecords(v_materialRecords.count).inputMaterialNumberCn, v_materialRecords(v_materialRecords.count).remaindNumberCn, v_materialRecords(v_materialRecords.count).totalPriceCn from material m
                join (select material_id, sum(material_mumber) totalnumber,sum(real_mumber) totalrealnumber, sum(price * real_mumber) totalPrice from material_in
                where material_id = emp_record.id
                and store_type = '产能储备'
                and instr(decode(deptId, '', 'yes', dept_id,'yes'), 'y') > 0
                and instr(decode(storeId, '', 'yes', store_id,'yes'), 'y') > 0
                group by material_id)mi
                on m.id = mi.material_id
                where 1=1
                and instr(decode(materialName, '', 'yes', m.name,'yes'), 'y') > 0;
           EXCEPTION
           WHEN no_data_found THEN
             null;
           END;
           v_i := v_i + 1;
        END LOOP;

        dbms_output.put_line('集合的长度为：'|| v_materialRecords.count);

        /*
        if v_materialRecords.count > 0   then
        for v_count in v_materialRecords.first..v_materialRecords.last  loop
             dbms_output.put_line(v_materialRecords(v_count).id || '  ' ||
                                  v_materialRecords(v_count).materialname || '  ' ||
                                  v_materialRecords(v_count).inputMaterialNumber || '  ' ||
                                  v_materialRecords(v_count).remaindnumber || '  ' ||
                                  v_materialRecords(v_count).totalprice || '  ' ||

                                  v_materialRecords(v_count).inputMaterialNumberSw || '  ' ||
                                  v_materialRecords(v_count).remaindNumberSw || '  ' ||
                                  v_materialRecords(v_count).totalPriceSw  || '  ' ||
                                  v_materialRecords(v_count).inputMaterialNumberXy || '  ' ||
                                  v_materialRecords(v_count).remaindNumberXy || '  ' ||
                                  v_materialRecords(v_count).totalPriceXy || '  ' ||
                                  v_materialRecords(v_count).inputMaterialNumberCn || '  ' ||
                                  v_materialRecords(v_count).remaindNumberCn || '  ' ||
                                  v_materialRecords(v_count).totalPriceCn
                                  );
        end loop;
        end if;
        */
   return v_materialRecords;
   end;
end pack_materialUtils;