How Query works

Before beginning to code any application, programming standards must be coded. Having moved beyond the rigors of 3GLs and their particular constraints, people question the need for such tight programming controls. However, they are still necessary perhaps even more so in working with SQL.

Coding standards for individual SQL statements are particularly important because of the way that the RDBMS engine handles the statements internally. When a SQL statement is parsed within the database the following steps are performed:

Check syntax
Is the SQL correctly structured? Do all the opening parentheses match the closing parentheses?

Search shared SQL area
Does a parsed copy of the same statement (i.e., in pcode form) already exist? Oracle searches a common area of the System Global Area (SGA) known as the Shared Pool. Among the structures of the Shared Pool is the Library Cache and its Shared SQL Area, which holds SQL statements. See the diagram below.

Search data dictionary
Performs object resolution (checks views, synonyms, etc.) to ensure that the specified objects actually exist. Checks security privileges to ensure that the user issuing the SQL statement has the appropriate permissions to access the objects.

Calculates search path
The Rule Based Optimizer (RBO) or the Cost Based Optimizer (CBO) is used to determine the optimal execution path There can be multiple paths for the database to satisfy the query. The Optimizer determines which execution path is the most optimal. The RBO uses a predefined set of rules to make those determinations. The CBO uses statistics on data distribution and makeup to help make better decisions. In Oracle 10g, the RBO is desupported and the CBO should be used instead.

Saves execution plan
The execution plan is stored in the Shared Pool. The next time the same SQL statement is issued, the work of parsing, evaluating, and determining the execution plan does not have to be done. Executing SQL statements becomes more efficient when the work is shared.

The critical factor is the second step of SQL parsing, "Search shared SQL area." This is where SQL standards for individual statements become important. SQL cannot be shared within the SGA unless it is absolutely identical. Statement components that must be the same include:

Word case (uppercase and lowercase characters)
 
Whitespace
 
Underlying schema objects
The following SQL statements are not the same and will not be shared within the SGA:

sql> SELECT NAME FROM S_CUSTOMER WHERE ID = 212;
sql> SELECT NAME FROM S_CUSTOMER WHERE ID = 213;
sql> SELECT NAME FROM S_CUSTOMER WHERE ID = :b1;
sql> SELECT name FROM s_customer WHERE id = 212;
sql> SELECT NAME FROM S_CUSTOMER WHERE ID=212;
sql> SELECT NAME
FROM S_CUSTOMER
WHERE ID=212;
Oracle recognized that two SQL statements can differ only in their literal values. For instance, the first two statement above are identical except for the value on the right side of the equals sign (212 in the first statement and 213 in the second statement). It would be nice to be able to share the cursors in the Shared Pool.

In Oracle 8i, the CURSOR_SHARING initialization parameter was introduced to help with this problem. The default for CURSOR_SHARING is EXACT, meaning that two cursors must match exactly in order for the cursors to share the same location in the Shared Pool. In Oracle 8i,  the CURSOR_SHARING parameter can be set to FORCE, which tells the database that if two SQL statements differ only in their literals, their cursors will be shared. When set to FORCE, the database will replace all literals with bind variables for you. At first glance, the CURSOR_SHARING parameter seemed to be a silver bullet for performance problems plaguing applications that did not use bind variables. Unfortunately, this new parameter's effects did not take into affect the distribution of the data. Below is an example showing why setting CURSOR_SHARING to FORCE can have a problem. Look at the data distribution of the STYLE_NUM column of our BOX table.

SQL> select style_num,count(*) from box
2 group by style_num;
STYLE_NUM COUNT(*)
---------- ----------
10 1
11 1
12 2
13 8
There is only one row of data where STYLE_NUM is 10 or 11, but eight rows of data where STYLE_NUM is 13. If the following two queries are issued, then the database shares their cursors:

select * from box where style_num = 10;
and

select * from box where style_num = 11;
In these cases, only one row would be returned and the hope is to use the index on the STYLE_NUM column. Now that those queries have been executed, if CURSOR_SHARING is set to FORCE and the following query is issued

select * from box where style_num = 13;
this will use the same index since it used the same cursor. In this case, an index is not desirable since 8 of the table's 12 rows are being returned. It would be desirable to not use the index.

