最近在复习下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
全文完。