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.