DB2 SQL procedure
-- This is a CLP script that creates an SQL procedure.-- To create the SQL procedure using this script, perform the following steps:
-- 1. connect to the database
-- 2. issue the command "db2 -td@ -vf <script-name>"
-- where <script-name> represents the name of this script
--
-- To call this SQL procedure from the command line, perform the following steps:
-- 1. connect to the database
-- 2. issue the following command:
-- db2 "CALL create_dept_table ('D11', ?)"
--
-- The sample "dynamic.sqc" demonstrates how to call this SQL procedure using
-- an embedded C client application.
CREATE PROCEDURE create_dept_table
(IN deptNumber VARCHAR(3), OUT table_name VARCHAR(30))
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE new_name VARCHAR(30);
DECLARE stmt VARCHAR(1000);
-- continue if sqlstate 42704 ('undefined object name')
DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
SET stmt = '';
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET table_name = 'PROCEDURE_FAILED';
SET new_name = 'DEPT_'||deptNumber||'_T';
SET stmt = 'DROP TABLE '||new_name;
PREPARE s1 FROM stmt;
EXECUTE s1;
SET stmt = 'CREATE TABLE '||new_name||
'( empno CHAR(6) NOT NULL, '||
'firstnme VARCHAR(12) NOT NULL, '||
'midinit CHAR(1) NOT NULL, '||
'lastname VARCHAR(15) NOT NULL, '||
'salary DECIMAL(9,2))';
PREPARE s2 FROM STMT;
EXECUTE s2;
SET stmt = 'INSERT INTO '||new_name || ' ' ||
'SELECT empno, firstnme, midinit, lastname, salary '||
'FROM employee '||
'WHERE workdept = ?';
PREPARE s3 FROM stmt;
EXECUTE s3 USING deptNumber;
SET table_name = new_name;
END @
页:
[1]