Home > SQL Developer Tutorial: Cre... > 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, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Procedures node in the schema hierarchy on the left side, select New Procedure, and enter the following information using the Create PL/SQL Procedure dialog box.
Object Name: list_a_rating
Click OK. A source window for the new procedure is opened. Enter (or copy and paste) the following procedure text, replacing any existing 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;
This procedure uses a cursor (named the_cursor) to return only rows where the book has the specified rating (in_rating parameter), and uses a loop to output the title of each book with that rating.
Click the Save icon to save the procedure.
As a usage example, after creating the procedure named LIST_A_RATING, if you have inserted data into the BOOKS table (for example, using the INSERT statements in Insert Data into the Tables), you could use the following statement to return all books with a rating of 10:
CALL list_a_rating(10);
To run this procedure within SQL Developer, right-click LIST_A_RATING in the Connections navigator hierarchy display and select Run. Under PL/SQL Block in the Run PL/SQL dialog box, change IN_RATING => IN_RATING to IN_RATING => 10, and click OK. The Log window display will now include the following output:
All books with a rating of 10: Moby Dick Software Wizardry
Related Topics
SQL Developer Tutorial: Creating Objects for a Small Database