Lesson 4 | Using parameters with cursors |
Objective | Identify correct syntax for using parameters with cursors. |
CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_name {IN | OUT | IN OUT} datatype [{:= | DEFAULT} value ...) ] BEGIN Procedure body END;
Each location in the table below is designated by a rectangle in the diagram above.
Location 1 | Choose one of these three modes: IN, OUT, or IN OUT. |
Location 2 | Specify a datatype, such as NUMBER, VARCHAR2, DATE, and BOOLEAN. Do not place a length on the datatype. |
Location 3 | Optional default value can be coded: = 'value' or DEFAULT 'value' |
Location 4 | Any incoming parameter that has a default value can be omitted from the call. To use positional method, however, you must list parameters with default values last, so that values listed in the call are assigned to the first (non-default) parameters. |
Location 5 | Example of a procedure that has two parameters: one IN, and one OUT. |
Location 6 | Example of a PL/SQL block that calls the above procedure. Two local variables are declared first. |
Examine the following series of images to see how the syntax works and learn how to open a cursor that uses parameters in PL/SQL.
CURSOR cursor_name [(parameter_name {IN} datatype [ {:= | DEFAULT} VALUE]...)] IS QUERY1) Here is the syntax of a cursor containing parameters. The primary difference between defining parameters for a cursor and parameters for procedures is that cursors can only use IN parameters.
DECLARE ..... CURSOR READ_CUSTOMER (I_CITY IN NUBMER, I_STATE VARCHAR2 := 'CA') AS SELECT FIRSTNAME, LASTNAME FROM CUSTOMER WHERE CUST_ID = I_CITY AND STATE = I_STATE ... BEGIN ... END; END;
DECLARE ..... CURSOR READ_CUSTOMER (I_CITY IN NUMBER, I_STATE VARCHAR2 := 'CA') AS SELECT FIRSTNAME, LASTNAME FROM CUSTOMER WHERE CUST_ID = I_CITY AND STATE = I_STATE ... BEGIN ... END; END;3) Continuing with the example, this screen shows you how to open the cursor while specifying values for the parameters. (The parameters could have been specified with variables, but I used literal values just to simplify the example) The same three methods of specifying parameters (position, assignment, and mixture) you saw demonstrated for procedure parameters can be used for cursor parameters. This screen uses the first (most common) method
DECLARE ..... CURSOR READ_CUSTOMER (I_CITY IN NUMBER, I_STATE VARCHAR2 := 'CA') AS SELECT FIRSTNAME, LASTNAME FROM CUSTOMER WHERE CUST_ID =I_CITY AND STATE = I_STATE; ..... BEGIN ... OPEN READ_CUSTOMER('San Francisco');4) This screen shows how you can omit the last parameter if you desire. This is possible because the second parameter has a default value defined. Refer to the Slide Show in the previous lesson if you want to see the other two methods for specifying parameters.
PROCEDURE explain_joke (main_category_in IN joke_category.category_id%TYPE) IS /* || Cursor with parameter list consisting of a single || string parameter. */ CURSOR joke_cur (category_in IN VARCHAR2) IS SELECT name, category, last_used_date FROM joke WHERE category = UPPER (category_in); joke_rec joke_cur%ROWTYPE; BEGIN /* Now when I open the cursor, I also pass the argument */ OPEN joke_cur (main_category_in); FETCH joke_cur INTO joke_rec;