Oracle dynamic SQL learning notes

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 is inProgramSeveral functions and processes are frequently used. 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;

Instance analysis
Create Or Replace Procedure P_oneproducIs
-- Variable definition
IIDInt;
IcountInt;
StrtblnameVarchar2( 100 );
SsqlVarchar2( 1024 );
SerrmsgVarchar2( 1024 );
Cid1Number; -- Dynamic Cursor number
Cid2 number; -- Dynamic Cursor number
IipnoNumber; -- Retrieves the variable for storing the value from the Dynamic Cursor
IdatenoNumber; -- Retrieves the variable for storing the value from the Dynamic Cursor
INumber;
CursorCur1Is SelectID, table_nameFromUserisitWhereStatus = 2 ; -- Define a cursor
-- Definition ends
Begin
 Select Count(*)IntoIcountFromUservisitWhereStatus = 2 ; // Query the User Access Table and save the summary statistics to icount
 IfIcount> 0 Then
OpenCur1; // open cur1 cursor
FetchCur1IntoIID, strtblname; // retrieve the cursor value and assign it to IID and strtblname. The corresponding variables and select variables are in the same order.
Exit WhenCur1 %Notfound; // Exit cur % notfound if the record is empty is a variable of cur, and true if the record is empty
-- Starting with dynamic SQL (For details, refer to the previous method description)
Ssql: = 'Select distinct ipno, dateno from' | Strtblname; -- defines an SQL statement, which is then executed using dynamic SQL
Cid1: = dbms_ SQL .open_cursor; -- defines a dynamic cursor to execute the previously defined SQL statement.
Dbms_ SQL .parse (cid1, ssql, dbms_ SQL .v7); -- analyze SQL statements
Dbms_ SQL .define_column (cid1, 1 , Iipno); -- defines the value of the field to be retrieved. 1 indicates that the first field is to be retrieved, that is, ipno of the SELECT statement.
Dbms_ SQL .define_column (cid1, 2 , Idateno); -- same as above
Icount: = dbms_ SQL. Execute (Cid1); -- execute the SQL statement. Here, icount gets an execution result.
-- The execution of SQL statements can be the same as that of a normal cursor.
I: = 0 ;
Loop
IfDbms_ SQL .fetch_rows (cid1)> 0 Then -- if the number of records of the dynamic cursor is greater than 0, the value operation is performed.
Begin
Dbms_ SQL .column_value (cid1, 1 , Iipno); -- get the value
Dbms_ SQL .column_value (cid1, 2 , Idateno); -- same as above

Select Count(*)IntoIcountFromPmhtmpunchkcpc @ unionbillWhereIpno = iipnoAndDateno = idateno;
IfIcount> 0 Then
Ssql: = 'Update firstcday _' | Idateno | '@ Unionbill set isreach = 1 where ipno =' | Iipno;
Execute ImmediateSsql; // execute an SQL statement dynamically and immediately
End if
End
End if
End Loop
Exception When Others Then
Null ;
End ;
-- Use the dynamic cursor to execute a set of insert operations (the pure break is to record the assignment usage in the dynamic insert, no logic at all)
Ssql: = 'insert uservisit ( Ipno, dateno) values (: iipno,: idateno )' ; -- Define an SQL statement, run dynamic SQL later
cid2: = dbms_ SQL .open_cursor; -- Define a dynamic cursor to execute the previously defined SQL statement
dbms_ SQL .parse (cid2, ssql, V7 );
for J in 1 .. 999 loop
dbms_ SQL .bind_variable (cid2, 'ipno', J);
dbms_ SQL .bind_variable (cid2, 'dateno', 2);
icount: = dbms_ SQL .execute (cursor2); -- insert data
end loop;

-- Don't forget to close the cursor.
 If(Dbms_ SQL .is_open (cid1 ))Then -- if the dynamic cursor is still on
Dbms_ SQL .close_cursor (cid1); -- disable
End If;
--- Remember to turn off the Second Dynamic Cursor
IfCur1 %Isopen Then
CloseCur1;
End If;