Oracle怎样保存表,[数据库]oracle从各个表取得数据保存到另一个表

[数据库]oracle从各个表取得数据保存到另一个表

0 2015-06-25 00:00:06

从各个表中取得数据保存另一个表中：

CREATE VIEW

PARAMETER_view AS

WITH

tall AS

(

SELECT

p.PI_NO,--产品序列号

p.SERIALNO,--产品编号

p.PI_NAME,--产品名称

p. PI_START_DATE,--产品起息日

p.PI_END_DATE,--产品期日期

p.PI_CUSTOMER_YIELD/100 PI_CUSTOMER_YIELD, --产品收益率

a.AI_NO, --资产序列号

a.CAS_ASSETS_NA,--资产的名称

a.DONO,--资产的编码

a.CAS_ISSUE_SUM,--资产发行金额

a.CAS_YIELD_RATE/100 CAS_YIELD_RATE, --资产收益率

a.CAS_VALUE_DATE , --资产起息日

a.CAS_DUE_DATE , --资产到期日期

NVL(FeeNum,0) FeeNum,--其他费用

NVL(FeeRate,0) FeeRate,--其他费率

NVL(fmfee,0) fmfee, --累计浮动管理费

NVL( fmfee3/100,0) fmfee3, --浮动管理费

NVL(ct.FEERATIO/100,0) FEERATIO, --产品的托管费

NVL( cg.cgrate/100,0) cgrate ， --产品的管理费

NVL( atrate/100,0) atrate，--资产的托管费

NVL( agrate/100,0) agrate ,--资产的管理费

NVL( cgrates/100,0) cgrates ,--产品的投顾费

NVL( agrates/100,0) agrates ,--资产的投顾费

gl_date， --交易日期

floor(SYSDATE-to_date(PI_START_DATE,'yyyy-mm-dd hh24:mi:ss')) pflday，--产品累加天数

floor(SYSDATE-to_date(CAS_VALUE_DATE,'yyyy-mm-dd hh24:mi:ss')) aflday--资产累加天数

FROM

GL_PRODUCTASSETS g

LEFT JOIN

Product_info p

ON

g.PBS_MAINPRO_NO=p.PI_NO

LEFT JOIN

ASSETS_INFO a

ON

g.CAS_ASSETS_NU=a.AI_NO

LEFT JOIN

(

SELECT

productid,

SUM(FMFEE) fmfee

FROM

PRODUCT_FMFEE

GROUP BY

productid ) f

ON

g.PBS_MAINPRO_NO=f.PRODUCTID

LEFT JOIN

(

SELECT

FEERATIO fmfee3,

FEEOBJECTNO

FROM

PRODUCT_COST_INFO

WHERE

FEETYPE='浮动管理费' ) f2

ON

p.PI_NO=f2.FEEOBJECTNO

LEFT JOIN

(

SELECT

*

FROM

PRODUCT_COST_INFO

WHERE

feeobjecttype='Product'

AND FEETYPE='托管费') ct

ON

p.PI_NO=ct.FEEOBJECTNO

LEFT JOIN

(

SELECT

sum( FEERATIO) cgrate ,

FEEOBJECTNO

FROM

PRODUCT_COST_INFO

WHERE

feeobjecttype='Product'

AND FEETYPE='管理费' group by FEEOBJECTNO ) cg

ON

p.PI_NO=cg.FEEOBJECTNO

LEFT JOIN

(

SELECT

FEERATIO cgrates ,

FEEOBJECTNO

FROM

PRODUCT_COST_INFO

WHERE

feeobjecttype='Product'

AND FEETYPE='投顾费') cgg

ON

p.PI_NO=cgg.FEEOBJECTNO

LEFT JOIN

(

SELECT

FEERATIO atrate ,

FEEOBJECTNO

FROM

PRODUCT_COST_INFO

WHERE

feeobjecttype='Asset'

AND FEETYPE='托管费') att

ON

a.AI_NO=att.FEEOBJECTNO

LEFT JOIN

(

SELECT

FEERATIO agrate ,

FEEOBJECTNO

FROM

PRODUCT_COST_INFO

WHERE

feeobjecttype='Asset'

AND FEETYPE='管理费') ag

ON

a.AI_NO=ag.FEEOBJECTNO

LEFT JOIN

(

SELECT

FEERATIO agrates ,

FEEOBJECTNO

FROM

PRODUCT_COST_INFO

WHERE

feeobjecttype='Asset'

AND FEETYPE='投顾费') agg

ON

a.AI_NO=agg.FEEOBJECTNO

LEFT JOIN

(

SELECT

SUM( FEERATIO ) FeeNum,

FEEOBJECTNO

FROM

PRODUCT_COST_INFO

WHERE

feeobjecttype='Product'

AND FEETYPE NOT IN ('托管费',

'投顾费',

'管理费',

'浮动管理费')

AND FEERATIO>1

GROUP BY

FEEOBJECTNO

) fs

ON

a.AI_NO=fs.FEEOBJECTNO

LEFT JOIN

(

SELECT

SUM( FEERATIO ) FeeRate,

FEEOBJECTNO

FROM

PRODUCT_COST_INFO

WHERE

feeobjecttype='Product'

AND FEETYPE NOT IN ('托管费',

'投顾费',

'管理费',

'浮动管理费')

AND FEERATIO<1

GROUP BY

FEEOBJECTNO

) fss

ON

a.AI_NO=fss.FEEOBJECTNO

WHERE

G.GL_STATE=1

)

