--修改如下,主要添加rownum = 1
create or replace package guide_day_value2 isprocedure GUIDE_DAY_VALUE(GUIDE_DATE in varchar2);
C_daiy_userpackages out varchar2 );end guide_day_value2;包体create or replace package body guide_day_value2 isprocedure GUIDE_DAY_VALUE(GUIDE_DATE in varchar2,
C_daiy_userpackages out varchar2 )
as
C_daiy_user VARCHAR2(256);
G_daiy_user VARCHAR2(256);
C_month_user VARCHAR2(256);
G_month_user VARCHAR2(256);
C_daiynew_user VARCHAR2(256);
G_daiynew_user VARCHAR2(256);
C_monthnew_user VARCHAR2(256);
G_monthnew_user VARCHAR2(256);
C_daiyleave_user VARCHAR2(256);
G_daiyleave_user VARCHAR2(256);
C_monthleave_user VARCHAR2(256);
G_monthleave_user VARCHAR2(256);
S_MONTH VARCHAR2(30);
begin
S_MONTH:=substr(GUIDE_DATE,1,4);
select guide_value
into C_daiy_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010101'
and GUIDE_DATE=GUIDE_DATE and AREA_ID ='AA'
and rownum = 1; --這兒加
select guide_value
into G_daiy_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010102'
and GUIDE_DATE=GUIDE_DATE and AREA_ID ='AA'
and rownum = 1; --這兒加
select SUM(guide_value)
into C_month_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010201'
and MONTH_ID=S_MONTH and AREA_ID ='AA' ;
select SUM(guide_value)
into G_month_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010202'
and MONTH_ID=S_MONTH and AREA_ID ='AA' ;
select guide_value
into C_daiynew_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010301'
and GUIDE_DATE=GUIDE_DATE and AREA_ID ='AA'
and rownum = 1; --這兒加
select guide_value
into G_daiynew_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010302'
and GUIDE_DATE=GUIDE_DATE and AREA_ID ='AA'
and rownum = 1; --這兒加
select SUM(guide_value)
into C_monthnew_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010401'
and MONTH_ID=S_MONTH and AREA_ID ='AA' ;
select SUM(guide_value)
into G_monthnew_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010402'
and MONTH_ID=S_MONTH and AREA_ID ='AA' ;
select guide_value
into C_daiyleave_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010501'
and GUIDE_DATE=GUIDE_DATE and AREA_ID ='AA'
and rownum = 1; --這兒加
select guide_value
into G_daiyleave_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010502'
and GUIDE_DATE=GUIDE_DATE and AREA_ID ='AA'
and rownum = 1; --這兒加
select SUM(guide_value)
into C_monthleave_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010601'
and MONTH_ID=S_MONTH and AREA_ID ='AA' ;
select SUM(guide_value)
into G_monthleave_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010602'
and MONTH_ID=S_MONTH and AREA_ID ='AA' ;
C_daiy_userpackages:= '当日活动用户数C网'||C_daiy_user||'万人'
||'当日活动用户数G网'||G_daiy_user||'万人'
||'当月活动用户数G网'||C_month_user||'万人'
||'当月活动用户数G网'||G_month_user||'万人'
||'当日新发展用户C网'||C_daiynew_user||'万人'
||'当日新发展用户G网'||G_daiynew_user||'万人'
||'当月累计新发展用户C网'||C_monthnew_user||'万人'
||'当月累计新发展用户G网'||G_monthnew_user||'万人'
||'当日离网用户C网'||C_daiyleave_user||'万人'
||'当日离网用户G网'||G_daiyleave_user||'万人'
||'当月累计离网用户C网'||C_monthleave_user||'万人'
||'当月累计离网用户G网'||G_monthleave_user||'万人';
DBMS_OUTPUT.put_line(C_daiy_userpackages);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;
end guide_day_value2;
create or replace package guide_day_value2 isprocedure GUIDE_DAY_VALUE(GUIDE_DATE in varchar2);
C_daiy_userpackages out varchar2 );end guide_day_value2;包体create or replace package body guide_day_value2 isprocedure GUIDE_DAY_VALUE(GUIDE_DATE in varchar2,
C_daiy_userpackages out varchar2 )
as
C_daiy_user VARCHAR2(256);
G_daiy_user VARCHAR2(256);
C_month_user VARCHAR2(256);
G_month_user VARCHAR2(256);
C_daiynew_user VARCHAR2(256);
G_daiynew_user VARCHAR2(256);
C_monthnew_user VARCHAR2(256);
G_monthnew_user VARCHAR2(256);
C_daiyleave_user VARCHAR2(256);
G_daiyleave_user VARCHAR2(256);
C_monthleave_user VARCHAR2(256);
G_monthleave_user VARCHAR2(256);
S_MONTH VARCHAR2(30);
begin
S_MONTH:=substr(GUIDE_DATE,1,4);
select guide_value
into C_daiy_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010101'
and GUIDE_DATE=GUIDE_DATE and AREA_ID ='AA'
and rownum = 1; --這兒加
select guide_value
into G_daiy_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010102'
and GUIDE_DATE=GUIDE_DATE and AREA_ID ='AA'
and rownum = 1; --這兒加
select SUM(guide_value)
into C_month_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010201'
and MONTH_ID=S_MONTH and AREA_ID ='AA' ;
select SUM(guide_value)
into G_month_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010202'
and MONTH_ID=S_MONTH and AREA_ID ='AA' ;
select guide_value
into C_daiynew_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010301'
and GUIDE_DATE=GUIDE_DATE and AREA_ID ='AA'
and rownum = 1; --這兒加
select guide_value
into G_daiynew_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010302'
and GUIDE_DATE=GUIDE_DATE and AREA_ID ='AA'
and rownum = 1; --這兒加
select SUM(guide_value)
into C_monthnew_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010401'
and MONTH_ID=S_MONTH and AREA_ID ='AA' ;
select SUM(guide_value)
into G_monthnew_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010402'
and MONTH_ID=S_MONTH and AREA_ID ='AA' ;
select guide_value
into C_daiyleave_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010501'
and GUIDE_DATE=GUIDE_DATE and AREA_ID ='AA'
and rownum = 1; --這兒加
select guide_value
into G_daiyleave_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010502'
and GUIDE_DATE=GUIDE_DATE and AREA_ID ='AA'
and rownum = 1; --這兒加
select SUM(guide_value)
into C_monthleave_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010601'
and MONTH_ID=S_MONTH and AREA_ID ='AA' ;
select SUM(guide_value)
into G_monthleave_user
from PORTAL_GUIDE_DAY_VALUE
where DETAIL_ID='010602'
and MONTH_ID=S_MONTH and AREA_ID ='AA' ;
C_daiy_userpackages:= '当日活动用户数C网'||C_daiy_user||'万人'
||'当日活动用户数G网'||G_daiy_user||'万人'
||'当月活动用户数G网'||C_month_user||'万人'
||'当月活动用户数G网'||G_month_user||'万人'
||'当日新发展用户C网'||C_daiynew_user||'万人'
||'当日新发展用户G网'||G_daiynew_user||'万人'
||'当月累计新发展用户C网'||C_monthnew_user||'万人'
||'当月累计新发展用户G网'||G_monthnew_user||'万人'
||'当日离网用户C网'||C_daiyleave_user||'万人'
||'当日离网用户G网'||G_daiyleave_user||'万人'
||'当月累计离网用户C网'||C_monthleave_user||'万人'
||'当月累计离网用户G网'||G_monthleave_user||'万人';
DBMS_OUTPUT.put_line(C_daiy_userpackages);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;
end guide_day_value2;
SQL>var v_bind varchar2;
SQL>exec guide_day_value2.GUIDE_DAY_VALUE("test",:v_bind); <- 把数据输出到 v_bind 变量
SQL>print v_bind; <- 查看返回的值