Skip to main content

11.1 PLSQL New Feature : CONTINUE Statement

In Oracle Database 11g PL/SQL has a new construct called CONTINUE , which is used in a loop. The statement moves the logic to the end of the loop and then to the beginning of the loop.The CONTINUE statement jumps out of the current loop iteration and starts the next one. It can be used on its own, or as part of a CONTINUE WHEN statement. This type of processing has always been possible using IF statements either on their own or with exceptions or GOTO statements, but the CONTINUE statement is neater and brings PL/SQL in line with other languages. 

Sample Code :

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    l_itr    NUMBER := 0;
  3  BEGIN
  4    FOR i IN 1 .. 100 LOOP
  5      CONTINUE WHEN MOD(i,5) = 0;
  6      l_itr := l_itr + 1;
  7    END LOOP;
  8
  9    DBMS_OUTPUT.put_line('Loop Interations Using CONTINUE WHEN : ' || l_itr);

 10
 11    l_itr := 0;
 12
 13    FOR i IN 1 .. 100 LOOP
 14      IF MOD(i,5) = 0 THEN
 15        CONTINUE;
 16      END IF;
 17      l_itr := l_itr + 1;
 18    END LOOP;
 19
 20    DBMS_OUTPUT.put_line('Loop Iterations using IF..CONTINUE ' || l_itr);
 21  END;
 22  /
Loop Interations Using CONTINUE WHEN : 80
Loop Iterations using IF..CONTINUE 80

PL/SQL procedure successfully completed.

SQL>

The execution of the loop was skipped 20 times using CONTINUE statement and hence the loop iterated only 80 times.

Comments

Post a Comment

Popular posts from this blog

11.2 PLSQL New Feature : How to use DBMS_PARALLEL_EXECUTE to Update Large Tables in Parallel

The DBMS_PARALLEL_EXECUTE package enables you to incrementally update the data in a large table in parallel, in two high-level steps: 1. Group sets of rows in the table into smaller chunks. 2. Apply the desired UPDATE statement to the chunks in parallel, committing each time you have finished processing a chunk. This technique is recommended whenever you are updating a lot of data. Its advantages are: o You lock only one set of rows at a time, for a relatively short time, instead of locking the entire table. o You do not lose work that has been done if something fails before the entire operation finishes. o You reduce rollback space consumption. o You improve performance Different Ways to Spilt Workload : 1. CREATE_CHUNKS_BY_NUMBER_COL: Chunks the table associated with the given task by the specified column. 2. CREATE_CHUNKS_BY_ROWID : Chunks the table associated with the given task by ROWID 3. CREATE_CHUNKS_BY_SQL : Chunks the table associated with the given task by me...
Post a Comment
Read more

Check Whether File Exists on the Server Using PLSQL

There are different packages using which you can check whether a file exists on the database / application server. The different ways are 1. Using UTL_HTTP from Web Server 2. Using UTL_FILE from Database Server 3. Using DBMS_LOB from Database Server 1. You can also use UTL_HTTP package to check whether a file exists on a web server from the database. SET SERVEROUTPUT ON DECLARE   url       VARCHAR2(256) := 'http://www.oracle.com/index1.html';   username  VARCHAR2(256);   password  VARCHAR2(256);   req       UTL_HTTP.REQ;   resp      UTL_HTTP.RESP; BEGIN   req := UTL_HTTP.BEGIN_REQUEST(url);   IF (username IS NOT NULL) THEN     UTL_HTTP.SET_AUTHENTICATION(req, username, password);   END IF;   resp := UTL_HTTP.GET_RESPONSE(req);   DBMS_OUTPUT.PUT_LINE('response -->' || resp.status_code); END; / If the f...
Post a Comment
Read more

Oracle 10g New feature : User-Defined Quote Character

Oracle 10g introduced the ability for user-defined quote characters. Well, prior to Oracle 10g, if you wanted to include quotes in text, you had to ‘escape’ the quote with another quote such as: Select 'My Name is Alwyn D''Souza having '' after D' from dual; This technique becomes inconvenient when you write a very long dynamic sql string.You miss one 'escape' character and the code runs into error during run-time and we waste lot of time finding the missing 'escape' character. Now with Oracle 10g, you can re-write this as: Select q ' { My Name is Alwyn D'Souza having ' after D } ' from dual; Select Q ' { My Name is Alwyn D'Souza having ' after D } ' from dual; Note that the quoted strings starts with the letter q/Q, followed by a single quote and the new quote character.  It ends with the new quote character and a single quote.  I’ve used a flower bracket ({) as my quote character, but you can use other ...
1 comment
Read more