关于DB2存储过程的几个小实例

xiu12

================
CREATE PROCEDURE proc_with_variables (IN p_empno VARCHAR(6))
     LANGUAGE SQL
     SPECIFIC proc_with_vars                            -- applies to LUW and iSeries
     -- WLM ENVIRONMENT <env>                         -- applies to zSeries
BEGIN
     DECLARE v_empno VARCHAR(6);
     DECLARE v_total, v_count INTEGER DEFAULT 0;
     SELECT empno INTO v_empno FROM employee WHERE empno = p_empno ;
END
================

CREATE PROCEDURE set_variables ()
     LANGUAGE SQL
     SPECIFIC set_variables                             -- applies to LUW and iSeries
     -- WLM ENVIRONMENT <env>                            -- applies to zSeries


BEGIN
     DECLARE v_rcount INTEGER;
     DECLARE v_max DECIMAL(9,2);
     DECLARE v_adate,v_another DATE;
     DECLARE v_total INTEGER DEFAULT 0;                     -- (1)
     SET v_total = v_total + 1;                                -- (2)
     SELECT MAX(salary) INTO v_max FROM employee;        -- (3)
     VALUES CURRENT DATE INTO v_adate;                     -- (4)
     SELECT CURRENT DATE, CURRENT DATE
        INTO v_adate, v_another
        FROM SYSIBM.SYSDUMMY1;                                 -- (5)
END



CREATE PROCEDURE registersample ( OUT p_start TIMESTAMP
                                         , OUT p_end    TIMESTAMP
                                         , OUT p_c1     TIMESTAMP
                                         , OUT p_c2     TIME
                                         , OUT p_user CHAR(20))
     LANGUAGE SQL
     SPECIFIC registersample                             -- applies to LUW and iSeries
-- WLM ENVIRONMENT <env>                                 -- applies to zSeries
BEGIN
     CREATE TABLE datetab (c1 TIMESTAMP,c2 TIME,c3 DATE);


     VALUES CURRENT TIMESTAMP INTO p_start;                    -- (1)
     INSERT INTO datetab VALUES( CURRENT TIMESTAMP
                                         , CURRENT TIME
                                         , CURRENT DATE + 3 DAYS); -- (2)
     SELECT c1,c2 INTO p_c1,p_c2 FROM datetab;
     VALUES CURRENT TIMESTAMP INTO p_end;
     SET p_user = USER;                                             -- (3)
     DROP TABLE datetab;
END



添加新员工的一段存储过程:
=================
CREATE PROCEDURE add_new_employee ( IN p_empno     VARCHAR(6)     -- (1)
                                             , IN p_firstnme CHAR(12)
                                             , IN p_midinit    CHAR(1)
                                             , IN p_lastname VARCHAR(15)
                                             , IN p_deptname VARCHAR(30)
                                             , IN p_edlevel    SMALLINT
                                             , OUT p_status     VARCHAR(100)
                                             , OUT p_ts         TIMESTAMP)
     LANGUAGE SQL
     SPECIFIC add_new_employee                 -- applies to LUW and iSeries
     -- WLM ENVIRONMENT <env>                 -- applies to zSeries


BEGIN
     DECLARE v_deptno CHAR(3) DEFAULT '    ';                             -- (2)
     DECLARE v_create_ts TIMESTAMP;                                         -- (3)
     SET v_create_ts = CURRENT TIMESTAMP;
     /* Get the corresponding department number */
     SELECT deptno
        INTO v_deptno                                                             -- (4)
        FROM department
     WHERE deptname = p_deptname;


     /* Insert new employee into table */                                 -- (5)
     INSERT INTO employee ( empno
                                 , firstnme
                                 , midinit
                                 , lastname
                                 , workdept
                                 , hiredate
                                 , edlevel)
     VALUES ( p_empno
                , p_firstnme
                , p_midinit
                , p_lastname
                , v_deptno
                , DATE(v_create_ts)
                , p_edlevel );

     SET p_status = 'Employee added';                                     -- (6)
     SET p_ts = v_create_ts;                                                 -- (7)
END
=================



一个小例子：

CREATE PROCEDURE p2( IN TRY_SQLCODE VARCHAR(10))
DYNAMIC RESULT SETS 1
LANGUAGE SQL

