Differences between Return and exit in Oracle

In Oracle stored procedures, if the Return statement is executed when the Return statement is used, the entire statement (if it is a loop, it will jump out of the entire loop) will not be executed, that is, the entire stored procedure is ended. The following uses an example to illustrate that the stored procedure is based on the employee ID to find out the employee name. In order to get the direct effect, I

In Oracle stored procedures, if the Return statement is executed when the Return statement is used, the entire statement (if it is a loop, it will jump out of the entire loop) will not be executed, that is, the entire stored procedure is ended. The following uses an example to illustrate that the stored procedure is based on the employee ID to find out the employee name. In order to get the direct effect, I

In Oracle stored procedures, if the Return statement is executed when the Return statement is used, the entire statement (if it is a loop, it will jump out of the entire loop) will not be executed, that is, the entire stored procedure is ended.

The following uses an example to describe how to use the stored procedure to find out the employee name based on the employee ID. In order to get the direct effect, I printed the statement in a specific place, it's a bit simple to write, and you may not understand:

Create or replace procedure pro_emp1 (v_eno in number, v_resultcode out number, v_resulterrinfo OUT VARCHAR2) IS iv_eno emp. empno % type; iv_name emp. ename % type; BEGIN iv_eno: = v_eno; ---- assign the input parameter variable to the defined variable v_resultcode: =-1; BEGIN select ename into iv_name from emp where empno = iv_eno; dbms_output.put_line ('employee name: '| iv_name); exception when others then v_resultcode: = SQLCODE; v_resulterrinfo: = 'employee name not desired:' | SQLERRM; RETURN; END; BEGIN dbms_output.put_line ('the method is used to execute a small in a large in '); END; dbms_output.put_line ('execution ends with a large in'); END;

Run this stored procedure to query non-existing employees. The following Beign statements and print statements are not executed:

SQL> var v_resultcode number; SQL> var v_resultinfo varchar2; SQL> exec pro_emp1 (7789,: v_resultcode,: v_resultinfo ); PL/SQL procedure successfully completedv_resultcode --------- 100v_resultinfo --------- do not want employee name: ORA-01403: Data not found

Enter the employee number that can be queried:

SQL> exec pro_emp1 (7788,: v_resultcode,: v_resultinfo); employee name: SCOTT executed the method to the small in the large Begin, and executed the method to the end of the large Begin. PL/SQL procedure successfully completedv_resultcode ---------- 1v_resultinfo ---------

The following is an image taken from the Internet. If you are interested, you can check it out:

Create or replace procedure Test5 (o_cellphone in varchar2) is v_cellphone percent % type; v_name cc_quiz_stat % rowtype; v_state percent % type; begin declare cursor cur_cc is select * from cc_quiz_stat; cursor cur_jc (v_n varchar2) is select state from nation; begin open cur_cc; loop fetch cur_cc into v_name; exit when cur_cc % notfound; open cur_jc (o_cellphone); loop fetch cur_jc into v_state; exit when cur_jc % notfound; if (o_cellphone = v_name.cellphone) then return; else dbms_output.put_line ('phone No. '| v_name.cellphone | 'province' | v_state); end if; end loop; close cur_jc; end loop; close cur_cc; end Test5;

Execution result:

Mobile phone no. 18900000000 Province National mobile phone no. 18900000000 province Nanjing mobile phone no. 18900000000 province Tianjin mobile phone no. 18900000000 province Shanghai mobile phone no. 18900000000 province Beijing

Create or replace procedure Test5 (o_cellphone in varchar2) is v_cellphone percent % type; v_name cc_quiz_stat % rowtype; v_state percent % type; begin declare cursor cur_cc is select * from cc_quiz_stat; cursor cur_jc (v_n varchar2) is select state from nation; begin open cur_cc; loop fetch cur_cc into v_name; exit when cur_cc % notfound; open cur_jc (o_cellphone); loop fetch cur_jc into v_state; exit when cur_jc % notfound; if (o_cellphone = v_name.cellphone) then exit; else dbms_output.put_line ('phone No. '| v_name.cellphone | 'province' | v_state); end if; end loop; close cur_jc; end loop; close cur_cc; end Test5;

Execution result:

Mobile phone number 18900000000 Province National mobile phone number 18900000000 province Nanjing mobile phone number 18900000000 province Tianjin mobile phone number 18900000000 province Shanghai mobile phone number 18900000000 province Beijing mobile phone number 18900000002 Province National mobile phone number 18900000002 province Nanjing mobile phone number 18900000002 province Tianjin mobile phone number 18900000002 province Shanghai mobile phone number 18900000002 province Beijing mobile phone number 18900000003 Province National mobile phone number 18900000003 province Nanjing mobile phone number 18900000003 province Tianjin mobile phone number 18900000003 province Shanghai mobile phone number 18900000003 province Beijing mobile phone number 18900000004 Province National mobile phone number 18900000004 province Nanjing mobile phone number 18900000004 province Tianjin mobile phone number 18900000004 province Shanghai mobile phone number 18900000004 province Beijing

The return Statement jumps out of the entire loop and is not executed after this loop,

Exit jumps out of this loop and continues executing this loop next time