Shulou(Shulou.com)06/01 Report--

Binding variables (bind variable) we often use in writing plsql, so let's continue the previous section to see the difference between static sql and dynamic sql in binding variables in plsql.

Declare v_value_a varchar2 (100,100); v_value_b varchar2 (100,100); v_name varchar2 (50); begin v_value_a: = 'SMITH'; select e.ename/*+test*/into v_name from scott.emp e where e.ename = vested valuevaluation; v_value_b: =' ALLEN'; select e.ename/*+test*/into v_name from scott.emp e where e.ename = vested valuevaluation

Query v$sql

Select * from v$sql d where d.SQL_TEXT like'% / * + test*/%'

Here we can see that only one sql_id,oracle is generated, which automatically changes the variable vested valuevalues to binding variables: B1.

Let's take a look at dynamic sql.

PLSQL_1:declare v_value_a varchar2; v_value_b varchar2; v_name varchar2; begin v_value_a: = 'SMITH'; v_value_b: =' ALLEN'; execute immediate 'select e.ename/*+sql*/ from scott.emp e where e.ename =: xxx' using v_value_a Execute immediate 'select e.ename/*+sql*/ from scott.emp e where e.ename =: yyy' using vastly valued

Query v$sql

Select * from v$sql d where d.SQL_TEXT like'% / * + sql*/%'

Here you can see that the dynamic sql is parsed with the execution of sql as the text, and if the two sql are different, they are different sql statements.

two。 Get the value of the bound variable

Can we get the value of the bound variable? You can query it through v$sql_bind_capture.

SQL_1:select d.NAME, d.POSITION, d.SQL_ID, value_string from v$sql_bind_capture d where d.SQL_ID in (select sql_id from v$sql v where v.SQL_TEXT like'% / * + sql*/%')

At this point, we see that the values of the binding variables are the values of v_value_a and v_value_b seen above.

Then go down and change the value of v_value_a:

V_value_a: = 'CLARK'

Execute PLSQL_1,SQL_1

The value of the bound variable has not changed, so why?

The v$sql_bind_capture view is a snapshot view, which does not save all the binding variables of the executed SQL, but only records the binding values that were last captured, and there is an interval between each capture. ORACLE provides us with an implicit parameter _ cursor_bind_capture_interval (default 900s), which is the parameter that controls the frequency of fetching bound variables.

Cannot be seen directly if the binding variable is of type DATE.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.