4.7 Create a PL/SQL Procedure

Create a procedure that lists all books with a specified rating. You can then call this procedure with an input parameter (a number from 1 to 10), and the output will be all the titles of all books with that rating.

To create the procedure:

1. Enter the following procedure text:

   CREATE OR REPLACE
   PROCEDURE list_a_rating(in_rating IN NUMBER) AS
     matching_title VARCHAR2(50);
     TYPE my_cursor IS REF CURSOR;
     the_cursor my_cursor;
   BEGIN
     OPEN the_cursor
       FOR 'SELECT title 
              FROM books 
             WHERE rating = :in_rating'
       USING in_rating;
     DBMS_OUTPUT.PUT_LINE('All books with a rating of ' || in_rating || ':');
     LOOP
       FETCH the_cursor INTO matching_title;
       EXIT WHEN the_cursor%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE(matching_title);
     END LOOP;
     CLOSE the_cursor;
   END list_a_rating;

2. Click Run Script.

   The following notificaton is displayed in the Script Output tab:

   Procedure LIST_A_RATING compiled

3. To run this procedure, right-click LIST_A_RATING in the Connections navigator and select Run. The parameter and it's properties are displayed.

4. Click View PL/SQL.

5. Change IN_RATING => IN_RATING to IN_RATING => 10, and click Run Statement.

   
   Description of the illustration plsql_run_procedure.png

   The result is shown in the Script Output pane:

   All books with a rating of 10:                                                  
   Moby Dick                                                                       
   Software Wizardry