SQL

最新推荐文章于 2024-07-11 09:20:32 发布

ZLHZHJ

最新推荐文章于 2024-07-11 09:20:32 发布

阅读量947

点赞数

分类专栏： oracle-SQL 文章标签： website integer domain exception date

本文链接：https://blog.csdn.net/ZLHZHJ/article/details/7719792

版权

oracle-SQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

create or replace package body MG_WEBSITE7 is

--用户查询
procedure Query(i_sitename in varchar2,
i_domain in varchar2,
i_typeid in number,
i_userid in varchar2,
i_tracktimesmin in number,
i_tracktimesmax in number,
i_status in number,
i_mediumid in number,
i_mediumgettimebegin in varchar2,
i_mediumgettimeend in varchar2,
i_pageIndex in number,
i_pageSize in number,
o_count out number,
c_cursor out type_cursor) is
v_minRowNum number := (i_pageIndex + 1) * i_pageSize - i_pageSize + 1;
v_maxRowNum number := (i_pageIndex + 1) * i_pageSize;
begin
open c_cursor for
select track,
websiteid,
sitename,
domain,
typename,
userid,
contactname,
mobilephone,
qq,
mediumgettime,
checkintime,
status
from (select rownum rn,
track,
t.websiteid,
t.sitename,
replace(ltrim(t.domain, ','), ',', '<br/>') domain,
t.typename,
t.userid,
t.contactname,
t.mobilephone,
t.qq,
nvl(to_char(t.mediumgettime, 'yy-mm-dd'), ' ') mediumgettime,
case trunc(sysdate) - trunc(t.checkintime)
when 0 then
to_char(t.checkintime, 'hh24:mi')
when 1 then
'昨天' || to_char(t.checkintime, 'hh24') || '点'
when 2 then
'前天' || to_char(t.checkintime, 'hh24') || '点'
else
to_char(t.checkintime, 'yy-mm-dd')
end checkintime,
t.statusname status
from (select a.tracktimes || '次 ' ||
decode(a.tracknextdate,
'',
'无预约',
'约' || to_char(a.tracknextdate, 'mm-dd')) track,
a.tracknextdate,
a.tracktimes,
a.websiteid,
a.name sitename,
',' || rtrim(replace(replace('http://' ||
replace(trim(a.domain),
'http://',
''),
'http://www.',
''),
'http://',
''),
'/') || e.domain domain,
d.typeid typeid,
d.name typename,
decode(a.webhostid,0, '未合作',
nvl(b.userid, '不存在')) userid,
nvl(c.contactname, ' ') contactname,
nvl(c.mobilephone, ' ') mobilephone,
nvl(c.qq, ' ') qq,
a.mediumid,
a.mediumgettime,
a.checkintime,
a.status,
decode(a.status,
3,
'注销',
8,
'回收站',
9,
'黑名单',
10,
'公海',
'正常') statusname
from ad_website a,
ad_webuser b,
(select websiteid, contactname, mobilephone, qq
from ad_website_contact
where isprimary = 1) c,
ad_webtype d,
(select websiteid,
max(sys_connect_by_path(domain, ',')) domain
from (select websiteid,
rtrim(replace(replace('http://' ||
replace(trim(domain),
'http://',
''),
'http://www.',
''),
'http://',
''),
'/') domain,
(row_number()
over(partition by websiteid order by
websiteid,
domain desc)) numid
from ad_domain)
connect by websiteid = prior websiteid
and numid - 1 = prior numid
group by websiteid) e
where a.webhostid = b.webhostid(+)
and a.websiteid = c.websiteid(+)
and a.sitetype = d.typeid
and a.websiteid = e.websiteid(+)
and a.type = 1

and (a.name like '%' || i_sitename || '%' or
nvl(i_sitename, ' ') = ' ')
/* and (a.domain like '%,' || i_domain || '%' or
nvl(i_domain, ' ') = ' ')*/
and (d.typeid = i_typeid or nvl(i_typeid, 0) = 0)
and (lower(b.userid) = lower(i_userid) or
nvl(i_userid, ' ') = ' ')
and (a.tracktimes >= i_tracktimesmin or
nvl(i_tracktimesmin, 0) = 0)
and (a.tracktimes <= i_tracktimesmax or
nvl(i_tracktimesmax, 0) = 0)
and (a.status = i_status)
and (a.mediumid = i_mediumid or i_status = 10)
and (a.mediumgettime >=
to_date(i_mediumgettimebegin, 'yyyy-mm-dd') or
nvl(i_mediumgettimebegin, ' ') = ' ')
and (a.mediumgettime <= to_date(i_mediumgettimeend, 'yyyy-mm-dd') or
nvl(i_mediumgettimeend, ' ') = ' ')
order by a.tracknextdate,
a.tracktimes,
a.mediumgettime desc,
a.checkintime desc) t
where (t.domain like '%' || i_domain || '%' or
nvl(i_domain, ' ') = ' ')
/* where (t.sitename like '%' || i_sitename || '%' or
nvl(i_sitename, ' ') = ' ')
and (t.domain like '%,' || i_domain || '%' or
nvl(i_domain, ' ') = ' ')
and (t.typeid = i_typeid or nvl(i_typeid, 0) = 0)
and (lower(t.userid) = lower(i_userid) or
nvl(i_userid, ' ') = ' ')
and (t.tracktimes >= i_tracktimesmin or
nvl(i_tracktimesmin, 0) = 0)
and (t.tracktimes <= i_tracktimesmax or
nvl(i_tracktimesmax, 0) = 0)
and (t.status = i_status)
and (t.mediumid = i_mediumid or i_status = 10)
and (t.mediumgettime >=
to_date(i_mediumgettimebegin, 'yyyy-mm-dd') or
nvl(i_mediumgettimebegin, ' ') = ' ')
and (t.mediumgettime <=
to_date(i_mediumgettimeend, 'yyyy-mm-dd') or
nvl(i_mediumgettimeend, ' ') = ' ')*/) s
where s.rn between v_minRowNum and v_maxRowNum order by websiteid ;

