RSS订阅优然探索
你的位置:首页 » 学习收藏 » 正文

Oracle 存储过程代码

选择字号: 超大 标准 发布时间:2009-1-31 23:37:4 | 作者:admin | 0个评论 | 人浏览

create or replace package body kpipage is

  type temp_cur is ref cursor;

  procedure kpipage_list(pindex in number,
                         psql   in varchar2,
                         psize  in number,
                         pcount out number,
                         v_cur  out type_cur) as
 
    v_sql   varchar2(4000);
    v_order varchar2(50);
    v_count number;
    v_plow  number;
    v_phei  number;
  begin
    ------------------------------------------------------------取分页总数
    v_sql := 'select count(*) from (' || psql || ')';
    execute immediate v_sql
      into v_count;
    --pcount := ceil(v_count/psize);
    pcount := v_count;
    ------------------------------------------------------------显示任意页内容
    v_phei := pindex * psize + psize;
    v_plow := v_phei - psize + 1;
    v_sql  := 'select * from (select rownum rnm, a.* from (' || psql ||
              ') a where rownum <=' || v_phei || v_order ||
              ' ) where rnm >=' || v_plow;
    open v_cur for v_sql;
  end kpipage_list;

  procedure setkpiedepartmentdetail(swhere       in farray,
                                    skpiegroupid in varchar2) as
    v_sql     varchar2(4000);
    v_sql1    varchar2(4000);
    cur_trail temp_cur;
    rtmp      tb_kpiedetail%rowtype;
    vindex    integer;
  begin
    ---------------------------------------
    for vindex in 1 .. swhere.count loop
      v_sql1 := v_sql1 || ' kpiedetailid = ' || swhere(vindex) || ' or';
    end loop;
    v_sql1 := substr(v_sql1, 0, length(v_sql1) - 3);
    v_sql  := 'select * from tb_kpiedetail where  ' || v_sql1; -- swhere(1);
    --v_sql := 'select * from tb_kpiedetail where kpiedetailid = 1003';
    --打开游标
    if not cur_trail%isopen then
      open cur_trail for v_sql;
    end if;
    loop
      ---------------------------------------
      --取得数据放到定义好的表变量里面
      fetch cur_trail
        into rtmp;
   
      exit when(cur_trail%notfound);
      ---------------------------------------
      --插入数据
      insert into tb_kpiedepartment
        (kpiedepartmentid,
         kpiedepartmentgroupid,
         kpiedetailid,
         kpiegroupid,
         dname,
         dinfo,
         dminvalue,
         dmaxvalue,
         showtype,
         defaultvalue,
         valuedir)
      values
        (seq_kpi.nextval,
         skpiegroupid,
         rtmp.kpiedetailid,
         rtmp.kpiegroupid,
         rtmp.dname,
         rtmp.dinfo,
         rtmp.dminvalue,
         rtmp.dmaxvalue,
         rtmp.showtype,
         rtmp.defaultvalue,
         rtmp.valuedir);
   
    end loop;
 
    commit;
    ---------------------------------------
    --关闭游标
    if cur_trail%isopen then
      close cur_trail;
    end if;
    ---------------------------------------
  end setkpiedepartmentdetail;

  ------------------------------------------------
  --单个数据增加
  ------------------------------------------------
  procedure setkpiedepartment(swhere in varchar2, skpiegroupid in varchar2) as
    v_sql     varchar2(4000);
    cur_trail temp_cur;
    rtmp      tb_kpiedetail%rowtype;
  begin
    ---------------------------------------
    v_sql := 'select * from tb_kpiedetail where  kpiedetailid=' || swhere;
 
    --打开游标
    if not cur_trail%isopen then
      open cur_trail for v_sql;
    end if;
 
    ---------------------------------------
    --取得数据放到定义好的表变量里面
    fetch cur_trail
      into rtmp;
 
    ---------------------------------------
    --插入数据
    insert into tb_kpiedepartment
      (kpiedepartmentid,
       kpiedepartmentgroupid,
       kpiedetailid,
       kpiegroupid,
       dname,
       dinfo,
       dminvalue,
       dmaxvalue,
       showtype,
       defaultvalue,
       valuedir)
    values
      (seq_kpi.nextval,
       skpiegroupid,
       rtmp.kpiedetailid,
       rtmp.kpiegroupid,
       rtmp.dname,
       rtmp.dinfo,
       rtmp.dminvalue,
       rtmp.dmaxvalue,
       rtmp.showtype,
       rtmp.defaultvalue,
       rtmp.valuedir);
 
    commit;
    ---------------------------------------
    --关闭游标
    if cur_trail%isopen then
      close cur_trail;
    end if;
    ---------------------------------------
  end setkpiedepartment;

  --删除分解的计划的判断
  procedure plandecompositionisdel(sid in varchar2, isbool out number) as
    v_sql     varchar2(4000);
    iparentid varchar2(100);
  begin
    v_sql := 'select parentid from tb_kpiplan where kpiplanid=' || sid; --取得parentid;
    execute immediate v_sql
      into iparentid;
 
    v_sql := 'select kpiplanid
      from (select kpiplanid
              from tb_kpiplan
             where parentid = ' || iparentid ||
             ' and isdel=''n''
             order by kpiplanid desc)
     where rownum <= 1
     order by rownum asc'; --取得parentid的kpiplanid;
    execute immediate v_sql
      into iparentid;
 
    if iparentid = sid then
      v_sql := 'update tb_kpiplan set isdel=''y'' where kpiplanid=' || sid;
      execute immediate v_sql;
      isbool := 0;
    else
      isbool := 1;
    end if;
  end plandecompositionisdel;

  --/*计划下派的时候复制子计划*/--
  procedure planassignedmanagechild(sparentid     in varchar2,
                                    sdepartmentid in varchar2,
                                    splanamount   in varchar2,
                                    isbool        out number) as
    v_sql     varchar2(4000);
    cur_trail temp_cur;
    rtmp      tb_kpiplan%rowtype;
    rownum    number;
  begin
    --/*判断计划是否存在
    --存在则更新,不存在则增加
    --需要判断深度和隶属级别更新
    --需要处理全部计划数据和实际数据的显示效果*/--
    isbool := 0;
    v_sql  := 'select * from tb_kpiplan where  kpiplanid=' || sparentid;
 
    --打开游标
    if not cur_trail%isopen then
      open cur_trail for v_sql;
    end if;
    --判断是增加还是修改
    v_sql := 'select count(*) from tb_kpiplan where  parentid=' ||
             sparentid || ' and kpiedepartmentid=''' || sdepartmentid ||
             ''' and isdel=''n''';
    execute immediate v_sql
      into rownum;
    ---------------------------------------
    --取得数据放到定义好的表变量里面
    fetch cur_trail
      into rtmp;
 
    ---------------------------------------
    --插入或更新错误数据
    if rownum = 0 then
      --插入数据
      insert into tb_kpiplan
        (kpiplanid, parentid, kpiedepartmentid, planamount)
      values
        (seq_kpiplan.nextval, sparentid, sdepartmentid, splanamount);
    else
      if rownum = 1 then
        --更新数据
        insert into tb_kpiplan
          (kpiplanid, parentid, kpiedepartmentid, planamount)
        values
          (seq_kpiplan.nextval, sparentid, sdepartmentid, splanamount);
      else
        --错误提示
        isbool := 1;
      end if;
    end if;
 
    commit;
    ---------------------------------------
    --关闭游标
    if cur_trail%isopen then
      close cur_trail;
    end if;
 
  end planassignedmanagechild;
end kpipage;
 

标签:

猜你喜欢

发表评论

必填

选填

选填

必填,不填不让过哦,嘻嘻。

记住我,下次回复时不用重新输入个人信息

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。