Lesson 5	Non-PL/SQL variables: bind and host
Objective	Use non-PL/SQL Bind and Host Variables to manipulate Data

Use non-PL/SQL Bind and Host Variables to manipulate Data

A bind variable is a variable that you declare in a host environment and then use to pass runtime values. These values can be 1) character or 2) numeric. You can pass these values either in or out of one or more PL/SQL programs, such as packages, procedures, or functions. SQL*Plus is a good example of a host environment.

Creating Bind Variables

To declare a bind variable in the SQL*Plus environment, you use the command VARIABLE.
For example,

VARIABLE g_return_value NUMBER

Using Bind Variables: One of the benefits of native dynamic SQL is the ability to use bind variables in your code. For example, consider this example from earlier in this chapter:
```
declare
Symbol VARCHAR2(6) := 'WOND';
begin
EXECUTE IMMEDIATE 'delete from STOCK where Symbol = :symbol'
USING Symbol;
end;
```
In the preceding listing, :symbol is a bind variable. Successive values of symbol can be passed in to the EXECUTE IMMEDIATE command. Because a bind variable is used, the command will not have to be parsed repeatedly. If you monitor the database as the application is used, you will see in V$SQL one entry for the command. If you had not used bind variables, you would see a separate entry in V$SQL for each separate execution of the command. That is, instead of seeing
```
delete from STOCK where Symbol = :symbol
```
in the library cache, you would see one entry for each unique deletion, such as
```
delete from STOCK where Symbol = 'WOND'
```

Using host Variables

Upon declaration, the "bind variables" now become host to that environment, and you can now use these variables within your PL/SQL programs, such as packages, procedures, or functions. To reference host variables, you must add a prefix to the reference with a colon (:) to distinguish the host variables from declared PL/SQL variables. Here is an example that computes a total salary based on a base salary plus a bonus. The computed value is stored in gnv_total_salary and is available to the SQL*Plus environment as it is declared out of the PL/SQL block with the VARIABLE command. The value is printed to the screen with the PRINT command.

VARIABLE gvn_total_salary NUMBER;
DECLARE
   vn_base_salary NUMBER := 3000;
   vn_bonus NUMBER := 1000;
BEGIN
   :gvn_total_salary := vn_base_salary + vn_bonus;
END;
PRINT gvn_total_salary

Each of these commands would be separately parsed, and each would be taking space in memory. Failing to use bind variables may thus fill your shared pool with a large number of very similar commands, which is a waste of resources. Database administrators can resolve a portion of this problem by setting the CURSOR_SHARING initialization parameter to SIMILAR, but at its heart this is a problem that should be solved by using a proper development approach. When the query is run, bind arguments replace their corresponding placeholders in the string. Each of the placeholders must be associated with a bind variable in the using clause or a returning into clause. Bind variables can be numeric, character, or string literals. The following example illustrates the use of bind variables within a dynamic update statement:

declare
sql_stmt VARCHAR2(200);
Symbol VARCHAR2(6) := 'ADSP';
CloseToday NUMBER(6,2);
begin
sql_stmt := 'update STOCK 
set CloseToday = 32 WHERE Symbol = :1
RETURNING CloseToday INTO :2';
EXECUTE IMMEDIATE sql_stmt USING Symbol 
RETURNING INTO CloseToday;
end;

The returning into clause is only used in insert, update, and delete commands that have a returning clause. For each value returned by the command, there must be a corresponding variable in the returning into clause.

The only SQL statements allowed in a PL/SQL program are DML and transaction control satements. Specifically, DDL statements are illegal.
EXPLAIN PLAN, although classified as DML, is also illegal. In order to explain why this is the case, we need to look at the way PL/SQL is designed. In general, a programming language can bind variables in two ways, early or late. Binding a variable is the process of identifying the storage location associated with an identifier in the program. In PL/SQL, binding also involves checking the database for permission to access the schema object referenced. A language that uses early binding does the bind during the compile phase, while a language that uses late binding postpones the bind until run time. Early binding means that the compile phase will take long, but execution will be faster, since the bind has already been completed. Late binding shortens the compile time but lengthens the execution time.
In the next lesson, the DBMS_OUTPUT.PUT_LINE package procedure will be discussed.