--取出总的行数
select count(1)
into o_count
from (select a.tracktimes || '次 ' ||
decode(a.tracknextdate,
'',
'无预约',
'约'|| to_char(a.tracknextdate, 'mm-dd')) track,
a.tracknextdate,
a.tracktimes,
a.websiteid,
a.name sitename,
',' || rtrim(replace(replace('http://' ||
replace(trim(a.domain),
'http://',
''),
'http://www.',
''),
'http://',
''),
'/') || e.domain domain,
d.typeid typeid,
d.name typename,
decode(a.webhostid, 0, '未合作', nvl(b.userid, '不存在')) userid,
nvl(c.contactname, ' ') contactname,
nvl(c.mobilephone, ' ') mobilephone,
nvl(c.qq, ' ') qq,
a.mediumid,
a.mediumgettime,
a.checkintime,
a.status,
decode(a.status,
3,
'注销',
8,
'回收站',
9,
'黑名单',
10,
'公海',
'正常') statusname
from ad_website a,
ad_webuser b,
(select websiteid, contactname, mobilephone, qq
from ad_website_contact
where isprimary = 1) c,
ad_webtype d,
(select websiteid,
max(sys_connect_by_path(domain, ',')) domain
from (select websiteid,
rtrim(replace(replace('http://' ||
replace(trim(domain),
'http://',
''),
'http://www.',
''),
'http://',
''),
'/') domain,
(row_number() over(partition by websiteid
order by websiteid,
domain desc)) numid
from ad_domain)
connect by websiteid = prior websiteid
and numid - 1 = prior numid
group by websiteid) e
where a.webhostid = b.webhostid(+)
and a.websiteid = c.websiteid(+)
and a.sitetype = d.typeid
and a.websiteid = e.websiteid(+)
and a.type = 1
order by a.tracknextdate,
a.tracktimes,
a.mediumgettime desc,
a.checkintime desc) t
where (t.sitename like '%' || i_sitename || '%' or
nvl(i_sitename, ' ') = ' ')
and (t.domain like '%,' || i_domain || '%' or
nvl(i_domain, ' ') = ' ')
and (t.typeid = i_typeid or nvl(i_typeid, 0) = 0)
and (lower(t.userid) = lower(i_userid) or nvl(i_userid, ' ') = ' ')
and (t.tracktimes >= i_tracktimesmin or nvl(i_tracktimesmin, 0) = 0)
and (t.tracktimes <= i_tracktimesmax or nvl(i_tracktimesmax, 0) = 0)
and (t.status = i_status)
and (t.mediumid = i_mediumid or i_status = 10)
and (t.mediumgettime >= to_date(i_mediumgettimebegin, 'yyyy-mm-dd') or
nvl(i_mediumgettimebegin, ' ') = ' ')
and (t.mediumgettime <= to_date(i_mediumgettimeend, 'yyyy-mm-dd') or
nvl(i_mediumgettimeend, ' ') = ' ');
end Query;
--批量回收广告（包括单个广告）
procedure RecycleBatch(i_websiteid in varchar2,
i_mediumid in number,
o_result out varchar2) is
v_id1 VARCHAR2(200) := i_websiteid;
v_id2 VARCHAR2(200) := '';
begin
LOOP
v_id2 := SUBSTR(v_id1, 1, INSTR(v_id1, ',') - 1);
if v_id2 is not null then
update ad_website a set a.status = 8,a.mediumid=i_mediumid where a.websiteid = v_id2;
end if;
v_id1 := SUBSTR(v_id1, INSTR(v_id1, ',') + 1, LENGTH(v_id1));
IF INSTR(v_id1, ',') = 0 THEN
update ad_website a set a.status = 8 ,a.mediumid=i_mediumid where a.websiteid = v_id1;
EXIT;
END IF;
END LOOP;
commit;
o_result := 'OK';
--异常回滚
exception
when others then
rollback;
end RecycleBatch;
--批量列入黑名单
procedure SiteBlackBatch(i_websiteid in varchar2,
i_mediumid in number,
o_result out varchar2) is
v_id1 VARCHAR2(200) := i_websiteid;
v_id2 VARCHAR2(200) := '';
begin
LOOP
v_id2 := SUBSTR(v_id1, 1, INSTR(v_id1, ',') - 1);
if v_id2 is not null then
update ad_website a set a.status = 9,a.mediumid=i_mediumid where a.websiteid = v_id2;
end if;
v_id1 := SUBSTR(v_id1, INSTR(v_id1, ',') + 1, LENGTH(v_id1));
IF INSTR(v_id1, ',') = 0 THEN
update ad_website a set a.status = 9,a.mediumid=i_mediumid where a.websiteid = v_id1;
EXIT;
END IF;
END LOOP;
commit;
o_result := 'OK';
--异常回滚
exception
when others then
rollback;
end SiteBlackBatch;
--批量释放公海
procedure SitePublicBatch(i_websiteid in varchar2, o_result out varchar2) is
v_id1 VARCHAR2(200) := i_websiteid;
v_id2 VARCHAR2(200) := '';
begin
LOOP
v_id2 := SUBSTR(v_id1, 1, INSTR(v_id1, ',') - 1);
if v_id2 is not null then
update ad_website a set a.status = 10,a.mediumid=null where a.websiteid = v_id2;
end if;
v_id1 := SUBSTR(v_id1, INSTR(v_id1, ',') + 1, LENGTH(v_id1));
IF INSTR(v_id1, ',') = 0 THEN
update ad_website a set a.status = 10,a.mediumid=null where a.websiteid = v_id1;
EXIT;
END IF;
END LOOP;
commit;
o_result := 'OK';
--异常回滚
exception
when others then
rollback;
end SitePublicBatch;
--批量恢复正常
procedure SiteNormalBatch(i_websiteid in varchar2,
i_mediumid in number,
o_result out varchar2) is
v_id1 VARCHAR2(200) := i_websiteid;
v_id2 VARCHAR2(200) := '';
begin
LOOP
v_id2 := SUBSTR(v_id1, 1, INSTR(v_id1, ',') - 1);
if v_id2 is not null then
update ad_website a set a.status = 2,a.mediumid=i_mediumid where a.websiteid = v_id2;
end if;
v_id1 := SUBSTR(v_id1, INSTR(v_id1, ',') + 1, LENGTH(v_id1));
IF INSTR(v_id1, ',') = 0 THEN
update ad_website a set a.status = 2,a.mediumid=i_mediumid where a.websiteid = v_id1;
EXIT;
END IF;
END LOOP;
commit;
o_result := 'OK';
--异常回滚
exception
when others then
rollback;
end SiteNormalBatch;
--注销站点
procedure SiteRemove(i_websiteid in varchar2, o_result out varchar2) is
begin
update ad_website a set a.status = 3 where a.websiteid = i_websiteid;
commit;
o_result := 'OK';
--异常回滚
exception
when others then
rollback;
end SiteRemove;
--查询站点的基本信息
Procedure QueryOneBasInfo(i_websiteid in varchar2,
c_cursor out type_cursor) is
Begin
open c_cursor for
select a.checkintime itemDetail_DistributeDate,
a.domain itemDetail_SiteUrl,
a.name itemDetail_SiteName,
b.typeid ITEMDETAIL_SITETYPE,
a.note itemDetail_SiteInfo,
a.address ITEMDETAIL_SITEADDRES,
a.otherinfo ITEMDETAIL_SITEINFOOTHER,
a.dayipweek itemDetail_DayIPWeek,
a.daypvweek itemDetail_DayPVWeek,
a.updatedate itemDetail_UpdateTime,
a.rankingglobal itemDetail_RankingGlobal,
a.rankingchina itemDetail_RankingChina

