Bind the SQL statement and pl/SQL statement to the variable in volume El.

Usage of variable binding:

Previous articles introduced the importance of binding variables to the system. Here we will further analyze and describe the use of binding variables.

1) how to bring bind value into SQL statements

SQL> variable x number;
SQL> exec: x := 100;

PL/SQL procedure successfully completed.

SQL> select * from t where id =: x;

No rows selected

SQL> exec: x := 101;

SQL> select * from t where id =: x;

No rows selected

SQL> select SQL _id, SQL _text, EXECUTIONS, parse_cils from v $ SQL where SQL _text like 'select * from t where id = % ';

SQL _ID SQL _TEXT EXECUTIONS parse_cils
--------------------------------------------------------------------------
3 yxwagyspybax select * from t where id =: x 1 1

SQL> select SQL _id, SQL _text, EXECUTIONS, parse_cils from v $ SQL where SQL _text like 'select * from t where id = % ';

SQL _ID SQL _TEXT EXECUTIONS parse_cils
--------------------------------------------------------------------------
3 yxwagyspybax select * from t where id =: x 2 2
For SQL statements, they are directly defined in a way similar to variable, and then exec value assignment can be used to bind variables in SQL statements.

Note that the & x method is not the correct method to bind variables. See the following example:

SQL> select * from t where id = & op;
Enter value for op: 10
Old 1: select * from t where id = & op
New 1: select * from t where id = 10

No rows selected

SQL> select SQL _id, SQL _text, EXECUTIONS, parse_cils from v $ SQL where SQL _text like 'select * from t where id = % ';

SQL _ID SQL _TEXT EXECUTIONS parse_cils
--------------------------------------------------------------------------
6wxj4tkdajgbx select * from t where id = 10 1 1
3 yxwagyspybax select * from t where id =: x 2 2
The method of bind value in pl/SQL:

Pl/SQL has two types of variables: one is the variable defined by pl/SQL, and the other is the variable bound, both of these variables can reduce the number of SQL statements parsed by the optimizer. Let's take a look at the following:

1. Variables defined in pl/SQL:

SQL> declare
2 a number;
3 v_name varchar2 (128 );
4 begin
5 for I in 1 .. 100 loop
6 delete from t where object_id = I + 1 returning object_name into v_name;
7 dbms_output.put_line (v_name );
8 commit;
9 end loop;
End;
11/

SQL> select SQL _id, SQL _text from v $ SQL where lower (SQL _text) like '% delete from t % ';

SQL _ID SQL _TEXT
------------- Begin -----------------------------------------------------------------------------------------------------------------
G3bug3dp68bqt delete from t where OBJECT_ID =: B1 + 1 RETURNING OBJECT_NAME INTO: O0
391g7kanthzt9 declare a number; v_name varchar2 (128); begin for I in 1 .. 100 loop delete from t where object_id = I + 1
Returning object_name into v_name; dbms_output.put_line (v_name); commit; end loop; end;
Oracle parses all the code to delete from t where OBJECT_ID =: B1 + 1 RETURNING OBJECT_NAME INTO: O0 cursor, reducing the resource consumption caused by repeated SQL parsing by the optimizer.

2. Bind variables in pl/SQL:
In pl/SQL programs, execute immediate [target SQL statement with variable binding] using [specific input value corresponding to variable binding] is used, the value of the variable bound to the preceding SQL statement is followed by the value of using. The value of using is followed by the number of variable bound, these values replace the bound variable values in the preceding SQL statement in order.

SQL> set serveroutput on;
SQL> declare
2 v_name varchar2 (32 );
3 begin
4 execute immediate 'Select object_name from xiaoyu01 where object_id =: 1 and owner =: 1 'into v_name using 10, 'sys ';
5 dbms_output.put_line (v_name );
6 end;
7/

PL/SQL procedure successfully completed.
Here, into is a way to assign a value to a variable in pl/SQL. This variable v_name is not bound to a variable.

Note 1: only using can be used to assign values to bind variables in pl/SQL, while into can be used to assign values to variables in pl/SQL.

Note 2: the difference between binding and SQL statements is that even if the names of the bound variables are identical in pl/SQL statements, they do not necessarily indicate that the specific values of the two bound variables are the same, bind variables in pl/SQL read the values following using in sequence.

