一个完整的存储过程（系统游标）

create   or   replace   procedure  GetCustomerInfo
(
      CompanyID  in   varchar2 ,AcceptDateStart  in   varchar2 ,AcceptDateEnd  in   varchar2 ,p_cursor OUT SYS_REFCURSOR
)
is
begin
      open  p_cursor  FOR
               select  agentcode  as  CompanyID,appntNO,appntName,appntsex,appntbirthday,appntidtype,appntidno,phone,ZipCode,postaladdress,homeaddress,companyaddress, '' ,ldoccupation.OccupationCode,( select  codename  from  ldcode  where  codetype = ' occupationtype '   and  code  =  ldoccupation.occupationtype)
               from  lccont,lcaddress,ldperson,ldoccupation
               where  managecom  =  CompanyID
               and  signdate  between  to_date(AcceptDateStart, ' YYYY_MM_DD ' )  and  to_date(AcceptDateEnd, ' YYYY_MM_DD ' )
               and  lccont.appntno = ldperson.customerno
               and  ldperson.customerno = lcaddress.customerno
               and  ldperson.occupationcode = ldoccupation.occupationcode
 
               union
       
               select  agentcode,insuredNO,insuredName,appntsex,appntbirthday,appntidtype,appntidno,phone,ZipCode,postaladdress,homeaddress,companyaddress, '' ,ldoccupation.OccupationCode,( select  codename  from  ldcode  where  codetype = ' occupationtype '   and  code  =  ldoccupation.occupationtype)
               from  lccont,lcaddress,ldperson,ldoccupation
               where  managecom  =  CompanyID
               and  signdate  between  to_date(AcceptDateStart, ' YYYY_MM_DD ' )  and  to_date(AcceptDateEnd, ' YYYY_MM_DD ' )
               and  lccont.appntno = ldperson.customerno
               and  ldperson.customerno = lcaddress.customerno
               and  ldperson.occupationcode = ldoccupation.occupationcode;
           
end  GetCustomerInfo;