Knew abt what u create

A word abt loop is necessary as it is the basic one which strikes every programmer when he sees anything to get repeated :)
But mostly the code written inside the loops should re-looked carefully again, if u are to make sure that the code is fine tuned.As in loops any inefficiency inside a loop's body will be magnified by the multiple executions of that code.
A common mistake is it put the code that is static for the loop, inside the loop.Identify those then extract the static code, assign the outcomes of that code to one or more attribute, and then reference those attributes inside the loop.
For ex:

CREATE OR REPLACE PROCEDURE summarize_reviews (
   summary_title_in IN VARCHAR2,
   isbn_in IN book.isbn%TYPE)
IS
 CURSOR review_cur IS
        SELECT text, TO_CHAR (SYSDATE, 'MM/DD/YYYY') today
        FROM book_review
       WHERE isbn = isbn_in;
BEGIN
   FOR review_rec IN review_cur
   LOOP
      IF LENGTH (review_rec.text) > 100
      THEN
         review_rec.text := SUBSTR (review_rec.text, 1, 100);
      END IF;
      
      review_pkg.summarize (
         UPPER (summary_title_in),
         today,
         UPPER (review_rec.text)
         );
   END LOOP;
END;

Here looking at the example above
1 . Cursor review_cur select SYSDATE with each row of query.SYSDATE is going to be same
2. the field of the review_rec record is overwritten again & again , is it needed ?
3. Look at the summary_title_in argument never changes, it UPPER case'd in each iteration of the loop. can't we put this outside the loop :).
4. And one more things is , watch out ... checking the length of the text for each row and then SUBSTR (and UPPER case), why not just SUBSTR inside SQL?
Which one is better Pl/Sql or Sql ? to write this...

Just re-written the code to make the changes said

CREATE OR REPLACE PROCEDURE summarize_reviews (
   summary_title_in IN VARCHAR2,
   isbn_in IN book.isbn%TYPE)
IS
   l_summary book_types.summary_t
      := UPPER (summary_title_in);
   
   l_today CONSTANT DATE := TRUNC (SYSDATE);
      
   CURSOR review_cur IS
      SELECT UPPER (SUBSTR (text, 1, 100)) text
        FROM book_review
       WHERE isbn = isbn_in;
BEGIN
   FOR review_rec IN review_cur
   LOOP
      review_pkg.summarize (
         l_summary, l_today, review_rec.text
         );
   END LOOP;
END;
/

In general, we can expect the performance of built-in functions such
as SUBSTR to work more efficiently in SQL than in PL/SQL.
so always mv the builtin function inside the Sql rather than Pl/Sql.
processing to the SQL layer whenever possible.

( Try to ) Exit once

Here's part of a program that compares two files for equality.
After reading the next line from each file, it checks for the following
conditions:

1. Did it reach the end of both files?
2. Are the lines different?
3. Did I reach the end of just one file?

In each case, set the "return value" for the function and also issue an EXIT statement:

LOOP
   read_line (file1, line1, file1_eof);
   read_line (file2, line2, file2_eof);
 
   IF (file1_eof AND file2_eof)
   THEN
      retval := TRUE;
      EXIT;    -- first time
   ELSIF (line1 != line2)
   THEN
      retval := FALSE;
      EXIT;    -- Second time repeated
   ELSIF (file1_eof OR file2_eof)
   THEN
      retval := FALSE;
      EXIT;    -- Third time repeated
   END IF;
END LOOP;


Sometimes it can be difficult to come up with just one EXIT statement.
This usually occurs when we need to check a condition at the beginning
and end of a loop.
We should also be careful to initialize your return value and
your loop terminator variable, to avoid unwanted NULL values that
might disrupt your logic.

Then rewrite this loop body as follows

LOOP
   read_line (file1, line1, file1_eof);
   read_line (file2, line2, file2_eof);
 
   IF (file1_eof AND file2_eof)
   THEN
      retval := TRUE;
      exit_loop := TRUE;
   ELSIF (line1 != line2)
   THEN
      retval := FALSE;
      exit_loop := TRUE;
   ELSIF (file1_eof OR file2_eof)
   THEN
      retval := FALSE;
      exit_loop := TRUE;
   END IF;
   EXIT WHEN exit_loop;  -- Only one time
