Bind variables in CLPPlus

Bind variables are used in place of literal values. If you issue SQL statements multiple times, you can use bind variables to reduce the number of literal values.

Authorization

No special authorization is required.

Declaration

A bind variable can be declared using the following syntax:

name: Specifies the name of the bind variable.
datatype: Specifies the data type that is associated with the bind variable. The data type can be one of: BOOLEAN, CHARACTER, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, REAL, SMALLINT, or VARCHAR.; REFCURSOR is also supported. REFCURSOR is used to receive the OUT parameter values of type CURSOR in procedures, functions, and anonymous PL/SQL blocks.; NUMBER, NUMBER(p[,s]), and VARCHAR2 are also supported. NUMBER and NUMBER(p[,s]) are implicitly mapped to the DECIMAL data type. VARCHAR2 is implicitly mapped to the VARCHAR data type.; CLPPlus allows the use of BOOLEAN, ROW, and ARRAY data types as parameters for stored procedures. You can run a stored procedure with the CALL or EXEC CLPPlus statements.

Scope

Bind variables persist over the duration of a user's CLPPlus session. When a CLPPlus session is started, bind variables can be declared and used during that session. When a CLPPlus session is ended, any bind variables are cleared.

Restrictions

When used in an SQL statement or an anonymous PL/SQL block, a bind variable can appear only once. If the bind variable is used more than once, an error from the database server is returned.

These restrictions apply to the EXECUTE CLPPlus command as well.

Examples

The following examples show how you can define, initialize, and use bind variables.

Bind variables that are named ID and LNAME of type VARCHAR:
```
 VARIABLE ID VARCHAR
 VARIABLE LNAME VARCHAR
```
A bind variable that is named ID initialized in a PL/SQL block:
```
BEGIN
   SET :ID = '000020';
END;
/
```

Bind variables ID and LNAME used in a PL/SQL block:

BEGIN
   SELECT lastname INTO :LNAME FROM employee
	WHERE empno = :ID;
END;
/

A single PL/SQL statement initializes a bind variable named ID :
```
EXECUTE SET :ID = '000022';
```
The variable ID is initialized from a substitution variable a (a is defined with the DEFINE CLPPlus command):
```
EXECUTE SET :ID = &a;
```
The ID bind variable is used in a SELECT statement:
```
SELECT lastname FROM employee WHERE empno = :ID;
```
The ID and LNAME bind variables are used in an UPDATE statement:
```
UPDATE employee SET lastname = :LNAME WHERE empno = :ID;
```
The salary bind variable is defined with the number data type:
```
variable salary number
exec :salary = 1000.00
```
The bonus bind variable is defined with the number(p[,s]) data type:
```
variable bonus number(6)
exec SET :bonus = 999.999
```

The comm bind variable is defined with the number(p[,s]) data type:

variable bonus comm(4,2)
exec SET :comm = 10.455

SQL> print comm
10.45

The name bind variable is defined with the varchar2 data type:
```
variable name varchar2
exec SET :name ='MICHAEL'
```
This example shows the substitution of bind variables as input and output arguments in procedure execution. Assume a file example_proc.db2 contains the following statement:
```
   CREATE PROCEDURE DEPT_MEDIAN 
   (IN deptNumber SMALLINT, OUT medianSalary DOUBLE)
```
Bind variables are substituted as input and output arguments. Define the bind variables:
```
   variable in_var integer
   variable out_var double
```
Run the procedure and substitute the variables as parameters:
```
   call dept_median(:in_var, :out_var)
```
Optionally print the contents of the output argument, which is the out_var bind variable:
```
   print out_var
```
This example shows a bind variable var1, which does not reflect the precision in the definition:
```
   variable var1 number(4,2)
   DB250000I: The command completed successfully.
```
Assign a value that has precision of 6 digits and scale of 3 digits:
```
   exec SET :var1 = 333.333
   /
DB250000I: The command completed successfully.
```
Print the contents of var1:
```
   print var1
   333.33
```
The scale is correct, 2. The precision is not 4 as defined. This scenario is a current limitation in functionality.