软件测试论坛 › [QuickTest Professional] › qtp 读取数据库里的数据

qdd6501 发表于 2011-4-6 16:52:37

qtp 读取数据库里的数据

当我有一条普通的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_part1NUMBER(38,10);
n_futures_gl_part2NUMBER(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_valueNUMBER(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 = 3and 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 发表于 2011-4-7 09:27:36

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

kavensyw 发表于 2011-4-12 14:06:32

本帖最后由 kavensyw 于 2011-4-12 15:33 编辑

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

试验脚本如下：

Dim WshShell
set WshShell = CreateObject("wscript.Shell")
WshShell.run "cmd"
wait 1
WshShell.SendKeys "sqlplus darwen/suzsoft@test"
wait 1
WshShell.SendKeys "{ENTER}"
wait 1
WshShell.SendKeys "@H:\Loader\Prog.sql;"
wait 1
WshShell.SendKeys "{ENTER}"
wait 1
WshShell.SendKeys "/"
wait 1
WshShell.SendKeys "{ENTER}"
wait 1
'关闭sqlplus
WshShell.SendKeys "exit"
wait 1
WshShell.SendKeys "{ENTER}"
'关闭cmd
WshShell.SendKeys "exit"
wait 1
WshShell.SendKeys "{ENTER}"


Prog.sql内容如下：
DECLARE
v_file UTL_FILE.file_type;
v_msg VARCHAR2(500);
BEGIN
v_file := UTL_FILE.fopen('TEST_LOADER', 'test.log', 'a');

SELECT 'mdt_customer记录总数： '||COUNT(1) INTO v_msg FROM mdt_customer a;
UTL_FILE.put_line(v_file,v_msg);
UTL_FILE.fclose(v_file);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

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

自动运行过程如下：

查看完整版本: qtp 读取数据库里的数据