END LOOP; 
A single EXIT is especially important in large, complex loop bodies;
It allows you to more easily trace and debug your code.

Challenges lies on how badly the loop was written initially,
we may need to perform substantial restructuring to improve the loop
code.

Dont ever try to redo the thing !

Sometimes, we will may write conditional statements that may be valid still, and are unnecessary and cumbersome. Such statements often reflect a lack of understanding about how you can and should use language structures.
for exempli gratia :

DECLARE
   boolean_variable BOOLEAN;
BEGIN
   IF boolean_variable = TRUE
   THEN
      ...
   ELSIF boolean_variable = FALSE
   THEN
      ...
   END IF;
END;

what we are gonna acheive by redoing the thing already
the compiler is doing for us.
( havent u heard of word like " never try to re-invent the wheel " ) ;;)

here goes the simplification of the code .

DECLARE
   boolean_variable BOOLEAN;
BEGIN
   IF boolean_variable
   THEN
      ...
   ELSIF NOT boolean_variable
   THEN
      ...
   END IF;
END;
It is implicit that the what the boolean is going to do for us,

one more classic example for the inefficient use of the boolena variable we can find at here;

IF DOB '<' SYSDATE THEN
   celebrated := TRUE;
ELSE
   celebrated := FALSE;
END IF; 

here u can see the idea behind this is to find the boolean, has it been celebrated.
may be simple thing here to notice will be.
there are only two state for the conditional statements.

celebrated := DOB '<' SYSDATE;

here watch out carefully, the DOB can be NULL ;

celebrated := NVL( DOB '<' SYSDATE);

The above statement offers a comparable expression ;) is it nt ?
( doing this may make ur code look expressive )

Best practices IF ELSE

When we need to write conditional logic that has several mutually exclusive clauses ;)
(in other words, if one clause is TRUE, no other clause evaluates to TRUE) or
(only one condition to be evaluated at a given point of time)
use the ELSIF construct: , never try to use IF ENDIF construct

suppoose ur intention is to write something like this :

BEGIN
   IF count  = 1
   THEN
      process_A;
   END IF;
   IF count  = 2
   THEN
      process_B;
   END IF;
   ...
   IF count  = 3
   THEN
      process_C;
   END IF;
END;

in the above case
Every IF statement is executed and each condition evaluated.
You should rewrite such logic as follows

BEGIN
   IF count = 1
   THEN
      process_A;
   ELSIF count  = 2
   THEN
      process_B;
   ...
   ELSIF count = 3
   THEN
      process_C;
   END IF;
END;

what is the benefit of these type of code

This structure clearly expresses the underlying "reality"
of your business logic:
if one condition is TRUE, no others can be TRUE.

ELSIF offers the most efficient implementation for
processing mutually exclusive clauses. When one
clause evaluates to TRUE, all subsequent clauses are ignored.

May be in the real world scenario we need to deal with
some convoluted logical expressions like following:

IF condA AND NOT ( condB OR condC ) 
THEN 
   process_A; 
ELSIF condA AND (condB OR condC) 
THEN 
   process_B; 
ELSIF NOT condA AND condD 
THEN
   process_C;
END IF;

from the above example u can very well see that
condition B & C are repeated in two of these conditional statement

It's also fairly common to get a headache trying to make sense
of the code we have written. You can often reduce the trauma
of headache by trading off the simplicity of the( code )
IF statement itself.
Rather than having seperate level for each condtion
we can club the clauses within multiple levels:

we will see ,how to re-write it :

IF condA 
THEN 
   IF (condB OR condC) 
   THEN 
      process_B; 
   ELSE 
      process_A;
   END IF; 
ELSIF condD 
THEN 
   process_C;
END IF; 

re-writing the code what we acheive

"if and when the logic changes, you can change one IF
clause without affecting the logic of others."

Always make your code easier to read and maintain. ;)
( may be for that only we are paid )

may b , now u may start to ask the question
how to write the code which is maintainable ?
Breaking an expression into smaller pieces can aid maintainability;