oracle测试程序,oracle简单存储过程测试

最近在复习下oracle的存储过程，写了个简单的例子。

create or replace procedure raise_comm(v_no emp.empno%type,

v_sale out emp.sale%type) as

begin

select sale into v_sale from emp where empno = v_no;

if v_sale is null then

raise_application_error(-20001, 'It is no sale for this employee');

end if;

exception

when no_data_found then

dbms_output.put_line('The employee is not exist');

when others then

dbms_output.put_line('Error No:'||SQLCODE);

dbms_output.put_line(SQLERRM);

end;

然后习惯性地在pl/sql下面的cmd window下测试，测试如下：

SQL>set serveroutput on;

SQL>variable g_sal number;

SQL>call raise_comm(7080,:g_sal);

Method called

g_sal

---------

看到结果当时愣了下，感觉有点不正常，我的异常信息怎么不见了，编号7080的记录不存在，不应该是这个结果，而是又换了个编号存在的继续测试，测试结果为：

SQL> call raise_comm(7821,:g_sal);

Method called

g_sal

---------

1250

我的异常信息真的不见了，而是换了种方式测试，找到存储过程，选中-->点击右键，选择Test

然后可以看到系统自动生成的测试方法：

在Variable下面的Value里面填值，我的一个是入参，一个是出参，只要v_no填值就OK了。先测试不存在的记录

直接按F8，在DBMS Output页签下可以看到打印结果。

在测试一个存在的正常数据。

可以看到出参用黄颜色标记了。

在测试一个异常的数据。v_no填11，可以在DBMS Output页签下面看到Exception信息。

看到Test测试正常，感觉在Cmd Window下面直接测试存储过程有点不靠谱,打印信息都不见了,仿照系统自动生成的脚本，自己写了个测试脚本。

declare

g_sal number;

begin

raise_comm(&v_no, :g_sal);

end;

在pl/sql的Cmd Window下运行：

SQL> start f:/saveFile/testsql.sql

6 /

The employee is not exist

PL/SQL procedure successfully completed

g_sal

---------

SQL> start f:/saveFile/testsql.sql

6 /

PL/SQL procedure successfully completed

g_sal

---------

980

SQL> start f:/saveFile/testsql.sql

6 /

Error No:-20001

ORA-20001: It is no sale for this employee

PL/SQL procedure successfully completed

g_sal

---------

可以看到DBMS Output的打印信息。

如果不想新建一个脚本，想直接在Cmd Window 下面看到打印结果，可以这样做：

SQL> set serveroutput on

SQL> declare

2 g_sal number;

3 begin

4 select sale into g_sal from emp where empno=&v_no;

5 if g_sal is null then

6 raise_application_error(-20001, 'It is no sale for this employee');

7 end if;

8 dbms_output.put_line('The employee is not exist');

9 dbms_output.put_line('g_sal='||g_sal);

10 exception

11 when no_data_found then

12 dbms_output.put_line('The employee is not exist');

13 when others then

14 dbms_output.put_line('Error No:'||SQLCODE);

15 dbms_output.put_line(SQLERRM);

16 end;

17 /

The employee is not exist

PL/SQL procedure successfully completed

SQL>

SQL> declare

2 g_sal number;

3 begin

4 select sale into g_sal from emp where empno=&v_no;

5 if g_sal is null then

6 raise_application_error(-20001, 'It is no sale for this employee');

7 end if;

8 dbms_output.put_line('The employee is not exist');

9 dbms_output.put_line('g_sal='||g_sal);

10 exception

11 when no_data_found then

12 dbms_output.put_line('The employee is not exist');

13 when others then

14 dbms_output.put_line('Error No:'||SQLCODE);

15 dbms_output.put_line(SQLERRM);

16 end;

17 /

The employee is not exist

g_sal=980

PL/SQL procedure successfully completed

SQL>

SQL> declare

2 g_sal number;

3 begin

4 select sale into g_sal from emp where empno=&v_no;

5 if g_sal is null then

6 raise_application_error(-20001, 'It is no sale for this employee');

7 end if;

8 dbms_output.put_line('The employee is not exist');

9 dbms_output.put_line('g_sal='||g_sal);

10 exception

11 when no_data_found then

12 dbms_output.put_line('The employee is not exist');

13 when others then

14 dbms_output.put_line('Error No:'||SQLCODE);

15 dbms_output.put_line(SQLERRM);

16 end;

17 /

Error No:-20001

ORA-20001: It is no sale for this employee

PL/SQL procedure successfully completed

全文完。