CODEWITHSUNDEEP
variablesplsqlbind
AFranzen
AFranzen

Reputation: 1

Variables in PL/SQL; always bind variables?

I read this question: Are PL/SQL variables in cursors effectively the same as bind parameters?

The answers given were very good. Rather than use that thread and ask for clarification (as the rule states), I wish to ask a clarification here. Should (can) we assume every variable in a PL/SQL statement is a bind variable?

Upvotes: 0

Views: 59

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95082

Yes, whenever we use a PL/SQL variable in embedded SQL it is a bind variable.

If we don't use it in SQL, then it is just a variable of course, because bind variables are an SQL thing only. As soon as we use it in SQL in our PL/SQL package, function or whatever, it becomes a bind variable for the SQL engine.

declare
  v_one    varchar2(100);
  v_two    varchar2(100);
  v_three  varchar2(100);
begin
  v_one := 'one';
  v_two := 'two';

  select col_three into v_three from mytable where col_one = v_one;
end;

v_one, v_two and v_three are all PL/SQL variables. However, there is SQL in our PL/SQL block and we are using v_one there in the WHERE clause. So v_one is a bind variable in that SQL statement. (v_three is just the return value from the query. into v_three is not SQL, but PL/SQL, as SQL knows no INTO keyword.) v_two is not even used in any SQL, so it is never being used as a bind variable.

Upvotes: 1

Related Questions