prepared statements in PL/SQL

[Lubosh Bazant]

I have a statement in a PL/SQL that is executed repeatedly in a loop. It is as follows
LOOP
select last_name from employees where department_id = epartment_id
END LOOP

The only change in each loop is the bind variable �departement_id�. For each loop ORACLE has to perform the following steps for the execution of the statement:
1, Open Cursor
2, Parse
3, Define Column
4, Bind Variable
5, Execute
6, Fetch Rows
7, Close cursor
Now, there is no need to execute steps 1, 2,3 and 7 every time my program makes a loop thus they should be outside of the loop. The only steps inside of the loop should be 4,5,6. The idea is similar to prepared statements in JDBC, but I want to apply it to a PL/SQL function. Also would explicit cursors solve this problem? If not, is there a special build-in library that can help me? (I have seen some PL/SQL packages that addressed this issue but all of the solutions were too complex. I think that this is such a trivial and common problem that ORACLE should already develop some solution. I am just trying to avoid �reinventing the wheel�)

[Mark Spritzler]

Actually since you used bind variables, the statement will only be hard parsed once, each query after will use the query that is in the SGA area.
Open cursor is like a call to runt he query, so each time you will have to do this. No you can use the loop mechanism.

 DECLARE
   CURSOR dept_cur IS
   SELECT deptno
     FROM dept
    ORDER BY deptno;
   -- Employee cursor all employees for a dept number
   CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS
   SELECT ename
     FROM emp
    WHERE deptno = v_dept_no;
BEGIN
   FOR dept_rec IN dept_cur LOOP
      dbms_output.put_line('Employees in Department '||TO_CHAR(dept_rec.deptno));
      FOR emp_rec in emp_cur(dept_rec.deptno) LOOP
         dbms_output.put_line('...Employee is '||emp_rec.ename);
      END LOOP;
  END LOOP;
END;
 

This was taken from this site.

Mark