Three methods of writing oracle dynamic statements

There are two ways to write SQL statements:

 

Oracle compiler PL/SQL program blocks are divided into two types: one is the early compilation (early binding), that is, the SQL statement has been determined during program compilation, and most of the compilation situations belong to this type; the other is late binding)

4) Because dynamic SQL statements are determined at runtime, compared with static SQL statements, it will lose some system resources in exchange for its flexibility.

 

Static and dynamic operations are only different in terms of pre-and post-joint editing.

Such as object information, syntax check, object check

 

Three writing methods

Fixed SQL, variable SQL binding, and hard-coded SQL

Dynamic
1 SQL = 'select * from table where name = 'Lee 3''
2 SQL = 'select * from table where name =: p_name'
3 SQL = 'select * from table where name = '| p_name;

 

Declare
Vc_ SQL varchar2 (1000 );
I number;
Begin

I: = 10;
Vc_ SQL: = 'select/* + demo 1 */* from t where object_id = 10 ';
Execute immediate vc_ SQL;

Vc_ SQL: = 'select/* + DEMO 2 */* from t where object_id =: 1 ';
Execute immediate vc_ SQL using I;

Vc_ SQL: = 'select/* + demo 3 */* from t where object_id = '| I;
Execute immediate vc_ SQL;

I: = 20;
Vc_ SQL: = 'select/* + demo 1 */* from t where object_id = 10 ';
Execute immediate vc_ SQL;

Vc_ SQL: = 'select/* + demo 2 */* from t where object_id =: 1 ';
Execute immediate vc_ SQL using I;

Vc_ SQL: = 'select/* + demo 3 */* from t where object_id = '| I;
Execute immediate vc_ SQL;

End;
/

 

SQL> select SQL _text from v $ sqlarea where SQL _text like 'select/* + demo % 'order by SQL _text;

SQL _TEXT
------------------------------
Select/* + demo 1 */* from t where object_id = 10

Select/* + demo 1 */* from t where object_id = 20

Select/* + demo 2 */* from t where object_id =: 1

Select/* + demo 3 */* from t where object_id = 10

Select/* + demo 3 */* from t where object_id = 20

If the repeated execution conditions remain unchanged, the three methods can be reused,

If conditions change,

The first type of literal writing can be reused,

The second type of variable binding can basically reuse ORACLE 10 Gb later, it will peat the value of the variable. If the data distribution information is stored, the execution plan may be regenerated.

The third parameterization will not be reused because the condition value changes,

The static SQL statement only removes the quotation marks, and the execute immediate statement is basically the same as the preceding statement.