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
Results
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.