Native Dynamic SQL: OPEN FOR, FETCH and CLOSE |
Using dynamic SQL with OPEN FOR, FETCH and CLOSE:
set serveroutput on declare -- define a type cursor. -- declare a variable of type cursor. This variable will -- execute the dynamic SQL stmt later (OPEN, LOOP-FETCH, CLOSE). type empcurtype is ref cursor; v_emp_c empcurtype; emp_rec employees%rowtype; v_stmt_str varchar2(200); v_e_job employees.job_id%type; begin -- dynamic SQL stmt. Note the placeholder (:j) for -- a bind variable. v_stmt_str := 'SELECT * FROM employees '|| 'WHERE job_id = :j'; -- Open the cursor. List the bind variables -- with the USING clause. Placeholders, in this -- case (single SQL stmt), are associated -- BY POSITION, and NOT by name. OPEN v_emp_c FOR v_stmt_str USING 'IT_PROG'; LOOP FETCH v_emp_c INTO emp_rec; exit when v_emp_c%notfound; dbms_output.put_line('Empid: '|| emp_rec.employee_id || '--'||emp_rec.first_name || ' ' || emp_rec.last_name ); END LOOP; CLOSE v_emp_c; end; nonymous block completed Empid: 103--Alexander Hunold Empid: 104--Bruce Ernst Empid: 105--David Austin Empid: 106--Valli Pataballa Empid: 107--Diana Lorentz
(cont..)
No comments:
Post a Comment