createorreplaceprocedure GetCustomerInfo ( CompanyID invarchar2,AcceptDateStart invarchar2,AcceptDateEnd invarchar2,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;
create or replace procedure GetCustomerInfo( CompanyID in varchar2,AcceptDateStart in varchar2,AcceptDateEnd in varchar2,p_cursor OUT SYS_REFCURSOR)isbegin open p_cursor FOR se