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 zSeriesBEGIN 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) ENDCREATE 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 zSeriesBEGIN 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 SQLBEGIN 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; END1. 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.htmlCREATE PROCEDURE REFERESH_ZHAOGW (IN ODD_TABLE_NAME VARCHAR(100), IN ODS_TABLE_NAME VARCHAR(100))LANGUAGE SQLBEGINDECLARE SSQL VARCHAR(1000) ;SET SSQL='CREATE TABLE '||ODS_TABLE_NAME||' AS SELECT * FROM '||ODD_TABLE_NAME ;PREPARE S1 FROM SSQL;EXECUTE S1;END;==============================================================================
