reate or replace procedure SP_PageQuery
(p_StrQuery in varchar2,
p_CurPage in out integer,
p_PageSize in out Integer,
p_TotalRecords out integer,
p_TotalPages out integer,
v_cur out SYS_REFCURSOR
)
is
v_strsql varchar2(4000):='';
v_FirstRecord integer;
v_LastRecord integer;
begin
if( p_StrQuery is not null) then
v_strsql:= 'select count(*) from ( '|| p_StrQuery ||') where 1=1';
EXECUTE IMMEDIATE v_strsql into p_TotalRecords ;
end if;
if(p_PageSize<0) then
p_PageSize:=0;
end if;
if(mod(p_TotalRecords,p_PageSize)=0) then
p_TotalPages := p_TotalRecords/p_PageSize;
else
p_TotalPages := p_TotalRecords/P_PageSize;
end if;
if(p_CurPage<1) then
p_CurPage :=1;
end if;
if(p_CurPage>p_TotalPages) then
p_CurPage :=p_TotalPages;
end if;
v_FirstRecord :=(p_CurPage - 1) * p_pageSize + 1;
v_LastRecord := p_CurPage*p_PageSize ;
v_strsql:=' with t1 as (select rownum as xh,a.* from ('
|| p_StrQuery ||' ) a) select t1.* from t1 where xh >= '
|| v_FirstRecord ||
' and xh<= '
||v_LastRecord;
open v_cur for v_strsql;
end SP_PageQuery;
一：
SELECT CASE WHEN CONVERT(varchar(10),lastpasstime,120)='2009-12-08' THEN ''
ELSE CONVERT(VARCHAR(20),lastpasstime,120) END a,* FROM users
二：select replace (CONVERT(VARCHAR(20),lastpasstime,120),‘lastpasstime’,'') from users