Difference between variable binding in SQL and PL/SQL

In Oracle, there are two optional parsing processes for a submitted SQL statement: Hard parsing and soft parsing.

A hard parsing requires parsing, creating execution paths, optimizing access plans, and many other steps. hard explanation not only consumes a lot of cpu, but also occupies important latch resources, the expansion of the system is severely affected (that is, the release of the system is restricted), and the resulting problems cannot be solved by increasing the number of memory stick and cpu. This is because the bolts are set to access and modify some memory areas in sequence. These memory areas cannot be modified at the same time. After an SQL statement is submitted, oracle first checks whether there are identical statements in the shared Buffer pool (shared pool). If yes, it only needs to perform soft analysis, otherwise, you must perform a hard analysis.

The only way to allow oracle to reuse the execution plan is to bind variables. The essence of variable binding is the substitution variable used to replace constants in SQL statements. Variable binding makes the SQL statements submitted each time identical.

1. Common SQL statements for binding variables in SQL: SELECT fname, lname, pcode FROM cust WHERE id = 674;
SELECT fname, lname and pcode FROM cust WHERE id = 234;
SELECT fname, lname, pcode FROM cust WHERE id = 332; SQL statement containing Bound variables: SELECT fname, lname, pcode FROM cust WHERE id =: cust_no; use the Bind variable in SQL * plus: SQL> variable x number; SQL> exec: x: = 123; SQL> SELECT fname, lname, pcode FROM cust WHERE id =: x; 2. pl, sqlpl, and SQL automatically bind variables without the worry of programmers. referencing variables is to reference the bound variables, that is, many SQL statements you write will automatically use the bound variables, example: create or replace procedure dsal (p_empno in number)
As
Begin
Update emp
Set sal = sal * 2
Where empno = p_empno;
Commit;
End;
/At this time, you may want to replace p_empno with the bound variable, but this is completely unnecessary, because in pl/SQL, the referenced variable references the bound variable.

However, when the referenced value is not a variable (constant or expression) and needs to be repeated for multiple times, you also need to dynamically bind the variable:

DECLARE

C_temp site % ROWTYPE;
TYPE cursortype is ref cursor;
Cur_temp cursortype;
BEGIN
OPEN cur_temp FOR ('select * from site where site_id =: 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.site_id;
End loop;
CLOSE cur_temp;
COMMIT;
END;

1 2 Next Page