Databases Reference
In-Depth Information
Another common problem related to cursor management, is the use of non-set operations.
While for the human mind it is simpler to think of an algorithm as an iterative sequence of
steps, relational databases are optimized for set operations. Many a times developers code
something like the following example code:
CREATE OR REPLACE PROCEDURE example1 (
JOBID IN hr.jobs.job_id%TYPE) IS
BEGIN
DECLARE
l_empid hr.employees.employee_id%TYPE;
l_sal hr.employees.salary%TYPE;
CURSOR jc IS SELECT e.employee_id, e.salary
FROM hr.employees e
INNER JOIN hr.jobs j ON j.job_id = e.job_id
WHERE e.job_id = JOBID
AND e.salary > (j.max_salary - j.min_salary) / 2;
BEGIN
OPEN jc;
LOOP
FETCH jc INTO l_empid, l_sal;
EXIT WHEN jc%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_empid) || ' ' ||
TO_CHAR(l_sal));
UPDATE hr.employees SET salary = l_sal * 0.9
WHERE employee_id = l_empid;
END LOOP;
CLOSE jc;
END;
END;
This example is trivial, but it's good enough to explain the concept. In the procedure, there is a
loop on the employees of a certain job, which decreases the salaries that are higher than the
average for a particular job. The stored procedure compiles and executes well, but there is a
better way to code this example, shown as follows:
CREATE OR REPLACE PROCEDURE example2 (
JOBID IN hr.jobs.job_id%TYPE) IS
BEGIN
UPDATE hr.employees e SET
e.salary = e.salary * 0.9
WHERE e.job_id = JOBID
AND e.salary > (SELECT (j.max_salary - j.min_salary) / 2 FROM
hr.jobs j
WHERE j.job_id = e.job_id);
END;
