latihan 6 (Quiz6) ilearning oracle

1. What is one of the advantages of using parameters with a cursor? 

You do not need to DECLARE the cursor at all.
You can use a cursor FOR loop.
It will execute much faster than a cursor without parameters.
You can declare the cursor FOR UPDATE.
You can use a single cursor to fetch a different set of rows each time the cursor is opened. (*)

2. The following cursor has been declared:  

CURSOR emp_curs
  (p_dept_id employees.department_id%TYPE,
  p_job_id employees.job_id%TYPE) IS
  SELECT * FROM employees
  WHERE department_id = p_dept_id
  AND job_id = p_job_id; 

Which of the following will correctly open the cursor?

OPEN emp_curs(20);

OPEN emp_curs('IT_PROG', 20);

FOR emp_rec IN emp_curs(20,'IT_PROG') LOOP ... (*)

FOR emp_rec IN emp_curs(p_dept_id p_job_id) LOOP ...

FOR emp_rec IN emp_curs(20) LOOP ...

 

3. Place the following statements in the correct sequence: 

Place the following statements in the correct sequence: OPEN my_curs; CLOSE my_curs; CURSOR my_curs IS SELECT my_column FROM my_table; FETCH my_curs INTO my_variable;
C,A,B,D
C,D,A,B
C,A,D,B (*)
A,C,D,B

4. An implicit cursor can be used for a multiple-row SELECT statement. True or False? 

TRUE

FALSE (*)

5. An explicit cursor must always be declared, opened, and closed by the PL/SQL programmer. True or False?

	TRUE FALSE (*)

6. Which of these is NOT a valid cursor declaration

CURSOR emp_dept_curs IS     SELECT e.salary, d.department_name     FROM employees e, departments d     WHERE e.department_id = d.department_id;

CURSOR emp_curs IS     SELECT salary INTO v_salary     FROM employees; (*)

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%';

7. Look at the following code

DECLARE   CURSOR emp_cursor IS    SELECT employee_id, last_name, salary FROM employees;   v_empcurs emp_cursor%ROWTYPE; What is the data type of V_EMPCURS?
Cursor
Row
Record (*)
Scalar

8. Examine the following code: 

DECLARE
  CURSOR country_curs IS
   SELECT country_id, country_name
   FROM wf_countries
   ORDER BY country_name;
  v_country country_curs%ROWTYPE;
BEGIN
  OPEN country_curs;
  LOOP
   FETCH country_curs INTO v_country;
   EXIT WHEN country_curs%NOTFOUND;
   ------- Line A
  END LOOP;
  CLOSE country_curs;
END;

You want to display the id and name of each FETCHed country. What would you code at Line A?

DBMS_OUTPUT.PUT_LINE(country_curs.country_id || ' ' || country_curs.country_name);

DBMS_OUTPUT.PUT_LINE(v_country(country_id) || ' ' || v_country(country_name));

DBMS_OUTPUT.PUT_LINE(country_id || ' ' || country_name);

DBMS_OUTPUT.PUT_LINE(v_country.country_id || ' ' || v_country.country_name); (*)

9. User TOM has locked a row in the WORKERS table. Now, user DICK wants to open the following cursor:
CURSOR c IS 

SELECT * FROM workers FOR UPDATE NOWAIT;

What will happen when DICK opens the cursor and tries to fetch rows?

Both sessions wait for a few seconds; then the system breaks all locks and both sessions raise an exception.

The c%NOWAIT attribute is set to TRUE.

DICK's session waits indefinitely.

DICK's session immediately raises an exception. (*)

TOM's session is rolled back. DICK's session successfully fetches rows from the cursor.

10. You want to fetch rows from the EMPLOYEES table. You want to lock the fetched rows to prevent other users from updating them. You declare the following cursor: CURSOR emp_curs IS 

SELECT employee_id, last_name, salary
   FROM employees
   -- Line A -- ;  What should you code at Line A?

FOR UPDATE (employees)
FOR UPDATE OF employees
FOR UPDATE (*)
FOR LOCK

11. 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 MARY's session is rolled back.

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.

SAEED's session waits for 5 seconds, then SAEED is disconnected from the database.

SAEED's session successfully fetches the first 5 rows and then waits indefinitely to fetch the 6th row.

12. You want to display all locations, and the departments in each location. Examine the following code: 

DECLARE
  CURSOR loc_curs IS SELECT * FROM locations;
  CURSOR dept_curs(p_loc_id NUMBER) IS
    SELECT * FROM departments WHERE location_id = p_loc_id;
BEGIN
  FOR loc_rec IN loc_curs LOOP
    DBMS_OUTPUT.PUT_LINE(loc_rec.city);
    FOR dept_rec IN dept_curs(-- Point A --) LOOP
      DBMS_OUTPUT.PUT_LINE(dept_rec.department_name);
    END LOOP;
  END LOOP;
END; 
 What should you code at Point A?

loc_rec.location_id (*)

p_loc_id

null

location_id

LOOP ... END LOOP;

13. Examine the following code: 

DECLARE
  CURSOR region_cur IS
   SELECT * FROM wf_world_regions;
  v_region_rec region_cur%ROWTYPE;
  CURSOR country_cur (p_region_id NUMBER) IS
   SELECT * FROM wf_countries
   WHERE region_id = p_region_id;
   v_country_rec country_cur%ROWTYPE;
BEGIN
  OPEN region_cur;
  LOOP
   FETCH region_cur INTO v_region_rec;
   EXIT WHEN region_cur%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE
    (v_region_rec.region_name);
   -- Line A --
   LOOP
    FETCH country_cur INTO v_country_rec;
    EXIT WHEN country_cur%NOTFOUND;
     ......

What would you code at Line A? 
 

OPEN country_cur (p_region_id);

OPEN country_cur (region_cur.region_id);

OPEN country_cur (wf_world_regions.region_id);

OPEN country_cur (v_region_rec.region_id); (*)

OPEN country_cur;

 

14. When using a cursor FOR loop, OPEN, CLOSE, and FETCH statements should not be explicitly coded. True or False? 

True (*)

False

15. The following code fragment shows a cursor FOR loop:

FOR emp_record IN emp_cursor LOOP ......

Which of the following do NOT need to be coded explicitly? (Choose three.)

(Choose all correct answers)

emp_record emp_cursor%ROWTYPE; (*)

OPEN emp_cursor; (*)

DECLARE CURSOR emp_cursor IS ...

FETCH emp_cursor INTO emp_record; (*)

END LOOP;