Geek Answers Handbook

What is the difference between Bind PLSQL variables and host variables

Regarding PLSQL, what is the difference between a "host variable" and a "variable binding"?

This one from askTom says that "the difference is blurred in plsql - it is very close to sql". Then what a tiny, blurry difference?

Statement 1:

SELECT 1 FROM dual WHERE dummy = :B1;

Statement 2:

SELECT 1 FROM dual WHERE dummy = v_var;

In these two operations, Statement 1 is a bind variable, and Statement 2 is a Host variable, is it correct?

+4
source share
3 answers

Consider this C # snippet:

int    v_empno = 7369;
string v_ename;

OracleCommand cmd = con.CreateCommand();
cmd.Parameters.Add("paramEmpno", OracleDbType.Decimal, v_empno, ParameterDirection.Input);
cmd.CommandText = "select e.ename from scott.emp e where e.empno = :1";
v_ename = cmd.ExecuteScalar().ToString();

v_empnoand v_ename- host variables. Here, you explicitly create your bind variable to use :1in your statement.

Consider this PL / SQL fragment:

declare
   v_empno  number := 7369;
   v_ename  varchar2(10);
begin
   select e.ename
     into v_ename
     from scott.emp e
    where e.empno = v_empno;
   dbms_output.put_line(v_ename);
end;
/

v_empno v_ename , SQL PL/SQL, / PL/SQL - , #. SQL, PL/SQL, :

   select e.ename
     from scott.emp e
    where e.empno = :B1

PL/SQL, :B1 v_empno PL/SQL. , Tom Kyte , PL/SQL. PL/SQL, PL/SQL- SQL-. PL/SQL, .

+4

- Oracle. , Oracle, Oracle . Oracle .

- C, . lvalue. .

- . , . PL/SQL , . PL/SQL .

:

int     empno; 
char    ename[10]; 
float   sal; 
... 
EXEC SQL SELECT ename, sal INTO :ename, :sal FROM emp 
     WHERE empno = :empno; 

empno, ename, sal are all Host variables
:empno, :ename, :sal are all bind variables

+3

Bind/Host embedded SQL/PLSQL...

Bind (Java Pro * C) Oracle..

Host - , Oracle...

, .

SQL PL/SQL, , , .

- , SQL .. Adaptive .

.. , PL/SQL ( Oracle , JDBC OCI) .. SQL.

:

 EXECUTE IMMEDIATE 'SELECT NAME FROM EMP WHERE ID = : ID' USING V_MYID

:ID - Bind ---- ( )
V_MYID - ..... ( / )

Embedded SQL.

+1

More articles:


All Articles