Wy no continue statement in Plsql

Why no CONTINUE command in PL/SQL?
Question:
Is there any CONTINUE command in PL/SQL (the counterpart of break, which exits from a loop if a condition is met) like the one in the C programming language?
Answer:
I've often wondered the same thing myself. So I asked Bryn Llewellyn, Oracle's PL/SQL Product Manager, the same question. Here's what he said:
"I can't defend PL/SQL's lack of a continue statement. There's no question that some algorithms are considerably easier to express when a CONTINUE statement is used. If you need convincing, consider this:
<<outer>>for ... loop
...
<<inner>>for ... loop
...
-- exit both loops
exit outer when ...;
end loop inner;
...
end loop outer;
It would take a fair effort to achieve this effect without using an EXIT statement. (You could try it, for sport.) The CONTINUE statement would look rather similar:
<<outer>>for ... loop
...
<<inner>>for ... loop
...
-- immediately start the next iteration of the outer loop
continue outer when ...;
end loop inner;
...
end loop outer;
Now write some PL/SQL that achieves this. You'll soon see that a continue statement in PL/SQL would be quite useful."
Of course, I asked Bryn when we might see this enhancement made. "Don't hold your breath," he said. "I'm afraid that there are too many other potential enhancements that would deliver bigger benefits competing for priority."

Working with Collections in Subprogram Headers

Question:
I have two questions: (1) How I can pass a collection as an argument to a procedure? and (2) How I return a collection from a procedure?
Answer:
I have received this question from a number of people over the years, and I find the question both surprising and understandable.
I am surprised because the syntax for passing a collection as an argument is actually no different than that for passing a string or a date. You simply define the parameter as having a datatype matching the collection type and....off you go!
I understand the question because many PL/SQL developers seem to be generally a bit wary of and confused by collections.
So perhaps more of an explanation is needed. Here we go!
Collections are array-like structures in PL/SQL. With them, you can manage lists of information, build stacks, queues, linked lists, single dimensional arrays, and more. You can emulate multi-dimensional arrays; you can index not only by integers but also by strings, giving you enormous flexibility. With the TABLE operator, you can treat a collection like a relational table inside a query.
In addition, you need to use collections in order to take advantage of two of the most powerful and useful statements in the PL/SQL tool box: FORALL and BULK COLLECT, which dramatically improve performance of multi-row SQL operations from a PL/SQL program.
To sum up, I believe that collections are an absolutely amazing and crucial feature of PL/SQL. All developers should come up to speed on collections, be comfortable using them, and apply them wherever possible.
Now, one way to apply them is to pass them to and from subprograms: procedures and functions. So, like I said above, there is nothing special about passing a collection as an argument. When you define the parameter in your subprogram, you must provide a name, a mode (IN is the default) and the datatype. I think that your question comes down to: how to specify the datatype?
And this can be a little bit tricky, because in most cases you will need to have previously defined the datatype
.
But let's take things a step at a time. Suppose that I want to write a program that accepts a collection of a type that has already been defined, for example, DBMS_SQL.VARCHAR2A. This collection type is defined in the DBMS_SQL package as follows:
type varchar2a is table of varchar2(32767)
index by binary_integer;
It is used for parsing very long SQL statements (those that are greater than 32K in length). But I could also use it as a "generic type" for "list of strings".
Here is an example of using the type in an anonymous block:
DECLARE
my_strings DBMS_SQL.VARCHAR2A;
BEGIN
DBMS_OUTPUT.put_line ('>>> Populate a DBMS_SQL.VARCHAR2A array');
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
END;
/
I declare a list of "my strings" based on the pre-defined collection type, and then populate the list with three values.
Now let's take one more step and create a local subprogram that accepts a collection of this type as an IN argument, and displays its contents:
DECLARE
my_strings DBMS_SQL.varchar2a;

PROCEDURE show_list (
title_in IN VARCHAR2, list_in IN DBMS_SQL.varchar2a)
IS
BEGIN
DBMS_OUTPUT.put_line (
'>>> DBMS_SQL.VARCHAR2A as IN parameter ' || title_in);

