EXECUTE_AND_FETCH procedure - Run a parsed SELECT command and fetch one row
The EXECUTE_AND_FETCH procedure executes a parsed SELECT command and fetches one row.
Syntax
Parameters
- c
- An input argument of type INTEGER that specifies the cursor id of the cursor for the SELECT command to be executed.
- exact
- An optional argument of type INTEGER. If set to 1, an exception is thrown if the number of rows in the result set is not exactly equal to 1. If set to 0, no exception is thrown. The default is 0. A NO_DATA_FOUND (SQL0100W) exception is thrown if exact is set to 1 and there are no rows in the result set. A TOO_MANY_ROWS (SQL0811N) exception is thrown if exact is set to 1 and there is more than one row in the result set.
- ret
- An output argument of type INTEGER that returns 1 if a row was fetched successfully, 0 if there are no rows to fetch.
Authorization
EXECUTE privilege on the DBMS_SQL module.
Usage notes
This procedure can be invoked using function invocation syntax in a PL/SQL assignment statement.
Examples
Example 1: 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.
SET SERVEROUTPUT ON@
CREATE TABLE emp (
empno DECIMAL(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR(10),
job VARCHAR(9),
mgr DECIMAL(4),
hiredate TIMESTAMP(0),
sal DECIMAL(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm DECIMAL(7,2) )@
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL)@
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300)@
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500)@
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL)@
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400)@
CREATE OR REPLACE PROCEDURE select_by_name(
IN p_ename ANCHOR TO emp.ename)
BEGIN
DECLARE curid INTEGER;
DECLARE v_empno ANCHOR TO emp.empno;
DECLARE v_hiredate ANCHOR TO emp.hiredate;
DECLARE v_sal ANCHOR TO emp.sal;
DECLARE v_comm ANCHOR TO emp.comm;
DECLARE v_disp_date VARCHAR(10);
DECLARE v_sql VARCHAR(120);
DECLARE v_status INTEGER;
SET v_sql = 'SELECT empno, hiredate, sal, NVL(comm, 0)
FROM emp e WHERE ename = :p_ename ';
CALL DBMS_SQL.OPEN_CURSOR(curid);
CALL DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
CALL DBMS_SQL.BIND_VARIABLE_VARCHAR(curid, ':p_ename', UPPER(p_ename));
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 1, v_empno);
CALL DBMS_SQL.DEFINE_COLUMN_DATE(curid, 2, v_hiredate);
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 3, v_sal);
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 4, v_comm);
CALL DBMS_SQL.EXECUTE_AND_FETCH(curid, 1 /*True*/, v_status);
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 1, v_empno);
CALL DBMS_SQL.COLUMN_VALUE_DATE(curid, 2, v_hiredate);
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 3, v_sal);
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 4, v_comm);
SET v_disp_date = TO_CHAR(v_hiredate, 'MM/DD/YYYY');
CALL DBMS_OUTPUT.PUT_LINE('Number : ' || v_empno);
CALL DBMS_OUTPUT.PUT_LINE('Name : ' || UPPER(p_ename));
CALL DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date);
CALL DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal);
CALL DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm);
CALL DBMS_SQL.CLOSE_CURSOR(curid);
END@
CALL select_by_name( 'MARTIN' )@This example results
in the following output:
SET SERVEROUTPUT ON
DB20000I The SET SERVEROUTPUT command completed successfully.
CREATE TABLE emp
( empno DECIMAL(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR(10),
job VARCHAR(9),
mgr DECIMAL(4),
hiredate TIMESTAMP(0),
sal DECIMAL(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm DECIMAL(7,2) )
DB20000I The SQL command completed successfully.
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL)
DB20000I The SQL command completed successfully.
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300)
DB20000I The SQL command completed successfully.
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500)
DB20000I The SQL command completed successfully.
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL)
DB20000I The SQL command completed successfully.
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400)
DB20000I The SQL command completed successfully.
CREATE OR REPLACE PROCEDURE select_by_name(
IN p_ename ANCHOR TO emp.ename)
BEGIN
DECLARE curid INTEGER;
DECLARE v_empno ANCHOR TO emp.empno;
DECLARE v_hiredate ANCHOR TO emp.hiredate;
DECLARE v_sal ANCHOR TO emp.sal;
DECLARE v_comm ANCHOR TO emp.comm;
DECLARE v_disp_date VARCHAR(10);
DECLARE v_sql VARCHAR(120);
DECLARE v_status INTEGER;
SET v_sql = 'SELECT empno, hiredate, sal, NVL(comm, 0)
FROM emp e WHERE ename = :p_ename ';
CALL DBMS_SQL.OPEN_CURSOR(curid);
CALL DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
CALL DBMS_SQL.BIND_VARIABLE_VARCHAR(curid, ':p_ename', UPPER(p_ename));
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 1, v_empno);
CALL DBMS_SQL.DEFINE_COLUMN_DATE(curid, 2, v_hiredate);
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 3, v_sal);
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 4, v_comm);
CALL DBMS_SQL.EXECUTE_AND_FETCH(curid, 1 /*True*/, v_status);
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 1, v_empno);
CALL DBMS_SQL.COLUMN_VALUE_DATE(curid, 2, v_hiredate);
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 3, v_sal);
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 4, v_comm);
SET v_disp_date = TO_CHAR(v_hiredate, 'MM/DD/YYYY');
CALL DBMS_OUTPUT.PUT_LINE('Number : ' || v_empno);
CALL DBMS_OUTPUT.PUT_LINE('Name : ' || UPPER(p_ename));
CALL DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date);
CALL DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal);
CALL DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm);
CALL DBMS_SQL.CLOSE_CURSOR(curid);
END
DB20000I The SQL command completed successfully.
CALL select_by_name( 'MARTIN' )
Return Status = 0
Number : 7654
Name : MARTIN
Hire Date : 09/28/1981
Salary : 1250.00
Commission: 1400.00