10-6. | Which of the following strings are valid cursor attributes (which means they are “attached” as a suffix to the name of the cursor)? %ROWNUM
%FOUND
%TOO_MANY_ROWS
%NO_DATA_FOUND
%NOTFOUND
%ROWCOUNT
%ISCLOSED
%ISOPENED
%ISOPEN
|
10-7. | Which of the following uses of cursor attributes are valid? If they are not valid, what is the problem? Assume that the following cursor is declared in the same block when referenced in the problems: CURSOR possibly_in_danger_cur IS
SELECT status
FROM genetically_modified_foods
WHERE product = 'CORN'
AND animal = 'MONARCH BUTTERFLY';BEGIN
IF possibly_in_danger_cur%FOUND
THEN
OPEN possibly_in_danger_cur;BEGIN
UPDATE jobs_to_mexico
SET total_count = 10000000
WHERE nafta_status = 'IN FORCE';
DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT);BEGIN
FOR big_loss IN possibly_in_danger_cur
LOOP
IF possibly_in_danger_cur%ROWCOUNT
THEN
...BEGIN
OPEN possibly_in_danger_cur;
FETCH possibly_in_danger_cur INTO I_am_sad;
IF I_am_sad%FOUND
THEN FOR indx IN 1 .. 12
LOOP
DELETE FROM genetically_modified_foods
WHERE product = 'CORN';
DBMS_OUTPUT.PUT_LINE (
'All gone: ' || genetically_modified_foods%ROWCOUNT);
END LOOP;
|
10-8. | What is wrong with the following code? Will you eventually run out of open cursors in the session if you execute this program, say, 100,000 times? CREATE OR REPLACE FUNCTION totalsales (year_in IN INTEGER)
RETURN NUMBER
IS
CURSOR sales_cur IS SELECT SUM (amt) FROM ...;
total NUMBER;
BEGIN
OPEN sales_cur;
FETCH sales_cur INTO total;
RETURN total;
CLOSE sales_cur;
END; |
10-9. | Which employee will become more and more loyal to the company when this function is run for everyone at year’s end? CREATE OR REPLACE FUNCTION increment_salary (
employee_id employee.employee_id%TYPE)
RETURN BOOLEAN
/*
|| Give the applicable employee a 10% Raise
*/
IS
-- cursor to get the employees current salary
CURSOR curs_get_curr_sal (
employee_id employee.employee_id%TYPE)
IS
SELECT salary FROM employee
WHERE employee_id = employee_id
FOR UPDATE OF salary;
v_curr_sal employee.salary%TYPE;
v_ret_val BOOLEAN := FALSE; -- return value
BEGIN
/*
|| Query the employee's current salary and increment it by 10%
*/
OPEN curs_get_curr_sal (employee_id);
FETCH curs_get_curr_sal INTO v_curr_sal;
IF curs_get_curr_sal%found
THEN
UPDATE employee SET salary = salary + (salary / 10)
WHERE CURRENT OF curs_get_curr_sal;
v_ret_val := TRUE;
END IF;
CLOSE curs_get_curr_sal;
-- Return TRUE if update was successful, FALSE if not
RETURN (v_ret_val);
END; |
10-10. | What is the output of the following code snippet, assuming there is an employee whose ID is 7566? DECLARE
v_name employee.last_name%TYPE;
PROCEDURE bpl (str IN VARCHAR2, bool IN BOOLEAN) IS
BEGIN
IF bool THEN DBMS_OUTPUT.PUT_LINE (str || '-TRUE'),
ELSIF NOT bool THEN DBMS_OUTPUT.PUT_LINE (str || '-FALSE'),
ELSE DBMS_OUTPUT.PUT_LINE (str || '-NULL'),
END IF;
END;
BEGIN
SELECT last_name INTO v_name FROM employee
WHERE employee_id = 7566;
bpl ('%FOUND', SQL%FOUND);
bpl ('%NOTFOUND', SQL%NOTFOUND);
bpl ('%ISOPEN', SQL%ISOPEN);
DBMS_OUTPUT.PUT_LINE ('%ROWCOUNT-' || SQL%ROWCOUNT);
END;
/ |
10-11. | What is required in this code snippet to validate that indeed no rows were returned? DECLARE
CURSOR curs_failure IS
SELECT null
FROM employee
WHERE 1 = 2;
v_dummy VARCHAR2(1);
BEGIN
OPEN curs_failure;
FETCH curs_failure INTO v_dummy;
CLOSE curs_failure;
END; |
10-12. | Write a function that uses a cursor to find the total of the three highest salaries in the employee table using the following cursor: CURSOR curs_get_salary IS
SELECT salary
FROM employee
ORDER BY salary desc; |
10-13. | Given the following cursor declaration: CURSOR curs_get_emps IS
SELECT employee_id, last_name, salary
FROM employee;Which of the following approaches is preferable from the standpoint of maintainability and robustness? v_employee_id employee.employee_id%TYPE;
v_name employee.last_name%TYPE;
v_salary employee.salary%TYPE;
BEGIN
OPEN curs_get_emps;
FETCH curs_get_emps INTO v_employee_id, v_last_name, v_salary; v_emprec curs_get_emps%ROWTYPE;
BEGIN
OPEN curs_get_emps;
FETCH curs_get_emps INTO v_emprec;
|
10-14. | What do you see in your session when the following procedure is executed (the employee table has 32 rows; the person with the lowest salary is named MURRAY)? CREATE OR REPLACE PROCEDURE show_lots
IS
CURSOR by_sal_cur IS
SELECT last_name
FROM employee
ORDER BY salary DESC;
rec by_sal_cur%ROWTYPE;
BEGIN
OPEN by_sal_cur;
FOR rows IN 1 .. 10000
LOOP
FETCH by_sal_cur INTO rec;
END LOOP;
DBMS_OUTPUT.PUT_LINE (by_sal_cur%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE (rec.last_name);
CLOSE by_sal_cur;
END;
/ |
10-15. | Which statement can be removed from the following block of code without changing the behavior or effect of the code? DECLARE
CURSOR brought_to_you_by_unions
IS
SELECT weekends, forty_hour_weeks, overtime_pay
FROM business_practices;
social_benefits brought_to_you_by_unions%ROWTYPE;
BEGIN
OPEN brought_to_you_by_unions;
FETCH brought_to_you_by_unions INTO social_benefits;
CLOSE brought_to_you_by_unions;
END;
/ |
10-16. | Rewrite the following program to reduce code volume to an absolute minimum (without changing the names of the elements): 
/* Filename on web page: unions.sql */
DECLARE
CURSOR brought_to_you_by_unions
IS
SELECT weekends, forty_hour_weeks, overtime_pay
FROM business_practices;
social_benefits brought_to_you_by_unions%ROWTYPE;
BEGIN
OPEN brought_to_you_by_unions;
LOOP
FETCH brought_to_you_by_unions INTO social_benefits;
EXIT WHEN brought_to_you_by_unions%NOTFOUND;
calculate_impact (social_benefits);
END LOOP;
CLOSE brought_to_you_by_unions;
END;
|
10-17. | Modify the script in the unions.sql file (see 10-16) so that (1) it uses a cursor FOR loop, and (2) after all rows have been processed, the block displays how many rows were fetched. Does the following implementation do the trick?
/* Filename on web page: unions.sql */
DECLARE
CURSOR brought_to_you_by_unions
IS
SELECT weekends, forty_hour_weeks, overtime_pay
FROM business_practices;
BEGIN
FOR social_benefits IN brought_to_you_by_unions
LOOP
calculate_impact (social_benefits);
END LOOP;
DBMS_OUTPUT.PUT_LINE (
'Calculated impact of ' || brought_to_you_by_unions%ROWCOUNT
|| ' benefits brought about by unions.'),
END; |
