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

最新推荐文章于 2022-09-22 21:12:34 发布

原创最新推荐文章于 2022-09-22 21:12:34 发布 · 702 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#存储 #date

此存储过程用于根据公司ID及签约日期范围检索客户信息，包括个人资料、地址等详细数据。

create or replace procedure GetCustomerInfo

(

CompanyID in varchar2,AcceptDateStart in varchar2,AcceptDateEnd in varchar2,p_cursor OUT SYS_REFCURSOR

)

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;