Oracle 查询

<MyCommand Name="日常测评列表" Database="ZDXMGL_SH" CommandType="Text">
<Parameters>
<Parameter Name="Sysid" Type="Int32" Direction="Input" />
<Parameter Name="ryfl" Type="Varchar2" Direction="Input" />
<Parameter Name="kw" Type="Varchar2" Direction="Input" />
<Parameter Name="page" Type="Int32" Direction="Input" />
<Parameter Name="limit" Type="Int32" Direction="Input" />

<Parameter Name="cur1" Type="RefCursor" Direction="Output" />
<Parameter Name="total" Type="Int32" Direction="Output" />
</Parameters>
<CommandText><![CDATA[
begin
open :cur1 for
select * from (
select t.*, rownum rn from (
Select to_char(s.cjsj,'yyyy') sjnf
,to_char(s.cjsj,'yyyy-MM-dd HH:mi:ss') psrq
,s.userid
,s.username
,u.deptname
,u.jobnamelist
,u.cardid
,(u2.deptname ||'('|| p.username||')') pusername
,s.df
,x.title
FROM t_Evaluation_Score s
Left Join t_evaluation_person p On s.PERSONID =p.sysid
Left Join t_Evaluation x On x.sysid=p.eVALUATIONID
Left Join vsys_user_employeedetail u On s.userid=u.USERID
Left Join vsys_user_employeedetail u2 On p.userid=u2.USERID
where x.sysid=(case when :Sysid =0 then x.sysid when :Sysid>0 then :Sysid end)
and to_char(u.cardid)=(Case When :ryfl is not null Then to_char(:ryfl) When :ryfl is null Then to_char(u.cardid) End)
--and (Case When :kw is not null Then instr( s.username ,:kw)>0 then :kw is null then 1=1 End)
and instr( s.username,(case when :kw is not null then to_char(:kw) when :kw is null then s.username end))>0
) t
where rownum <= :page*:limit
)
where rn > (:page-1)*:limit;

Select count(*) into :total
FROM t_Evaluation_Score s
Left Join t_evaluation_person p On s.PERSONID =p.sysid
Left Join t_Evaluation x On x.sysid=p.eVALUATIONID
Left Join vsys_user_employeedetail u On s.userid=u.USERID
where x.sysid=(case when :Sysid =0 then x.sysid when :Sysid>0 then :Sysid end)
and to_char(u.cardid)=(Case When :ryfl is not null Then to_char(:ryfl) When :ryfl is null Then to_char(u.cardid) End)
and instr( s.username,(case when :kw is not null then to_char(:kw) when :kw is null then s.username end))>0
;
end;

]]></CommandText>
</MyCommand>