Note 3: execute immediate SQL _text declares dynamic SQL statements. Here, SQL _text must meet the syntax of the SQL engine, and non-dynamic SQL must meet the syntax of the pl/SQL storage engine.

Note 4: Although a dynamic SQL statement is encapsulated by a pl/SQL program, the SQL engine generates a separate cursor object based on the original text of the dynamic SQL statement (case-insensitive conversion is not performed.

SQL> select SQL _id, SQL _text from v $ SQL where SQL _text like 'Select object_name from xiaoyu01 % ';

SQL _ID
-------------
SQL _TEXT
--------------------------------------------------------------------------------
G9zuzpp1r1jh0
Select object_name from xiaoyu01 where object_id =: 1 and owner =: 1
How to generate a cursor cache using non-dynamic SQL statements in pl/SQL:
SQL and pl/SQL are actually operated by two storage engines. SQL statements in pl/SQL are handed over to the SQL engine for processing, for dynamic SQL statements, the test above shows that oracle generates cursor based on the original SQL _text text of the dynamic SQL statement. If it is not a dynamic SQL statement, how does oracle store the cursor corresponding to this pl/SQL statement? In fact, there are two situations:

1 if it is a select into statement, oracle only has this pl/SQL Program body stored in the cursor cache.

SQL> declare
2 v_name varchar2 (64 );
3 begin
4 select object_name into v_name from t where rownum <2;
End;
6/

PL/SQL procedure successfully completed.

SQL> select SQL _id, SQL _text from v $ SQL where lower (SQL _text) like '% select object_name into v_name from t where rownum <2% ';

SQL _ID SQL _TEXT
------------- Begin -----------------------------------------------------------------------------------------------------------------
1chbdafu44tcy declare v_name varchar2 (64); begin select object_name into v_name from t where rownum <2;
End;

Bnprfwc84nv7u select SQL _id, SQL _text from v $ SQL where lower (SQL _text) like '% select object_name into v_name from t
Where rownum <2%'
2. If it is a DML statement, oracle will store the original pl/SQL statement and change all the original DML statements in pl/SQL to uppercase, A new cursor is generated and stored in the cursor cache.

SQL> alter system flush shared_pool;

System altered.

SQL> declare
2 v_name varchar2 (64 );
3 begin
4 delete from t where rownum <2;
End;
6/

PL/SQL procedure successfully completed.
SQL> select SQL _id, SQL _text, first_load_time from v $ SQL where lower (SQL _text) like '% delete from t where rownum <2% ';

SQL _ID SQL _TEXT FIRST_LOAD_TIME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Cffprycyhasgg declare v_name varchar2 (64); begin delete from t where rownum <2; end; 2015-05-22/00: 35: 14
3366ddvwzdg35d delete from t where rownum <2 2015-05-22/00: 35: 14
1k8m36uynvrpy select SQL _id, SQL _text, first_load_time from v $ SQL where lower (SQL _text) like '% delete from t where r 2015-05-22/00: 36: 14
Ownum <2%'
Use the bind variable in the DML statement in the pl/SQL Program body:

1. Use the bind variable in the insert into statement

SQL> declare
2 begin
3 execute immediate 'Insert into t (object_id, object_name) values (: 1,: 2) 'using 100, 'xiaoyu ';
4 commit;
5 end;
6/

PL/SQL procedure successfully completed.
2 use the bind variable in the delete from statement

SQL> declare
2 begin
3 execute immediate 'delete from t where object_id =: 1 'using 100;
4 commit;
5 end;
6/

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;
SQL> declare
2 v_name varchar2 (128 );
3 v_ SQL varchar2 (128 );
4 begin
5 v_ SQL: = 'delete from t where rownum =: 1 returning object_name into: 2 ';
6 execute immediate v_ SQL using 1 returning into v_name;
7 dbms_output.put_line (v_name );
8 commit;
9 end;
10/

C_COBJ #

PL/SQL procedure successfully completed.

SQL> select SQL _text, SQL _id from v $ SQL where SQL _text like 'delete from t where rownum =: 100 ';

SQL _TEXT
--------------------------------------------------------------------------------
SQL _ID
-------------
Delete from t where rownum =: 1 returning object_name into: 2
784c1dh672vvc
Supplement the returning into knowledge: returning [column_name] into [variable] records the column values of data rows affected by dml statements into variables, in pl/SQL procedure and SQL statements, you can use the following syntax:

In PL/SQL procedure, the column values of the data rows affected by the insert, update, and delete operations can be returned. The column values of the modified data rows are recorded using the returning into method.

SQL> declare
2 v_name varchar2 (128 );
3 begin
4 delete from t where rownum <2 returning object_name into v_name;
5 dbms_output.put_line (v_name );
6 commit;
7 end;
8/

PROXY_ROLE_DATA $

PL/SQL procedure successfully completed.
You can also add a returning into record to modify the column values of a row in an SQL statement:

SQL> variable v_name varchar2 (32 );
SQL> delete from t where rownum <2 returning object_name into: v_name;

1 row deleted.

SQL> rollback;

Rollback complete.

SQL> print: v_name;

V_NAME
--------------------------------------------------------------------------------
I _IND1
Since pl/SQL engines must Exchange SQL statements with SQL engines every time they process them (here, SQL statements are not only dynamic SQL statements, but also select, insert, update, delete, and other SQL statements ), however, if the bulk storage engine exchange consumes a lot of resources, for example, the following two forms:

1 when an explicit cursor or reference cursor requires a fetch operation, the cyclic operation needs to be processed by the pl/SQL engine, while a fetch record corresponds to the SQL statement to be executed by the SQL engine.
2 when you need to execute an SQL statement inside an explicit cursor or a reference cursor loop

If a batch of records can be fetch at a time or a batch of SQL statements can be executed at a time, the interactions between the pl/SQL engine and the SQL engine can be effectively reduced, of course, pl/SQL code performance will also be improved.

Briefly describe development-related knowledge, such as % type and % rowtype, type typename is table of YPE Ype, sys_refcursor, and constant pls_integer.

% Type indicates that the Type of a variable is the same as that of another defined variable or a column in the table. oracle provides the % type definition method, if the referenced variable is modified, the new variable will be automatically modified immediately, for example, defining the eName emp. name % type. If the name field type of the emp table is modified, the variable ename is also automatically modified.
% Rowtype is used to define that a variable is consistent with all data types in the table. For example, if we want to obtain all data in a row in the table, it is too troublesome to define a variable % type for each column, v_emp emp % rowtype can be directly defined, and v_emp.name is the value of the name column corresponding to the row of data in the emp table.
Sys_refcursor is the system's built-in method for defining the cursor, with cursor c is select... Different from emp, sys_refcursor is mostly used for dynamic cursors, that is, it is assigned to the cursor only when the open state is used. The cursor defines the specific value of the cursor at the time of definition.
Type typename is table of YPE is a custom data type (arrays can also be declared), such as type type1 is table of emp. ename % type; type type2 is table of emp $ rowtype; type type3 is table of varchar2 (32 );

After introducing the above development knowledge, let's take a look at how to batch fetch the cursor to a specific array variable example:

Declare
Cur_t sys_refcursor;
Type typelist is table of varchar2 (32); defines the array type typelist
Type_name typelist;
V_ SQL varchar2 (100 );
CN_BATCH_SIZE constant pls_integer: = 1000; constant pls_integer defines a constant
Begin
V_ SQL: = 'SELECT name from t where id =: ID ';
Open cur_t for v_ SQL using 100; assigns a value to the cur_t of the cursor. A dynamic cursor is assigned a value when it is opened, which is different from a static cursor.
Loop
Fetch cur_t bulk collect into type_name limit CN_BATCH_SIZE; assign the cursor to the array. Here the fetch cursor_name bulk collect into custom array limit CN_BATCH_SIZE is used to batch fetch
For I in 1 .. type_name.count loop array output, array. count indicates the total length of data, array (I) indicates which array
Dbms_output.put_line (type_name (I ));
End loop;
Exit when type_name.count <CN_BATCH_SIZE;
End loop;
Close cur_t;
End;
/
Cui Hua's blog that executes SQL statements in batches has corresponding article templates. Here only the syntax for batch execution is described:
Forall 1 in 1 .. [length of the custom array]
Execute immediate [target SQL statement with variable binding] using [specific input value for variable binding];

The initial usage of variable binding ends here. We will introduce some important points for you to view, classify, and use variables!