PL/SQL Programming   «Prev  Next»
Lesson 5Non-PL/SQL variables: bind and host
ObjectiveUse non-PL/SQL Bind, Host Variables to manipulate Data

Use non-PL/SQL Bind, 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 character or 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 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;
   vn_base_salary NUMBER := 3000;
   vn_bonus NUMBER := 1000;
   :gvn_total_salary := vn_base_salary + vn_bonus;
PRINT gvn_total_salary

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.