EXEC_SQL.EXECUTE_AND_FETCH
built-in functionThis function calls EXEC_SQL.EXECUTE
and then EXEC_SQL.FETCH_ROWS
.
It executes a SQL statement at a specified cursor and retrieves the first row
that satisfies the query. Calling EXEC_SQL.EXECUTE_AND_FETCH
may
reduce the number of round-trips when used against a remote database.
FUNCTION EXEC_SQL.EXECUTE_AND_FETCH
([connid IN CONNTYPE],
curs_id IN CURSTYPE,
exact IN BOOLEAN DEFAULT FALSE)
RETURN PLS_INTEGER;
Parameter |
Description |
|
Is the handle to the connection you want to use. If you do not specify
a connection, |
|
Is the cursor handle to the SQL statement you want to execute. |
|
The default is |
The number of rows fetched (either 0 or 1).
PROCEDURE getData(input_empno NUMBER) IS
connection_id EXEC_SQL.CONNTYPE;
cursorID EXEC_SQL.CURSTYPE;
sqlstr VARCHAR2(1000);
loc_ename VARCHAR2(30);
loc_eno NUMBER;
loc_hiredate DATE;
nIgn PLS_INTEGER;
...
BEGIN
connection_id := EXEC_SQL.OPEN_CONNECTION(connect_str);
cursorID := EXEC_SQL.OPEN_CURSOR(connection_id);
--
-- assuming that empno is a primary key of the table emp, the where clause guarantees
-- that 0 or 1 row is returned
--
sqlstr := 'select ename, empno, hiredate from emp '
sqlstr := sqlstr || ' where empno = ' || input_empno;
EXEC_SQL.PARSE(connection_id, cursorID, sqlstr, exec_sql.V7);
EXEC_SQL.BIND_VARIABLE(connection_id, cursorID, ':bn', input_empno);
EXEC_SQL.DEFINE_COLUMN(connection_id, cursorID, 1, loc_ename, 30);
EXEC_SQL.DEFINE_COLUMN(connection_id, cursorID, 2, loc_eno);
EXEC_SQL.DEFINE_COLUMN(connection_id, cursorID, 3, loc_hiredate);
--
-- do execute_and_fetch after parsing the statement, and calling bind_variable and
-- define_column if necessary
--
nIgn := EXEC_SQL.EXECUTE_AND_FETCH (connection_id, cursorID);
IF (nIgn = 0 ) THEN
TEXT_IO.PUT_LINE (' No employee has empno = ' || input_empno);
ELSE IF (nIgn = 1) THEN
TEXT_IO.PUT_LINE (' Found one employee with empno ' || input_empno);
--
-- obtain the values in this row
--
EXEC_SQL.column_value(connection_id, cursorID, 1, loc_ename);
EXEC_SQL.column_value(connection_id, cursorID, 2, loc_eno);
EXEC_SQL.column_value(connection_id, cursorID, 3, loc_hiredate);
...
END IF;
...
END;
About the EXEC_SQL
built-in package
Copyright © 1984, 2005, Oracle. All rights reserved.