Oracle 查詢

後生哥哥發表於2024-08-21
Oracle
<MyCommand Name="日常測評列表" Database="ZDXMGL_SH" CommandType="Text">
        <Parameters>
            <!--<Parameter Name="Sysid" Type="Int32" 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
with tb as (
 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
)
OPEN :cur1 FOR  select * from tb;



Select count(*) into :total FROM tb;

end;

]]></CommandText>
    </MyCommand>

相關文章