Dmbs_ SQL package Introduction

From http://hqhe-nj.javaeye.com/blog/427891

In PL/SQLProgramDuring the design process, there will be many areas where dynamic SQL is required. The dmbs_ SQL package provided by the Oracle system can help you solve the problem.
(1) Introduction
The dbms_ SQL system package provides a lot of functions and processes. The following describes the frequently used functions:

Function open_cursor: open a dynamic cursor and return an integer;

Procedure close_cursor (C in out integer); closes a dynamic cursor. The parameter is the cursor opened by open_cursor;

Procedure parse (C in integer, statement in varchar2, language_flag in integer): parses the SQL statements provided by the dynamic cursor. Parameter C indicates the cursor, and statement indicates the SQL statement, language-flag is the Oracle version used to parse SQL statements. It generally includes V6, V7, and Native (native is used when you do not understand the connected database version );

Procedure define_column (C in integer, position in integer, column any datatype, [column_size in integer]): defines the corresponding values for dynamic cursors, where C is a dynamic cursors, positon is the position in the corresponding dynamic SQL statement (starting from 1), and column is the variable corresponding to this value. It can be of any type, column_size only applies to types with column defined length, such as varchar2 and Char. (in many cases, only the commonly used types are described here );

Function execute (C in integer): executes the cursor and returns an integer that indicates the processing result (it is meaningful for insert, delete, and update, but can be ignored for select statements );

Function fetch_rows (C in integer): cyclically retrieves data from the cursor and returns an integer. If the value is 0, the cursor is obtained;

Procedure column_value (C in integer, position in integer, value): Assign the obtained cursor data to the corresponding variable, C is the cursor, position is the position, value is the corresponding variable;

Procedure bind_variable (C in integer, name in varchar2, value): defines the value of the corresponding field in the dynamic SQL statement (DML), C is the cursor, name is the field name, value is the value of the field;

The above are several functions and processes that are frequently used in the program. For other functions and procedures, see the Definition Statement dbmssql. SQL provided by Oracle.

(2) general process
For general select operations, the following steps are required if dynamic SQL statements are used:
Open cursor ---> parse ---> define column ---> excute ---> fetch rows ---> close cursor;
For DML operations (insert, update), perform the following steps:
Open cursor ---> parse ---> Bind Variable ---> execute ---> close cursor;
Perform the following steps for the delete operation:
Open cursor ---> parse ---> execute ---> close cursor;