优化问题
对一个过程,要连续的调用,过程是这样的
CREATE OR REPLACE PROCEDURE XJ_DEVICE_VALUE_INSERT_AUTO
(
p_DEVICE_ID IN XJ_USER.XJ_DEVICE_VALUE.DEVICE_ID%TYPE,
p_XJ_DATE IN XJ_USER.XJ_DEVICE_VALUE.XJ_DATE%TYPE,
p_IB_ID IN XJ_USER.XJ_DEVICE_VALUE.IB_ID%TYPE,
p_ITEMID IN XJ_USER.XJ_DEVICE_VALUE.ITEMID%TYPE,
p_XJ_VALUE IN XJ_USER.XJ_DEVICE_VALUE.XJ_VALUE%TYPE,
p_USERID IN XJ_USER.XJ_DEVICE_VALUE.USERID%TYPE,
p_STATE IN XJ_USER.XJ_DEVICE_VALUE.STATE%TYPE
)
iS
ItemType integer;
ValueUp VARCHAR2(10);
ValueDown VARCHAR2(10);
tempVALUETYPE integer;
PlanID integer;
notPatrolledPlanCount integer;
PatrolledPlanCount integer;
--优化动态查询部分
type rc_idcount is ref cursor;
type rc_PatrolledPlanCount is ref cursor;
type rc_ItemType is ref cursor;
type rc_PlanID is ref cursor;
type rc_valueMaxMin is ref cursor;
l_rc_idcount rc_idcount;
l_rc_PatrolledPlanCount rc_PatrolledPlanCount;
l_rc_ItemType rc_ItemType;
l_rc_PlanID rc_PlanID;
l_rc_valueMaxMin rc_valueMaxMin;
--优化更新插入部分
l_stmt_UpdatePlan long;
l_stmt_InsertValue long;
l_stmt_UpdateValue long;
l_stmt_InsertValueIgnore long;
l_cursor_UpdatePlan integer;
l_cursor_InsertValue integer;
l_cursor_UpdateValue integer;
l_cursor_InsertValueIgnore integer;
l_status number;
begin
tempVALUETYPE:=0;
ValueDown:='111';
ValueUp:='00';
open l_rc_idcount for
'select count(XJ_USER.XJ_PLAN.ID) FROM XJ_USER.XJ_PLAN WHERE (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60<to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60> -to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and XJ_USER.XJ_PLAN.DEVICEID=:p_DEVICE_ID and XJ_USER.XJ_PLAN.ITEMID=:p_ITEMID and XJ_USER.XJ_PLAN.ISSTATE=0'
using p_XJ_DATE, p_XJ_DATE, p_DEVICE_ID, p_ITEMID;
open l_rc_PatrolledPlanCount for
'SELECT count(XJ_USER.XJ_PLAN.ID) FROM XJ_USER.XJ_PLAN WHERE (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60<to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60> -to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and XJ_USER.XJ_PLAN.DEVICEID=:p_DEVICE_ID and XJ_USER.XJ_PLAN.ITEMID=:p_ITEMID and XJ_USER.XJ_PLAN.ISSTATE=1'
using p_XJ_DATE, p_XJ_DATE, p_DEVICE_ID, p_ITEMID;
open l_rc_ItemType for
'select XJ_USER.XJ_ITEM_DICTIONARY.TYPE from XJ_USER.XJ_ITEM_DICTIONARY where XJ_USER.XJ_ITEM_DICTIONARY.ITEMID=:p_ITEMID'
using p_ITEMID;
open l_rc_PlanID for
'SELECT nvl(XJ_USER.XJ_PLAN.ID,0) FROM XJ_USER.XJ_PLAN WHERE (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60<to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60> -to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and XJ_USER.XJ_PLAN.DEVICEID=:p_DEVICE_ID and XJ_USER.XJ_PLAN.ITEMID=:p_ITEMID'
using p_XJ_DATE, p_XJ_DATE, p_DEVICE_ID, p_ITEMID;
open l_rc_valueMaxMin for
'SELECT XJ_USER.XJ_PLAN.DOWNVALUE, XJ_USER.XJ_PLAN.UPVALUE FROM XJ_USER.XJ_PLAN WHERE (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60<to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and (XJ_USER.XJ_PLAN.DATETIME-:p_XJ_DATE)*24*60> -to_number(XJ_USER.XJ_PLAN.SLEEPTIEM) and XJ_USER.XJ_PLAN.DEVICEID=:p_DEVICE_ID and XJ_USER.XJ_PLAN.ITEMID=:p_ITEMID'
using p_XJ_DATE, p_XJ_DATE, p_DEVICE_ID, p_ITEMID;
fetch l_rc_idcount into notPatrolledPlanCount;
fetch l_rc_PatrolledPlanCount into PatrolledPlanCount;
fetch l_rc_ItemType into ItemType;
fetch l_rc_PlanID into PlanID;
fetch l_rc_valueMaxMin into ValueDown, ValueUp;
close l_rc_idcount;
close l_rc_PatrolledPlanCount;
close l_rc_ItemType;
close l_rc_PlanID;
close l_rc_valueMaxMin;