DB2存储过程
------------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.sql
2.单表备份
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%:保存的地址