Using Explicit Cursors  «Prev  Next»

Build a Cursor Block

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.