create or replace procedure getPerTimeAndGradePro (tyear in number,tmonth in number,torgId in number)
as
processId wfworkitem.processinstid%type;
operatorName eosoperator.operatorname%type;
leadApproveGrade pl_month_work_sum.lead_grade_sum%type;
orgName eosorg_v_organization.orgName%type; cursor cur_coordinate is select distinct(item.processinstid),oper.operatorname,pws.lead_grade_sum,evo.orgName
from pl_month_workplan workplan,wfactivityinst act,wfworkitem item,
plan_flow_record rec,eosoperator oper,
eosorg_v_organization evo,
eosorg_t_employee ete,
pl_month_work_sum pws
where workplan.wf_proc_inst_id=act.processinstid
and item.workitemid=rec.workitemid(+)
and workplan.employee_id=oper.operatorid
and act.activityinstid=item.activityinstid(+)
and oper.operatorid=ete.operatorid and ete.orgid=evo.orgID and item.processinstid is not null
and pws.workplanid=workplan.work_plan_id
and workplan.time_value1=tyear
and workplan.time_value2=tmonth
and evo.orgID=torgId; begin
delete perApproveTimeAndGrade;
open cur_coordinate;
loop fetch cur_coordinate into processId,operatorName,leadApproveGrade,orgName;
exit when cur_coordinate %notfound;
dbms_output.put_line(processId); -- insert into personApprovePlan(operatorname,mkTime )values (operatorName,RetrievePersonPlan( 'mk ',processId),RetrievePersonPlan( 'app ',processId),RetrievePersonPlan( 'upper ',processId),RetrievePersonPlan( 'ex ',processId),RetrievePersonPlan( 'info ',processId));
insert into perApproveTimeAndGrade values (orgName,operatorName,RetrievePersonPlan( 'mk ',processId),RetrievePersonPlan( 'app ',processId),RetrievePersonPlan( 'upper ',processId),RetrievePersonPlan( 'ex ',processId),RetrievePersonPlan( 'infon ',processId),leadApproveGrade); end loop;
commit;
close cur_coordinate;
end;
RetrievePersonPlan为我写的一个函数,速度太慢了,查100多记录,都要1分钟,大家能给给意见吗,我用的是oracle,能把优化后的语句写出来就更好了,谢谢先!
as
processId wfworkitem.processinstid%type;
operatorName eosoperator.operatorname%type;
leadApproveGrade pl_month_work_sum.lead_grade_sum%type;
orgName eosorg_v_organization.orgName%type; cursor cur_coordinate is select distinct(item.processinstid),oper.operatorname,pws.lead_grade_sum,evo.orgName
from pl_month_workplan workplan,wfactivityinst act,wfworkitem item,
plan_flow_record rec,eosoperator oper,
eosorg_v_organization evo,
eosorg_t_employee ete,
pl_month_work_sum pws
where workplan.wf_proc_inst_id=act.processinstid
and item.workitemid=rec.workitemid(+)
and workplan.employee_id=oper.operatorid
and act.activityinstid=item.activityinstid(+)
and oper.operatorid=ete.operatorid and ete.orgid=evo.orgID and item.processinstid is not null
and pws.workplanid=workplan.work_plan_id
and workplan.time_value1=tyear
and workplan.time_value2=tmonth
and evo.orgID=torgId; begin
delete perApproveTimeAndGrade;
open cur_coordinate;
loop fetch cur_coordinate into processId,operatorName,leadApproveGrade,orgName;
exit when cur_coordinate %notfound;
dbms_output.put_line(processId); -- insert into personApprovePlan(operatorname,mkTime )values (operatorName,RetrievePersonPlan( 'mk ',processId),RetrievePersonPlan( 'app ',processId),RetrievePersonPlan( 'upper ',processId),RetrievePersonPlan( 'ex ',processId),RetrievePersonPlan( 'info ',processId));
insert into perApproveTimeAndGrade values (orgName,operatorName,RetrievePersonPlan( 'mk ',processId),RetrievePersonPlan( 'app ',processId),RetrievePersonPlan( 'upper ',processId),RetrievePersonPlan( 'ex ',processId),RetrievePersonPlan( 'infon ',processId),leadApproveGrade); end loop;
commit;
close cur_coordinate;
end;
RetrievePersonPlan为我写的一个函数,速度太慢了,查100多记录,都要1分钟,大家能给给意见吗,我用的是oracle,能把优化后的语句写出来就更好了,谢谢先!
解决方案 »
- 求帮助写一个外连接SQL!!!!!!!!外连接,或者子查询~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- 如何知道当前用户的默认表空间是什么
- 求助各位高手哥哥姐姐弟弟妹妹
- 关于数据库图片存放的问题
- 用Oracle开发的程序怎么打包?谢谢1!1!1!1!1!
- 如何恢复列的默认值?
- 知道View或TRIGGER的名称,我想通过SQL把该View或TRIGGER的原代码导出来 谢谢
- 高手来帮我建个变态视图,对我来说太难了. 实现了再加200分,我在这里级别不够直接出怎么多分
- 为什么装不了oracle的客户端?帮忙啊!!!
- 能使用SQL语句从ftp上拿txt文件并实现入库吗?
- 数据库面试的一道问题
- 问问问....如果将 1对多.两个表..转换成一个表....................
select distinct(item.processinstid),oper.operatorname,pws.lead_grade_sum,evo.orgName
from pl_month_workplan workplan,wfactivityinst act,wfworkitem item,
plan_flow_record rec,eosoperator oper,
eosorg_v_organization evo,
eosorg_t_employee ete,
pl_month_work_sum pws
where workplan.wf_proc_inst_id=act.processinstid
and item.workitemid=rec.workitemid(+)
and workplan.employee_id=oper.operatorid
and act.activityinstid=item.activityinstid(+)
and oper.operatorid=ete.operatorid and ete.orgid=evo.orgID
and item.processinstid is not null
and pws.workplanid=workplan.work_plan_id
and workplan.time_value1=:tyear
and workplan.time_value2=:tmonth
and evo.orgID=:torgId;