Oracle 9i fixed this problem by adding another value to the CURSOR_SHARING initialization parameter, SIMILAR. If CURSOR_SHARING is set to SIMILAR, then the database will only share cursors if they differ in their literals and that literal value would not significantly alter the execution plan. If that literal value would not alter the execution plan, then the cursor will not be shared. A new cursor will be used instead. The CURSOR_SHARING = SIMILAR initialization parameter value is a much better choice.

Iterative Processing

1>
Never Declare the FOR Loop Index

FOR year_ind IN 1 .. 20
LOOP
calc_profits (year_ind);
END LOOP;
Do not declare the loop index variable (year_ind in the example above). PL/SQL does that for you automatically. For both numeric and cursor FOR loops, the identifier after the FOR keyword is automatically declared by PL/SQL with type BINARY_INTEGER or a record to match the cursor. If you declare a variable with same name as loop index, it is a different variable.

You could refer to the FOR loop index outside the loop and your code will compile, but it will not be doing what you think or what you want.

This code will compile, but it will not work as intended. This kind of code is very hard to understand and debug.

DECLARE
CURSOR emp_cur IS
SELECT empno, ename FROM emp;
emp_rec emp_cur%ROWTYPE;
BEGIN
FOR emp_rec IN emp_cur
LOOP
display_emp (emp_rec.ename);
END LOOP;
IF emp_rec.ename = 'FEUERSTEIN'
THEN
give_raise
(emp_rec.empno, 1000000);
END IF;
END;
Suppose you need the value of the loop index (year_count in the following example) for debugging:

DECLARE year_count INTEGER := NULL;
BEGIN
FOR year_count IN 1 .. 20
LOOP
calc_pnl (year_count);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE
('Error in year ' ||
TO_CHAR (year_count));
END;
In this case use a local variable and copy the loop index to local variable:

DECLARE my_count INTEGER := NULL;
BEGIN
FOR year_count IN 1 .. 20
LOOP
my_count := year_count;
calc_pnl (year_count);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE
('Error in year ' ||
TO_CHAR(my_count));
END;

----
2>


Avoid Unstructured Exits from Loops
Do not EXIT or RETURN out of a FOR loop.
A FOR loop should only be used when you want to execute the body a fixed number of times.
Stay for the duration or use a different loop construct.
Note: If an exception is raised and the loop stops, that is a legitimate “early termination.”
Do not use the EXIT syntax in a WHILE loop.
The loop should be terminated only when the condition in the boundary evaluates to FALSE.
-----
3>
Iteration control is obtained through the use of LOOP constructs. LOOP constructs fall into three general forms:
Infinite loops with IF or EXIT WHEN control exits
Counted loops using FOR control statements
Conditional loops using the WHILE conditional loop control statement
The general form of a LOOP construct is:

BEGIN
LOOP
processing statements
END LOOP;
END;
Loops can be labeled so that control can pass in and out of a loop based on various conditional statements within the body of the procedure or function. Labels are surrounded by a double set of angle brackets (<< >>):

BEGIN
...
<>
LOOP
processing statements
EXIT WHEN condition;
END LOOP;
IF condition THEN
GOTO gamma_loop;
END IF;
END;
NOTE: You can use labeled loops with GOTO in your PL/SQL programs, but we recommend that you avoid using GOTO whenever possible. It results in code that is hard to understand, maintain and enhance.

Loop exits are accomplished three ways:

Normal completion of a counted loop
Forced exit via the EXIT command based on a conditional statement (IF-THEN) or WHILE
Conditional exit based on EXIT WHEN statement
Loops can be nested to any depth within the length limits of a PL/SQL block.
Conditional LOOP Control
Conditional LOOP control is established via the WHILE command structure. The general format of the WHILE structure is:

WHILE condition LOOP
action statements
END LOOP;
The WHILE controlled loop can be set to exit for any SQL or PL/SQL verifiable condition such as no more records found, boolean record conditions, or specific record values. Some predefined WHILE construct conditions are:

%FOUND - Check for existence of records in a table or view
%NOTFOUND - Check for the non-existence of records in a table or view
%ISOPEN - Check if a particular cursor is open or closed
%ROWCOUNT - Check on number of rows processed, take action such as exiting the loop if a certain number is shown.
The conditions that variables can be checked for include:

