PL/SQL Quizz Section#5

Section 5: Using Cursors and Parameters

---

5.01. Introduction to Explicit Cursors

1.  Which of the following best describes the difference between implicit and explicit cursors?

♦Implicit cursors are used for SELECT statements, while explicit cursors are used for DML statements.
♦Implicit cursor are named by the PL/SQL programmer, while explicit cursors are always named SQL.
♦Implicit cursors are defined automatically by Oracle, while explicit cursors must be declared by the PL/SQL programmer. (*)
♦Implicit cursors store rows on disk, while explicit cursors store rows in memory.

---

2.  You execute the following code:

DECLARE
CURSOR emp_curs IS SELECT last_name FROM employees;
v_last_name employees.last_name%TYPE;
BEGIN
OPEN emp_curs;
LOOP — Point A
FETCH emp_curs INTO v_last_name;
EXIT WHEN emp_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_last_name);
END LOOP;
CLOSE emp_curs;
END;

At Point A (after you have OPENed the cursor) another user updates an employee’s last_name from ‘Smith’ to ‘Jones’ and immediately COMMITs.

When your block FETCHes this row, which value will be fetched and displayed?

♦1
♦Smith (*)
♦Jones
♦Smith and Jones (the row will be fetched twice)
♦An INVALID_CURSOR exception will be raised when you try to FETCH the row.

---

3.   Examine the following code:

DECLARE
CURSOR dept_curs IS SELECT department_name FROM departments;
v_dept_name departments.department_name%TYPE;
BEGIN
OPEN dept_curs;
LOOP
FETCH dept_curs INTO v_dept_name;
DBMS_OUTPUT.PUT_LINE(v_dept_name);
EXIT WHEN dept_curs%NOTFOUND;
END LOOP;
CLOSE dept_curs;
END;

There are 10 rows in the DEPARTMENTS table. What will happen when this code is executed?

♦10 rows will be displayed.
♦10 rows will be displayed, followed by a row of NULL values.
♦The last row will be displayed twice. (*)
♦A NO_DATA_FOUND exception will be raised.
♦The loop will execute for ever; the same 10 rows will be displayed over and over again.

---

 

4.  Which one of the following statements is NOT true?

♦You can use ORDER BY when declaring an explicit cursor.
♦You can not use an INTO clause when declaring an explicit cursor.
♦An explicit cursor can select from only one table. No joins are allowed. (*)
♦An explicit cursor must be DECLAREd before it can be OPENed.

---

5.  What is wrong with the following code?

DECLARE
CURSOR dept_curs IS SELECT department_name FROM departments;
v_dept_name departments.department_name%TYPE;
BEGIN
OPEN dept_curs;
LOOP
FETCH dept_curs INTO v_dept_name;
EXIT WHEN dept_curs%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_dept_name);
CLOSE dept_curs;
END LOOP;
END;

♦Nothing is wrong, all the rows will be FETCHed and displayed.
♦The OPEN statement should be inside the loop.
♦The EXIT WHEN … statement should be coded outside the loop.
♦The CLOSE statement should be coded after END LOOP; (*)
♦The loop should be a WHILE loop, not a basic loop.

---

6.  Which one of the following explicit cursor declarations is NOT valid?

♦CURSOR country_curs IS
SELECT country_name, region_name
FROM wf_countries c, wf_world_regions r
WHERE c.region_id = r.region_id;

♦CURSOR country_curs IS
SELECT country_name INTO v_country_name
FROM wf_countries; (*)

♦CURSOR country_curs IS
SELECT country_name
FROM wf_countries
ORDER BY population DESC;

♦CURSOR country_curs IS
SELECT country_name
FROM wf_countries
WHERE region_id IN
(SELECT region_id FROM wf_world_regions
WHERE LOWER(region_name) LIKE ‘%asia%’);

---

7.  There are 8 countries in REGION_ID 13 (Central America). What will happen when the following code is executed?

DECLARE
CURSOR country_curs IS SELECT country_name FROM wf_countries
WHERE region_id = 13;
v_country_name wf_countries.country_name%TYPE;
BEGIN
OPEN country_curs;
WHILE country_curs%FOUND
LOOP
FETCH country_curs INTO v_country_name;
DBMS_OUTPUT.PUT_LINE(v_country_name);
END LOOP;
CLOSE country_curs;
END;

♦Eight rows will be fetched and displayed successfully.
♦The last seven rows will be fetched and displayed.
♦The block will execute, but no rows will be displayed. (*)
♦The block will fail because you can not use a WHILE loop with an explicit cursor.
♦None of the above.

---

8.  You cannot OPEN or CLOSE an implicit cursor. Why not?
♦Because an implicit cursor is always called SQL.
♦Because an implicit cursor is OPENed and CLOSEd automatically by Oracle. (*)

---

9.  One (and only one) employee has LAST_NAME = ‘Grant’. You need to code:

SELECT … FROM employees WHERE last_name = ‘Grant’;

Which type of cursor should you use, and why?

♦An implicit cursor, because there is only one ‘Grant’.
♦An implicit cursor, because SELECT is a SQL statement and implicit cursors are always called “SQL”.
♦An explicit cursor, because there could be more than one ‘Grant’ in the future. (*)
♦An explicit cursor, because you can use an implicit cursor only for DML statements.

---

10.   When must you declare and use an explicit cursor?

♦You need to UPDATE more than one row in a table.
♦You want to use a MERGE statement.
♦You need to SELECT more than one row from a table. (*)
♦You want to be able to ROLLBACK a transaction if needed.

---

11.  What is wrong with the following code?

DECLARE
CURSOR emp_curs IS SELECT last_name, salary FROM employees;
v_last_name employees.last_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
FETCH emp_curs INTO v_last_name, v_salary;
OPEN emp_curs;
FETCH emp_curs INTO v_last_name, v_salary;
CLOSE emp_curs;
END;

♦When FETCHing more than one row, you MUST use a loop.
♦The cursor declaration does not include a WHERE condition.
♦The cursor declaration does not include an INTO clause.
♦The first row is FETCHed before the cursor is OPENed. (*)

---

12.  You have declared a cursor EMP_CURSOR to select many rows from the EMPLOYEES table. The following five statements will be in the executable section:

A. FETCH emp_cursor INTO v_empno,v_last_name;
B. OPEN emp_cursor;
C. END LOOP;
D. CLOSE emp_cursor;
E. LOOP

In which order should you code these statements?
♦B, E, A, C, D (*)
♦E, B, A, C, D
♦B, E, A, D, C
♦B, A, E, D, C

---

13.  You must make sure you have the same number of variables in your INTO statement as you have in your SELECT list. True or False?

♦True (*)
♦False

---

14.  Which statement correctly places the employee id and last name into the stated variables?
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id = 30;
v_empno employees.employee_id%TYPE;
v_lname employees.last_name%TYPE;
BEGIN
OPEN emp_cursor;
— Point A
…

♦GET emp_cursor INTO v_empno, v_lname;
♦FETCH emp_cursor INTO v_empno, v_lname; (*)
♦GET emp_cursor.employee_id, emp_cursor.last_name INTO v_empno, v_lname;
♦FETCH emp_cursor.employee_id, emp_cursor.last_name INTO v_empno, v_lname;