qtp 读取数据库里的数据

qdd6501

当我有一条普通的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，因为后面还有一大段类似的语句，而且每个输出值都是后面一个的输入参数。

coolwind09

写的一些什么啊，看不懂！学习吧```

kavensyw

（1）可以的话最好用存储过程，写日志表，然后在QTP中用ADODB
（2）实在不行用如下方式
1.把sql块保存为.sql脚本，要取的变量值用unl_file等方法写到到.txt日志文件,
2.在QTP中调用sqlplus,运行sql脚本文件,
3. QTP中读取日志文件中保存的相应值；
4. 日志文件不过不需要，则删除；需要则保留

试验脚本如下：

1. 
   
2. Dim WshShell
   
3. set WshShell = CreateObject("wscript.Shell")
   
4. WshShell.run "cmd"
   
5. wait 1
   
6. WshShell.SendKeys "sqlplus darwen/suzsoft@test"
   
7. wait 1
   
8. WshShell.SendKeys "{ENTER}"
   
9. wait 1
   
10. WshShell.SendKeys "@H:\Loader\Prog.sql;"
    
11. wait 1
    
12. WshShell.SendKeys "{ENTER}"
    
13. wait 1
    
14. WshShell.SendKeys "/"
    
15. wait 1
    
16. WshShell.SendKeys "{ENTER}"
    
17. wait 1
    
18. '关闭sqlplus
    
19. WshShell.SendKeys "exit"
    
20. wait 1
    
21. WshShell.SendKeys "{ENTER}"
    
22. '关闭cmd
    
23. WshShell.SendKeys "exit"
    
24. wait 1
    
25. WshShell.SendKeys "{ENTER}"
    

复制代码

Prog.sql内容如下：

1. DECLARE
   
2. v_file UTL_FILE.file_type;
   
3. v_msg VARCHAR2(500);
   
4. BEGIN
   
5. v_file := UTL_FILE.fopen('TEST_LOADER', 'test.log', 'a');
   
6. 
   
7. SELECT 'mdt_customer记录总数： '||COUNT(1) INTO v_msg FROM mdt_customer a;
   
8. UTL_FILE.put_line(v_file,v_msg);
   
9. UTL_FILE.fclose(v_file);
   
10. EXCEPTION
    
11. WHEN OTHERS THEN
    
12. NULL;
    
13. END;

复制代码

log结果如下：
mdt_customer记录总数： 70

自动运行过程如下：