|
当我有一条普通的sql语句的时候,我用下面的vbscript代码去执行这条sql,从而把oracle数据库里相应的数据取出来:
Public Function GetMultiRowData(sqlQuery,intColNum,arrExpData())
Dim rowcount
Set oResultSet = oCon.Execute(sqlQuery)
' Place the cursor at the first record
'oResultSet.MoveFirst
rowcount =0
Do Until oResultSet.EOF
rowcount=rowcount + 1
oResultSet.MoveNext
Loop
If rowcount>=1 Then
ReDim arrExpData(rowcount,intColNum)
rowcount =0
oResultSet.MoveFirst
Do Until oResultSet.EOF
For i = 0 to intColNum
arrExpData(rowcount,i) = oResultSet.Fields(i)
Next
oResultSet.MoveNext
rowcount=rowcount + 1
Loop
oResultSet.close
Set oResultSet = Nothing
else
End If
End Function
现在我手头的这段sql不是普通的sql,是关于dbms_output.Put_LINE的,比如说:
declare
s_selected_portfolio VARCHAR2(9):= '00IE' ;
s_inception_date DATE := to_date('2008/10/08', 'yyyy/mm/dd') ;
s_to_date DATE := to_date('2009/10/28', 'yyyy/mm/dd') ;
n_port_key NUMBER(38) ;
n_inception_date_key NUMBER ;
n_to_date_key NUMBER ;
n_base_curr_key NUMBER(38);
n_starting_cash NUMBER(38,10);
n_equity_gl_part1 NUMBER(38,10);
n_equity_gl_part2 NUMBER(38,10);
n_equity_gl NUMBER(38,10);
n_futures_gl_part1 NUMBER(38,10);
n_futures_gl_part2 NUMBER(38,10);
n_futures_gl NUMBER(38,10);
n_currency_gl_part1 NUMBER(38,10);
n_currency_gl_part2 NUMBER(38,10);
n_currency_gl NUMBER(38,10);
n_cash_gl NUMBER(38,10);
n_cash_flows NUMBER(38,10);
n_current_market_value NUMBER(38,10);
n_gross_future_gl NUMBER(38,10);
n_gross_future_gl_part1 NUMBER(38,10);
n_gross_future_gl_part2 NUMBER(38,10);
n_net_future_gl NUMBER(38,10);
begin
--- get port_key for the selected primary portfolio
select port_key
into n_port_key
from gdm_psr_dim_portfolio
where port_new_code = s_selected_portfolio
and port_record_Status ='A' ;
--- get date_key for inception date and to date
select dimd_date_key
into n_inception_date_key
from gdm_psr_dim_date
where dimd_full_date = s_inception_date ;
select dimd_date_key
into n_to_date_key
from gdm_psr_dim_date
where dimd_full_date = s_to_date ;
--functional currency key for the portfolio
select cccy_key
into n_base_curr_key
from gdm_psr_currency
where cccy_record_status = 'A'
and cccy_code = (select port_primary_ccy from gdm_psr_dim_portfolio where port_new_code = s_selected_portfolio and port_record_Status ='A' ) ;
---current market value
--get starting cash
select tmtr_amount
into n_starting_cash
from (
select tmtr_amount,tmtr_trade_date_key
from GDM_PSR_FACT_TM_TRANSACTION
where tmtr_amount is not null and tmtr_amount <> 0 and TMTR_CATEGORY_CD = 3 and tmtr_type_key = 521 -- 700 cash contrib
and tmtr_port_key = n_port_key
and ( select MGRG_MANAGER_GROUP_CODE from GDM_PSR_DIM_MANAGER_GROUP where mgrg_manager_group_key = tmtr_manager_group_key) = '5CA'
and ( select basi_currency_basis_code from GDM_PSR_DIM_BASIS where basi_basis_key = tmtr_basis_key and basi_currency_basis_desc = 'FUNCTIONAL' and basi_currency_basis_code = 2) = 2
and ( select basi_date_basis_desc from GDM_PSR_DIM_BASIS where basi_basis_key = tmtr_basis_key ) = 'Trade'
order by tmtr_trade_date_key
) where rownum = 1 ;
dbms_output.PUT_LINE('Starting Cash = '||n_starting_cash);
那我要怎么利用这段现有的sql去读数据库里的Starting Cash这个值呢?前提是不想拆分这些sql,因为后面还有一大段类似的语句,而且每个输出值都是后面一个的输入参数。 |
|