Geek asks and answers

Quit execution when a PL / SQL error occurs

I would like to know how I can exit execution when an error occurs. There is a sentence in Microsoft SQL Server RETURNthat does this work. But I would like to know similar functionality in Oracle. I am using Oracle Sql Developer. Here is the script I am using:

The first block throws an error due to a violation of the unique key, although it throws an error, execution proceeds to the next block and executes the insert statement. I want to finish the execution or exit the first block of the code itself. Please help me write the code.

The first anonymous PL / SQL block:

set serveroutput on;

BEGIN 
  insert into test values(1);
  insert into test values(1);
  COMMIT;  

  dbms_output.put_line('PRINT SOMETHING 1'); 

EXCEPTION
   WHEN OTHERS THEN
     if sqlcode <> 0
     then
        dbms_output.put_line(SQLCODE || '  ' || SQLERRM);
        RAISE; 
     end if;
     return;
END;
/

Second anonymous PL / SQL block:

set serveroutput on;

BEGIN 
  insert into test values(6);
  COMMIT;  

  dbms_output.put_line('PRINT SOMETHING'); 

EXCEPTION
   WHEN OTHERS THEN
     if sqlcode <> 0
     then
        dbms_output.put_line(SQLCODE || '  ' || SQLERRM);
        RAISE; 
     end if;
     return;
 END;
/
+3
source share
5 answers

, , , return.

, proc:

create or replace procedure myProc as
begin
   dbms_ouput.put_line('i am here');
   return;
   dbms_ouput.put_line('and not here');
end;

sqlplus :

exec myProc();
+3

"exit" - . Oracle : http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12023.htm

, SqlPlus, , .

:

WHENEVER SQLERROR EXIT SQL.SQLCODE

Oracle: http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12052.htm

+1

" ". , , .

set serveroutput on;

BEGIN
  BEGIN 
    insert into test values(1);
    insert into test values(1);
    COMMIT;  

    dbms_output.put_line('PRINT SOMETHING 1'); 

  EXCEPTION
     WHEN OTHERS THEN
       if sqlcode <> 0
       then
          dbms_output.put_line(SQLCODE || '  ' || SQLERRM);
          RAISE; 
       end if;
       return;
  END;
  BEGIN 
    insert into test values(6);
    COMMIT;  

    dbms_output.put_line('PRINT SOMETHING'); 

  EXCEPTION
     WHEN OTHERS THEN
       if sqlcode <> 0
       then
          dbms_output.put_line(SQLCODE || '  ' || SQLERRM);
          RAISE; 
       end if;
       return;
  END;
END;
/
+1

. JoshL, EXIT, . , ( PL/SQL). "WHENEVER SQLERROR EXIT" , , sql- InstallShield, InstallShield .

set serveroutput on;

BEGIN 

insert into test values(1);
insert into test values(1);

COMMIT;  

  dbms_output.put_line('PRINT SOMETHING 1'); 

  EXCEPTION
    WHEN OTHERS THEN
    if sqlcode <> 0
    then
    dbms_output.put_line(SQLCODE || '  ' || SQLERRM);
    RAISE; 
    exit;
    end if;

END;
/
0

EXIT PL/SQL. EXIT . EXIT PL/SQL, .

EXIT SQLPlus SQLPlus.

, Oracle. SQL * Plus PL/SQL, EXIT , .

0
source

More articles:


All Articles