------------DB2版本------------
CREATE OR REPLACE PROCEDURE procedure_name(IN | OUT | INOUT parameter_name data-type,...) )--存储过程可以设定输入参数和输出参数LANGUAGE SQL--DB2可以用多种语言编写存储过程，这里用的是纯SQL BEGIN--开始DECLARE vID smallint;--定义变量，和Oracle一样 DECLARE变量名 变量的数据类型;FOR V AS SELECT BRND_CD FROM TMP_BRND_CD--for循环 tmp_brnd_cd预先创建好DO--循环体开始SET vID=BRND_CD;--对vID赋值，db2可以用set赋值，也可以用values赋值，这里可以写成values(BRND_CD) into vID INSERT INTO WWM_FORINSERT_TEST VALUES(vID);--往wwm_forinsert_test 插入数据END FOR;--循环体结束END @--存储过程结束 --循环 •LOOP 循环，简单的循环 CREATE PROCEDURE LEAVE_LOOP (DEPTIN char(3), OUT p_counter INTEGER)Ll: BEGIN DECLARE v_at_end , v_counter INTEGER DEFAULT 0; DECLARE v_lastname VARCHAR(15); DECLARE v_birthd, v_hired DATE;DECLARE c1 CURSOR
FOR SELECT lastname, hiredate, birthdate FROM employee--定义游标： WHERE WORKDEPT = deptin;DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1;
OPEN c1;--打开游标
FETCH_LOOP: LOOP FETCH c1 INTO v_lastname, v_hired, v_birthd; IF v_at_end <> 0 THEN -- loop until last row of the cursor LEAVE FETCH_LOOP; --跳出loop循环？？ END IF; SET v_counter = v_counter + 1; INSERT INTO REPORT_INFO_DEPT values(v_lastname, v_hired, v_birthd); END LOOP FETCH_LOOP; SET p_counter = v_counter; END Ll •WHILE 循环，进去循环前检查条件是否满足 CREATE PROCEDURE DEPT_REPT (DEPTIN char(3), OUT p_counter INTEGER) P1: BEGIN DECLARE v_at_end , v_counter INTEGER DEFAULT 0; DECLARE v_lastname VARCHAR(15); DECLARE v_birthd, v_hired DATE;DECLARE c1 CURSOR
FOR SELECT lastname, hiredate, birthdate FROM employee WHERE WORKDEPT = deptin;DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1;
OPEN c1;
FETCH c1 INTO v_lastname, v_hired, v_birthd; WHILE (v_at_end = 0) DO INSERT INTO REPORT_INFO_DEPT values(v_lastname, v_hired, v_birthd); SET v_counter = v_counter + 1; FETCH c1 INTO v_lastname, v_hired, v_birthd; --？？ END WHILE; SET p_counter = v_counter; END P1 •REPEAT 循环，退出前检查条件 •FOR 循环 CREATE PROCEDURE DEPT_REPT1 (DEPTIN char(3), OUT p_counter INT)P1:BEGIN DECLARE v_counter INT DEFAULT 0; FOR dept_loop AS --此处给 FOR 起了名字 SELECT lastname, hiredate, birthdate FROM employee WHERE WORKDEPT = deptin DO INSERT INTO REPORT_INFO_DEPT values(dept_loop.lastname,dept_loop.hiredate,dept_loop.birthdate); SET v_counter = v_counter + 1; END FOR; SET p_counter = v_counter; END P1--条件判断：if语句
CREATE PROCEDURE UPDATE_SAL (IN empNum CHAR(6),INOUT rating SMALLINT) LANGUAGE SQL BEGIN IF rating = 1 THEN UPDATE employee SET salary = salary * 1.10, bonus = 1000 WHERE empno = empNum; ELSIF rating = 2 THEN UPDATE employee SET salary = salary * 1.05, bonus = 500 WHERE empno = empNum; ELSE UPDATE employee SET salary = salary * 1.03, bonus = 0 WHERE empno = empNum; END IF; END •CASE 语句：支持两种类型的 CASE 语句，以根据一个条件的状态实现逻辑的分支 ◦simple CASE 语句用于根据一个字面值进入某个逻辑 ◦searched CASE 语句用于根据一个表达式的值进入某个逻辑--使用 searched CASE 语句的存储过程CREATE PROCEDURE sal_increase_lim1 (empid CHAR(6))BEGIN DECLARE years_of_serv INT DEFAULT 0; DECLARE v_incr_rate DEC(9,2) DEFAULT 0.0; SELECT YEAR(CURRENT DATE) - YEAR(hiredate) INTO years_of_serv FROM empl1 WHERE empno = empid; CASE WHEN years_of_serv > 30 THEN SET v_incr_rate = 0.08; WHEN years_of_serv > 20 THEN SET v_incr_rate = 0.07; WHEN years_of_serv > 10 THEN SET v_incr_rate = 0.05; ELSE SET v_incr_rate = 0.04; END CASE; UPDATE empl1 SET salary = salary+salary*v_incr_rate WHERE empno = empid;END-----------游标--•定义游标：DECLARE 游标名 CURSOR FOR SELECT 语句
在 SQL 过程中，游标除了迭代结果集中的行，还可以将结果集返回给调用程序或其他过程
•WITHOUT RETURN/WITH return：选项指定游标的结果表是否用于作为从一个过程中返回的结果集。•WITH RETURN TO CALLER：选项指定将来自游标的结果集返回给调用者，后者可以是另一个过程或一个客户机应用程序。这是默认选项。•WITH RETURN TO CLIENT：选项指定将来自游标的结果集返回给客户机应用程序，绕过任何中间的嵌套过程。--下面是游标声明的几个例子：
1.DECLARE c1 CURSOR FOR select * from staff; --（DECLARE关键字，cl游标名称，CURSOR是必须有的，指通过c1的游标来操作staff里所有的数据）最常用的最普通的。 2.DECLARE c1 CURSOR WITH HOLD FOR select * form staff; --如果不加with hold则此游标遇到commit或者rollback语句会自动关闭。加上了with hold 则必须用close关闭。3.DECLARE c1 CURSOR WITH RETURN TO CALLER FOR select * form staff; 4.DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR select * form staff;若要从一个过程中返回结果集，需要： •创建一个过程，创建时指定 DYNAMIC RESULT SETS 子句中声明存储过程将要返回结果集的数量。 •声明游标，声明时指定 WITH RETURN 子句。 •打开该游标，并使之保持 open 状态，如果关闭该游标，则结果集将不能返回给调用者应用程序。 --返回一个结果集的游标的声明 CREATE PROCEDURE emp_from_dept() DYNAMIC RESULT SETS 1 --返回的结果集超出定义的数量会返回一个警告 P1: BEGIN DECLARE c_emp_dept CURSOR WITH RETURN FOR SELECT empno, lastname, job, salary, comm. FROM employee WHERE workdept = ‘E21’; OPEN c_emp_dept; END P1 ----------临时表空间•要使用临时表，数据库中必须存在一个用户临时表空间（默认情况下没有），创建表空间：
create user temporary tablespace usertemp1 managed by system using ('usertemp1') •可使用 DECLARE GLOBAL TEMPORARY TABLE 语句来定义临时表。DB2的临时表是基于会话的，且在会话之间是隔离的。 当会话结束时，临时表的数据被删除，临时表被隐式卸下。对临时表的定义不会在 SYSCAT.TABLES 中出现。--NOT LOGGED：不记录事务日志
动态表复制（CREATE TABLE AS&CREATE TABLE LIKE）--CREATE TABLE AS：所有约束、注释和序列都没有被拷贝，但数据成功拷贝了--CREATE TABLE LIKE：拷贝了所有NOT-NULL约束，并且没有拷贝表数据DECLARE GLOBAL TEMPORARY TABLE gbl_temp
LIKE empltabl --没有拷贝数据 ON COMMIT DELETE ROWS NOT LOGGED IN usr_tbsp--------定义临时表
/*方法1：ON COMMIT DELETE ROWS：在提交的时候清空数据，默认属性*/ DECLARE GLOBAL TEMPORARY TABLE 表名 ( --参数列表 NAME VARCHAR(10),---姓名 DEPT SMALLINT,---部门 SALARY DEC(7,2)---工资 ) ON COMMIT DELETE ROWS; /* 方法2：1. ON COMMIT PRESERVE ROWS：在执行commit操作时，保留表中的所有行，一直到会话结束2. LIKE 引用表名 INCLUDING COLUMN DEFAULTS WITH REPLACE：字段和类型引用其他表*/ DECLARE GLOBAL TEMPORARY TABLE 表名 LIKE 引用表名 INCLUDING COLUMN DEFAULTS WITH REPLACE ON COMMIT PRESERVE ROWS; /*方法3：DEFINITION ONLY WITH REPLACE：程序结束后自动删除该临时表*/DECLARE GLOBAL TEMPORARY TABLE 表名 AS ( SELECT * FROM staff WHERE <condition> ) DEFINITION ONLY WITH REPLACE; -----------异常处理begin（程序开始）前声明异常 DECLARE handler-type HANDLER FOR SQLEXCEPTION\SQLWARNING\NOT FOUND ，
SQLEXCEPTION\SQLWARNING\NOT FOUND 为异常类型，handler-type处理类型有以下几种： •UNDO 在处理器操作执行之前，DB2会回滚存储过程中执行的SQL操作。在处理器操作完成之后，存储过程会终止，并将控制返回给调用者 •EXIT 在处理器操作完成之后，存储过程会终止，并将控制返回给调用者 •CONTINUE 在处理器操作完成之后，会继续执行产生这个异常语句之后的下一条语句，•需要注意的问题：
◦DB2存储过程会在SQL执行完成后，将返回信息赋值给SQLCODE和SQLSTATE。这两个DB2内置变量在使用前需要定义。 ◦定义UNDO类型的异常处理，在定义后面可以跟上操作语句。 ◦UNDO类型的异常处理，必须是处理原子操作的SQL块，DB2默认不是原子操作的。在BEGIN跟上ATOMIC即可。CREATE OR REPLACE PROCEDURE TEST ( IN num INTEGER, OUT P_ERRORCODE VARCHAR(256), --SQL返回码 OUT P_ERRORDESC VARCHAR(1024)) -- SQL返回信息 BEGIN ATOMIC DECLARE SQLCODE INTEGER DEFAULT 0; DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; -- 定义一个UNDO类型的异常处理 DECLARE UNDO HANDLER FOR SQLEXCEPTION set P_ERRORCODE = to_char(sqlcode), P_ERRORDESC = '处理失败'; update userinfo t set t.usercode = '000000000003' where t.usercode='000000000002'; update userinfo t set t.usercode = '00000000000100' where t.usercode='000000000001'; --这里会因usercode字段超长出现异常 set P_ERRORCODE = to_char(abs(SQLCODE)); set P_ERRORDESC = '处理成功'; END; SELECT RTRIM( csz ) INTO v_sys_status--sd:复制表：要求目标表不存在，因为在插入时会自动创建。 FROM pas.xtb_xtcs WHERE UPPER( csmc )= 'SYS_STATUS';FETCH cur_jkwj INTO v_tablename,--sd:把数据放到变量里 v_filename, v_extname; --------------导入数据 load from 'd:\CSB_KM.del' of del replace into CSB_KM; load from 'v_fileallname' of del insert into v_tablename nonrecoverable； DEL:/*装载数据文件的类型，大家用到比较多的应该是DEL，一般有4种，IXF,DEL,ASC,CURSOR ASC 表示不分界的 ASCII 数据，数据的划分由位置决定。DEL 表示分界的 ASCII 数据，每行的数据长度可变。 分界的数据可以使用多种修饰符，主要的两种是 COLDEL 和 CHARDEL； COLDEL 决定列和列之间如何分界，CHARDEL 决定字符串数据如何分界*/ insert：/*DB2共有4个选项，其中INSERT为插入模式，相当于append（追加模式）; 而REPLACE 为替换模式,可以理解为clear table then insert; TERMINATE/RESTART,指定当 DB2 LOAD 由于任何原因未完成时，是停掉，还是重新load。*/ NONRECOVERABLE / COPY YES --以nonrecoverable模式还是以copy yes模式来load数据，一般来讲用NONRECOVERABLE(也可以不写) runstats on table pas.jxdx_ckzh and index all;--sd：优化器：创建index后以及表有很大的变化后，最好执行该语句 ---当文件名以.ixf结束时，使用import，其他的用load。。 import from 'd:\CSB_KM.ixf' of ixf modified by compound=100 commitcount 10000 insert into ,,; SELECT * FROM SYSCAT.COLUMNS WHERE COLNAME='HYDH';--可以查看哪些表中用到了‘HYDH’字段--DB2多行转一行【XML方式】--分组然后合并，然后去除XML标签SELECT replace(replace(replace(xml2clob(xmlagg(xmlelement(name A, 【字段】))),'</A><A>', '【分隔符】'),'</A>',''),'<A>','') from 【表】group by 【分组字段】文件备份：
1.表结构备份db2look -d 数据库名 -i 用户名 -w 密码 -e -c -o f:/bjg/ycpas_tjrq.sql2.单表备份db2 "export to f:khdx_hy_tjrq.dat of del select * from khdx_hy"3.全库备份db2 force application all--杀死进程（应该多执行几次）db2 backup db %DATA_BASE_NAME% to %BAKUP_TMP% PARALLELISM 2 COMPRESS--%DATA_BASE_NAME%:数据库名 %BAKUP_TMP%：保存的地址