EXECUTE_AND_FETCH v11
Function EXECUTE_AND_FETCH
executes a parsed SELECT
command and fetches one row.
<status> INTEGER EXECUTE_AND_FETCH(<c> INTEGER [, <exact> BOOLEAN ])
Parameters
c
Cursor id of the cursor for the SELECT
command to be executed.
exact
If set to TRUE
, an exception is thrown if the number of rows in the result set is not exactly equal to 1. If set to FALSE
, no exception is thrown. The default is FALSE
. A NO_DATA_FOUND
exception is thrown if exact
is TRUE
and there are no rows in the result set. A TOO_MANY_ROWS
exception is thrown if exact
is TRUE
and there is more than one row in the result set.
status
Returns 1 if a row was successfully fetched, 0 if no rows to fetch. If an exception is thrown, no value is returned.
Examples
The following stored procedure uses the EXECUTE_AND_FETCH
function to retrieve one employee using the employee’s name. An exception will be thrown if the employee is not found, or there is more than one employee with the same name.
CREATE OR REPLACE PROCEDURE select_by_name( p_ename emp.ename%TYPE ) IS curid INTEGER; v_empno emp.empno%TYPE; v_hiredate emp.hiredate%TYPE; v_sal emp.sal%TYPE; v_comm emp.comm%TYPE; v_dname dept.dname%TYPE; v_disp_date VARCHAR2(10); v_sql VARCHAR2(120) := 'SELECT empno, hiredate, sal, ' || 'NVL(comm, 0), dname ' || 'FROM emp e, dept d ' || 'WHERE ename = :p_ename ' || 'AND e.deptno = d.deptno'; v_status INTEGER; BEGIN curid := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native); DBMS_SQL.BIND_VARIABLE(curid,':p_ename',UPPER(p_ename)); DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno); DBMS_SQL.DEFINE_COLUMN(curid,2,v_hiredate); DBMS_SQL.DEFINE_COLUMN(curid,3,v_sal); DBMS_SQL.DEFINE_COLUMN(curid,4,v_comm); DBMS_SQL.DEFINE_COLUMN(curid,5,v_dname,14); v_status := DBMS_SQL.EXECUTE_AND_FETCH(curid,TRUE); DBMS_SQL.COLUMN_VALUE(curid,1,v_empno); DBMS_SQL.COLUMN_VALUE(curid,2,v_hiredate); DBMS_SQL.COLUMN_VALUE(curid,3,v_sal); DBMS_SQL.COLUMN_VALUE(curid,4,v_comm); DBMS_SQL.COLUMN_VALUE(curid,5,v_dname); v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY'); DBMS_OUTPUT.PUT_LINE('Number : ' || v_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || UPPER(p_ename)); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm); DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname); DBMS_SQL.CLOSE_CURSOR(curid); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_ename || ' not found'); DBMS_SQL.CLOSE_CURSOR(curid); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Too many employees named, ' || p_ename || ', found'); DBMS_SQL.CLOSE_CURSOR(curid); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); DBMS_SQL.CLOSE_CURSOR(curid); END; EXEC select_by_name('MARTIN') Number : 7654 Name : MARTIN Hire Date : 09/28/1981 Salary : 1250 Commission: 1400 Department: SALES