<>
not equal
!=
not equal
~=
Like
^=
Not Like
<=
Less than or equal to
>=
Greater than or equal to
=
Equal to
>
Greater than
<
Less than
IS (NULL | NOT NULL)
Check for null values
The condition can also be any allowed expression such as x+y=10.

The example on the following screen is from the DBMS_UTILITY package, TABLE_TO_COMMA procedure ...

PROCEDURE table_to_comma ( tab IN uncl_array,
tablen OUT BINARY_INTEGER
list OUT VARCHAR2) is
temp VARCHAR2(6500) := '';
i BINARY_INTEGER := 1;
BEGIN
IF tab(i) IS NOT NULL THEN
temp := tab(i);
i := i + 1;
WHILE tab(i) IS NOT NULL LOOP
temp := temp || ',' || tab(i);
i := i + 1;
END LOOP;
END IF;
tablen := i-1;
list := temp
EXCEPTION WHEN NO_DATA_FOUND THEN
tablen := i-1;
list := temp;
END;
In the example procedure, the user passes the procedure a single column list of table names. The procedure first checks if the list starts with a null (invalid list). If it starts with a name value, it then passes control to the WHILE loop which appends the subsequent table names to the first one with a comma separating them. Once the loop runs out of names (tab(i) equates to NULL or no-data-found) processing ends and the comma separated list is moved from the temp variable to the returned variable list. The length of the list equates to the value of i-1 and this is loaded into the tablen return variable.

The WHILE loop is a top exiting loop. This means that if the first check of the condition produces a false condition, the loop doesn't execute at all.

To get the functionality of a LOOP UNTIL, REPEAT UNTIL or other bottom exit loop structures use the EXIT WHEN statement as shown in the LOOP-END LOOP; examples below. A bottom exit loop can also be simulated by using a boolean variable that is set by a specific condition occurring at the bottom of the loop:

fini := FALSE;
WHILE NOT fini LOOP
action statements
IF condition THEN fini := TRUE;
END LOOP;
This is functionally equivalent to:

LOOP
action statements
EXIT WHEN condition;
END LOOP;
Example is taken from the DBMS_UTILITY package, COMMA_TO_TABLE procedure:

PROCEDURE comma_to_table( list IN VARCHAR2,
tablen OUT BINARY_INTEGER,
tab OUT uncl_array ) IS
nextpos BINARY_INTEGER;
oldpos BINARY_INTEGER;
done BOOLEAN;
i BINARY_INTEGER;
len BINARY_INTEGER;
dummy VARCHAR2(128);
BEGIN
-- get ready
nextpos := 1;
done := FALSE;
i := 1;
len := NVL(LENGTH(list),0);
WHILE NOT done LOOP
oldpos := nextpos;
dbms_utility.name_tokensize( SUBSTR(list, oldpos),
dummy, dummy, dummy, dummy, nextpos );
tab(i) := SUBSTR( list, oldpos, nextpos );
nextpos := oldpos + nextpos;
IF nextpos > len THEN
done := TRUE;
ELSIF SUBSTR( list, nextpos, 1) = ',' then
nextpos := nextpos + 1;
ELSE
raise_application_error ( -20001,
'comma-separated list invalid near ' ||
SUBSTR( list, nextpos-2, 5));
END IF;
i := i + 1;
END LOOP;
-- handle the end of the list
tab(i) := NULL;
tablen := i-1;
END;
The previous example takes a comma separated list of names and converts it to a column list with a null at the end. Note the boolean variable is set initially to TRUE and the value is reset to FALSE when the variable nextpos exceeds the length of the comma separated list, len.

Loop Iteration Control
Iterative loop control, that is, controlling the number of times a loop executes is accomplished via the FOR loop construct in PL/SQL. Another method would involve the setting of a count variable, iteration of this variable and then exiting the loop when the desired number of iterations is met. This second method is less flexible and more difficult to program than a simple FOR construct.
The format of a FOR construct is:

FOR counter IN [REVERSE] lower...higher LOOP
desired action statements;
END LOOP;
Unfortunately there is no way to control non-sequential iteration via the loop itself, for example, only processing on even, odd, multiple of five, etc. However, this can be accomplished by adding a call to a mathematical function and processing accordingly.

