数据库

DB2存储过程语法

2017-12-12  本文已影响2295人  RLM233

1. DB2存储过程示例：

CREATE 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 @--存储过程结束

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;
    ELSEIF 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

SET total = 100;
VALUES(100,200,200+1)INTO var1,var2,var3; --并行赋值，效率高
SET total = NULL;
SET total = (select sum(c1) from T1);
SET sch = CURRENT SCHEMA;

2. 游标

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 c1 CURSOR FOR select * from staff;
--（DECLARE关键字，cl游标名称，CURSOR是必须有的，指通过c1的游标来操作staff里所有的数据）最常用的最普通的。
2.DECLARE c1 CURSOR WITH HOLD FOR select * form staff;
3.DECLARE c1 CURSOR WITH RETURN TO CALLER FOR select * form  staff;
4.DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR select * form staff;

--返回一个结果集的游标的声明 
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

3. 条件判断

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; 

--使用 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

4. 循环

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

5. 临时表空间

http://www.51cto.com/specbook/49/3606.htm

create user temporary tablespace usertemp1 
    managed by system using ('usertemp1')

/*
NOT LOGGED：不记录事务日志
*/
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：一直到会话结束
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;

此语句创建一个名为 gbl_temp 的用户临时表。定义此用户临时表所使用的列的名称和说明与 empltabl 的列的名称和说明完全相同。隐式定义只包括列名、数据类型、可为空特性和列缺省值属性。未定义所有其他列属性，包括唯一约束、外部关键字约束、触发器和索引。执行 COMMIT 操作时，若未对该表打开 WITH HOLD 游标，则该表中的所有数据都被删除。不记录对用户临时表所作的更改。用户临时表被放在指定的用户临时表空间中。此表空间必须存在，否则此表的声明将失败。

CONNECT TO SAMPLE@
-- Declare a temporary table from CLP 
--创建表空间
DECLARE GLOBAL TEMPORARY TABLE temp_employee LIKE employee ON COMMIT PRESERVE ROWS@

-- create a procedure which references the temporary table. 
CREATE PROCEDURE p1 
RESULT SETS 1

BEGIN
    --声明游标
    DECLARE cur CURSOR WITH HOLD WITH RETURN TO CLIENT FOR 
        SELECT empno, firstnme, lastname FROM SESSION.temp_employee FETCH FIRST 5 ROWS ONLY;

    OPEN cur;
END
@

-- Now, from the CLP (an application)
-- INSERT into the temp table
INSERT INTO SESSION.temp_employee SELECT * FROM EMPLOYEE@

-- CALL the procedure, which returns an open cursor back to the 
-- CLP, proving that the temp table data was received.
CALL p1@

在上面的例子中，创建 CLP 连接后声明了一个临时表。然后创建了引用临时表的过程 p1。这里要注意的关键是，即使在过程体中没有声明临时表，该过程也被成功创建。

6. 异常处理

DECLARE CONTINUE HANDLER FOR not_found, SQLEXCEPTION
    SET at_end = 1;
DECLARE CONTINUE HANDLER FOR trunc BEGIN
    SET truncated = 1;
    SET msg=’message’;
  END;

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;
上一篇下一篇

猜你喜欢

热点阅读