Section 5
Which of the following cursor attributes is set to the total
number of rows returned so far?
%NOTFOUND
%FOUND
%ISOPEN
%ROWCOUNT
(*)
Which of the following
statements about the %ISOPEN cursor attribute is true?
You can
issue the %ISOPEN cursor attribute only when a cursor is open.
You can
issue the %ISOPEN cursor attribute when a cursor is open or closed. (*)
If a
cursor is open, then the value of %ISOPEN is false.
You can
issue the %ISOPEN cursor attribute only when more than one record is returned.
You want to use explicit
cursors to fetch and display all the countries in a specific region. There are
19 rows in the WF_WORLD_REGIONS table. You want to use a different region each
time the cursor is opened. How many cursors should you declare?
20
cursors, in case an extra row is inserted into WF_WORLD_REGIONS later.
None of
the these.
19
cursors, all in the same PL/SQL block.
One
cursor with a parameter in the WHERE clause. (*)
19
cursors in 19 PL/SQL blocks (one in each block).
What is missing from the
following cursor declaration?
CURSOR emp_curs
IS
SELECT * FROM departments
WHERE location_id =
p_loc_id;
Nothing
is wrong; the cursor declaration is correct.
The
declaration is invalid. You cannot reference a cursor parameter in a WHERE
clause.
A
parameter is missing. The parameter should be coded as: (p_loc_id NUMBER) (*)
A
parameter is missing. The parameter should be coded as: (p_loc_id IN NUMBER)
You want to display each row from the DEPARTMENTS table, and immediately underneath it, a list of all EMPLOYEES in that department. Which of the following is a good way to do this?
Change
the physical model so that all employee and department data is in a single
table.
Use two
cursors, one for each of the two tables. Declare the EMPLOYEES cursor with a
parameter for the DEPARTMENT_ID. (*)
Use a
single cursor, declared as SELECT * FROM employees GROUP BY department_id;
Use a
single cursor with a cursor FOR loop.
Write a
SELECT statement which JOINs the two tables, and use CONNECT BY PRIOR and LEVEL
to display the rows in the correct order.
Which of the following is a good reason to use two cursors
in a single PL/SQL block?
To
speed up the execution of the PL/SQL block.
It is
the only way to declare a cursor with a parameter.
To
allow rows to be locked as they are FETCHed.
To
allow one cursor to be opened twice at the same time.
When
two tables are related to each other (often by a foreign key) and we want to
produce a multilevel report using data from both tables. (*)
Which one of the following
statements is NOT true?
An
explicit cursor can select from only one table. No joins are allowed. (*)
You can
use ORDER BY when declaring an explicit cursor.
An
explicit cursor must be DECLAREd before it can be OPENed.
You can
not use an INTO clause when declaring an explicit cursor.
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
loop should be a WHILE loop, not a basic loop.
The
OPEN statement should be inside the loop.
The
CLOSE statement should be coded after END LOOP; (*)
The
EXIT WHEN ... statement should be coded outside the loop.
Which of these statements about
implicit cursors is NOT true?
They
are declared automatically by Oracle for all DML statements.
They
are declared by the PL/SQL programmer. (*)
They
are opened and closed automatically by Oracle.
They
are declared automatically by Oracle for single-row SELECT statements.
Which of these is NOT a valid
cursor declaration?
CURSOR
emp_curs IS
SELECT salary
FROM employees
ORDER BY salary
DESC;
CURSOR
emp_curs IS
SELECT salary
FROM employees
WHERE last_name
LIKE 'S%';
CURSOR
emp_curs IS
SELECT salary INTO
v_salary
FROM employees;
(*)
CURSOR
emp_dept_curs IS
SELECT e.salary,
d.department_name
FROM employees e,
departments d
WHERE
e.department_id = d.department_id;
Which
of the following is a benefit of using a cursor FOR loop?
Because
there is less code, the loop executes faster.
You can
OPEN the same cursor twice at the same time.
The
exception handling is done automatically. .
The
OPEN, CLOSE, FETCH and EXIT from the loop are done automatically. (*)
%ROWCOUNT
increments automatically each time a row is FETCHed.
When using a cursor FOR loop,
OPEN, CLOSE, and FETCH statements should not be explicitly coded. True or
False?
True
(*)
False
What is the difference between
the following two blocks of code?
--Block A
DECLARE
CURSOR emp_cursor
IS
SELECT
employee_id, last_name
FROM employees
WHERE
department_id = 80
FOR UPDATE OF
salary;
--Block B
DECLARE
CURSOR emp_cursor
IS
SELECT
employee_id, last_name
FROM employees
WHERE
department_id = 80
FOR UPDATE OF
salary
NOWAIT;
There
is no difference; the programs behave exactly the same way.
In
Block A, the program waits indefinitely until locked rows are available. In
Block B, control is returned to your program after 5 seconds so that it can do
other work.
In
Block A, the program waits indefinitely until locked rows are available. In
Block B, the program returns control immediately so that it can do other work.
(*)
Examine the following code:
DECLARE
CURSOR c IS SELECT *
FROM employees FOR UPDATE;
c_rec c%ROWTYPE;
BEGIN
OPEN c;
FOR i IN 1..20 LOOP
FETCH c INTO
c_rec;
IF i = 6 THEN
UPDATE employees
SET first_name = 'Joe'
WHERE CURRENT OF
c;
END IF;
END LOOP;
CLOSE c;
END;
Which employee row or rows will be updated when this block
is executed?
The
block will not compile because the cursor should have been declared .... FOR
UPDATE WAIT 5;
The 6th
fetched row will be updated. (*)
None of
these.
No rows
will be updated because you locked the rows when the cursor was opened.
The
first 6 fetched rows will be updated.
User MARY has locked a row of the
EMPLOYEES table. Now, user SAEED tries to open the following cursor:
CURSOR c IS
SELECT * FROM
employees
FOR UPDATE WAIT 5;
What will happen when SAEED's session tries to fetch the row
that MARY has locked?
SAEED's
session waits for 5 seconds, then SAEED is disconnected from the database.
SAEED's
session waits for 5 seconds, then MARY's session is rolled back.
SAEED's
session successfully fetches the first 5 rows and then waits indefinitely to
fetch the 6th row.
SAEED's
session waits for 5 seconds, and then raises an exception if MARY has not
unlocked the row. (*)
SAEED's
session waits for 5 minutes, and then raises an exception if MARY has not
unlocked the row.
No comments:
Post a Comment