FOR deuce IN 2..10 LOOP
IF MOD(deuce,2)=0 THEN
statements;
END IF;
END LOOP;
In the above example the counter, deuce, counts through the integers 2,3,4...10, but because the actual loop statements occur within the IF construct that uses the modula value of deuce divided by 2 (modula will produce zero if the result has no remainder) to determine if processing should continue.

Dynamic ranges can be performed by selecting a count into a bind variable and then using this bind variable as the higher range value.

SELECT COUNT(UNIQUE(owner)) INTO loop_ctr FROM DBA_TABLES;
FOR ctr IN 1..loop_ctr LOOP
processing statements;
END LOOP;
The variable used as the loop counter has no value outside of the loop, any attempt to use it outside of the loop will fail. However, if you store the results of the loop iteration into a second bind variable, that variable can be used outside of the loop.

DECLARE
loop_value integer;
FOR i IN 1..10 LOOP
processing statements;
loop_value := i;
END LOOP;
SUM := 1 - loop_value; --(Using i - 1 won't work since i is undefined)
A FOR loop can be exited using EXIT WHEN if you need for processing to end prematurely (such as at a predefined maximum value in a calculation). If you label a set of nested loops, you can exit to any level of the nest by specifying the loop label to exit from.

<>
FOR i IN 1..2000 LOOP
SELECT owner INTO owns FROM assets WHERE rowcount = i;
<>
FOR j IN 1..200 LOOP
FETCH c1 INTO total_value;
EXIT alpha WHEN c1%NOTFOUND;
END LOOP beta;
END LOOP alpha;
Example from the DBMS_ALERT package, the SESSION_HASH function.

function session_hash(name varchar2) return integer is
hashval binary_integer := 0;
strlen binary_integer := lengthb(name);
begin
for i in 1..strlen loop
hashval := hashval+ ascii(substrb(name, i,1));
end loop;
return 2000001021 + (hashval mod 1021);
end;
This example shows a simple FOR loop. One unique thing about this FOR loop is that it uses a bind variable for its upper bound that is set based on the length of a string passed to the function.

The next example is extracted from the DBMS_UTILITY package, and the COMPILE_SCHEMA procedure (this isn't the complete procedure).

for rec in c1(schema) loop
if rec.object_type<> 'PACKAGE BODY' and (schema <> 'SYS' or
rec.object_name not in ('DBMS_UTILITY', 'DBMS_SESSION',
'DBMS_TRANSACTION')) then
begin
dbms_ddl.alter_compile( rec.object_type, schema,
rec.object_name);
exception when NOT_EXIST_OR_NO_PRIV then
raise_application_error(-2000,
'You have insufficient privileges for an object in this schema.');
end;
end if;
end loop;
In this example, the loop is controlled by the value returned by the c1 cursor. Additional control is exerted by the if-then construct that prevents compilation of specific objects and schemas.

----
Always choose the loop which offers the simplest, cleanest solution using the least amount of code. Sometimes it is a SQL statement!
If the loop executes for a fixed number of times (by number or number of records), use the FOR loop.

If you want to make sure the loop executes at least once, use the simple loop.

If you want the termination condition evaluated before the body executes, use the WHILE loop.

You should avoid redundant coding for initialization and body of loops. You must initialize the values which are used in the termination condition.

If the code used to initialize is similar to the body, then switch to a simple loop. For example, the following WHILE loop relies on two fetch statements:

OPEN emp_cur;
FETCH emp_cur INTO emp_rec;
WHILE emp_cur%FOUND AND
emp_rec.sal < avg_sal
LOOP

FETCH emp_cur INTO emp_rec;
END LOOP;
whereas a simple loop has a single fetch:


LOOP
FETCH emp_cur INTO emp_rec;
EXIT WHEN emp_cur%NOTFOUND OR
emp_rec.sal >= avg_sal;
END LOOP;

----
Do not use PL/SQL where you can use a SQL statement instead. The SQL statement will often be much faster. You should replace PL/SQL loops with single SQL statements when possible.
Slower PL/SQL Version:

FOR year_count IN 1 .. 20
LOOP
INSERT INTO v1table1
SELECT * FROM v1table2
WHERE yr_nu = year_count;
END LOOP;
Faster, Simpler SQL Version:

INSERT INTO v1table1
SELECT * FROM v1table2
WHERE yr_nu BETWEEN 1 AND 20;

PL/SQL Loops Versus SQL Processing
One of the indicators that a numeric FOR loop is being used incorrectly is that the loop index is not used for anything but traffic control inside the loop. The actual body of executable statements completely ignores the loop index. When that is the case, there is a good chance that you don’t need the loop at all.
When should you use standard SQL to accomplish your task and when should you rely on PL/SQL loops? Sometimes the choice is clear: if you do not need to interact with the database, then there is clearly no need for SQL. In addition, SQL can’t always provide the necessary flexibility to get the job done. Conversely, if you are performing a single record insert into a table then there is no need for a loop. Often, however, the choice is less obvious. For example, a SELECT statement queries one or more rows from the database. A cursor FOR loop also queries rows from the database based on a SELECT statement. In fact, PL/SQL and native SQL often can both accomplish the task at hand. Given that fact, you will need to choose your implementation according to more subtle issues like performance and maintainability of code.

Before we look at some examples of scenarios which call for one or the other approach, let’s review the difference between the implicit looping of the SQL set-at-a-time approach and the PL/SQL loop.

SQL statements such as SELECT, UPDATE, INSERT, and DELETE work on a set of data. That set (actually, a collection of rows from a table or tables) is determined by the WHERE clause (or lack thereof) in the SQL statement. SQL derives much of its power and effectiveness as a database language from this set-at-a-time processing approach. There is, however, a drawback: SQL often does not give you the flexibility you might need to handle individual records and specialized logic which must be applied differently to different records.

The PL/SQL cursor offers the ability to access a record at a time and to take action based on the contents of that specific record. It is not always clear, however, which language component would best fit the needs of the moment. We have seen a number of programs where developers went overboard in their drive to PL/SQL-ize the SQL access to their data. This happens most frequently when using a cursor FOR loop.

The PL/SQL block in the code below moves checked-out pets from the pet hotel occupancy table to the pets_history table using a cursor FOR loop. For each record fetched (implicitly) from the cursor, the body of the loop first inserts a record into the pet_history table and then deletes the record from the occupancy table:

DECLARE
CURSOR checked_out_cur IS
SELECT pet_id, name, checkout_date
FROM occupancy
S checkout_date IS NOT NULL;
BEGIN
FOR checked_out_rec IN checked_out_cur
LOOP
INSERT INTO occupancy_history (pet_id, name, checkout_date)
VALUES (checked_out_rec.pet_id, checked_out_rec.name,
checked_out_rec.checkout_date);
DELETE FROM occupancy WHERE pet_id = checked_out_rec.pet_id;
END LOOP;
END;
This will work just fine but do we really need to use a cursor FOR loop to accomplish this task? Suppose 20 animals checked out today. This block of code will then perform 20 distinct inserts and 20 distinct deletes. The same code can be written completely within the SQL language as shown below:

INSERT INTO occupancy_history (pet_id, name, checkout_date)
SELECT pet_id, name, checkout_date
FROM occupancy
WHERE checkout_date IS NOT NULL;
DELETE FROM occupancy WHERE checkout_date IS NOT NULL;
Here a single insert (making use of the INSERT...SELECT syntax) and a single delete (which now checks for the checkout_date and not the employee_id) accomplish the transfer of the data to the history table. This reliance on native SQL, without the help of PL/SQL, allows you to take full advantage of array processing. It significantly reduces network traffic in a client-server environment because only two SQL statements (instead of 40) are passed to the RDBMS.

The cursor FOR loop was not really needed here; the body of the loop did not perform any procedural logic which could not be handled by SQL itself. If, on the other hand, the program needed to selectively reject records for the transfer, or otherwise perform procedural logic not possible within SQL, then either the cursor FOR loop or a WHILE loop would make sense.

----
Naming Loop Indexes
How would you like to try to understand—much less maintain—code that looks like this?
FOR i IN start_id .. end_id
LOOP
FOR j IN 1 .. 7
LOOP
FOR k IN 1 .. 24
LOOP
build_schedule (i, j, k);
END LOOP;
END LOOP;
END LOOP;
It is hard to imagine that someone would write code based on such generic integer variable names (right out of Algebra 101), yet it happens all the time. The habits we pick up in our earliest days of programming have an incredible half-life. Unless you are constantly vigilant, you will find yourself writing the most abominable code. In this case, the solution is simple: use variable names for the loop indexes that are meaningful and therefore self-documenting:

FOR focus_account IN start_id .. end_id
LOOP
FOR day_in_week IN 1 .. 7
LOOP
FOR month_in_biyear IN 1 .. 24
LOOP
build_schedule (focus_account, day_in_week, month_in_biyear);
END LOOP;
END LOOP;
END LOOP;
Software programmers should not have to make Sherlock Holmes-like deductions about the meaning of the start and end range values of the innermost FOR loops in order to understand their purpose. Use names that self-document the purposes of variables and loops. That way other people will understand your code and you will remember what your own code does when you review it three months later.

----
Premature FOR Loop Termination
 The syntax of the FOR loop states your intent explicitly and should only be a FOR loop if you know in advance how many times the loop needs to execute. For example, the following loop is very clear:

FOR month_count IN 1 .. 12
LOOP
analyze_month (month_count);
END LOOP;
It states: “I am going to execute the analyze_month procedure 12 times, once for each month in the year.” Straightforward and easy to understand.

Now take a look at the next numeric FOR loop:

FOR year_count IN 1 .. years_displayed
LOOP
IF year_count > 10 AND :store.status = 'CLOSED'
THEN
EXIT;
END IF;
analyze_month (month_count);
END LOOP;
In this case, the loop boundary states: “Run the loop for the number of years displayed in the form.” Yet in the body of the loop, an IF statement allows a premature termination of the loop. If the year count (the loop index) exceeds 10 and the current store status is CLOSED, then an EXIT statement is issued and the loop halts.

This approach is very unstructured and contradictory. The loop boundary states one thing, but the loop body executes something very different.

You should always let a FOR loop (whether numeric or cursor) complete its stated number of iterations. If you do need to conditionally halt loop execution, you should choose either an infinite or a WHILE loop. The above FOR loop could, for example, be easily recoded as follows:

FOR year_count IN 1 .. LEAST (years_displayed, 11)
LOOP
analyze_month (month_count);
END LOOP;
Similar guidelines apply to the infinite and WHILE loop.

----
EXIT and EXIT WHEN Statements
Neither the FOR loop nor the WHILE loop should use the EXIT and EXIT WHEN statements. Consider the following WHILE loop:

WHILE more_records
LOOP
NEXT_RECORD;
EXIT WHEN :caller.name IS NULL;
END LOOP;
In this case, even though the loop boundary indicates that the body should execute until more_records evaluates to FALSE, the EXIT WHEN in the loop body bypasses that condition. Instead of using EXITs in your WHILE loop, you should always rely exclusively on your loop condition to determine whether the looping should continue. The previous WHILE loop can be redesigned as follows:

WHILE more_records
LOOP
NEXT_RECORD;
more_records := :caller.name IS NOT NULL;
END LOOP;
RETURN statement
The RETURN statement will cause instant termination of a function and return the specified value back to the calling program.

----
Never Use a RETURN Statement Inside a Loop
Unfortunately, such things have been known to happen. In the following example of terrifically poor programming practice, the FOR loop is interrupted—not with an EXIT, which would be unstructured enough, but with a RETURN statement:

BEGIN
the_rowcount := Get_Group_Row_Count( rg_id );

FOR j IN 1..the_rowcount
LOOP

col_val := Get_Group_Char_Cell( gc_id, j );

IF UPPER(col_val) = UPPER(the_value)
THEN
RETURN j;
END IF;

END LOOP;
END;
Once again, if the loop should be conditionally terminated, do not use a FOR loop. Instead, use a WHILE or infinite loop and then issue the RETURN after the loop is completed. The following code replaces the unstructured IF statement shown above:

BEGIN
/* Initialize the loop boundary variables. */
row_index := 0;
the_rowcount := Get_Group_Row_Count (rg_id);

/* Use a WHILE loop. */
WHILE row_index <= the_rowcount AND
match_not_found
LOOP
row_index := row_index + 1;
col_val := Get_Group_Char_Cell (gc_id, row_index);
match_not_found := UPPER (col_val) != UPPER (the_value)
END LOOP;

/* Now issue the RETURN statement. */
RETURN row_index;
END;
The same rules apply to the use of a GOTO. If you use a GOTO to exit from a loop, you bypass the logical structure of the loop. You end up with code that is very difficult to trace, debug, fix, and maintain.

-----

Avoiding the Phony Loop
You should not use a numeric FOR loop if you cannot specify in a range scheme of lowest and highest bounds the number of times the loop must execute. Just because you know the number of iterations of some code, however, doesn’t mean that you should use a loop.

We have run across a number of programs which execute variations on this kind of FOR loop:

FOR i IN 1 .. 2
LOOP
IF i = 1
THEN
give_bonus (president_id, 2000000);
ELSIF i = 2
THEN
give_bonus (ceo_id, 5000000);
END IF;
END LOOP;
This loop provides hefty bonuses to the president and CEO of a company. While it might appear that we need to use the loop so the code executes twice to make sure both the president and CEO receive their compensation this code should not be inside a loop. It does not need iteration to perform its job; the LOOP syntax just confuses the issue.

The two sections within the IF-THEN-ELSE construct in the previous example both need to be executed all the time; this is straight sequential code and should be written as follows:

give_bonus (president_id, 2000000);
give_bonus (ceo_id, 5000000);
 

IF statements

Avoid Unnecessary Nested IFs

The following statements are equivalent. The flat structure expresses the logic more clearly and with less code.
Nested:

IF
THEN
...
ELSE
IF
THEN
...
ELSE
IF
THEN
...
ELSE
IF
THEN
...
END IF;
END IF;
END IF;
END IF;
Flat:

IF
THEN
...
ELSIF
THEN
...
ELSIF
THEN
...
ELSIF
THEN
...
END IF;

-----
Generally, you will want to use an ELSIF statement instead of nested IFs.
A good candidate for a nested IF, however, arises when one condition is much more resource-intensive than the other. Suppose condition A consumes .05 CPU seconds and condition B consumes 10 minutes. You don’t want to execute B unless A is TRUE -- and you don’t want to rely on the compiler to decide which clause is evaluated first.

IF condition A
THEN
IF condition B
THEN
...
END IF;
END IF;

-----
The implication of ELSIF clauses is that if one condition is fulfilled, all others would fail -- they are mutually exclusive. The following IF statement is a classic misuse of ELSIF clauses. It might not cause any errors, but that would just be a matter of luck. In many cases, the issue of exclusivity is less obviously determined.

IF sal BETWEEN 0 AND 10000
THEN
...
ELSIF sal BETWEEN 10000 AND 20000
THEN
...
ELSIF sal BETWEEN 20000 AND 30000
THEN
...
END IF;

-----
You can code real Boolean variables and literals (TRUE, FALSE and NULL values) in PL/SQL.
Boolean variables and functions allow you to greatly improve readability of programs. You can hide complex expressions behind a name, which describes the expression.

Compare the two IF statements below.

IF total_sal BETWEEN 10000 AND 50000 AND
emp_status (emp_rec.empno) = 'N' AND
(MONTHS_BETWEEN
(emp_rec.hiredate, SYSDATE) > 10)
THEN
give_raise (emp_rec.empno);
END IF;
The second IF statement:

IF eligible_for_raise (emp_rec.empno)
THEN
give_raise (emp_rec.empno);
END IF;

-----
Avoid IF With Booleans
Sometimes you will code or come across conditional statements which, while valid, are unnecessary and cumbersome. Replace this IF statement:

IF hiredate < SYSDATE
THEN
date_in_past := TRUE;
ELSE
date_in_past := FALSE;
END IF;
With this:

date_in_past :=
hiredate < SYSDATE;
You can assign a Boolean expression directly to a Boolean variable

-----

Employ indentation rigorously with IF statements to show logical levels. Each IF, THEN, ELSE, ELSIF, and END IF should align within each level. For example:
Nested IFs:

IF
THEN
...
ELSE
IF
THEN
...
ELSE
END IF;
END IF;
IF with multiple ELSIFs:

IF
THEN
...
ELSIF
THEN
...
ELSIF
THEN
...
END IF;