from ad_website a, ad_webtype b
where a.sitetype = b.typeid
and a.websiteid = i_websiteid
and a.type = 1;
end QueryOneBasInfo;
--查询单个站点的联系方式（根据contactid取前三条）
Procedure QueryOneContact(i_websiteid in varchar2,
c_cursor out type_cursor) is
Begin
open c_cursor for
select c.contactname itemDetail_ContactName,
c.contactid itemDetail_ContactId,
c.isprimary itemDetail_ContactMain,
c.sex itemDetail_ContactSex,
c.position itemDetail_ContactPosition,
c.telephone itemDetail_ContactPhone,
c.qq itemDetail_ContactQQ,
c.email itemDetail_ContactEmail,
c.note itemDetail_ContactOther,
to_char(c.brithday, 'yyyy-mm-dd') itemDetail_ContactBirthday
from ad_website_contact c
where c.websiteid = i_websiteid;
end QueryOneContact;
--查询单个站点的最新跟踪记录
Procedure QueryOneTrackRecord(i_websiteid in varchar2,
c_cursor out type_cursor) is
Begin
open c_cursor for
/* select a.trackdate TrackDate,a.tracktype TrackType,a.trackcontactname TrackContactName,
a.trackcontactattitude TrackContactAttitude,a.trackcontacttype TrackContactType,b.tracknextdate TrackContactNextTime,a.tracknote TRACKNOTE
from DM_WEBTRACK a,ad_website b
where a.websiteid=b.websiteid
and a.websiteid=i_websiteid
and b.type=1;*/