SELECT

ta.*,

(a_day_receive+p_day_manage+p_day_tm+p_day_tes-p_day_receive-a_day_manage-a_day_tm-a_day_tes+

fmfee3+FeeNum) all_get

FROM

(

SELECT

PI_NO,

PI_NAME,

PI_CUSTOMER_YIELD*CAS_ISSUE_SUM/365 p_day_receive， --产品编号，产品名称，产品每日收益

PI_CUSTOMER_YIELD*CAS_ISSUE_SUM/365*pflday p_all_receive, --起息日到当天的累计

cgrate*CAS_ISSUE_SUM/365 p_day_manage， --产品当天管理费

cgrate*CAS_ISSUE_SUM/365*pflday p_all_manage， --产品累计管理费

FEERATIO*CAS_ISSUE_SUM/365 p_day_tm， --产品当天托管费

FEERATIO*CAS_ISSUE_SUM/365*pflday p_all_tm， --产品累计托管费

CAS_ASSETS_NA，CAS_YIELD_RATE*CAS_ISSUE_SUM/365 a_day_receive， --资产编号，资产名称，资产每日收益

CAS_YIELD_RATE*CAS_ISSUE_SUM/365*aflday a_all_receieve, --资产起息日到当天的累计

agrate*CAS_ISSUE_SUM/365 a_day_manage， --资产当天管理费

agrate*CAS_ISSUE_SUM/365*aflday a_all_manage， --产品累计管理费

atrate*CAS_ISSUE_SUM/365 a_day_tm， --资产当天托管费

atrate*CAS_ISSUE_SUM/365*aflday a_all_tm, --资产累计托管费

'0' tdfee,

'0' tdallfee,

fmfee3,

cgrates*CAS_ISSUE_SUM/365 p_day_tes,--产品当天投顾费

cgrates*CAS_ISSUE_SUM/365*pflday p_all_tes,--产品累计投顾费

agrates*CAS_ISSUE_SUM/365 a_day_tes，--资产当天投顾费

agrates*CAS_ISSUE_SUM/365*aflday a_all_tes, --资产累计投顾费

FeeNum --其他费用

FROM

tall) ta ;

SELECT

DECODE(gl_date,a.CAS_VALUE_DATE, p.PI_NO , p.PI_NAME )

FROM

GL_PRODUCTASSETS g

LEFT JOIN

Product_info p

ON

g.PBS_MAINPRO_NO=p.PI_NO

LEFT JOIN

ASSETS_INFO a

ON

g.CAS_ASSETS_NU=a.AI_NO

LEFT JOIN

(

SELECT

productid,

SUM(FMFEE) fmfee

FROM

PRODUCT_FMFEE

GROUP BY

productid ) f

ON

g.PBS_MAINPRO_NO=f.PRODUCTID

LEFT JOIN

(

SELECT

productid,

SUM(FMFEE) fmfee3

FROM

PRODUCT_FMFEE

WHERE

INPUTDATE = TO_CHAR(SYSDATE,'YYYY/MM/DD')

GROUP BY

productid) f2

ON

g.PBS_MAINPRO_NO=f2.PRODUCTID

LEFT JOIN

(

SELECT

*

FROM

PRODUCT_COST_INFO

WHERE

feeobjecttype='Product'

AND FEETYPE='托管费') ct

ON

p.PI_NO=ct.FEEOBJECTNO

LEFT JOIN

(

SELECT

FEERATIO cgrate ,

FEEOBJECTNO

FROM

PRODUCT_COST_INFO

WHERE

feeobjecttype='Product'

AND FEETYPE='管理费') cg

ON

p.PI_NO=cg.FEEOBJECTNO

LEFT JOIN

(

SELECT

FEERATIO cgrates ,

FEEOBJECTNO

FROM

PRODUCT_COST_INFO

WHERE

feeobjecttype='Product'

AND FEETYPE='投顾费') cgg

ON

p.PI_NO=cg.FEEOBJECTNO

LEFT JOIN

(

SELECT

FEERATIO atrate ,

FEEOBJECTNO

FROM

PRODUCT_COST_INFO

WHERE

feeobjecttype='Asset'

AND FEETYPE='托管费') att

ON

a.AI_NO=att.FEEOBJECTNO

LEFT JOIN

(

SELECT

FEERATIO agrate ,

FEEOBJECTNO

FROM

PRODUCT_COST_INFO

WHERE

feeobjecttype='Asset'

AND FEETYPE='管理费') ag

ON

a.AI_NO=ag.FEEOBJECTNO

LEFT JOIN

(

SELECT

FEERATIO agrates ,

FEEOBJECTNO

FROM

PRODUCT_COST_INFO

WHERE

feeobjecttype='Asset'

AND FEETYPE='投顾费') agg

ON

a.AI_NO=ag.FEEOBJECTNO

WHERE

G.GL_STATE=1

然后把这个视图数据保存到你要保存的表：

insert into (和视图的列要对应的列名)表A(select *  from 视图表)

转载请保留本文网址：http://www.shaoqun.com/a/122127.html

*特别声明：以上内容来自于网络收集，著作权属原作者所有，如有侵权，请联系我们：admin@shaoqun.com。

oracle

0