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:
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.
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:
You opened the cursor by typing the following text at the following prompts and pressing Enter:
4> BEGIN
5> OPEN get_product;
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;
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.');
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> /
SQL*Plus then displayed the result of the compilation of your block.