/* I assume below that the collection is densely filled. */
FOR indx IN 1 .. list_in.COUNT
LOOP
DBMS_OUTPUT.put_line (list_in (indx));
END LOOP;
END show_list;
BEGIN
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
show_list ('My name', my_strings);
END;
/
And here, then, you can see how to pass a collection into a procedure. "Just do it!" I can also return a collection through a function. In the following block, I added a function to "double" the values in my list. Notice that I pass in a collection as an IN argument, but also pass a collection of the same type as the RETURN clause of the function.
DECLARE
my_strings DBMS_SQL.varchar2a;
my_strings2 DBMS_SQL.varchar2a;

PROCEDURE show_list (
title_in IN VARCHAR2, list_in IN DBMS_SQL.varchar2a)
IS
BEGIN
DBMS_OUTPUT.put_line (
'>>> DBMS_SQL.VARCHAR2A as RETURN clause ' || title_in);

FOR indx IN 1 .. list_in.COUNT
LOOP
DBMS_OUTPUT.put_line (list_in (indx));
END LOOP;
END show_list;

FUNCTION double_values (list_in IN DBMS_SQL.varchar2a)
RETURN DBMS_SQL.varchar2a
IS
l_return DBMS_SQL.varchar2a;
BEGIN
FOR indx IN 1 .. list_in.COUNT
LOOP
l_return (l_return.COUNT + 1) :=
list_in (indx) || '-'
|| list_in (indx);
END LOOP;

RETURN l_return;
END double_values;
BEGIN
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
my_strings2 := double_values (my_strings);
show_list ('My name - Doubled', my_strings2);
END;
/
Again, note that there is no special syntax required to return a collection; just specify the datatype.
I have now showed you how to use a pre-defined collection type in your parameter list. One last topic to cover: how to create your own collection types and use them in your parameter list. The collection type has to be available in the header of your program, so either it has to be defined:
1. at the schema level as a database object;
2. in the specification of a package on which your schema has execute authority;
3. in the declaration section of the block in which the program is then defined.
I demonstrate each of these below:
1. Define a collection type at the schema level as a database object. This is possible only for nested tables and varrays. Notice that there is very little difference from the original example, except for syntax changes needed to work with nested tables (use of the constructor function, strings_ntt (), and the extend statement).
CREATE OR REPLACE TYPE strings_ntt IS TABLE OF VARCHAR2 (32767)
/

DECLARE
my_strings strings_ntt := strings_ntt ();

PROCEDURE show_list (title_in IN VARCHAR2, list_in IN strings_ntt)
IS
BEGIN
DBMS_OUTPUT.put_line (
'>>> Schema-level collection type ' || title_in);

FOR indx IN 1 .. list_in.COUNT
LOOP
DBMS_OUTPUT.put_line (list_in (indx));
END LOOP;
END show_list;
BEGIN
my_strings.EXTEND (3);
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
show_list ('My name', my_strings);
END;
/
2. Define the collection type in the specification of a package on which your schema has execute authority. I will do, in other words, precisely what Oracle did in the DBMS_SQL package, but for my own type:
CREATE OR REPLACE PACKAGE coll_types_pkg
IS
TYPE strings_aat IS TABLE OF VARCHAR2 (32767)
INDEX BY PLS_INTEGER;

TYPE dates_aat IS TABLE OF DATE
INDEX BY PLS_INTEGER;

TYPE booleans_aat IS TABLE OF BOOLEAN
INDEX BY PLS_INTEGER;
END coll_types_pkg;
/

DECLARE
my_strings coll_types_pkg.strings_aat;

PROCEDURE show_list (
title_in IN VARCHAR2, list_in IN coll_types_pkg.strings_aat)
IS
BEGIN
DBMS_OUTPUT.put_line (
'>>> Collection type in package spec ' || title_in);

FOR indx IN 1 .. list_in.COUNT
LOOP
DBMS_OUTPUT.put_line (list_in (indx));
END LOOP;
END show_list;
BEGIN
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
show_list ('My name', my_strings);
END;
/
3. Declare the collection type in the declaration section of the block in which the program is then defined. The code below is exactly the same as the original example using DBMS_SQL.VARCHAR2A, but I have my own type statement.
DECLARE
TYPE strings_aat IS TABLE OF VARCHAR2 (32767)
INDEX BY PLS_INTEGER;

