Oracle PL/SQL Programming: A Developer's Workbook

Chapter 10. Cursors

In one form or another, every SQL statement in your PL/SQL program is associated with either an implicit or an explicit cursor. You can think of a cursor as a pointer into the result set of a SQL statement; its job is to allow you to access and manipulate the data inside the set. This chapter tests your ability to define cursors for use in you PL/SQL programs, to process the data within a cursor, and to create maintainable and efficient cursor definitions.

Beginner

10-1.	What does it mean to be an implicit cursor in a PL/SQL program? Which of the following SQL statements are implicit cursors? BEGIN UPDATE preferences SET ice_cream_flavor = 'CHOCOLATE' WHERE name = 'STEVEN'; DECLARE CURSOR around_the_house_jobs_cur IS SELECT job, frequencey FROM adolescent_workload WHERE name = 'ELI'; DECLARE next_key employee.employee_id%TYPE; BEGIN SELECT employee_seq.NEXTVAL INTO next_key FROM dual; SQL> ALTER TABLE ceo_compensation MODIFY options NUMBER; FOR indx IN 1 .. 12 LOOP DELETE FROM ceo_compensation WHERE layoffs > 1000 * indx AND options > 100000; END LOOP;
10-2.	What does it mean to be an explicit cursor in a PL/SQL program?
10-3.	What predefined exceptions can be raised when a block executes an implicit cursor?
10-4.	How many times does the Oracle SQL engine attempt to fetch a row from the employee table in this block (assume that a row exists for employee_id = 150667)? DECLARE my_reward NUMBER; BEGIN SELECT salary INTO my_reward FROM employee WHERE employee_id = 150667; END; ...