EXECUTE_AND_FETCH v17
Function EXECUTE_AND_FETCH
executes a parsed SELECT
command and fetches one row.
<status> INTEGER EXECUTE_AND_FETCH(<c> NUMBER [, <exact> BOOLEAN ])
Parameters
c
Cursor id of the cursor for the SELECT
command to execute.
exact
If set to TRUE
, an exception is thrown if the number of rows in the result set isn't 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 more than one row is 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
This stored procedure uses the EXECUTE_AND_FETCH
function to retrieve one employee using the employee’s name. An exception is thrown if the employee isn't found or more than one employee has the same name.
CREATE OR REPLACE PROCEDURE select_by_name( p_ename emp.ename%TYPE ) IS curid NUMBER; 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
- On this page
- Parameters
- Examples