Using Explicit Cursors  «Prev  Next»

Lesson 8 Building a Cursor Block
ObjectiveDescribe how to Build a Cursor Block

Building Cursor Block

In this lesson, you will build a cursor block by using the concepts that you have learned so far.
Follow these steps to build a cursor block:
  1. Define a cursor.
  2. Open the cursor.
  3. Repeatedly fetch the data to process it.
  4. Close the cursor.

In the following Slide Show, the cursor block
  1. selects the product name and its inventory count from the product table,
  2. opens the cursor,
  3. fetches records into the cursor,
  4. displays the information, then
  5. closes the cursor.

Building Cursor Block using PL/SQL

1) In the DECLARE section, PL/SQL variables and the cursor are defined.
1) This PL/SQL block declares a variable pet_count as a NUMBER, a variable pet_name as a VARCHAR2 with a maximum length of 20 characters, and a cursor named pets. This cursor selects the PRODUCT_NAME and CURRENT_INVENTORY_COUNT columns from a table called PRODUCT where the PRODUCT_NAME is 'Canary'. 
DECLARE
 pet_count NUMBER;
 pet_name VARCHAR2(20);
 cursor pets is SELECT PRODUCT_NAME, 
  CURRENT_INVENTORY_COUNT
  FROM PRODUCT
  WHERE PRODUCT_NAME = 'Canary';


2) OPEN signals the opening of the cursor.
2) In this PL/SQL block, it checks whether the cursor pets is already open using the %ISOPEN attribute. If the cursor is not open, it then opens the cursor with the OPEN statement. 
BEGIN
    IF NOT pets%ISOPEN THEN
        OPEN pets;
    END IF;
END;

3) FETCH signals the retrieval of records into the cursor variable.
3) In this PL/SQL block, the FETCH statement is used to retrieve the next row from the cursor pets and store the values in the variables pet_name and pet_count. If there are no more rows to fetch, indicated by pets%NOTFOUND, the loop will exit. Then it uses DBMS_OUTPUT.PUT_LINE to display the number of items for pet_name. 
FETCH pets INTO pet_name, pet_count;
EXIT WHEN pets%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('NUMBER OF ' || pet_name || ' = ' || pet_count);

4) CLOSE signals the closing of the cursor after the processing of data is complete.
4) CLOSE signals the closing of the cursor after the processing of data is complete.
This is the closing section of a PL/SQL block. The CLOSE statement is used to close the previously opened cursor named pets. It is essential to close a cursor to free up resources once you have finished processing the data it retrieved. The END; statement signifies the end of the PL/SQL block. 
CLOSE pets;
END;


Ad Oracle PL/SQL Programming
Now you have learned about the basic structure of a cursor block and how to build one, how to open a cursor, how to fetch the records, and how to close the cursor.
In the next lesson, we will look at the cursor FOR loop, an alternate way to write cursor blocks. The following section discusses how to build a cursor block by walking you through the steps.

Course project: Build a cursor block

Your solution should have followed these steps:
  1. You connected to the database by using PETSTORE as the user name, GREATPETS as the password, and MYDB as the host string and clicked the OK button to continue.
  2. You started building the PL/SQL block by declaring a variable v_prod_id of type number and declaring a cursor called get_product that selects the product ID from product table for the product Rabbit. You typed the following text at the following prompts and pressed Enter: 
    SQL> DECLARE
2> v_prod_id NUMBER;
3> CURSOR get_product IS
SELECT PRODUCT_ID FROM PRODUCT
WHERE PRODUCT_NAME = 'Rabbit';
  3. You opened the cursor by typing the following text at the following prompts and pressing Enter:
    4> BEGIN
    5> OPEN get_product;
  4. You fetched the data into the predefined variable by by typing the following text after the prompt and pressing Enter:
    6> FETCH get_product INTO v_prod_id;
  5. You typed a record into the PET_CARE_LOG at the following prompt, and pressed Enter:
    7> INSERT INTO PET_CARE_LOG
    (PRODUCT_ID, LOG_DATETIME, LOG_TEXT)
    VALUES (v_prod_id, to_date('2-SEP-99 05:30PM','dd-mon-yy hh:miPM'), 'Rabbits eat fresh grass only.');
  6. You closed the cursor, committed the changes, and ended the PL/SQL block by typing the following text after each prompt and pressing Enter:
    8> CLOSE get_product;
    9> COMMIT;
    10> END;
    11> /
  7. SQL*Plus then displayed the result of the compilation of your block.


SEMrush Software