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 with Db2® servers. 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.

Db2 for z/OS® and Informix® Dynamic Server data servers have the following limitations with the usage of bind variables:

Bind variables cannot be initialized using the EXEC command.
```
Exec :var_name:='john'	/* this is not supported */
```

Bind variables cannot be initialized using a begin-end block.

begin
:var_name:='john';	/* this is not supported in a begin-end block */
end;

Since PL/SQL is not supported on Db2 for z/OS and Informix Dynamic Server data servers, bind variables are not supported in a PL/SQL body.

Variables with type CURSOR are not supported.

SQL> CREATE PROCEDURE getEmployeeData( ID INT, OUT NAME char(10),
           OUT DOB Date, OUT SAL DECIMAL(7,2))
           LET NAME='dummy';
           LET DOB='10/10/2010';
           LET SAL=0;
     SELECT empname, empdob, salary INTO name, dob, sal FROM emp   WHERE empid = ID; 
     END PROCEDURE;
/	
DB250000I: The command completed successfully.

SQL> define var_id=1001		/* usage of substitution variable */
SQL> Variable name varchar(10)
DB250000I: The command completed successfully.
SQL> Variable dob date
DB250000I: The command completed successfully.
SQL> Variable salary double
DB250000I: The command completed successfully.

Call getEmployeeData(&var_id,  :name, :dob, :salary)
DB250000I: The command completed successfully.  
SQL> Print name 
'JOHN'  
SQL> Print dob
'26/04/1982'  
SQL> Print salary
10000.50

Precision and scale values can be specified while creating bind variables of with the NUMBER and DECIMAL data types. There is a limitation in precision support. Any decimal or number values that are assigned are not modified to the precision specified in the definition of the variable. See example 13 for more details.

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.