Using Bind Variables

Suppose that you want to be able to display the variables you use in your PL/SQL subprograms in SQL*Plus or use the same variables in multiple subprograms. If you declare a variable in a PL/SQL subprogram, you cannot display that variable in SQL*Plus. Use a bind variable in PL/SQL to access the variable from SQL*Plus.

Bind variables are variables you create in SQL*Plus and then reference in PL/SQL. If you create a bind variable in SQL*Plus, you can use the variable as you would a declared variable in your PL/SQL subprogram and then access the variable from SQL*Plus. You can use bind variables for such things as storing return codes or debugging your PL/SQL subprograms.

Because bind variables are recognized by SQL*Plus, you can display their values in SQL*Plus or reference them in other PL/SQL subprograms that you run in SQL*Plus.

Creating Bind Variables

You create bind variables in SQL*Plus with the VARIABLE command. For example

VARIABLE ret_val NUMBER

This command creates a bind variable named ret_val with a datatype of NUMBER. For more information, see the VARIABLE command in Chapter 8. (To list all of the bind variables created in a session, type VARIABLE without any arguments.)

Referencing Bind Variables

You reference bind variables in PL/SQL by typing a colon (:) followed immediately by the name of the variable. For example

:ret_val := 1;

This command assigns a value to the bind variable named ret_val.

Displaying Bind Variables

To display the value of a bind variable in SQL*Plus, you use the SQL*Plus PRINT command. For example

PRINT ret_val

This command displays a bind variable named ret_val. For more information about displaying bind variables, see the PRINT command in the "Command Reference" in Chapter 8.

Example 3-17 Creating, Referencing, and Displaying Bind Variables

To declare a local bind variable named id with a datatype of NUMBER, enter

SQL> VARIABLE id NUMBER

Next, put a value of "1" into the bind variable you have just created:

SQL> BEGIN
  2  :id := 1;
  3  END;

If you want to display a list of values for the bind variable named id, enter

SQL> PRINT id

Try creating some new departments using the variable:

SQL> EXECUTE :id := dept_management.new('ACCOUNTING','NEW YORK')
SQL> EXECUTE :id := dept_management.new('RESEARCH','DALLAS')
SQL> EXECUTE :id := dept_management.new('SALES','CHICAGO')
SQL> EXECUTE :id := dept_management.new('OPERATIONS','BOSTON')
SQL> PRINT id
SQL> COMMIT






Note:



dept_management.new refers to a PL/SQL function, "new", in a package (dept_management). The function "new" adds the department data to a table.