-- 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 @
分享到:
相关推荐
DB2 9.5 SQL Procedure Developer exam 735 prep, Part 1 SQL Procedure Language
DB2 SQL 消息,非常方便管理员与操作者查找以便解决错误.比如SQL0440N 找不到具有兼容自变量的类型为 例程类型 的名为 例程名 的已授权例程。
刚刚从官网搞来的,新鲜出炉,分享下 英文版的格式比较好,因为是直接从官网打印的。 中文版的只能按页面打印所以格式不是太好,各位凑合吧,或者谁有格式好的给大家分享下。
介绍SQL PL开发,这类书比较少,希望有所帮助,说实话,DB2的Procedure开发语言比较晦涩,相比Oracle要差一些。Developer Center及其难用,V9的DWB也不怎么地。
基础知识体系 ...• SQL Procedure环境配置 • SQL Procedure基础原理 • SQL Procedure流程控制 • SQL Procedure游标 • SQL Procedure异常处理 • SQL Procedure动态SQL • 开发工具-DB2开发中心 • 结束
1、procedure-name: 存储过程的名字,在同一个数据库的同一模式下,不能存在存储过程名相同参数数目相同的存储过程,即使参数的类型不同也不行。 2、(IN | OUT | INOUT parameter-name data-type,...) :传入参数 ...
您学习了SQL Procedure Language 的所有基本要素,包括变量声明和赋值、语法和使用以及用于控制过程逻辑的流程的条件语句和迭代语句。您还学习了如何使用错误处理和结果集。这使您能够构建可集成到数据库应用程序中...
stored procedure support (with mutiple result sets for IBM DB2, Microsoft and Sybase SQL Server) bi-directional and unidirectional result sets cached updates record filtering support of data-aware...
stored procedure support (with mutiple result sets for IBM DB2, Microsoft and Sybase SQL Server) bi-directional and unidirectional result sets cached updates record filtering support of data-aware ...
对日项目的开发工具,可视化sql工具,支持oracle,mysql,db2等数据库,国内项目也可以使用哦。
掌握DB2必备第二部!想学DB2赶快下,超值!!!
b.2 使用db2 b.3 使用macromedia coldfusion b.4 使用microsoft access b.5 使用microsoft asp b.6 使用microsoft asp.net b.7 使用microsoft query b.8 使用microsoft sql server...
CSqlParameter Represents a parameter or argument associated with a SQL statement or stored procedure CSqlField Represents a column of data in a recordset CSqlLocator Manipulates a locator to access ...
DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 7、说明:添加主键: Alter table tabname add primary key(col) 说明:删除主键: Alter table tabname drop primary key(col) 8、说明...
And also the creating process of stored procedure has no difference with that of function What’s more, the process of creating user function is also applicable to other DBMS, in which comma is the ...
精通DB2必看,精彩内容,不容错过,你想作一个db2开发高手吗?一定要看看这个哦!!!超赞!!!!
DB2入门必备参考资料
C.4 CREATE PROCEDURE... 168 C.5 CREATE TABLE... 168 C.6 CREATE VIEW... 168 C.7 DELETE... 169 C.8 DROP... 169 C.9 INSERT... 169 C.10 INSERT SELECT... 169 C.11 ROLLBACK... 169 C.12 SELECT... 170 C.13 ...
C.4 CREATE PROCEDURE 168 C.5 CREATE TABLE 168 C.6 CREATE VIEW 168 C.7 DELETE 169 C.8 DROP 169 C.9 INSERT 169 C.10 INSERT SELECT 169 C.11 ROLLBACK 169 C.12 SELECT 170 C.13 UPDATE 170 附录D ...
Source参数可以是一个Command对象名称、一段SQL命令、一个指定的数据表名称或是一个Stored Procedure。假如省略这个参数,系统则采用Recordset对象的Source属性。 ActiveConnection Recordset对象可以通过...