发表评论
◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。
RSS订阅优然探索
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;
