以下存储过程编译是可以通过的,在执行测试时会报错,请大家帮帮忙,谢谢!
create or replace package body PG_CallOrg1 is
procedure P_AgentlogOrg
(
FromDate varchar2,
EndDate varchar2,
Fristtime varchar2,
Lasttime varchar2,
AUid varchar2,
Extn varchar2,
AGrp varchar2,
UserIds varchar2,
CallOrg1 out PG_CallOrg1.OrgCur1
)
as
OrgIds varchar2(100);
ChildCount number(10);
FristDate varchar2(50);
LastDate varchar2(50);
sqlstr varchar2(2000);
sqlby varchar2(50);
currFrom date;
--currEnd date;
currDate date;
begin
select org_layer into OrgIds from sys_organization where Org_Id in (select User_Org_Id from UR_USERS where User_Id=UserIds);
select count(Org_Id) into ChildCount from sys_organization where left(org_layer,lengthb(OrgIds))=OrgIds;
if Fristtime is null then
begin
FristDate:=FromDate ||' '||'00:00:00';
end;
else
begin
FristDate:=FromDate ||' '||Fristtime ||':00:00';
end;
end if;
if Lasttime is null then
begin
LastDate:=EndDate ||' '||'00:00:00';
end;
else
begin
LastDate:=EndDate ||' '||Lasttime ||':00:00';
end;
end if;
currFrom:=to_date(FristDate,'yyyy-mm-dd HH24:mi:ss');
currDate:=to_date(LastDate,'yyyy-mm-dd HH24:mi:ss');
if ChildCount=1 then
begin
sqlstr:='
select gname,agentid,logintime,logouttime,extno,loginip from SNO_AGENTLOG
inner join (select user_login_name,user_org_id as orgid from ur_users)ur on cg.agentid=ur.user_login_name
inner join (select org_name as gname,org_id,Org_layer from sys_organization) org on ur.orgid=org.org_id
where logintime>='|| currFrom ||'and logintime<='|| currDate ||'and and org_layer='|| OrgIds;
sqlby:=' ordder by logintime';
if AUid is not null then
begin
sqlstr:=sqlstr ||' and agentid='|| AUid;
end;
end if;
if Extn is not null then
begin
sqlstr:=sqlstr ||' and extno='|| Extn;
end;
end if;
if AGrp is not null then
begin
sqlstr:=sqlstr ||' and left(org_layer,lengthb('|| Agrp ||'))='|| AGrp;
end;
end if;
sqlstr:=sqlstr || sqlby;
open CallOrg1 for --报此处错误!
sqlstr;
end;
else
begin
sqlstr:='
select gname,agentid,logintime,logouttime,extno,loginip from SNO_AGENTLOG
inner join (select user_login_name,user_org_id as orgid from ur_users)ur on cg.agentid=ur.user_login_name
inner join (select org_name as gname,org_id,Org_layer from sys_organization) org on ur.orgid=org.org_id
where logintime>='||currFrom ||'and logintime<='||currDate ||'and and left(org_layer,lengthb('|| OrgIds ||'))='||OrgIds;
sqlby:=' ordder by logintime';
if AUid is not null then
begin
sqlstr:=sqlstr ||' and agentid='|| AUid;
end;
end if;
if Extn is not null then
begin
sqlstr:=sqlstr ||' and extno='|| Extn;
end;
end if;
if AGrp is not null then
begin
sqlstr:=sqlstr ||' and left(org_layer,lengthb('||Agrp ||'))='|| AGrp;
end;
end if;
sqlstr:=sqlstr || sqlby;
open CallOrg1 for --报此处错误!
sqlstr;
end;
end if;
end P_AgentlogOrg;
end PG_CallOrg1;
create or replace package body PG_CallOrg1 is
procedure P_AgentlogOrg
(
FromDate varchar2,
EndDate varchar2,
Fristtime varchar2,
Lasttime varchar2,
AUid varchar2,
Extn varchar2,
AGrp varchar2,
UserIds varchar2,
CallOrg1 out PG_CallOrg1.OrgCur1
)
as
OrgIds varchar2(100);
ChildCount number(10);
FristDate varchar2(50);
LastDate varchar2(50);
sqlstr varchar2(2000);
sqlby varchar2(50);
currFrom date;
--currEnd date;
currDate date;
begin
select org_layer into OrgIds from sys_organization where Org_Id in (select User_Org_Id from UR_USERS where User_Id=UserIds);
select count(Org_Id) into ChildCount from sys_organization where left(org_layer,lengthb(OrgIds))=OrgIds;
if Fristtime is null then
begin
FristDate:=FromDate ||' '||'00:00:00';
end;
else
begin
FristDate:=FromDate ||' '||Fristtime ||':00:00';
end;
end if;
if Lasttime is null then
begin
LastDate:=EndDate ||' '||'00:00:00';
end;
else
begin
LastDate:=EndDate ||' '||Lasttime ||':00:00';
end;
end if;
currFrom:=to_date(FristDate,'yyyy-mm-dd HH24:mi:ss');
currDate:=to_date(LastDate,'yyyy-mm-dd HH24:mi:ss');
if ChildCount=1 then
begin
sqlstr:='
select gname,agentid,logintime,logouttime,extno,loginip from SNO_AGENTLOG
inner join (select user_login_name,user_org_id as orgid from ur_users)ur on cg.agentid=ur.user_login_name
inner join (select org_name as gname,org_id,Org_layer from sys_organization) org on ur.orgid=org.org_id
where logintime>='|| currFrom ||'and logintime<='|| currDate ||'and and org_layer='|| OrgIds;
sqlby:=' ordder by logintime';
if AUid is not null then
begin
sqlstr:=sqlstr ||' and agentid='|| AUid;
end;
end if;
if Extn is not null then
begin
sqlstr:=sqlstr ||' and extno='|| Extn;
end;
end if;
if AGrp is not null then
begin
sqlstr:=sqlstr ||' and left(org_layer,lengthb('|| Agrp ||'))='|| AGrp;
end;
end if;
sqlstr:=sqlstr || sqlby;
open CallOrg1 for --报此处错误!
sqlstr;
end;
else
begin
sqlstr:='
select gname,agentid,logintime,logouttime,extno,loginip from SNO_AGENTLOG
inner join (select user_login_name,user_org_id as orgid from ur_users)ur on cg.agentid=ur.user_login_name
inner join (select org_name as gname,org_id,Org_layer from sys_organization) org on ur.orgid=org.org_id
where logintime>='||currFrom ||'and logintime<='||currDate ||'and and left(org_layer,lengthb('|| OrgIds ||'))='||OrgIds;
sqlby:=' ordder by logintime';
if AUid is not null then
begin
sqlstr:=sqlstr ||' and agentid='|| AUid;
end;
end if;
if Extn is not null then
begin
sqlstr:=sqlstr ||' and extno='|| Extn;
end;
end if;
if AGrp is not null then
begin
sqlstr:=sqlstr ||' and left(org_layer,lengthb('||Agrp ||'))='|| AGrp;
end;
end if;
sqlstr:=sqlstr || sqlby;
open CallOrg1 for --报此处错误!
sqlstr;
end;
end if;
end P_AgentlogOrg;
end PG_CallOrg1;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货