DB2 Version 10.1 for Linux, UNIX, and Windows

Creating PL/SQL procedures and functions from a CLP script

You can create PL/SQL procedures and functions from a DB2® command line processor (CLP) script.

Procedure

Formulate PL/SQL procedure or function definitions within a CLP script file. Terminate each statement with a new line and a forward slash character (/). Other statement termination characters are also supported.
Save the file. In this example, the file name is script.db2.
Execute the script from the CLP. If a forward slash character or a semicolon was used to terminate statements, issue the following command:
```
db2 -td/ -vf script.db2
```
If another statement termination character (for example, the @ character) was used in the script file, you must specify that character in the command string. For example:
```
db2 -td@ -vf script.db2
```

Results

The CLP script should execute successfully if there are no syntax errors.

Example

The following example of a CLP script creates a PL/SQL function and procedure, and then calls the PL/SQL procedure.

CONNECT TO mydb
/

CREATE TABLE emp (
     name            VARCHAR2(10),
     salary          NUMBER,
     comm            NUMBER,
     tot_comp        NUMBER
)
/

INSERT INTO emp VALUES ('Larry', 1000, 50, 0)
/
INSERT INTO emp VALUES ('Curly', 200, 5, 0)
/
INSERT INTO emp VALUES ('Moe', 10000, 1000, 0)
/

CREATE OR REPLACE FUNCTION emp_comp (
     p_sal           NUMBER,
     p_comm          NUMBER )
RETURN NUMBER
IS
BEGIN
    RETURN (p_sal + NVL(p_comm, 0)) * 24;
END emp_comp
/

CREATE OR REPLACE PROCEDURE update_comp(p_name IN VARCHAR) AS
BEGIN
    UPDATE emp SET tot_comp = emp_comp(salary, comm)
      WHERE name = p_name;
END update_comp
/

CALL update_comp('Curly')
/

SELECT * FROM emp
/

CONNECT RESET
/

This script produces the following sample output:

CALL update_comp('Curly')

  Return Status = 0

SELECT * FROM emp

NAME       SALARY             COMM               TOT_COMP
---------- ------...--------- ----...----------- --------...-------
Larry                    1000                 50                  0
Curly                     200                  5               4920
Moe                     10000               1000                  0

  3 record(s) selected.

What to do next

Test your new procedures or functions by invoking them. For procedures, use the CALL statement. For functions, execute queries or other SQL statements that contain references to those functions.