原帖由 newkid 于 2010-4-6 22:59 发表
你确定是卡在赋值这句？
这个东西要放在GROUP BY里面吗？非常怪异，因为这些可能不整除的。
是这样的，原本的SQL是这样的
SELECT page_url AS url,
page_pv AS pv,
page_uniquepv AS uniquepv,
round(page_interval / page_visits, 2) AS avginterval,
100 * round(page_jumptimes / page_pv, 4) AS jumprate,
100 * round(page_exittimes / page_pv, 4) AS exitrate
FROM pd_analy_page
where TRUNC(page_createtime) = sdate
and page_siteid = aiSiteID
group by page_url,
page_pv,
page_uniquepv,
(page_interval / page_visits),
(page_jumptimes / page_pv),
(page_exittimes / page_pv)
然后我准备改造成分页，调用我自己写的一个分页的存储过程
这就是改造的
PROCEDURE proc_content_pageurl(
aiSiteIDIN number, --网站id
mPageSize in number, --每页显示记录数
mPageIndex in number,--当前页
mOrderStyle in number, --排序方式0-升序 1-降序
astrUrl in VARCHAR2, --URL地址
adtStartDate IN VARCHAR2,--起始日期
adtEndDate IN VARCHAR2, --结束日期
mOrderField in varchar2, --排序字段
mTotalRecords out number, --总记录数
mResult out sys_refcursor--记录集
) IS
--sdate date;
--edate date;
mTableName varchar2(50); --表名
mSQL varchar2(1000); --SQL语句
mWhere varchar2(1000); --条件
mGroupField varchar2(50); --Group by字段
mCountFiled varchar2(50); --计算总记录时的Distinct字段
mUrl varchar2(512); --关键字
BEGIN
--sdate:=to_date(adtStartDate,'yyyy-mm-dd');
--edate:=to_date(adtEndDate,'yyyy-mm-dd');
mTableName := 'pd_analy_page';
/*---查询的Select语句---*/
mSQL := ' SELECT page_url AS url
,page_pv AS pv
,page_uniquepv AS uniquepv
,round(page_interval/page_visits,2) AS avginterval
,100*round(page_jumptimes/page_pv,4) AS jumprate
,100*round(page_exittimes/page_pv,4) AS exitrate
FROMpd_analy_page ';
/*---查询的Where条件---*/
mWhere := ' TRUNC(page_createtime) >= to_date('''||adtStartDate||''',''yyyy-mm-dd'')
AND TRUNC(page_createtime)0 then
mUrl := ''''||'%'||astrUrl||'%'||'''';
mWhere := mWhere || ' and page_url like ' || mUrl;
end if;
/*---计算总记录时Distinct的字段---*/
mCountFiled := 'page_url';
/*---Group by的字段---*/
mGroupField := 'page_url
,page_pv
,page_uniquepv,(page_interval/page_visits)
,(page_jumptimes/page_pv)
,(page_exittimes/page_pv)';
/*---调用数据分页---*/
page_select.proc_page_select(mTableName,mSQL,mWhere,mGroupField,mCountFiled,
mPageSize,mPageIndex,mOrderField,mOrderStyle,mTotalRecords,mResult);
END;
我自己写的通用的分页存储过程和函数如下：（附件中是测试数据）
/*----- 获取记录数-----*/
function FUNC_GetCount(mTableName varchar2, mWhere varchar2, mCountFiled varchar2) return number is
i number;
vSql varchar2(1000);
begin
i := 0;
if length(mCountFiled) > 0 then
vSql := 'select count(distinct ' || mCountFiled || ')from ' || mTableName ;
else
vSql := 'select count(*)from ' || mTableName ;
end if;
if length(mWhere) > 0 then
vSql := vSql || ' where ' || mWhere;
end if;
execute IMMEDIATEvSql into i;
return(i);
end;
/*----- 数据分页 ------*/
procedure proc_page_select
(
mTableName in varchar2, --表名
mSQL in varchar2, --SQL语句
mWhere in varchar2,--条件
mGroupField in varchar2, --Group by的字段
mCountFiled in varchar2,
mPageSize in number, --每页显示记录数
mPageIndex in number,--当前页页码
mOrderField in varchar2, --排序字段
mOrderStyle in number, --排序方式0-升序 1-降序
mTotalRecords out number,--总记录数
mResult OUT SYS_REFCURSOR--记录集
) IS
BEGIN
declare
Invalid_Input Exception;
vSql varchar2(1000);
Start_page number;
End_page number;
n_PageIndex number;
n_PageSize number;
begin
--计算总记录数
mTotalRecords := FUNC_GetCount(trim(mTableName), trim(mWhere), trim(mCountFiled));
vSql := mSQL;
--Where条件、Groupby字段、Order字段判断
if length(trim(mWhere)) > 0 then
vSql := vSql || ' where ' || trim(mWhere) ;
end if;
if length(trim(mGroupField)) > 0 then
vSql := vSql || ' Group by ' || trim(mGroupField) ;
end if;
if mOrderField is not null and length(trim(mOrderField)) > 0 then
begin
vSql := vSql || ' order by ' || trim(mOrderField);
if morderStyle > 0 then
vSql := vSql || ' desc ';
end if;
end;
end if;
--判断页面记录数、当前页面
n_PageSize := mPageSize;
if mPageSize0 and n_PageIndex > 1 then
begin
--如果n_pageindex大于实际的页数，则取实际页数
if n_PageIndex > round((mTotalRecords / n_PageSize) + 0.5) then
n_PageIndex := round((mTotalRecords / n_PageSize) + 0.5);
end if;
end;
end if;
Start_page := (n_PageIndex - 1) * n_PageSize + 1;
End_page := n_PageIndex * n_PageSize;
vSql := 'SELECT * FROM (SELECT A.*, rownum r FROM ( ' || vSql || ' ) A WHERE rownum = '|| Start_page;
Open mResult for vSql;
exception
When Invalid_Input Then
open mResult for select null from dual;
end;
END;
|
