RelationalDBDesign  
prev next prev next
  Course navigation
 
Lesson 5
 Non-PL/SQL variables: bind and host
 
Objective
 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;
DECLARE
   vn_base_salary NUMBER := 3000;
   vn_bonus NUMBER := 1000;
BEGIN
   :gvn_total_salary := vn_base_salary + vn_bonus;
END;
PRINT gvn_total_salary
    In the next lesson, the DBMS_OUTPUT.PUT_LINE package procedure will be discussed.
  Course navigation