select *
from (select to_char(a.trackdate, 'yyyy-mm-dd') TrackDate,
a.tracktype TrackType,
a.trackcontactname TrackContactName,
a.trackcontactattitude TrackContactAttitude,
a.trackcontacttype TrackContactType,
to_char(b.tracknextdate, 'yyyy-mm-dd') TrackContactNextTime,
a.tracknote TRACKNOTE
from DM_WEBTRACK a, ad_website b
where a.websiteid = b.websiteid
and a.websiteid = i_websiteid
and b.type = 1
order by a.trackdate desc)
where rownum = 1;
end QueryOneTrackRecord;
--查询单个站点的合作广告商
Procedure QueryOneCompetitor(i_websiteid in varchar2,
c_cursor out type_cursor) is
Begin
open c_cursor for
select a.competitors competitors
from dm_competitormems a, ad_website b
where a.websiteid = b.websiteid
and a.websiteid = i_websiteid
and b.type = 1;
end QueryOneCompetitor;

--查询单个站点的跟踪记录
Procedure QueryOneTrackRecords(i_websiteid in varchar2,
c_cursor out type_cursor) is
Begin
open c_cursor for
select rownum rn, t.*
from (select to_char(a.trackdate, 'yyyy-mm-dd') TrackDate,
a.tracktype TrackType,
a.trackcontactname TrackContactName,
a.trackcontactattitude TrackContactAttitude,
a.trackcontacttype TrackContactType,
to_char(a.tracknextdate, 'yyyy-mm-dd') TrackContactNextTime,
a.tracknote TRACKNOTE
from DM_WEBTRACK a, ad_website b
where a.websiteid = b.websiteid
and a.websiteid = i_websiteid
and b.type = 1
order by a.trackdate desc) t
where rownum <= 100;
end QueryOneTrackRecords;
--绑定到对话框中站点类型下拉框
procedure SiteTypeBound(c_cursor out type_cursor) is
begin
open c_cursor for
select a.typeid, a.name from ad_webtype a;
end SiteTypeBound;
--通过对话框修改信息(基本信息)
procedure ModifyBasInfo(i_websiteid in integer,
i_name in varchar2,
i_sitetype in integer,
i_addres in varchar2,
i_note in varchar2,
i_infoother in varchar2,
o_result out varchar2) is
begin
update ad_website a
set a.name = i_name,
a.sitetype = i_sitetype,
a.address = i_addres,
a.note = i_note,
a.otherinfo = i_infoother
where a.websiteid = i_websiteid;
--提交
commit;
o_result := 'OK';
--异常
exception
when others then
--回滚
rollback;
end ModifyBasInfo;
--通过对话框修改信息（跟踪信息）
/* procedure ModifyForTrackRecord(i_websiteid in integer,
i_trackdate in varchar2,
i_sitetype in varchar2,
i_trackcontactname in varchar2,
i_trackcontactattitude in varchar2,
i_trackcontacttype in varchar2,
i_tracknote in varchar2,
i_tracknextdate in varchar2,
o_result out varchar2) is
begin
/ update ad_website a
set a.tracknextdate = to_date(i_tracknextdate, 'yyyy-mm-dd'),
a.TRACKTIMES = a.TRACKTIMES + 1
where a.websiteid = i_websiteid;
insert into DM_WEBTRACK
values
(i_websiteid,
to_date(i_trackdate, 'yyyy-mm-dd'),
i_sitetype,
i_trackcontactname,
i_trackcontactattitude,
i_trackcontacttype,
i_tracknote,
to_date(i_tracknextdate, 'yyyy-mm-dd'));
--提交
commit;
o_result := 'OK';
--异常
exception
when others then
--回滚
rollback;
end ModifyForTrackRecord;*/
--通过对话框修改信息（合作广告商记录信息）
procedure CompetitorIdModify(i_websiteid in integer,
i_competitorid in varchar2,
o_result out varchar2) is
v_strfunc VARCHAR2(200) := i_competitorid;
v_str VARCHAR2(200) := '';
begin
if INSTR(v_strfunc, ',') = 0 then
delete from dm_competitormems a where a.websiteid = i_websiteid;
insert into dm_competitormems values (i_websiteid, v_strfunc);
else
delete from dm_competitormems a where a.websiteid = i_websiteid;
loop
v_str := SUBSTR(v_strfunc, 1, INSTR(v_strfunc, ',') - 1);
insert into dm_competitormems values (i_websiteid, v_str);
v_strfunc := SUBSTR(v_strfunc,
INSTR(v_strfunc, ',') + 1,
LENGTH(v_strfunc));
if INSTR(v_strfunc, ',') = 0 then
insert into dm_competitormems values (i_websiteid, v_strfunc);
exit;
end if;
end loop;
end if;
--提交
commit;
o_result := 'OK';
--异常回滚
exception
when others then
rollback;
end CompetitorIdModify;
--通过对话框修改信息（联系人记录信息）
procedure ContactModify(i_websiteid in integer,
i_contactid in integer,
i_contactname in varchar2,
i_isprimary in integer,
i_sex in integer,
i_position in varchar2,
i_telephone in varchar2,
i_qq in varchar2,
i_email in varchar2,
i_note in varchar2,
i_brithday in varchar2,
o_result out varchar2) is
begin
update ad_website_contact a
set a.contactname = i_contactname,
a.isprimary = i_isprimary,
a.sex = i_sex,
a.position = i_position,
a.telephone = i_telephone,
a.qq = i_qq,
a.email = i_email,
a.note = i_note,
a.brithday = to_date(i_brithday, 'yyyy-mm-dd')
where a.websiteid = i_websiteid
and a.contactid = i_contactid;
--提交
commit;
o_result := 'OK';
--异常回滚
exception
when others then
rollback;
end ContactModify;
--修改站点的信息（基本信息、联系方式记录、跟踪记录、合作广告商记录）
procedure WebSiteInfoModify(i_websiteid in integer,
i_name in varchar2,
i_sitetype in integer,
i_addres in varchar2,
i_note in varchar2,
i_infoother in varchar2,
--更新联系人信息
i_contactid1 in integer,
i_contactname1 in varchar2,
i_isprimary1 in integer,
i_sex1 in integer,
i_position1 in varchar2,
i_telephone1 in varchar2,
i_mobilephone1 in varchar2,
i_qq1 in varchar2,
i_email1 in varchar2,
i_note1 in varchar2,
i_brithday1 in varchar2,

i_contactid2 in integer,
i_contactname2 in varchar2,
i_isprimary2 in integer,
i_sex2 in integer,
i_position2 in varchar2,
i_telephone2 in varchar2,
i_mobilephone2 in varchar2,
i_qq2 in varchar2,
i_email2 in varchar2,
i_note2 in varchar2,
i_brithday2 in varchar2,

i_contactid3 in integer,
i_contactname3 in varchar2,
i_isprimary3 in integer,
i_sex3 in integer,
i_position3 in varchar2,
i_telephone3 in varchar2,
i_mobilephone3 in varchar2,
i_qq3 in varchar2,
i_email3 in varchar2,
i_note3 in varchar2,
i_brithday3 in varchar2,

--修改跟踪信息
i_dateold in varchar2,
i_idold in number,
i_typeold in varchar2,
i_contactnameold in varchar2,
i_contactattitudeold in varchar2,
i_contacttypeold in varchar2,
i_contactnexttimeold in varchar2,
i_noteold in varchar2,
--插入新的跟踪信息
i_trackdate in varchar2,
i_tracktype in varchar2,
i_trackcontactname in varchar2,
i_trackcontactattitude in varchar2,
i_trackcontacttype in varchar2,
i_tracknote in varchar2,
i_tracknextdate in varchar2,
--修改合作广告商信息
i_competitorid in varchar2,
o_result out varchar2

) is
v_strfunc VARCHAR2(200) := i_competitorid;
v_str VARCHAR2(200) := '';
begin



