create or replace procedure test_static_cursor is
/*定义一个公共实体，根据实际给实体赋值，并将实体插入表*/
type streport is record(
REP_ID VARCHAR2(32), --Y 主键id
REP_OWNERID VARCHAR2(32), --Y 渠道id
REP_OWNERNAME VARCHAR2(32), --Y 渠道名称
REP_PARKID VARCHAR2(32), --Y 景区id
REP_PARKNAME VARCHAR2(32), --Y 景区名称
REP_ORDERNO VARCHAR2(32), --Y 订单号
REP_ITEMID VARCHAR2(32), --Y 订单明细
REP_THIRDORDERNO VARCHAR2(32), --Y 第三方订单号
REP_PROID VARCHAR2(22), --Y 产品id
REP_PRONAME VARCHAR2(32), --Y 产品名称
REP_CLID VARCHAR2(22), --Y 产品类型id
REP_CLNAME VARCHAR2(32), --Y 产品类型名称
REP_PRICE NUMBER(8,2), --Y 单价
REP_NUMBER NUMBER(8), --Y 数量
REP_CHNUM NUMBER(8), --Y 核销数量
REP_RFDNUM NUMBER(8), --Y 退票数量
REP_CHTLNUM NUMBER(8), --Y 已核销总数量
REP_RFDTLNUM NUMBER(8), --Y 已退总数量
REP_NAME VARCHAR2(32), --Y 游客姓名
REP_PHONE VARCHAR2(32), --Y 游客手机号
REP_CARDNO VARCHAR2(25), --Y 游客身份证号
REP_CODE VARCHAR2(20), --Y 串码
REP_SDATE DATE, --Y 有效开始日期
REP_EDATE DATE, --Y 有效结束日期
REP_PAYTIME DATE, --Y 支付日期
REP_PTYPE VARCHAR2(2), --Y 支付类型
REP_PNAME VARCHAR2(21), --Y 支付类型名称
REP_REMARK VARCHAR2(300), --Y 订单备注
REP_OPERATION VARCHAR2(2), --Y 业态
REP_ORDERTYPE NUMBER(1), --Y 订单类型 0散客 1 团体
REP_ORDERBUS NUMBER(1), --Y 订单来源( 0官网、 1微信、2OTA、3旅行社、4全民营销)
REP_CDATE DATE, --Y 创建时间/核销/退票
REP_FWDDATE DATE --Y 结转时间
);
---根据自定义数据类型创建一个集合
TYPE c_user_array IS TABLE OF streport INDEX BY BINARY_INTEGER;
---集合对象
user_array c_user_array;
---数据对象
user streport;--存储值的实体
smpno varchar2(20);--插入表主键ID
P_dorder t_biz_dorder%ROWTYPE;--订单表
P_lxscomu co_gcpdatacomu%ROWTYPE;--旅行社
P_parkcomu co_gcpdatacomu%ROWTYPE;--景区
P_product co_product%ROWTYPE;--产品表
P_productclass co_product_class%ROWTYPE;--产品类别表
p_settlement co_settlement%ROWTYPE;--账户表
refundnum NUMBER(8);--退票总数量
smpnum NUMBER(10);--插入表主键ID
is_haverefnum NUMBER(8);--是否存在退票数量
begin
smpnum:=1;
for ditems in (select * from t_biz_ditems where orderitems_reportflag='0'
and orderitems_id in('F0157088301610182','F0156965501608948','F0156900901608300','F0156400001603257','F0155938201598578') order by orderitems_id asc) loop
select * into P_dorder from t_biz_dorder where t_biz_dorder.order_id=ditems.ORDER_ID;
select * into P_lxscomu from co_gcpdatacomu where syscpid =P_dorder.Order_Ownerid;
select * into P_parkcomu from co_gcpdatacomu where syscpid =ditems.orderitems_comid;
select * into P_product from co_product where pro_id =ditems.orderitems_productid;
if(P_product.Pro_Class is not null) then
select * into P_productclass from co_product_class where proclass_id =P_product.Pro_Class;
end if;
if(P_dorder.Order_Paytype is not null) then
select * into p_settlement from co_settlement where sttmid =P_dorder.Order_Paytype;
end if;
select sum(refund_num) into refundnum from order_refund where refund_status='2' and refund_code=ditems.orderitems_code;
/*散客订单表赋值开始 */
--pro_sys_get_maxno('t_biz_streport',8,smpno);
user.rep_id:= smpnum;
user.rep_ownerid:=P_dorder.order_ownerid;
user.rep_ownername:=P_lxscomu.Syscpname;
user.rep_parkid:=ditems.orderitems_comid;
user.rep_parkname:=P_parkcomu.Syscpname;
user.rep_orderno:=ditems.order_no;
user.rep_itemid:=ditems.orderitems_id;
user.rep_thirdorderno:=ditems.dorderitems_thirdpartyno;
user.rep_proid:=ditems.orderitems_productid;
user.rep_proname:=ditems.orderitems_productname;
user.rep_clid:=P_product.pro_class;
user.rep_clname:='';
if(P_productclass.proclass_name is not null) then
user.rep_clname:=P_productclass.proclass_name;
end if;
user.rep_price:=ditems.orderitems_price;
user.rep_number:=ditems.orderitems_count;
user.rep_chnum:=0;
user.rep_rfdnum:=0;
user.rep_chtlnum:=ditems.orderitems_checktotal;
user.rep_code:=ditems.orderitems_code;
user.rep_rfdtlnum:=refundnum;
user.rep_name:=ditems.orderitems_name;
user.rep_phone:=ditems.orderitems_phone;
user.rep_cardno:=ditems.orderitems_icno;
user.rep_sdate:=ditems.orderitems_sdate;
user.rep_edate:=ditems.orderitems_edate;
user.rep_paytime:=P_dorder.order_paytime;
user.rep_ptype:=P_dorder.Order_Paytype;
user.rep_pname:='';
if(p_settlement.sttmname is not null) then
user.rep_pname:=p_settlement.sttmname;
end if;
user.rep_remark:=P_dorder.order_remark;
user.rep_operation:=P_product.pro_type;
user.rep_ordertype:=0;
user.rep_orderbus:=P_dorder.order_ditch;
user.rep_cdate:=P_dorder.order_createtime;
user.rep_fwddate:=sysdate;
/*散客订单表赋值结束 */
/**按照下单时间插入数据开始*
insert into t_biz_streport
(rep_id, rep_ownerid, rep_ownername, rep_parkid, rep_parkname, rep_orderno, rep_itemid, rep_thirdorderno, rep_proid, rep_proname, rep_clid, rep_clname, rep_price, rep_number, rep_chnum, rep_rfdnum, rep_chtlnum, rep_rfdtlnum, rep_name, rep_phone, rep_cardno, rep_code, rep_sdate, rep_edate, rep_paytime, rep_ptype, rep_pname, rep_remark, rep_operation, rep_ordertype, rep_orderbus, rep_cdate, rep_fwddate)
values
(user.rep_id, user.rep_ownerid, user.rep_ownername, user.rep_parkid, user.rep_parkname, user.rep_orderno, user.rep_itemid, user.rep_thirdorderno, user.rep_proid, user.rep_proname, user.rep_clid, user.rep_clname, user.rep_price, user.rep_number, user.rep_chnum, user.rep_rfdnum, user.rep_chtlnum, user.rep_rfdtlnum, user.rep_name, user.rep_phone, user.rep_cardno, user.rep_code, user.rep_sdate, user.rep_edate, user.rep_paytime, user.rep_ptype, user.rep_pname, user.rep_remark, user.rep_operation, user.rep_ordertype, user.rep_orderbus, user.rep_cdate, user.rep_fwddate);
if (SQL%ROWCOUNT < 1) then
update t_biz_ditems set orderitems_reportflag='1' where orderitems_id =ditems.orderitems_id;
end if;
*按照下单时间插入数据结束**/
user_array(user.rep_id):=user;
smpnum:=smpnum+1;
/*定义一个公共实体，根据实际给实体赋值，并将实体插入表*/
type streport is record(
REP_ID VARCHAR2(32), --Y 主键id
REP_OWNERID VARCHAR2(32), --Y 渠道id
REP_OWNERNAME VARCHAR2(32), --Y 渠道名称
REP_PARKID VARCHAR2(32), --Y 景区id
REP_PARKNAME VARCHAR2(32), --Y 景区名称
REP_ORDERNO VARCHAR2(32), --Y 订单号
REP_ITEMID VARCHAR2(32), --Y 订单明细
REP_THIRDORDERNO VARCHAR2(32), --Y 第三方订单号
REP_PROID VARCHAR2(22), --Y 产品id
REP_PRONAME VARCHAR2(32), --Y 产品名称
REP_CLID VARCHAR2(22), --Y 产品类型id
REP_CLNAME VARCHAR2(32), --Y 产品类型名称
REP_PRICE NUMBER(8,2), --Y 单价
REP_NUMBER NUMBER(8), --Y 数量
REP_CHNUM NUMBER(8), --Y 核销数量
REP_RFDNUM NUMBER(8), --Y 退票数量
REP_CHTLNUM NUMBER(8), --Y 已核销总数量
REP_RFDTLNUM NUMBER(8), --Y 已退总数量
REP_NAME VARCHAR2(32), --Y 游客姓名
REP_PHONE VARCHAR2(32), --Y 游客手机号
REP_CARDNO VARCHAR2(25), --Y 游客身份证号
REP_CODE VARCHAR2(20), --Y 串码
REP_SDATE DATE, --Y 有效开始日期
REP_EDATE DATE, --Y 有效结束日期
REP_PAYTIME DATE, --Y 支付日期
REP_PTYPE VARCHAR2(2), --Y 支付类型
REP_PNAME VARCHAR2(21), --Y 支付类型名称
REP_REMARK VARCHAR2(300), --Y 订单备注
REP_OPERATION VARCHAR2(2), --Y 业态
REP_ORDERTYPE NUMBER(1), --Y 订单类型 0散客 1 团体
REP_ORDERBUS NUMBER(1), --Y 订单来源( 0官网、 1微信、2OTA、3旅行社、4全民营销)
REP_CDATE DATE, --Y 创建时间/核销/退票
REP_FWDDATE DATE --Y 结转时间
);
---根据自定义数据类型创建一个集合
TYPE c_user_array IS TABLE OF streport INDEX BY BINARY_INTEGER;
---集合对象
user_array c_user_array;
---数据对象
user streport;--存储值的实体
smpno varchar2(20);--插入表主键ID
P_dorder t_biz_dorder%ROWTYPE;--订单表
P_lxscomu co_gcpdatacomu%ROWTYPE;--旅行社
P_parkcomu co_gcpdatacomu%ROWTYPE;--景区
P_product co_product%ROWTYPE;--产品表
P_productclass co_product_class%ROWTYPE;--产品类别表
p_settlement co_settlement%ROWTYPE;--账户表
refundnum NUMBER(8);--退票总数量
smpnum NUMBER(10);--插入表主键ID
is_haverefnum NUMBER(8);--是否存在退票数量
begin
smpnum:=1;
for ditems in (select * from t_biz_ditems where orderitems_reportflag='0'
and orderitems_id in('F0157088301610182','F0156965501608948','F0156900901608300','F0156400001603257','F0155938201598578') order by orderitems_id asc) loop
select * into P_dorder from t_biz_dorder where t_biz_dorder.order_id=ditems.ORDER_ID;
select * into P_lxscomu from co_gcpdatacomu where syscpid =P_dorder.Order_Ownerid;
select * into P_parkcomu from co_gcpdatacomu where syscpid =ditems.orderitems_comid;
select * into P_product from co_product where pro_id =ditems.orderitems_productid;
if(P_product.Pro_Class is not null) then
select * into P_productclass from co_product_class where proclass_id =P_product.Pro_Class;
end if;
if(P_dorder.Order_Paytype is not null) then
select * into p_settlement from co_settlement where sttmid =P_dorder.Order_Paytype;
end if;
select sum(refund_num) into refundnum from order_refund where refund_status='2' and refund_code=ditems.orderitems_code;
/*散客订单表赋值开始 */
--pro_sys_get_maxno('t_biz_streport',8,smpno);
user.rep_id:= smpnum;
user.rep_ownerid:=P_dorder.order_ownerid;
user.rep_ownername:=P_lxscomu.Syscpname;
user.rep_parkid:=ditems.orderitems_comid;
user.rep_parkname:=P_parkcomu.Syscpname;
user.rep_orderno:=ditems.order_no;
user.rep_itemid:=ditems.orderitems_id;
user.rep_thirdorderno:=ditems.dorderitems_thirdpartyno;
user.rep_proid:=ditems.orderitems_productid;
user.rep_proname:=ditems.orderitems_productname;
user.rep_clid:=P_product.pro_class;
user.rep_clname:='';
if(P_productclass.proclass_name is not null) then
user.rep_clname:=P_productclass.proclass_name;
end if;
user.rep_price:=ditems.orderitems_price;
user.rep_number:=ditems.orderitems_count;
user.rep_chnum:=0;
user.rep_rfdnum:=0;
user.rep_chtlnum:=ditems.orderitems_checktotal;
user.rep_code:=ditems.orderitems_code;
user.rep_rfdtlnum:=refundnum;
user.rep_name:=ditems.orderitems_name;
user.rep_phone:=ditems.orderitems_phone;
user.rep_cardno:=ditems.orderitems_icno;
user.rep_sdate:=ditems.orderitems_sdate;
user.rep_edate:=ditems.orderitems_edate;
user.rep_paytime:=P_dorder.order_paytime;
user.rep_ptype:=P_dorder.Order_Paytype;
user.rep_pname:='';
if(p_settlement.sttmname is not null) then
user.rep_pname:=p_settlement.sttmname;
end if;
user.rep_remark:=P_dorder.order_remark;
user.rep_operation:=P_product.pro_type;
user.rep_ordertype:=0;
user.rep_orderbus:=P_dorder.order_ditch;
user.rep_cdate:=P_dorder.order_createtime;
user.rep_fwddate:=sysdate;
/*散客订单表赋值结束 */
/**按照下单时间插入数据开始*
insert into t_biz_streport
(rep_id, rep_ownerid, rep_ownername, rep_parkid, rep_parkname, rep_orderno, rep_itemid, rep_thirdorderno, rep_proid, rep_proname, rep_clid, rep_clname, rep_price, rep_number, rep_chnum, rep_rfdnum, rep_chtlnum, rep_rfdtlnum, rep_name, rep_phone, rep_cardno, rep_code, rep_sdate, rep_edate, rep_paytime, rep_ptype, rep_pname, rep_remark, rep_operation, rep_ordertype, rep_orderbus, rep_cdate, rep_fwddate)
values
(user.rep_id, user.rep_ownerid, user.rep_ownername, user.rep_parkid, user.rep_parkname, user.rep_orderno, user.rep_itemid, user.rep_thirdorderno, user.rep_proid, user.rep_proname, user.rep_clid, user.rep_clname, user.rep_price, user.rep_number, user.rep_chnum, user.rep_rfdnum, user.rep_chtlnum, user.rep_rfdtlnum, user.rep_name, user.rep_phone, user.rep_cardno, user.rep_code, user.rep_sdate, user.rep_edate, user.rep_paytime, user.rep_ptype, user.rep_pname, user.rep_remark, user.rep_operation, user.rep_ordertype, user.rep_orderbus, user.rep_cdate, user.rep_fwddate);
if (SQL%ROWCOUNT < 1) then
update t_biz_ditems set orderitems_reportflag='1' where orderitems_id =ditems.orderitems_id;
end if;
*按照下单时间插入数据结束**/
user_array(user.rep_id):=user;
smpnum:=smpnum+1;
for ticketnum in (select * from twb_ticketnum where twb_ticketnum.tnum_code =ditems.orderitems_code) loop
/*散客核销赋值开始 */
--pro_sys_get_maxno('t_biz_streport',8,smpno);
user.rep_id:= smpnum;
user.rep_number:=0;
user.rep_chnum:=ticketnum.tnum_num;
user.rep_rfdnum:=0;
user.rep_cdate:=ticketnum.tnum_date;
user.rep_fwddate:=sysdate;
/*散客核销赋值结束 */
/* *按照核销时间插入数据开始*
insert into t_biz_streport
(rep_id, rep_ownerid, rep_ownername, rep_parkid, rep_parkname, rep_orderno, rep_itemid, rep_thirdorderno, rep_proid, rep_proname, rep_clid, rep_clname, rep_price, rep_number, rep_chnum, rep_rfdnum, rep_chtlnum, rep_rfdtlnum, rep_name, rep_phone, rep_cardno, rep_code, rep_sdate, rep_edate, rep_paytime, rep_ptype, rep_pname, rep_remark, rep_operation, rep_ordertype, rep_orderbus, rep_cdate, rep_fwddate)
values
(user.rep_id, user.rep_ownerid, user.rep_ownername, user.rep_parkid, user.rep_parkname, user.rep_orderno, user.rep_itemid, user.rep_thirdorderno, user.rep_proid, user.rep_proname, user.rep_clid, user.rep_clname, user.rep_price, user.rep_number, user.rep_chnum, user.rep_rfdnum, user.rep_chtlnum, user.rep_rfdtlnum, user.rep_name, user.rep_phone, user.rep_cardno, user.rep_code, user.rep_sdate, user.rep_edate, user.rep_paytime, user.rep_ptype, user.rep_pname, user.rep_remark, user.rep_operation, user.rep_ordertype, user.rep_orderbus, user.rep_cdate, user.rep_fwddate);
*按照核销时间插入数据结束**/
user_array(user.rep_id):=user;
smpnum:=smpnum+1;
end loop;
select count(1) into is_haverefnum from order_refund where order_refund.refund_code =ditems.orderitems_code and order_refund.refund_status='2';
if(is_haverefnum>0) then
for refund in (select * from order_refund where order_refund.refund_code =ditems.orderitems_code and order_refund.refund_status='2') loop
/*散客退票赋值开始 */
--pro_sys_get_maxno('t_biz_streport',8,smpno);
user.rep_id:= smpnum;
user.rep_number:=0;
user.rep_chnum:=0;
user.rep_rfdnum:=refund.refund_num;
user.rep_cdate:=refund.refund_etime;
user.rep_fwddate:=sysdate;
/*散客退票赋值结束 */
/* *按照退票时间插入数据开始*
insert into t_biz_streport
(rep_id, rep_ownerid, rep_ownername, rep_parkid, rep_parkname, rep_orderno, rep_itemid, rep_thirdorderno, rep_proid, rep_proname, rep_clid, rep_clname, rep_price, rep_number, rep_chnum, rep_rfdnum, rep_chtlnum, rep_rfdtlnum, rep_name, rep_phone, rep_cardno, rep_code, rep_sdate, rep_edate, rep_paytime, rep_ptype, rep_pname, rep_remark, rep_operation, rep_ordertype, rep_orderbus, rep_cdate, rep_fwddate)
values
(user.rep_id, user.rep_ownerid, user.rep_ownername, user.rep_parkid, user.rep_parkname, user.rep_orderno, user.rep_itemid, user.rep_thirdorderno, user.rep_proid, user.rep_proname, user.rep_clid, user.rep_clname, user.rep_price, user.rep_number, user.rep_chnum, user.rep_rfdnum, user.rep_chtlnum, user.rep_rfdtlnum, user.rep_name, user.rep_phone, user.rep_cardno, user.rep_code, user.rep_sdate, user.rep_edate, user.rep_paytime, user.rep_ptype, user.rep_pname, user.rep_remark, user.rep_operation, user.rep_ordertype, user.rep_orderbus, user.rep_cdate, user.rep_fwddate);
*按照退票时间插入数据结束**/
user_array(user.rep_id):=user;
smpnum:=smpnum+1;
end loop;
--else
/*看结转表是否存在退票数据，如果存在修改当前所有明细记录保持一致*/
end if;
begin
for v_counter in 1 .. user_array.count loop
pro_sys_get_maxno('t_biz_streport',8,smpno);
insert into t_biz_streport
(rep_id, rep_ownerid, rep_ownername, rep_parkid, rep_parkname, rep_orderno, rep_itemid, rep_thirdorderno, rep_proid, rep_proname, rep_clid, rep_clname, rep_price, rep_number, rep_chnum, rep_rfdnum, rep_chtlnum, rep_rfdtlnum, rep_name, rep_phone, rep_cardno, rep_code, rep_sdate, rep_edate, rep_paytime, rep_ptype, rep_pname, rep_remark, rep_operation, rep_ordertype, rep_orderbus, rep_cdate, rep_fwddate)
values
(smpno, user_array(v_counter).rep_ownerid, user_array(v_counter).rep_ownername, user_array(v_counter).rep_parkid, user_array(v_counter).rep_parkname, user_array(v_counter).rep_orderno, user_array(v_counter).rep_itemid, user_array(v_counter).rep_thirdorderno, user_array(v_counter).rep_proid, user_array(v_counter).rep_proname, user_array(v_counter).rep_clid, user_array(v_counter).rep_clname, user_array(v_counter).rep_price, user_array(v_counter).rep_number, user_array(v_counter).rep_chnum, user_array(v_counter).rep_rfdnum, user_array(v_counter).rep_chtlnum, user_array(v_counter).rep_rfdtlnum, user_array(v_counter).rep_name, user_array(v_counter).rep_phone, user_array(v_counter).rep_cardno, user_array(v_counter).rep_code, user_array(v_counter).rep_sdate, user_array(v_counter).rep_edate, user_array(v_counter).rep_paytime, user_array(v_counter).rep_ptype, user_array(v_counter).rep_pname, user_array(v_counter).rep_remark, user_array(v_counter).rep_operation, user_array(v_counter).rep_ordertype, user_array(v_counter).rep_orderbus, user_array(v_counter).rep_cdate, user_array(v_counter).rep_fwddate);
if (SQL%ROWCOUNT > 0) then
update t_biz_ditems set orderitems_reportflag='1' where orderitems_id =ditems.orderitems_id;
end if;
end loop;
end;
end loop;
end;