Oracle cursor and bound variable

Oracle executes SQL statements to open, parse, execute, and close a cursor. After learning about the several phases of the cursor, we can figure out the SQL Execution Process.

Oracle executes SQL statements to open, parse, execute, and close a cursor. After learning about the several phases of the cursor, we can figure out the SQL Execution Process.

Oracle executes SQL statements to open, parse, execute, and close a cursor. After learning about the several phases of the cursor, We have figured out the SQL Execution process. This is the first content to be introduced in this article. In java programming, we usually say that SQL statements should be written in the form of preprocessing (for example, select * from table where A = ?), That is, the form of variable binding. Because, this is highly efficient. So, why is it more efficient to bind A variable than to not bind A variable (for example, select * from table where A = '123? This is the second content to be introduced in this article.

I. lifecycle of a cursor

The execution process of an SQL statement is the lifecycle of a cursor. As shown in:

1. Open cursor: The system allocates a memory structure for the cursor.

2. Resolve the cursor: Associate an SQL statement with this cursor. Parse this SQL statement and load the parsed result to the shared pool.

3. Define output variables: If this SQL statement returns data, first define the variables for receiving data.

4. Define input variables: If the SQL statement uses bind variables, provide their values.

5. Execute the cursor: Execute the SQL statement.

6. Obtain the cursor: If the SQL statement returns data, it receives the returned data.

7. Close the cursor: Release the memory allocated in the first step for other cursors. However, the SQL results (that is, shared cursors) parsed in the second step will not be released to be reused.

We can use a piece of PL/SQL code to look at the cursor steps:

DECLARE
Rochelle ename emp. ename % TYPE: = 'Scott ';
Rochelle empno emp. empno % TYPE;
Rochelle cursor INTEGER;
Rochelle retval INTEGER;
BEGIN
Rochelle cursor: = dbms_ SQL .open_cursor;/* Open the cursor */
Dbms_ SQL .parse (l_cursor, 'select empno FROM emp WHERE ename =: ename', 1);/* resolve cursor */
Dbms_ SQL .define_column (l_cursor, 1, l_empno);/* define the output variable */
Dbms_ SQL .bind_variable (l_cursor, ': ename', l_ename);/* define the input variable */
Rochelle retval: = dbms_ SQL .execute (l_cursor);/* execution cursor */
IF dbms_ SQL .fetch_rows (l_cursor)> 0/* Get cursor */
THEN
Dbms_ SQL .column_value (l_cursor, 1, l_empno );
Dbms_output.put_line (l_empno );
End if;
Dbms_ SQL .close_cursor (l_cursor);/* close the cursor */
END;