--更新联系人信息修改已经存在的联系人
if i_isprimary1 !=-1 and i_contactname1 is not null and i_contactid1 !=-1
and i_sex1 is not null then
update ad_website_contact a
set a.contactname = i_contactname1,
a.isprimary = i_isprimary1,
a.sex = i_sex1,
a.position = nvl(i_position1,' '),
a.telephone = nvl(i_telephone1,' '),
a.mobilephone = nvl(i_mobilephone1,' '),
a.qq = nvl(i_qq1,' '),
a.email = nvl(i_email1,' '),
a.note = nvl(i_note1,' '),
a.brithday = nvl(to_date(i_brithday1,'yyyy-mm-dd'),to_date('2050-01-01','yyyy-mm-dd'))
where a.websiteid = i_websiteid
and a.contactid = i_contactid1;
end if;

if i_isprimary2 !=-1 and i_contactname2 is not null and i_contactid2 !=-1
and i_sex2 is not null then
update ad_website_contact a
set a.contactname = i_contactname2,
a.isprimary = i_isprimary2,
a.sex = i_sex2,
a.position = nvl(i_position2,' '),
a.telephone = nvl(i_telephone2,' '),
a.mobilephone = nvl(i_mobilephone2,' '),
a.qq = nvl( i_qq2,' '),
a.email = nvl (i_email2,' '),
a.note = nvl (i_note2,' '),
a.brithday = nvl(to_date(i_brithday2,'yyyy-mm-dd'),to_date('2050-01-01','yyyy-mm-dd'))
where a.websiteid = i_websiteid
and a.contactid = i_contactid2;
end if;

if i_isprimary3 !=-1 and i_contactname3 is not null and i_contactid3 !=-1
and i_sex3 is not null then
update ad_website_contact a
set a.contactname = i_contactname3,
a.isprimary = i_isprimary3,
a.sex = i_sex3,
a.position = nvl(i_position3,' '),
a.telephone = nvl(i_telephone3,' '),
a.mobilephone = nvl(i_mobilephone3,' '),
a.qq = nvl(i_qq3,' '),
a.email = nvl(i_email3,' '),
a.note = nvl(i_note3,' '),
a.brithday = nvl(to_date(i_brithday3,'yyyy-mm-dd'),to_date('2050-01-01','yyyy-mm-dd'))
where a.websiteid = i_websiteid
and a.contactid = i_contactid3;
end if;
--添加联系人联系人1存在则修改不存在判断必填项若不为空则插入新的联系人
if i_isprimary1 !=-1 and i_contactname1 is not null and i_contactid1 =-1
and i_sex1 is not null then
insert into ad_website_contact(contactid,websiteid,isprimary,contactname,sex,
position,telephone,mobilephone,qq,email,note,brithday) values(ad_website_contact_seq.nextval,
i_websiteid,i_isprimary1,i_contactname1,i_sex1,nvl(i_position1,' '),nvl(i_telephone1,' '),nvl(i_mobilephone1,' '),nvl(i_qq1,' '),
nvl(i_email1,' '),nvl(i_note1,' '),nvl(to_date(i_brithday1,'yyyy-mm-dd'),to_date('2050-01-01','yyyy-mm-dd')));
end if;
--联系人2存在则修改不存在判断必填项若不为空则插入新的联系人

