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

sdchy

关于DB2存储过程的几个小实例
2010-08-27 15:55

http://blog.myspace.cn/e/402393233.htm ============================================================================== 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; 　　==============================================================================