Variable binding in Oracle PL/SQL

From the design of the Oracle shared pool and the PL/SQL writing method recommended by Oracle, we can see that variable binding has a great impact on performance, how to bind variables in PL/SQL?

First, let's take a look at the usage of not using variable binding:

Declare

Cursor cur_temp (ID number) is
Select * From table_a where a = ID;

C_temp cur_temp % rowtype;

Beign

Open cur_temp (1 );

Loop

Fetch cur_temp into c_temp;

Exit when cur_temp % notfound;

Insert into B values (c_temp.a );

End loop;

Close cur_temp;

Commit;

End;

No variable binding is used above, including the cursor and operation statement.

Let's look at the usage of variable binding below:

First

Type cursortype is ref cursor;

Then:

Declare

Cur_temp cursortype;

C_temp table_a % rowtype;

Begin

Open cur_temp for 'select * From table_a where a =: 1 'using 91;

Loop

Fetch cur_temp into c_temp;

Exit when cur_temp % notfound;

Execute immediate 'insert into B values (: 1) 'using c_temp.a;

End loop;

Close cur_temp;

Commit;

End;

The above is written in PL/SQL blocks. This method is also applicable to stored procedures, triggers, functions, packages, and other places where PL/SQL can be used.

To use into, you can use the following:

I number (6 );

Execute immediate 'select count (*) from table_a where a =: 1 'into I using 89;

After execution, the retrieved values are stored in variable I.

In PL/SQL, the common usage of variable binding is basically as shown above. We recommend that you bind all variables.