if i_isprimary2 !=-1 and i_contactname2 is not null and i_contactid2 =-1
and i_sex2 is not null then
insert into ad_website_contact(contactid,websiteid,isprimary,contactname,sex,
position,telephone,mobilephone,qq,email,note,brithday) values(ad_website_contact_seq.nextval,
i_websiteid,i_isprimary2,i_contactname2,i_sex2,nvl(i_position2,' '),nvl(i_telephone2,' '),nvl(i_mobilephone2,' '),nvl(i_qq2,' '),
nvl(i_email2,' '),nvl(i_note2,' '),nvl(to_date(i_brithday2,'yyyy-mm-dd'),to_date('2050-01-01','yyyy-mm-dd')));
end if;
--联系人3存在则修改不存在判断必填项若不为空则插入新的联系人
if i_isprimary3 !=-1 and i_contactname3 is not null and i_contactid3 =-1
and i_sex3 is not null then
insert into ad_website_contact(contactid,websiteid,isprimary,contactname,sex,
position,telephone,mobilephone,qq,email,note,brithday) values(ad_website_contact_seq.nextval,
i_websiteid,i_isprimary3,i_contactname3,i_sex1,nvl(i_position3,' '),nvl(i_telephone3,' '),nvl(i_mobilephone3,' '),nvl(i_qq3,' '),
nvl(i_email3,' '),nvl(i_note3,' '),nvl(to_date(i_brithday3,'yyyy-mm-dd'),to_date('2050-01-01','yyyy-mm-dd')));
end if;
--修改跟踪信息
if i_idold is not null and i_dateold is not null and i_typeold is not null then
update Dm_Webtrack a set a.trackdate=to_date(i_dateold,'yyyy-mm-dd'),a.tracktype=i_typeold,a.trackcontactname=i_contactnameold,
a.trackcontactattitude=i_contactattitudeold,a.trackcontacttype=i_contacttypeold,a.tracknote=i_noteold,
a.tracknextdate=to_date(i_contactnexttimeold,'yyyy-mm-dd') where a.trackid=i_idold;

update ad_website a
set a.tracknextdate = to_date(i_contactnexttimeold, 'yyyy-mm-dd')
where a.websiteid = i_websiteid;
end if;

--插入新的跟踪信息
if i_websiteid is not null and i_trackdate is not null and
i_tracktype is not null then
insert into DM_WEBTRACK
values
(i_websiteid,
to_date(i_trackdate, 'yyyy-mm-dd'),
i_tracktype,
i_trackcontactname,
i_trackcontactattitude,
i_trackcontacttype,
i_tracknote,
to_date(i_tracknextdate, 'yyyy-mm-dd'),
DM_WEBTRACK_SEQ.NEXTVAL
);

--更新基本信息
update ad_website a
set a.name = i_name,
a.sitetype = i_sitetype,
a.address = i_addres,
a.note = i_note,
a.otherinfo = i_infoother,
a.tracknextdate = to_date(i_tracknextdate, 'yyyy-mm-dd'),
a.TRACKTIMES = a.TRACKTIMES + 1
where a.websiteid = i_websiteid;
else
update ad_website a
set a.name = i_name,
a.sitetype = i_sitetype,
a.address = i_addres,
a.note = i_note,
a.otherinfo = i_infoother
where a.websiteid = i_websiteid;
end if;

--修改站点的合作广告商
if v_strfunc is null then
delete from dm_competitormems a where a.websiteid = i_websiteid;
else
delete from dm_competitormems a where a.websiteid = i_websiteid;
if INSTR(v_strfunc, ',') = 0 then
insert into dm_competitormems values (i_websiteid, v_strfunc);
else
loop
v_str := SUBSTR(v_strfunc, 1, INSTR(v_strfunc, ',') - 1);
insert into dm_competitormems values (i_websiteid, v_str);
v_strfunc := SUBSTR(v_strfunc,
INSTR(v_strfunc, ',') + 1,
LENGTH(v_strfunc));
if INSTR(v_strfunc, ',') = 0 then
insert into dm_competitormems values (i_websiteid, v_strfunc);
exit;
end if;
end loop;
end if;
end if;
--提交
commit;
o_result := 'OK';
--异常
exception
when others then
--回滚
rollback;
end WebSiteInfoModify;
--查询站点的信息（基本信息、联系方式记录、跟踪记录、合作广告商记录）
Procedure WebSiteInfoQueryOne(i_websiteid in varchar2,
c_cursor out type_cursor) is
begin
open c_cursor for
select *
from (select a.websiteid,
a.checkintime itemDetail_DistributeDate,
a.domain itemDetail_SiteUrl,
a.name itemDetail_SiteName,
b.typeid ITEMDETAIL_SITETYPE,
a.note itemDetail_SiteInfo,
a.address ITEMDETAIL_SITEADDRES,
a.otherinfo ITEMDETAIL_SITEINFOOTHER,
a.dayipweek itemDetail_DayIPWeek,
a.daypvweek itemDetail_DayPVWeek,
a.updatedate itemDetail_UpdateTime,
a.rankingglobal itemDetail_RankingGlobal,
a.rankingchina itemDetail_RankingChina
from ad_website a, ad_webtype b
where a.sitetype = b.typeid
and a.websiteid = i_websiteid
and a.type = 1) h,