my_strings strings_aat;

PROCEDURE show_list (title_in IN VARCHAR2, list_in IN strings_aat)
IS
BEGIN
DBMS_OUTPUT.put_line (
'>>> Locally declared collection type' || title_in);

FOR indx IN 1 .. list_in.COUNT
LOOP
DBMS_OUTPUT.put_line (list_in (indx));
END LOOP;
END show_list;
BEGIN
my_strings (1) := 'Steven';
my_strings (2) := 'Eric';
my_strings (3) := 'Feuerstein';
show_list ('My name', my_strings);
END;
/
To conclude with a best practice recommendation or two:
• Avoid declaring collection types inside anonymous blocks or declaration sections of subprograms. It is very likely that you will need to use the type more than once.
• Define your collection types in the database (with a schema-level CREATE OR REPLACE TYPE) if you need to use that type as a column in a relational table.
• Define your collection types in a package specification whenever you want or need to use an associative array (they support negative indexes, sparsely-filled collections, string indexes), or you are using these collection types solely in PL/SQL code (not as columns in tables.

Why doesn't an UPDATE raise the NO_DATA_FOUND exception

Question:
Why doesn't an UPDATE raise the NO_DATA_FOUND exception?
Answer:
Arun, a number of people have asked that question over the years. At first glance, it seems like an inconsistency. I try to query some rows and if none are found, I get an error. Yet if I try to update some rows and none are found, I don't receive an error. Instead, SQL%ROWCOUNT is set to 0.
Certainly, Oracle as well as the ANSI standards committee could have specified that NO_DATA_FOUND be raised when no rows are updated. I can only speculate as follows:
• When I execute an update statement I am saying "Update all the rows that satisfy this WHERE clause, or all rows if the WHERE clause is missing." If my WHERE clause identifies no rows, that isn't really an error; the UPDATE simply followed your command and updated the rows you specified—of which there were none.
• When you execute a query, you want to retrieve data. That's the point of the query. So if I don't get any back, that is an exceptional situation and Oracle raises the exception to indicate that fact.
Of course, another is simply "That's the way the ANSII standard goes..."
By the way, I suggest that you always place your queries inside a function so that you can trap the NO_DATA_FOUND exception and decide if you want to propagate that exception out to the enclosing block. You could take the same approach with UPDATE: put it inside a procedure and have it raise an exception when SQL%ROWCOUNT is set to 0, if that is the behavior you want/need in your application.
I recommend functions and procedures, by the way, so that you are more easily able to reuse those SQL statements.

Who needs user-defined exceptions

Question:
Oracle defines a number of exceptions for me, like no_data_found. Why would I ever need to or want to define my own exceptions?
Answer:
Oracle does, indeed, pre-define a number of exceptions. Many of these may be found inside the standard package of PL/SQL, one of the two default packages in the PL/SQL language (the other being dbms_standard). You can view these exception definitions by looking at the stdspec.sql file in the $ORACLE_HOME/Rdbms/Admin directory of an Oracle10g installation.
Here is a portion of that file showing the declarations of a few such exceptions:
/********** Predefined exceptions **********/

CURSOR_ALREADY_OPEN exception;
pragma EXCEPTION_INIT(CURSOR_ALREADY_OPEN, '-6511');

DUP_VAL_ON_INDEX exception;
pragma EXCEPTION_INIT(DUP_VAL_ON_INDEX, '-0001');

TIMEOUT_ON_RESOURCE exception;
pragma EXCEPTION_INIT(TIMEOUT_ON_RESOURCE, '-0051');

In this code, Oracle declares an exception and then associates that named exception with an error code using the exception_init pragma.
You can declare exceptions just like Oracle does in the above code fragment; you can also raise application-specific exceptions "on the fly" with the raise_application_error built-in.
You will generally want to do this in order to improve the readability and maintainability of your code. Let's look at two examples: handling exceptions raised by Oracle and raising application-specific exceptions.