DB2存储过程

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;    

==============================================================================    

db2 存储过程常用语句格式  

http://hi.baidu.com/heiru/blog/item/fb4132adb07e7e074a36d631.html  

----定义      

    DECLARE CC VARCHAR(4000);  

    DECLARE SQLSTR VARCHAR(4000);  

    DECLARE st STATEMENT;      

    DECLARE CUR CURSOR WITH RETURN TO CLIENT FOR CC;  

----执行动态SQL不返回  

    PREPARE st FROM SQLSTR;  

    EXECUTE st;  

----执行动态SQL返回  

    PREPARE CC FROM SQLSTR;  

    OPEN CUR;  

----判断是否为空，使用值替代  

    COALESCE(判断对象，替代值)  

----定义临时表  

DECLARE GLOBAL TEMPORARY TABLE SESSION.TempResultTable  

(  

   Organization int,  

   OrganizationName varchar(100),   

   AnimalTypeName varchar(20),     

   ProcessType int,  

   OperatorName varchar(100),      

   OperateCount int  

)   

WITH REPLACE       -- 如果存在此临时表，则替换                              

NOT LOGGED;  

----字符串函数  

    Substr  

----隐形游标迭代  

    for 游标名 as select....... do   

       使用 游标名.字段名  

       内容区块  

    end for;  

----直接返回值或变量  

    declare rs1 cursor with return to caller for select 0 from sysibm.sysdummy1;  

----判断表是否存在  

    select count(*) into @exists from syscat.tables where tabschema = current schema and tabname='ZY_PROCESSLOG';  

----取前面N条记录  

    FETCH FIRST N ROWS ONLY   

----定义返回值  

   declare rs0 cursor with return to caller for select 0 from sysibm.sysdummy1;  

   declare rs1 cursor with return to caller for select 1 from sysibm.sysdummy1;  

----得到插入的自增长列最大值  

    VALUES IDENTITY_VAL_LOCAL() INTO 变量  

==============================================================================    

DB2中执行动态SQL的例子  

http://www.cnblogs.com/kfarvid/archive/2009/11/03/1595064.html  

CREATE PROCEDURE REFERESH_ZHAOGW   

(  

IN ODD_TABLE_NAME VARCHAR(100),   

IN ODS_TABLE_NAME VARCHAR(100)  

)  

LANGUAGE SQL  

BEGIN  

DECLARE SSQL VARCHAR(1000) ;  

SET SSQL='CREATE TABLE '||ODS_TABLE_NAME||' AS SELECT * FROM '||ODD_TABLE_NAME ;  

PREPARE S1 FROM SSQL;  

EXECUTE S1;  

END; 

=========================get max value from a col in a table

CREATE PROCEDURE identity_max (IN colname VARCHAR(50), IN tabschema VARCHAR(50), IN tabname VARCHAR(50), OUT maxvalue int)

LANGUAGE SQL

BEGIN atomic

    DECLARE v_sql varchar(300);

    DECLARE v_stmt statement;

    declare c2 cursor for v_stmt;

    set v_sql = 'select max('||colname||') from '||tabschema||'.'||tabname;

    PREPARE v_stmt FROM v_sql ;  

    open c2;

    fetch c2 into maxvalue;

    close c2;

END @

-------------------

db2 -td@ -f pro_max.ddl

db2 "call identity_max('a','TEST','TEST_PRO',?)"

  Value of output parameters

  --------------------------

  Parameter Name  : MAXVALUE

  Parameter Value : 3

  Return Status = 0