BEGIN
DECLARE SQLCODE    INT;
         DECLARE ERR5        VARCHAR(40) default '初始值';
         DECLARE ERR3        VARCHAR(40);
         DECLARE ERR2        VARCHAR(40);
         DECLARE ERR1        VARCHAR(40);
         DECLARE ERRID     VARCHAR(40);

         DECLARE CUR_SQLCODE CURSOR WITH RETURN TO CLIENT FOR SELECT * FROM RI;

         DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
                         SET ERR5 = char(SQLCODE);
         SELECT ID
             INTO ERRID
             FROM RINGS
            WHERE ID=TRY_SQLCODE; --创造各种sqlcode条件的参数

         IF SQLCODE = 100 THEN
             SET ERR1='NOT FOUND';
                     INSERT INTO RINGS VALUES('1',ERR1);
         ELSEIF SQLCODE < 0 THEN
             SET ERR3 = 'EXCEPTION';
                     INSERT INTO RINGS VALUES('3',ERR3);
         END IF;

         INSERT INTO RINGS VALUES('100',ERR5);
         COMMIT;
         OPEN CUR_SQLCODE;
END

1.       JAVA 调用db2存储过程最简单的例子：

存储过程创建代码：


sql 代码

          SET SCHEMA IES      ;   
      
    Create procedure ies.test()   
      
    LANGUAGE SQL   
      
    Update t_ryxx set xm =’xy’ where ryxxid=’xm’   

java 代码

    conn = DbMaster.getConn();      
    System.out.println("begin………");      
    proc = conn.prepareCall("{call test()}");      
    proc.execute();   



2. Java调用db2带输入参数存储过程的例子：

Db2创建存储过程的代码：
sql 代码

    Drop procedure ies.test();   
    SET SCHEMA IES      ;   
    Create procedure ies.test(in i_ryxxid varchar(50))   
    LANGUAGE SQL   
    Update t_ryxx set xm =’xy’ where ryxxid=i_ryxxid   
      

java 代码

    conn = DbMaster.getConn();   
    System.out.println("begin");   
    proc = conn.prepareCall("{call test(?)}");   
    proc.setString(1,"RY0003");   
    proc.execute();   
    System.out.println("end:");   



3.有输入输出参数的代码：

创建存储过程的代码：
sql 代码

    SET SCHEMA IES      ;   
    CREATE PROCEDURE IES.test (IN in_ryxxid varchar(50),out out_xm varchar(50))   
       LANGUAGE SQL   
      
    select xm into out_xm from ies.t_ryxx where ryxxid=in_ryxxid;   


java 代码

    proc = conn.prepareCall("{ call test(?,?)}");   
    proc.setString(1, "011900380103");   
    proc.registerOutParameter(2, Types.VARCHAR);   
    proc.execute();   
    String xm = proc.getString(2);   
    System.out.println("end:"+xm);  



4.带有游标的存储过程（不知道这里的游标什么时候close的。刚开始学，不懂 菜鸟真郁闷）

创建存储过程的代码：（这个存储过程的具体看上面一篇文章：DB2 存储过程开发最佳实践（转载）的最佳实践 3：正确设定游标的返回类型。http://acme1921209.javaeye.com/blog/97829）
sql 代码

    SET SCHEMA IES      ;   
    CREATE PROCEDURE IES.test (IN in_state varchar(50))   
    result set 1   
    language sql   
    P1:BEGIN  
    DECLARE CUR cursor with return for select rybh,xm from ies.t_ryxx where ryzt=in_state with ur;   
      
    OPEN CUR;   
      
      
    END P1;     


java 代码

    proc = conn.prepareCall("{ call test(?)}");   
    proc.setString(1, "停用");   
    proc.execute();   
    rst = proc.getResultSet();   
    while(rst.next()){   
         System.err.println(rst.getString(1)+"     "+rst.getString(2));   
         }  





====返回多个结果集的处理方法：

db2 8.2 存储过程创建代码：
sql 代码

    create procedure getpeople()   
    dynamic result sets 2   
    READS SQL DATA   
    LANGUAGE SQL   
    BEGIN  
         DECLARE rs1 CURSOR WITH RETURN TO CLIENT FOR  
                 SELECT RYBH,XM FROM IES.T_RYXX WHERE RYZT='停用' with ur;   
        DECLARE rs2 CURSOR WITH RETURN TO CALLER FOR   
                SELECT RYBH ,XM FROM IES.T_RYXX WHERE RYZT='正常' with ur;           
        OPEN rs1;   
        OPEN rs2;   
    END;               


java 代码

    proc = conn.prepareCall("{ call getpeople()}");   
    proc.execute();   
    rst = proc.getResultSet();   
    int i = 2 ,j = 0;   
    while(rst.next()&&j//j的作用：记录太多了，只想打印几个测试下   
          System.out.println(rst.getString(1)+"     "+rst.getString(2));   
           j++;   
    }   
    System.err.println("---------------------------------------------");   
    if (proc.getMoreResults()){  //getMoreResults()具体看api文档   
         j = 0;   
        while(rst.next()&&j//j的作用：记录太多了，只想打印几个测试下   
         System.out.println(rst.getString(1)+"     "+rst.getString(2));   
         j++;   
             }   
         }   
    j = 0;