- 浏览: 1475031 次
- 性别:
- 来自: 深圳
文章分类
- 全部博客 (523)
- JAVA (334)
- J2EE (18)
- JSP (21)
- JavaScript (14)
- it life (2)
- mobile develop (4)
- UBUNTU (14)
- Algorithm (14)
- DataBase (56)
- Browser/Server (1)
- linux (6)
- fedora (2)
- CSS (1)
- AjAX (3)
- HTML5 (1)
- EJB (1)
- osworkflow (2)
- Java face (5)
- spring (4)
- ICE (1)
- webService (2)
- MongoDB (1)
- JavaThread (4)
- JavaCollections (3)
- Hibernate (2)
- JavaMail (1)
- JavaBasic (1)
- Cache (1)
- https (4)
- DOM4J (1)
- JDOM (0)
- Mybatis (1)
- JUNIT (1)
- eclipse (1)
- easyMock (2)
最新评论
-
yadongliang:
...
自己认为的一些技术点 -
yadongliang:
每次看你头像都看的荷尔蒙分泌失调
WebService的两种方式SOAP和REST比较 (转) -
yadongliang:
hollo 写道一直有这种感觉,盲目的跟风,确实有一些人为了潮 ...
WebService的两种方式SOAP和REST比较 (转) -
welss:
博主,JNative怎么调用dll中的这种方法: int ...
JNative调用DLL -
Java_Antelope:
session.setAttribute和session.getAttribute(
================
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;
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;
发表评论
-
sql 统计类语句
2014-03-17 16:59 11131、统计每月的成交量,按照逆序排序(2013年以来的每月销量, ... -
Jdbc 操作Oracle CLOB字段
2013-01-08 22:10 4441//这个适用于oracle10,11,以前oracle9操作C ... -
SQL重复记录处理(查找,过滤,删除)
2012-12-30 23:43 4788HZT表结构 ID int Title nvarchar( ... -
sql 如何过滤重复记录
2012-12-29 21:37 2403请各位大侠帮忙了 问题1:对于以下几个记录 ID 123456 ... -
Mysql 触发器和存储过程
2012-09-17 12:34 9631、首先建表: create table tababin( i ... -
Sql ACID
2012-09-12 17:57 844http://nathanchen.github.com/Re ... -
Oracle 表分区学习
2012-09-12 17:52 790http://love-flying-snow.iteye.c ... -
Oracle表分区
2012-09-12 17:19 1331废话少说,直接讲分区语法。 Oracle表分区分为四种:范围分 ... -
MYSQL中 ENUM 类型的详细解释
2012-03-12 23:30 2374ENUM 类型 ENUM 是一个字 ... -
Mysql 字段数据类型/长度及表类型详细说明
2012-03-12 23:20 14956一、MySQL 字段数据类型/长度 1、数值类型 列类型 ... -
MySQL中的datetime与timestamp比较
2012-03-04 19:30 1178相同 显示 TIMESTAMP列的显示格式与DATETI ... -
mysql 存储过程例子
2011-08-21 01:35 3949mysql 存储过程例子(当前我使用的是mysql 5.5) ... -
Oracle Cursor介绍
2011-08-21 00:54 995一 概念 游标是SQL的一个内存工作区,由系统或用户以变量的 ... -
oracle 游标
2011-08-20 14:09 10341、隐式游标 实例1、用sql%found 相当于sql.fo ... -
oracle 游标遍历%rowtype中的记录
2011-08-20 01:49 7442那么我们使用Oracle游标 游标分为:静态游标和引用游标( ... -
oracle concat
2011-08-19 23:26 1159oracle 连接字符串: 连接两个字符串 sel ... -
oracle 存储过程
2011-08-19 21:55 956oracle的for循环,commit放在不同的位置,一个是每 ... -
sql 删除重复的记录
2011-08-19 16:32 928通过建立临时表删除: SQL>create ta ... -
Oracle 经典
2011-08-17 17:05 11981.删除重复项, 只保留第一条 delete table t ... -
DB2 分页
2011-07-25 01:59 1492//db.jsp <%@ page langua ...
相关推荐
db2 存储过程 db2 存储过程 db2 存储过程
DB2存储过程的入门实例文档,介绍DB2存储过程的基本知识
db2存储过程db2存储过程db2存储过程db2存储过程db2存储过程db2存储过程db2存储过程db2存储过程db2存储过程
随教程写的一些简单存储过程,对于初学者很有好处
DB2 存储过程 教程DB2 存储过程 教程DB2 存储过程 教程
DB2数据库存储过程小结,详细解释DB2存储过程。
DB2存储过程语法大全 存储过程及其类型 动态SQL语句
DB2 存储过程 SQL DB2 SQL存储过程基础 SQL
DB2存储过程介绍 存储过程定义 指针 动态SQL语句
db2存储过程基础
银行db2存储过程开发内部专用指导手册,难得的内部规范材料
DB2存储过程精简教程,DB2存储过程精简教程,DB2存储过程精简教程
DB2存储过程基础文档、基本变量定义、基本语法描述。适合初学者。
DB2存储过程基础培训
在本教程中,您学习了用于编写过程、用户定义函数和触发器的SQL Procedural Language。您学习了SQL Procedure Language 的所有基本要素,包括变量声明和赋值、语法和使用以及用于控制过程逻辑的流程的条件语句和迭代...
DB2 存储过程的基本语法,简单 易学。
JDBC调DB2存储过程
DB2数据库存储过程入门 首先,打开DB2开发中心,新建项目后,连接上指定的数据库。右键点击新建存储过程,可以直接新建,也可以使用向导建立。建好后就如下所视:
db2存储过程入门资料,为pdf文档,详细介绍了存储过程的各种实现方法,是不可多得的经典材料。
java 调用db2存储过程,将db2数据库的存储过程描述,讲的比较清楚。