DB2开发系列之一——基本语法

John Aiz

于 2024-07-24 03:52:33 发布

阅读量12

点赞数

最近看了些db2开发方面的资料，现做摘要，以供自己和大家参考：

1、变量声明

DECLARE v_salary DEC(9,2) DEFAULT 0.0; 
DECLARE v_status char(3) DEFAULT ‘YES’; 
DECLARE v_descrition VARCHAR(80); 
DECLARE v1, v2 INT DEFAULT 0;

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

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;

4、专用寄存器

1）常用寄存器
CURRENT DATE 
CURRENT TIME 
CURRENT TIMESTAMP 
CURRENT USER 
CURRENT PATH
2）示例
CREATE PROCEDURE get_datetime (out cdate date, out ctime time )
 P1: BEGIN
  VALUES CURRENT DATE INTO cdate;
  VALUES CURRENT TIME INTO ctime;
 END P1 
SET CURRENT_SCHEMA = MYSCHEMA

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

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

原创作者: lhdzbj 转载于: https://blog.51cto.com/lhdzbj/11542253

John Aiz

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
DB2开发系列之一——基本语法

最近看了些db2开发方面的资料，现做摘要，以供自己和大家参考：1、变量声明登录后复制 DECLARE v_salary DEC(9,2) DEFAULT 0.0; DECLARE v_status char(3) DEFAULT ‘YES’; DECLARE v_descrition VARCHAR(80); DE...
复制链接

扫一扫