(select *
from (select a.websiteid,
to_char(a.trackdate, 'yyyy-mm-dd') TrackDate,
a.tracktype TrackType,
a.trackcontactname TrackContactName,
a.trackcontactattitude TrackContactAttitude,
a.trackcontacttype TrackContactType,
to_char(a.tracknextdate, 'yyyy-mm-dd') TrackContactNextTime,
a.tracknote TRACKNOTE,
a.trackid TRACKID
from DM_WEBTRACK a, ad_website b
where a.websiteid = b.websiteid
and a.websiteid = i_websiteid
and b.type = 1
order by a.trackid desc)
where rownum = 1) i,

(select websiteid,
ltrim(max(sys_connect_by_path(competitors, '|')), '|') competitors
from (select a.websiteid,
competitors,
(row_number()
over(partition by a.websiteid order by
a.websiteid,
a.competitors desc)) numid
from dm_competitormems a, ad_website b
where a.websiteid = b.websiteid
and a.websiteid = i_websiteid
and b.type = 1)
connect by websiteid = prior websiteid
and numid - 1 = prior numid
group by websiteid) j,

( select rownum rn, t.*
from (

select n.websiteid,
ltrim(n.itemDetail_ContactId,',') itemDetail_ContactId,
ltrim(max(n.itemDetail_ContactName),',') itemDetail_ContactName,
ltrim(max(n.itemDetail_ContactMain),',') itemDetail_ContactMain,
ltrim(max(n.itemDetail_ContactSex),',') itemDetail_ContactSex,
ltrim(max(n.itemDetail_ContactPosition),',') itemDetail_ContactPosition,
ltrim(max(n.itemDetail_ContactPhone),',') itemDetail_ContactPhone,
ltrim(max(n.itemDetail_ContactMobilePhone),',') itemDetail_ContactMobilePhone,
ltrim( max(n.itemDetail_ContactQQ),',') itemDetail_ContactQQ,
ltrim( max(n.itemDetail_ContactEmail),',') itemDetail_ContactEmail,
ltrim( max(n.itemDetail_ContactOther),',') itemDetail_ContactOther,
ltrim( max(n.itemDetail_ContactBirthday),',') itemDetail_ContactBirthday
from (select websiteid,
sys_connect_by_path(contactname, ',') itemDetail_ContactName,
sys_connect_by_path(contactid, ',') itemDetail_ContactId,
sys_connect_by_path(isprimary, ',') itemDetail_ContactMain,
sys_connect_by_path(sex, ',') itemDetail_ContactSex,
sys_connect_by_path(position, ',') itemDetail_ContactPosition,
sys_connect_by_path(telephone, ',') itemDetail_ContactPhone,
sys_connect_by_path(mobilephone, ',') itemDetail_ContactMobilePhone,
sys_connect_by_path(qq, ',') itemDetail_ContactQQ,
sys_connect_by_path(email, ',') itemDetail_ContactEmail,
sys_connect_by_path(note, ',') itemDetail_ContactOther,
sys_connect_by_path(to_char(brithday,'yyyy-mm-dd'), ',') itemDetail_ContactBirthday
from (select e.websiteid,
e.contactname,
e.contactid,
e.isprimary,
e.sex,
e.position,
e.telephone,
e.mobilephone,
e.qq,
e.email,
e.note,
e.brithday,
(row_number()
over(partition by e.websiteid order by
e.contactid,e.websiteid)) numid
from ad_website_contact e
where e.websiteid = i_websiteid)
connect by websiteid = prior websiteid
and numid - 1 = prior numid
start with numid = 1
) n
group by n.websiteid, n.itemDetail_ContactId
order by itemDetail_ContactName desc
) t
where rownum=1) k

