最近看了些db2开发方面的资料,现做摘要,以供自己和大家参考:
1、变量声明
2、数组数据类型
CREATE TYPE numbers as INTEGER ARRAY[100];
CREATE TYPE names as VARCHAR(30) ARRAY[];
CREATE TYPE MYSCHEMA.totalcomp as DECIMAL(12,2) ARRAY[];
CREATE PROCEDURE PROC_VARRAY_test (out mynames names)
BEGIN
DECLARE v_pnumb numbers;
SET v_pnumb = ARRAY[1,2,3,5,7,11];
SET mynames(1) =’MARINA’;
…
END
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
3、赋值
1)一般方法
SET var1 = 10;
SET total = (select sum(c1) from T1);
SET var2 = POSSTR(‘MYTEST’,’TEST’);
SET v_numb(10) = 20; SET v_numb = ARRAY[1,2,3,4];
2)其他方法
VALUES INTO
SELECT (or FETCH) INTO
VALUES 2 INTO v1;
VALUES ‘TEST’ INTO var2;SELECT SUM(c1) INTO var1 FROM T1;
SELECT POSSTR(‘MYTEST’,’TEST’) INTO v1 FROM SYSIBM.SYSDUMMY1;
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
4、专用寄存器
5、游标
1)声明
DECLARE mycur1 CURSOR
FOR SELECT e.empno, e.lastname, e.job
FROM employee e, department d
WHERE e.workdept = d.deptno
AND deptname =’PLANNING’;DECLARE v_dept CHAR(3) DEAFULT ‘ ‘;
DECLARE myres_set CURSOR
FOR SELECT empno, lastname, job, salary, comm.
FROM employee
WHERE workdept = v_dept;
2)游标和结果集
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
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
6、条件语句
1)if语句
IF years_of_serv > 30 THEN SET gl_sal_increase = 15000;
ELSEIF years_of_serv > 20 THEN
SET gl_sal_increase = 12000;
ELSE
SET gl_sal_increase = 10000;
END IF;
2)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
3)迭代语句
LOOP 循环 -- 简单的循环
L1: LOOP
SQL statements;
LEAVE L1;
END LOOP L1; WHILE 循环 -- 进入前检查条件
WHILE condition
DO
SQL statements
END WHILE; REPEAT 循环 -- 退出前检查条件
REPEAT
SQL statements;
UNTIL condition
END REPEAT; FOR 循环 -- 结果集上的隐式循环
FOR loop_name AS
SELECT … FROM
DO
SQL statements;
END FOR;
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;
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
CREATE PROCEDURE DEPT_REPT (DEPTIN char(3), OUT p_counter INTEGER)
Pl: 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
CREATE PROCEDURE DEPT_REPT1 (DEPTIN char(3), OUT p_counter INT)
P1:BEGIN
DECLARE v_counter INT DEFAULT 0;
FOR dept_loop AS
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
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
- 89.
- 90.
- 91.
- 92.
- 93.
- 94.
- 95.
- 96.
- 97.
- 98.
- 99.
- 100.
- 101.
7、异常处理机制
1)DECLARE 有名称的条件
DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE ‘23503’;
DECLARE overflow CONDITION FOR SQLSTATE '22003';2)DECLARE 条件处理程序
CREATE PROCEDURE simple_error
(IN new_job CHAR(8), IN p_empno CHAR(6),
OUT p_state_out CHAR(5),OUT p_code_out INT)
SPECIFIC simple_error1
BEGIN
DECLARE SQLCODE INT DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’; DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE, SQLCODE
INTO p_sqlstate_out, p_sqlcode_out
FROM SYSIBM.SYSDUMMY1; UPDATE EMPLOYEE
SET job = new_job
WHERE empno = p_empno;
ENDCREATE PROCEDURE proc1 (IN num int, IN new_status varchar(10))
P1: BEGIN
DECLARE SQLCODE INTEGER default 0;
DECLARE SQLSTATE CHAR(5) default ‘ ‘;
DECLARE v_trunc INTEGER default 0;
DECLARE overflow CONDITION FOR SQLSTATE '22001'; DECLARE CONTINUE HANDLER FOR overflow
BEGIN
INSERT INTO tab1 VALUES (num, substr (new_sataus,1,5));
SET v_trunc = 2;
END;
INSERT INTO tab1 VALUES(num, new_status);
RETURN v_trunc;
END P1
3)强制发出异常 -- SIGNAL SQLSTATE DECLARE condition overflow for SQLSTATE ‘22001’;
…
SIGNAL overflow SET MESSAGE_TEXT = ‘Too many characters, truncated’;
CREATE PROCEDURE sign_test (IN num int, IN new_status varchar(10))
P1: BEGIN
DECLARE SQLCODE INTEGER default 0;
DECLARE SQLSTATE CHAR(5) default '';
IF length (new_status) > 5 THEN
SIGNAL SQLSTATE '72001' SET MESSAGE_TEXT = 'INPUT VALUE TOO LONG';
END IF;
INSERT INTO TAB1 VALUES (num, new_status);
END P1
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.