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

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;