where h.websiteid = i.websiteid(+)
and h.websiteid = j.websiteid(+)
and h.websiteid = k.websiteid(+);
end WebSiteInfoQueryOne;
--添加站点
procedure WebSiteAdd(i_sitename in varchar2,
i_sitedomain in varchar2,
i_sitetype in number,
i_mediumid in number,
i_contactman in varchar2,
i_mobilephone in varchar2,
o_result out varchar2) is
sequence number;
temp number;
begin
select count(1) into temp from ad_website a where replace(a.domain,'http://www.','')=replace(i_sitedomain,'http://www.','') ;
if temp<1 then
select ad_website_seq.nextval into sequence from dual;
insert into ad_website(websiteid,name,domain,checkintime,status,webhostid,sitetype,mediumid,MEDIUMGETTIME)
values(sequence,i_sitename,i_sitedomain,sysdate,2,0,i_sitetype,i_mediumid,sysdate);
insert into ad_website_contact(contactid,websiteid,isprimary,contactname,mobilephone)
values (ad_website_contact_seq.nextval,sequence,1,i_contactman,i_mobilephone);
--提交
o_result := 'OK';
else
o_result := '添加的域名与数据库中域名重复，请更改！';
end if;
commit;
--异常
exception
when others then
--回滚
rollback;
end WebSiteAdd;
--更新站点排名（基本信息）
procedure WebSiteRankingModify(i_websiteid in integer,
i_dayipweek in varchar2,
i_daypvweek in varchar2,
i_rankingglobal in varchar2,
i_rankingchina in varchar2,
o_result out varchar2
) is
begin
if i_rankingchina='没有排名数据' or i_rankingglobal='没有排名数据' or i_daypvweek='-2' then
update ad_website a set a.dayipweek=-1,a.daypvweek=-1,
a.rankingglobal=-1,a.rankingchina=-1,a.updatedate=sysdate
where websiteid = i_websiteid;
else
update ad_website a set a.dayipweek=i_dayipweek,a.daypvweek=i_daypvweek,
a.rankingglobal=i_rankingglobal,a.rankingchina=i_rankingchina,a.updatedate=sysdate
where websiteid = i_websiteid;
end if;
--提交
commit;
o_result := 'OK';
--异常回滚
exception
when others then
rollback;
end WebSiteRankingModify;
--获取媒介的登陆ID
procedure MediumIdGet(i_userid in varchar2,o_result out number) is
begin
select a.roleid into o_result from mg_users a where a.userid=i_userid;
commit;
exception
when others then
rollback;
end MediumIdGet;
--媒介获取未分配的站点
procedure WebSiteObtain(i_mediumid in number,o_result out varchar2) is
intSection number;
i int:=1;
temp number:=0;
begin
select (trunc(sysdate) -trunc(max(MEDIUMGETTIME))) into temp from ad_website a where a.mediumid=i_mediumid;
if temp>=1 or temp is null then
--先求出未分配总的行数
select (count(1)/10) into intSection from ad_website a where a.mediumid is null;
--取每个区间的10行
for i in 1..10 loop
update ad_website a
set a.mediumid=i_mediumid,a.MEDIUMGETTIME=sysdate,a.status=2
where a.websiteid in
(select websiteid from

(select * from
(select * from
(select rownum rn, t.*
from (select websiteid
from ad_website a where a.mediumid is null order by a.rankingglobal desc) t) s
where s.rn between (i-1)*intSection+1 and i*intSection) n
ORDER BY dbms_random.value)
where rownum<=10);
end loop;
o_result:='OK';
else
o_result:='您今天已经获取过啦，请明天再试吧';
end if;
commit;
exception
when others then
rollback;
end WebSiteObtain;

end MG_WEBSITE7;

select * from
(
select accesstime,websitename,placeid,outid,visitorurl,row_number() over(partition by placeid order by accesstime) total from
(
select distinct * from aaaa_visitorurl_lm
where accesstime = to_date('2012-3-5','yyyy-mm-dd')
and placeid in (142458,140630)
order by placeid,outid,visitorurl
)
)
where total<=5

select videoid, wm_concat(c_name)
from pmh_video_category t6, pmh_category_config t7
where t6.cid = t7.cid
and t6.videoid = 33898148
group by videoid

//随机

select dbms_random.value() random, mediumid,mediumname,qq
from ad_union_medium a where a.status=1 and a.qq is not null and rownum<10 order by random;

select accesstime,
                                   c.placeid,
                                   e.name as webname,
                                   nvl(sum(decode(a.pointid,
                                                  -1,
                                                  decode(a.kindtechid, 21, a.ipcnt, 0),
                                                  0)),
                                       0) as clickip,
                                   nvl(sum(decode(a.pointid,
                                                  -2,
                                                  decode(a.kindtechid, 21, a.ipcnt, 0),
                                                  0)),
                                       0) as showip,
                                   nvl(sum(case
                                             when d.name like '%注册%' then
                                              decode(a.kindtechid, 31, ipcnt, 0)
                                           end),
                                       0) as regcnt,
                                   nvl(sum(case
                                             when d.name like '%二次点击%' then
                                              decode(a.kindtechid, 21, ipcnt, 0)
                                           end),
                                       0) as secclick,
                                   nvl(sum(case
                                             when d.name like '%页面到达%' then
                                              decode(a.kindtechid, 21, ipcnt, 0)
                                           end),
                                       0) as arrivalip
                              from ad_accessrec a
                             left join ad_adset b on a.adid = b.adid
                              left join ad_monpoint d on a.pointid = d.pointid
                             /* left join ad_statkind f on a.pointid = f.pointid
                                                     and a.adid = f.adid
                                                     and a.kindtechid = f.kindtechid*/
                             inner join ad_place c on a.placeid = c.placeid
                             inner join ad_website e on c.websiteid = e.websiteid
                             inner join ad_webuser f on e.webhostid=f.webhostid
                             where (c.placeid = i_placeid or nvl(i_placeid,' ')=' ')
                               and (accesstime <= to_date(i_timestop,'yyyy-mm-dd') /*or nvl(i_timestop,' ')=' '*/)
                               and (accesstime >= to_date(i_timestart,'yyyy-mm-dd') /* or nvl(i_timestart,' ')=' '*/)
                               and (f.RECOMMENDID=i_recommendid or i_recommendid=-1)
                             group by accesstime, c.placeid,e.name