Using cursors to get data and dynamic SQL

1. Cursor Concept:

When DML (redaction) is performed in a PL/SQL block, Oracle assigns it a context area, which is a pointer to the context area

2. Cursor Classification:

A. An implicit cursor

A. Automatic creation of an implicit cursor when using DML statements in PL/SQL

B. An implicit cursor is automatically declared, opened, and closed with the name SQL

C. Obtaining information about the most recently executed DML statement by checking the properties of an implicit cursor

D. The properties of an implicit cursor are:

%found–sql statement affects one or more rows as TRUE

%notfound–sql statement does not affect any row is true

The number of rows affected by the%rowcount–sql statement

%isopen-whether the cursor is open, always false

E. Error handling for implicit cursors: no_data_found: No rows were queried. Too_many_rows: Returns multiple rows.

B. An explicit cursor

A. Steps to use an explicit cursor:

1) Sound obvious tour slogan method:

cursor cursor name [(cursor parameter list)] [return value specification] is SELECT statement [for UPDATE of [column Name list]];

2) Open the Explicit tour banner method:

OPEN explicit cursor name (parameter list);

3) Extract data syntax:

FETCH cursor name into record or variable list;

4) Close the Tour banner method:

CLOSE cursor name;

B. Explicit cursor properties

Explicit Cursor Name% property

%found–sql statement affects one or more rows as TRUE

%notfound–sql statement does not affect any row is true

The number of rows affected by the%rowcount–sql statement

%isopen-whether the cursor is open, always false

3. Circular cursors:

Used to simplify cursor handling code, when a user needs to extract all records from a cursor

The syntax for a loop cursor is as follows:

For <record_index> in <cursor_name>

LOOP

<executable statements>

END LOOP;

4. BULK COLLECT

Why use bulk COLLECT?

Submits query efficiency for the SELECT statement in PL/SQL.

Principle: Reduces the number of switches between PL/SQL and the database engine, thus accelerating data acquisition speed

Grammar:

... BULK COLLECT into Collection

5. REF cursors and cursor variables: SQL queries for handling run-time dynamic execution

To use a cursor variable:

A. Creating a cursor variable requires two steps:

A. Declaring a REF cursor type

B. Declaring a variable of a REF cursor type

The syntax for declaring a REF cursor type is:

TYPE <ref_cursor_name> is ref CURSOR [RETURN <return_type>];

Syntax for declaring a REF CURSOR type variable: variable name cursor type name;

B. The syntax for opening a cursor variable is as follows:

OPEN cursor_name for select_statement;

C. Extracting data syntax:

A. FETCH cursor variable name into the variable that receives cursor data ...;

B. FETCH cursor variable name into the collection variable that receives the cursor result .... [LIMIT rows];

D. Closing cursor variable syntax: close cursor variable name;

6. Advantages and limitations of cursor variables

A. The advantages of a cursor variable are:

A. You can extract a result set from a different SELECT statement

B. Can be passed as a parameter of a procedure

C. All properties of a cursor can be referenced

D. Assignment operations can be performed

B. Limitations of using cursor variables:

A. You cannot declare a cursor variable in a package

B. The FOR UPDATE clause cannot be used with a cursor variable

C. You cannot use comparison operators

7. Dynamic SQL Overview

A. Bindings

A. The process by which the compiler identifies where the identifiers in the program are stored.

B. Classification: Early binding and late binding

B. Dynamic SQL refers to SQL statements that are generated when the PL/SQL program executes

C. DDL statement Commands and session control statements cannot be used directly in PL/SQL, but can be performed by dynamic

D. Two dynamic SQL Technologies

A. Dbms_sql package: Oracle 8i used previously.

B. Local dynamic sql:oracle 9i is used later.

Contrast: Local dynamic SQL is simpler and faster to execute.

E. Executing dynamic SQL

A. Syntax 1:execute immediate ' SQL statement string '

B. Syntax 2:open x for ' SQL statement string '

8. Binding variables

A. Why bind variables are used: Reduce the number of compiled SQL and commit execution efficiency

B. Description of the binding variable

Execute immediate

' INSERT into T1 (F1,F2) VALUES (: a,:b) ' using X, y;

Note: The variable following the using is corresponding to the binding variable according to position one by one. X-->a, Y-->b

C. Use of bound variables

A. Using binding variables in Sql*plus (Command window)

Can only be used in sql*plus interactive environments.

B. Using binding variables in PL/SQL (stored procedures and functions)

The parameter reference is already a bound variable.

C. Using bound variables in dynamic SQL

can only bind literals (replace specific values)

Cannot replace database object name

D. Using binding variables in JDBC

PreparedStatement Object

9. Dynamic SQL syntax

Syntax 1:execute immediate ' SQL statement string '

Syntax 2:open for ' SQL statement string '

Using cursors to get data and dynamic SQL