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

 

    全文完。