Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use the binding variables in ORACLE". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to use binding variables in ORACLE".

First, the role of binding variables

Variable_name (alphanumeric, or alphanumeric combination)

Effectively reduce hard parsing

Second, the typical usage of binding variables

In sql

Var x number

Exec: X: = 7876

Select * from emp where empno =: X

In pl/sql, for example

Declare

Vc_name varchar2 (10)

Begin

Execute immediate 'select ename from emp where empno=: 1' into vc_name using 7876

Dbms_output.put_line (vc_name)

End

So the standard syntax for binding variables in pl/sql is:

Execute immediate [statement using bound variable] using corresponds to the specific input value of the bound variable

Typical usage of binding variables for DML statements in PL/SQL

Declare

V_sql1 varchar2 (50)

V_sql2 varchar2 (50)

V_num1 number

V_num2 number

Begin

V_sql1: = 'insert into emp (empno,ename,sal) values

Execute immediate v_sql1 using 8001 and jackpot 1000

V_num1: = sql%rowcount;-- the sql in sql%rowcount is the internal cursor of oracle, and rowcount means how many rows of data are affected by the previous dml sql statement

Execute immediate v_sql1 using 8002, "mikewarm", 2000

V_num2: = sql%rowcount

Dbms_output.put_line (v_num1+v_num2)

End

Binding variables can also be used in dynamic sql, and the keyword returning can be used in conjunction with SQL with bound variables to extract the row record fields affected by the SQL.

III. Typical use of batch binding in pl/sql

Batch binding: processing a batch of data at a time

The pl/sql engine refers to the subsystem in which Oracle processes all the rest of the pl/sql code (such as variables, loops, arrays, assignments) in the database except SQL statements.

Batch binding can effectively reduce the number of interactions between SQL engine and pl/sql engine.

In theory, as long as the SQL statement is executed in pl/sql, there will be interaction between the two engines. In fact, the performance impact of the interaction between the two engines is mainly reflected in the following two aspects

1. When explicit cursors or reference cursors need to perform fetch operations in a loop, the loop is executed by pl/sql, and the SQL in fetch is executed by the SQL engine, so that for each fetch record, the two engines need to interact once

2. When the SQL statement needs to be executed inside the loop of an explicit cursor or a reference cursor, as above, it also needs to interact with each other.

So when fetch records a batch, or executes a batch of SQL at a time, it will greatly improve the efficiency of pl/sql.

Syntax corresponding to batch fetch

Fetch cursorname bulk colletc into [custom array]

Syntax for batch execution of SQL in pl/sql

Forall i in 1.. [custom array length]

Execute immediate [sql with bound variable] using [bind variable input value]

Declare

Cur_emp sys_refcursor

V_sql varchar2 (4000)

Type namelist is table of varchar2 (10)

Enames namelist

Cn_batch_size constant pls_integer: = 1000

Begin

Vandsql _ = 'select ename from emp where empno >: 1'

Open cur_emp for v_sql using 7900

Loop

Fetch cur_emp bulk collect into enames limit cn_batch_size

For i in 1..enames.count loop

Dbms_output.put_line (enames (I))

End loop

Exit when enames.count

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.