无名

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