- 浏览: 1118403 次
- 性别:
- 来自: 上海
文章分类
- 全部博客 (1055)
- quartz定时任务 (1)
- json (9)
- 接口 (1)
- http (1)
- sccket (2)
- xmlbean (1)
- webservice (1)
- flex (1)
- sitemesh (2)
- json组装 (2)
- 存储过程 (6)
- ajax后台返回前台上下问 (3)
- flect反射机制 (1)
- 页面列表 (1)
- oracal结果集合并 (1)
- lucene (1)
- 读取配置文件工具 (1)
- 框架整合 (1)
- jms (3)
- 断点续传 (1)
- 页面之间的传值 (1)
- jpa (6)
- sql (8)
- jsp跳转 (1)
- jsp (6)
- jquery (2)
- ui (1)
- js与jsp之间的值的交互应用 (1)
- js空值判断 (1)
- Ajax (9)
- web-inf 编译目录 (1)
- 多线程 (9)
- 资料 (0)
- oracal (4)
- 多线程webservice (1)
- ztree (1)
- 加载与内容的变化 (1)
- 页面的公用变量 (1)
- web-inf目录结构 (1)
- 登陆系列 (1)
- jass应用于webservice认证 (2)
- 路径问题 (1)
- list合并 (1)
- list map中的value获取 (2)
- 标签tag (4)
- 方法 (1)
- 树形数据及其渲染 (1)
- require js 回调 (1)
- tab 页面切换 (2)
- 计时获取验证码 (1)
- 模式 (6)
- easyui (3)
- dialog (2)
- 乱码问题 (1)
- spring (29)
- sqlserver (1)
- iis,netframework (0)
- iis (1)
- netframework (1)
- 处理乱码问题 (1)
- struts-ajax (0)
- ,理论,了;,; (0)
- 排错方法 (1)
- hibernate (1)
- 二级缓存 (1)
- portal (1)
- date类型 (0)
- jvm out of memery StackOverflowError (1)
- echart数据填充 (0)
- echart (1)
- jmv (1)
- 学习 (0)
- java基础 (1)
- oracle性能优化 (1)
- 数据库三范式 (1)
- easy UI (1)
- oracle11g (1)
- form (3)
- servlet (2)
- spring mvc (1)
- htm5 (0)
- ibatise (1)
- 序列化 (1)
- tomcat双认证 (1)
- maven Lib (1)
- tomcat eclipse (2)
- ehcache (1)
- http 代替 ajax (2)
- rest (6)
- script脚本占位模板 (1)
- strust 标签 el表达式 (1)
- js bo (1)
- 公司资料 (0)
- js return (1)
- spring 第三方插件的工具类 (1)
- linux (10)
- linux 克隆 (1)
- linux xshell连接 (1)
- yum expect (1)
- linux 应用命令 (1)
- yum 缓存rmp包 (1)
- yum 本地库离线安装 (2)
- yum错误 (1)
- jsp页面用定时调用 (1)
- ajax setup (2)
- Exception,RuntimeException (1)
- 对话框 dialog (1)
- maven (17)
- PropertyPlaceholderConfigurer properties文件 (1)
- 错误集 (1)
- mybatise (0)
- 拦截器filter (1)
- http get post 区别 (1)
- rest 客户端两种返回的数据处理方式 (1)
- listener (2)
- maven工程 (1)
- ContainerResponseFilter (1)
- ajax action 打印任意实体类jsp (1)
- yum svn mvn (1)
- 分页 (1)
- angularjs (1)
- memcached (1)
- git (12)
- yyyyyyyy (0)
- oracal安装 (4)
- jaxwswebservice (1)
- spring bean (1)
- 排方法 (0)
- java 生成pdf (2)
- 创建目录 (1)
- 下载与生产pdf文件 (1)
- base64 (2)
- 配置文件 (2)
- blob (1)
- pdf (1)
- yum linux 安装Oracle (1)
- excl 插入数据库 (1)
- 搭建框架 (1)
- .classpath和jdk jre (1)
- mybatise 配置文件通配 (1)
- 用Maven插件生成Mybatis代码 (2)
- springmvc值的传递 (1)
- js jsp html (1)
- ajax跨域 (1)
- javamail (0)
- 提交复杂格式json数据 (1)
- get中文乱码/post (1)
- 弹出框,承载弹出链接----页面,显示大图 (1)
- @RequestBody list bean (1)
- 隐藏域 (1)
- 多页保存 (1)
- form 校验 (1)
- 注册,上传图片 (1)
- 登录验证 (1)
- 全局的异常处理 (0)
- 异常捕获,显示在页面 (0)
- mybatise事物配置 (1)
- maven compile (1)
- 时间格式 (1)
- js 跳转页面 (1)
- pringsecurity 角色授资源要重启服务 (0)
- Retrofi restfull (1)
- session过期推出,直接点击退出,退出 (0)
- 参数放于requestbody (1)
- maven本地仓库jar包 (1)
- 编译环境出错jdk1.6 1,7 (1)
- jsonarray ---json数组格式 (1)
- java后台文件上传,接受的2种方式 (0)
- 生成保存图片 (1)
- 权限mysql数据库Md5加密sql (1)
- mysql一对多关联查询 (1)
- mysql批量更新 (0)
- oracle in exists 区别 (1)
- https (1)
- linux发布脚本 (1)
- dubbo zookeepr (1)
- request.getParameter() (1)
- request.setAttribute() (1)
- @RequestMapping @ResponseBody (1)
- @RequestMapping @responsebody src控件 后台如何写入src值 (1)
- dwz (8)
- lib jar maven (1)
- web服务访问名称 (1)
- mvc返回内容设置,拦截 (1)
- @ResponseBody 返回json处理 (1)
- 视图,函数,存储过程 (1)
- <context:annotation-config/> (1)
- <context:component-scan/> (1)
- xml路劲通配 (2)
- 杂项 (1)
- dubbo (34)
- redis (25)
- mybatis (34)
- springmvc (15)
- js (7)
- sprinvmvc (1)
- 设计理念 (1)
- 工程 (1)
- 其他 (149)
- 导出,下载 (1)
- session (4)
- token (1)
- Exception处理 (2)
- 注解 (2)
- 框架 (3)
- shrio (1)
- login (1)
- 项目结构设计 (1)
- mybatis两种关联查询 (1)
- mybaits (2)
- 迭代模式 (1)
- oralce树形数据结构构建 (2)
- 树形结构的jsp展现 (1)
- filter intecept(Struts) (1)
- 树形结构数据的提交 (1)
- @RequestBody @RequestParam (1)
- div (1)
- equals重写 (1)
- 程序设计 (1)
- f多层for循环跳出 (1)
- 看源码 (0)
- 权限系统普遍通性 (1)
- 设计程序 (1)
- 动态拼接元素样式问题 (1)
- sql迭代 (1)
- debug断点 (1)
- spring mvc 扫描注解(ioc (1)
- di) (1)
- jdk (2)
- cglib (1)
- mybatis 多层括号(超过三层)解析不了 (1)
- tiles (2)
- cacheManager缓存的切换 (1)
- jsp,xml中el表达式等占位符中的数据操作 (1)
- jpa 及spring data jpa开发 (1)
- compareTo equals toString (1)
- 子页面用父页面js (1)
- jsp相对路径自加问题 (1)
- bean之间的相互拷贝 (1)
- DECODE (2)
- 泛型 (2)
- NVL (1)
- jsp jstl函数 用标签声明页面变量,供jstl使用,时间格式问题 (1)
- form表单切换action值 (1)
- response.getWriter().write()功能优于springMvc的返回 (1)
- 快速复制构建项目 (1)
- activeMq (15)
- cache (2)
- sql获取序列号 (1)
- 上传 (2)
- tfs (1)
- jsp标签 (2)
- qita (1)
- 触发器 (1)
- Exception (1)
- mybais (1)
- 标签 (1)
- connect by (1)
- for ... in (1)
- map转化为list (1)
- 树形结构list构建(树形实体) (1)
- 左侧菜单的设计 (1)
- response.getWriter().write() (1)
- eclipse打断点之后断无效 (1)
- 直接访问/web-inf/下的页面 (1)
- clob (1)
- freemark (1)
- 框架的设计 (1)
- get方式处理乱码 (1)
- Request的getParameter和getAttribute方法的区别 (1)
- cas (9)
- 时间格式问题 (1)
- ResponseUtil.writeToResponse (1)
- 树形数据的反选 (1)
- spring data jpa (1)
- jsp异常提示 (1)
- jquery js (1)
- eclipse (1)
- 乱码 (1)
- Json OBJETC (1)
- PROCEDURE (1)
- pl/sql oracle (1)
- 设计 (1)
- el表达式 (3)
- iframe (1)
- map (1)
- jsp中调用Java (1)
- response.getWriter().write() ajax (1)
- mybatis xml 传入参数 (0)
- response与request在返回时作用区别 (1)
- spring cache (1)
- 模型驱动 (1)
- 关于mybatis传空值的处理 (1)
- 日志配置 (1)
- mapper (1)
- mapper mybatis (1)
- mapper mybatis-spring spring-data-jpa (1)
- js插件 (1)
- spring-data-jpa (2)
- 字节流转化过程 (1)
- 数据库 (11)
- jsp页面 (1)
- 局域网络访问问题 (1)
- plsql (2)
- response (1)
- plsq (1)
- Proxool (1)
- cas security (1)
- 系统配置化 (1)
- 集群 (4)
- springboot (24)
- 设计模式 (5)
- 通知 (1)
- 架构 (48)
- zookeeper (5)
- mvc框架 (1)
- 事物隔离策略 (1)
- jvm (8)
- 调研 (1)
- Java工具类 (1)
- 并发容器 (3)
- 多线程管理器 (4)
- 简历 (0)
- 查询 (1)
- 集群工具 (1)
- springMcv (1)
- ConcurrentHashMap (1)
- hashtable (1)
- cac (1)
- 树形结构 (1)
- 定时任务 (1)
- tortoiseGit (2)
- struts (11)
- shiro (31)
- log4j (3)
- struts2 (2)
- 编码 (1)
- request (3)
- Nginx (14)
- tomcat (1)
- idea (12)
- mvc (2)
- BeanUtils (2)
- image (1)
- mino (1)
- httpClient (1)
- volecity (1)
- swagger (3)
- 调错 (0)
- data (1)
- log (1)
- shell (1)
- 事物 (3)
- junit (1)
- RestTemplate (2)
- 线程 (0)
- Exception异常处理 (1)
- Exception异常注解 (1)
- lock (1)
- HashMap (1)
- 面试 (0)
- rabbitMq (1)
- rainCat (1)
- 其它 (1)
- activemq消息传送机制以及ACK机制详解 (0)
- mysql (7)
- fildder (1)
- UML (1)
- 校验 (1)
- 反射 (1)
- 切面 (1)
- springioc (1)
- urule2 (0)
- skywalking (1)
- urule (2)
- docker (2)
- 前端 (1)
- bootstrap (1)
- eureka (2)
- springcloud (16)
- jenkins (2)
- springsecurity (1)
- 数据结构 (4)
- gradle (1)
- mycat (3)
- nacos (1)
- canary (1)
- 线程池 (1)
- solr (1)
- resteasy (2)
- BI (1)
- dfs (2)
- elasticsearch (1)
- ruby (1)
- logstash (1)
- clickhouse (2)
- davinci (15)
- java (1)
- davinvi (1)
- fastdfs (1)
- vue (1)
- 《将博客搬至CSDN》 (1)
最新评论
-
nizhipeng123:
private SessionFactory sessio ...
用map接收参数的几个注意的问题
////调用存储过程方法
import java.sql.Connection;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@SuppressWarnings("unchecked")
protected void doTestConnect() throws Throwable {
Connection con = null;
try {
Class driverClass = Class
.forName("oracle.jdbc.driver.OracleDriver");
java.sql.DriverManager.registerDriver((Driver) driverClass
.newInstance());
con = java.sql.DriverManager.getConnection(
"jdbc:oracle:thin:@//192.168.7.190:1521/DSDB", "JS12110xc",
"JS12110xc");
try {
PreparedStatement pstmt = con
.prepareStatement("{call dbo.uspGetEmployeeManagers(?)}");
pstmt.setInt(1, 50);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
}
rs.close();
pstmt.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
} catch (ClassNotFoundException ce) {
throw ce;
} catch (Exception e) {
throw e;
} finally {
if (con != null)
try {
con.close();
} catch (SQLException e) {
throw e;
}
}
}
public static void executeStoredProcedure(Connection con) ...{
try ...{
CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
cstmt.setInt(1, 5);
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
cstmt.execute();
System.out.println("MANAGER ID: " + cstmt.getInt(2));
}
catch (Exception e) ...{
e.printStackTrace();
}
}
public static void executeUpdateStoredProcedure(Connection con) ...{
try ...{
CallableStatement cstmt = con.prepareCall("{call dbo.UpdateTestTable(?, ?)}");
cstmt.setString(1, "A");
cstmt.setInt(2, 100);
cstmt.execute();
int count = cstmt.getUpdateCount();
cstmt.close();
System.out.println("ROWS AFFECTED: " + count);
}
catch (Exception e) ...{
e.printStackTrace();
}
}
CallableStatement cstmt = con.prepareCall("{? = call dbo.CheckContactCity(?)}");
cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
cstmt.setString(2, "Atlanta");
cstmt.execute();
System.out.println("RETURN STATUS: " + cstmt.getInt(1));
public static void executeSprocNoParams(Connection con) ...{
try ...{
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("{call dbo.GetContactFormalNames}");
while (rs.next()) ...{
System.out.println(rs.getString("FormalName"));
}
rs.close();
stmt.close();
}
catch (Exception e) ...{
e.printStackTrace();
}
}
protected void doTestConnect(String begintime, String endtime)
throws Throwable {
Connection con = null;
try {
Class driverClass = Class
.forName("oracle.jdbc.driver.OracleDriver");
java.sql.DriverManager.registerDriver((Driver) driverClass
.newInstance());
con = java.sql.DriverManager.getConnection(
"jdbc:oracle:thin:@//10.32.203.151:1521/DSDB", "dsdb",
"manager");
try {
PreparedStatement pstmt = con
.prepareStatement("{call dsdb.proc_stat_lbs(?, ?)}");
pstmt.setString(1, begintime);
pstmt.setString(2, endtime);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
}
rs.close();
pstmt.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
} catch (ClassNotFoundException ce) {
throw ce;
} catch (Exception e) {
throw e;
} finally {
if (con != null)
try {
con.close();
} catch (SQLException e) {
throw e;
}
}
}
存储过程:
create or replace procedure proc_stat_lbs as
nowtime varchar(50);
begin
nowtime := to_char(sysdate, 'yyyymmdd');
DELETE FROM T_TEMP_QUERY_LBS;
INSERT INTO T_TEMP_QUERY_LBS (YYS, PUSH_COUNT, REQ_COUNT, SUCCESS_COUNT) VALUES (0, 0, 0, 0);
INSERT INTO T_TEMP_QUERY_LBS (YYS, PUSH_COUNT, REQ_COUNT, SUCCESS_COUNT) VALUES (1, 0, 0, 0);
INSERT INTO T_TEMP_QUERY_LBS (YYS, PUSH_COUNT, REQ_COUNT, SUCCESS_COUNT) VALUES (2, 0, 0, 0);
INSERT INTO T_TEMP_QUERY_LBS (YYS, PUSH_COUNT, REQ_COUNT, SUCCESS_COUNT) VALUES (3, 0, 0, 0);
UPDATE T_TEMP_QUERY_LBS SET
REQ_COUNT = (select count(*) from ds_lbs_info where yysid = '1'
and to_char(querytime, 'yyyymmdd') >= nowtime)
where yys = 1;
UPDATE T_TEMP_QUERY_LBS SET
SUCCESS_COUNT = (select count(*) from ds_lbs_info where yysid = '1' and senderx is not nulL
and to_char(querytime, 'yyyymmdd') >= nowtime)
where yys = 1;
UPDATE T_TEMP_QUERY_LBS SET
REQ_COUNT = (select count(*) from ds_lbs_info where yysid = '3'
and to_char(querytime, 'yyyymmdd') >= nowtime)
where yys = 3;
UPDATE T_TEMP_QUERY_LBS SET
SUCCESS_COUNT = (select count(*) from ds_lbs_info where yysid = '3' and senderx is not nulL
and to_char(querytime, 'yyyymmdd') >= nowtime)
where yys = 3;
update t_temp_query_lbs set req_count =
(select sum(Req_count) from t_temP_query_lbs where yys <> 0) where yys = 0;
update t_temp_query_lbs set SUCCESS_COUNT =
(select sum(SUCCESS_COUNT) from t_temP_query_lbs where yys <> 0) where yys = 0;
END;
public String flex() throws Throwable {
Connection con = null;
try {
Class driverClass = Class
.forName("oracle.jdbc.driver.OracleDriver");
java.sql.DriverManager.registerDriver((Driver) driverClass
.newInstance());
con = java.sql.DriverManager.getConnection(
"jdbc:oracle:thin:@//10.10.46.2:1521/orcl", "zwww",
"zwww");
try {
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("{call zwww.flex}"); //用户名
rs.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
} catch (ClassNotFoundException ce) {
throw ce;
} catch (Exception e) {
throw e;
} finally {
if (con != null)
try {
con.close();
} catch (SQLException e) {
throw e;
}
}
}
===========select count(1)用这个解决统计没有数字出现的的现=======
create or replace procedure test is
ok number;
bad number;
zs number;
i number;
j number;
k number;
okwl number;
badwl number;
okfw number;
badfw number;
oksj number;
badsj number;
Cursor cursor1 is
select distinct(t.district_code) as code from T_B_ORG t;
my_record cursor1%rowtype;
begin
open cursor1;
delete from t_b_flex;
--一个区域多个用户节点上每类资源可用性
i := 1;
if cursor1%isopen then
loop
fetch cursor1
into my_record;
if cursor1%found then
k := 1;
-- select count(1) into j from T_B_ORG t where t.district_code =my_record.code;
-- while k<=j loop
-- begin
--安全设备
select count(1)
into ok
from (select a.asset_type, a.asset_state
from t_b_topsec_asset a
where a.ip in
(select t.ip
from T_B_ORG t
where t.district_code = my_record.code)
and a.asset_state = '0'
group by a.asset_type, a.asset_state);
--select t.ip from T_B_ORG t where t.district_code =my_record.code and rownum=k
select count(1)
into bad
from (select decode(count(1), null, '0', count(1)) c2
from t_b_topsec_asset a
where a.ip in
(select t.ip
from T_B_ORG t
where t.district_code = my_record.code)
and a.asset_state = '1'
group by a.asset_type, a.asset_state) /*,a.ip*/;
--网络设备
select count(1) into okwl
from t_b_resource t
where t.ip in (select t.ip
from T_B_ORG t
where t.district_code = my_record.code)
and t.is_netdevice = 1
and t.resource_status like 'true';
select count(1) into badwl
from t_b_resource t
where t.ip in (select t.ip
from T_B_ORG t
where t.district_code = my_record.code)
and t.is_netdevice = 1
and t.resource_status like 'flase';
--服务器及存储
select count(1) into okfw
from t_b_resource t
where t.ip in (select t.ip
from T_B_ORG t
where t.district_code = my_record.code)
and t.is_netdevice = 0
and t.resource_status like 'true';
select count(1) into badfw
from t_b_resource t
where t.ip in (select t.ip
from T_B_ORG t
where t.district_code = my_record.code)
and t.is_netdevice = 0
and t.resource_status like 'flase';
--事件工单
select count(1) into oksj
from T_B_TROUBLEWORK t
where t.ip in (select t.ip
from T_B_ORG t
where t.district_code = my_record.code)
and t.gzsfjj like '是';
select count(1) into badsj
from T_B_TROUBLEWORK t
where t.ip in (select t.ip
from T_B_ORG t
where t.district_code = my_record.code)
and t.gzsfjj not like '是';
insert into t_b_flex
(id, okaq, badaq, okwl, badwl,okfw, badfw,oksj, badsj ,dis_code)
values
(i, ok, bad, okwl, badwl,okfw, badfw,oksj, badsj , my_record.code);
i := i + 1;
-- end;
-- end loop;
else
exit;
end if;
end loop;
else
dbms_output.put_line('游标没有打开');
end if;
close cursor1;
commit;
exception
when others then
rollback;
end test;
返回游标形式输出参数的存数过程
public static void pro() throws Exception{
Connection con = null;
Class driverClass = Class
.forName("oracle.jdbc.driver.OracleDriver");
java.sql.DriverManager.registerDriver((Driver) driverClass
.newInstance());
con = java.sql.DriverManager.getConnection(
"jdbc:oracle:thin:@//10.10.46.2:1521/orcl", "zwww",
"zwww");
CallableStatement cs = con.prepareCall("{ call retCursor(?) }");
cs.registerOutParameter(1,OracleTypes.CURSOR);
cs.execute();
ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);
while(rs.next())
{
System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+ rs.getString(4)+" "+rs.getString(5));
}
}
create or replace procedure retCursor(ret_cursor out sys_refcursor)is
ret_cursor_value sys_refcursor;
begin
open ret_cursor_value for select * from t_b_processlog;
ret_cursor:=ret_cursor_value;
end retCursor;
http://qinya.iteye.com/blog/622300
http://wenku.baidu.com/link?url=M-IRJaygbBWptMALrzbYf5ZwBGxj-JOd5yMnZiay_vZtIQJCmihUwjuFjhKR59LlEztFhPHJB5NFwuKIFxaXRv-kQ3BTvPG0ORWeOnAkTY3
import java.sql.Connection;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@SuppressWarnings("unchecked")
protected void doTestConnect() throws Throwable {
Connection con = null;
try {
Class driverClass = Class
.forName("oracle.jdbc.driver.OracleDriver");
java.sql.DriverManager.registerDriver((Driver) driverClass
.newInstance());
con = java.sql.DriverManager.getConnection(
"jdbc:oracle:thin:@//192.168.7.190:1521/DSDB", "JS12110xc",
"JS12110xc");
try {
PreparedStatement pstmt = con
.prepareStatement("{call dbo.uspGetEmployeeManagers(?)}");
pstmt.setInt(1, 50);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
}
rs.close();
pstmt.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
} catch (ClassNotFoundException ce) {
throw ce;
} catch (Exception e) {
throw e;
} finally {
if (con != null)
try {
con.close();
} catch (SQLException e) {
throw e;
}
}
}
public static void executeStoredProcedure(Connection con) ...{
try ...{
CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
cstmt.setInt(1, 5);
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
cstmt.execute();
System.out.println("MANAGER ID: " + cstmt.getInt(2));
}
catch (Exception e) ...{
e.printStackTrace();
}
}
public static void executeUpdateStoredProcedure(Connection con) ...{
try ...{
CallableStatement cstmt = con.prepareCall("{call dbo.UpdateTestTable(?, ?)}");
cstmt.setString(1, "A");
cstmt.setInt(2, 100);
cstmt.execute();
int count = cstmt.getUpdateCount();
cstmt.close();
System.out.println("ROWS AFFECTED: " + count);
}
catch (Exception e) ...{
e.printStackTrace();
}
}
CallableStatement cstmt = con.prepareCall("{? = call dbo.CheckContactCity(?)}");
cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
cstmt.setString(2, "Atlanta");
cstmt.execute();
System.out.println("RETURN STATUS: " + cstmt.getInt(1));
public static void executeSprocNoParams(Connection con) ...{
try ...{
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("{call dbo.GetContactFormalNames}");
while (rs.next()) ...{
System.out.println(rs.getString("FormalName"));
}
rs.close();
stmt.close();
}
catch (Exception e) ...{
e.printStackTrace();
}
}
protected void doTestConnect(String begintime, String endtime)
throws Throwable {
Connection con = null;
try {
Class driverClass = Class
.forName("oracle.jdbc.driver.OracleDriver");
java.sql.DriverManager.registerDriver((Driver) driverClass
.newInstance());
con = java.sql.DriverManager.getConnection(
"jdbc:oracle:thin:@//10.32.203.151:1521/DSDB", "dsdb",
"manager");
try {
PreparedStatement pstmt = con
.prepareStatement("{call dsdb.proc_stat_lbs(?, ?)}");
pstmt.setString(1, begintime);
pstmt.setString(2, endtime);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
}
rs.close();
pstmt.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
} catch (ClassNotFoundException ce) {
throw ce;
} catch (Exception e) {
throw e;
} finally {
if (con != null)
try {
con.close();
} catch (SQLException e) {
throw e;
}
}
}
存储过程:
create or replace procedure proc_stat_lbs as
nowtime varchar(50);
begin
nowtime := to_char(sysdate, 'yyyymmdd');
DELETE FROM T_TEMP_QUERY_LBS;
INSERT INTO T_TEMP_QUERY_LBS (YYS, PUSH_COUNT, REQ_COUNT, SUCCESS_COUNT) VALUES (0, 0, 0, 0);
INSERT INTO T_TEMP_QUERY_LBS (YYS, PUSH_COUNT, REQ_COUNT, SUCCESS_COUNT) VALUES (1, 0, 0, 0);
INSERT INTO T_TEMP_QUERY_LBS (YYS, PUSH_COUNT, REQ_COUNT, SUCCESS_COUNT) VALUES (2, 0, 0, 0);
INSERT INTO T_TEMP_QUERY_LBS (YYS, PUSH_COUNT, REQ_COUNT, SUCCESS_COUNT) VALUES (3, 0, 0, 0);
UPDATE T_TEMP_QUERY_LBS SET
REQ_COUNT = (select count(*) from ds_lbs_info where yysid = '1'
and to_char(querytime, 'yyyymmdd') >= nowtime)
where yys = 1;
UPDATE T_TEMP_QUERY_LBS SET
SUCCESS_COUNT = (select count(*) from ds_lbs_info where yysid = '1' and senderx is not nulL
and to_char(querytime, 'yyyymmdd') >= nowtime)
where yys = 1;
UPDATE T_TEMP_QUERY_LBS SET
REQ_COUNT = (select count(*) from ds_lbs_info where yysid = '3'
and to_char(querytime, 'yyyymmdd') >= nowtime)
where yys = 3;
UPDATE T_TEMP_QUERY_LBS SET
SUCCESS_COUNT = (select count(*) from ds_lbs_info where yysid = '3' and senderx is not nulL
and to_char(querytime, 'yyyymmdd') >= nowtime)
where yys = 3;
update t_temp_query_lbs set req_count =
(select sum(Req_count) from t_temP_query_lbs where yys <> 0) where yys = 0;
update t_temp_query_lbs set SUCCESS_COUNT =
(select sum(SUCCESS_COUNT) from t_temP_query_lbs where yys <> 0) where yys = 0;
END;
public String flex() throws Throwable {
Connection con = null;
try {
Class driverClass = Class
.forName("oracle.jdbc.driver.OracleDriver");
java.sql.DriverManager.registerDriver((Driver) driverClass
.newInstance());
con = java.sql.DriverManager.getConnection(
"jdbc:oracle:thin:@//10.10.46.2:1521/orcl", "zwww",
"zwww");
try {
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("{call zwww.flex}"); //用户名
rs.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
} catch (ClassNotFoundException ce) {
throw ce;
} catch (Exception e) {
throw e;
} finally {
if (con != null)
try {
con.close();
} catch (SQLException e) {
throw e;
}
}
}
===========select count(1)用这个解决统计没有数字出现的的现=======
create or replace procedure test is
ok number;
bad number;
zs number;
i number;
j number;
k number;
okwl number;
badwl number;
okfw number;
badfw number;
oksj number;
badsj number;
Cursor cursor1 is
select distinct(t.district_code) as code from T_B_ORG t;
my_record cursor1%rowtype;
begin
open cursor1;
delete from t_b_flex;
--一个区域多个用户节点上每类资源可用性
i := 1;
if cursor1%isopen then
loop
fetch cursor1
into my_record;
if cursor1%found then
k := 1;
-- select count(1) into j from T_B_ORG t where t.district_code =my_record.code;
-- while k<=j loop
-- begin
--安全设备
select count(1)
into ok
from (select a.asset_type, a.asset_state
from t_b_topsec_asset a
where a.ip in
(select t.ip
from T_B_ORG t
where t.district_code = my_record.code)
and a.asset_state = '0'
group by a.asset_type, a.asset_state);
--select t.ip from T_B_ORG t where t.district_code =my_record.code and rownum=k
select count(1)
into bad
from (select decode(count(1), null, '0', count(1)) c2
from t_b_topsec_asset a
where a.ip in
(select t.ip
from T_B_ORG t
where t.district_code = my_record.code)
and a.asset_state = '1'
group by a.asset_type, a.asset_state) /*,a.ip*/;
--网络设备
select count(1) into okwl
from t_b_resource t
where t.ip in (select t.ip
from T_B_ORG t
where t.district_code = my_record.code)
and t.is_netdevice = 1
and t.resource_status like 'true';
select count(1) into badwl
from t_b_resource t
where t.ip in (select t.ip
from T_B_ORG t
where t.district_code = my_record.code)
and t.is_netdevice = 1
and t.resource_status like 'flase';
--服务器及存储
select count(1) into okfw
from t_b_resource t
where t.ip in (select t.ip
from T_B_ORG t
where t.district_code = my_record.code)
and t.is_netdevice = 0
and t.resource_status like 'true';
select count(1) into badfw
from t_b_resource t
where t.ip in (select t.ip
from T_B_ORG t
where t.district_code = my_record.code)
and t.is_netdevice = 0
and t.resource_status like 'flase';
--事件工单
select count(1) into oksj
from T_B_TROUBLEWORK t
where t.ip in (select t.ip
from T_B_ORG t
where t.district_code = my_record.code)
and t.gzsfjj like '是';
select count(1) into badsj
from T_B_TROUBLEWORK t
where t.ip in (select t.ip
from T_B_ORG t
where t.district_code = my_record.code)
and t.gzsfjj not like '是';
insert into t_b_flex
(id, okaq, badaq, okwl, badwl,okfw, badfw,oksj, badsj ,dis_code)
values
(i, ok, bad, okwl, badwl,okfw, badfw,oksj, badsj , my_record.code);
i := i + 1;
-- end;
-- end loop;
else
exit;
end if;
end loop;
else
dbms_output.put_line('游标没有打开');
end if;
close cursor1;
commit;
exception
when others then
rollback;
end test;
返回游标形式输出参数的存数过程
public static void pro() throws Exception{
Connection con = null;
Class driverClass = Class
.forName("oracle.jdbc.driver.OracleDriver");
java.sql.DriverManager.registerDriver((Driver) driverClass
.newInstance());
con = java.sql.DriverManager.getConnection(
"jdbc:oracle:thin:@//10.10.46.2:1521/orcl", "zwww",
"zwww");
CallableStatement cs = con.prepareCall("{ call retCursor(?) }");
cs.registerOutParameter(1,OracleTypes.CURSOR);
cs.execute();
ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);
while(rs.next())
{
System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+ rs.getString(4)+" "+rs.getString(5));
}
}
create or replace procedure retCursor(ret_cursor out sys_refcursor)is
ret_cursor_value sys_refcursor;
begin
open ret_cursor_value for select * from t_b_processlog;
ret_cursor:=ret_cursor_value;
end retCursor;
http://qinya.iteye.com/blog/622300
http://wenku.baidu.com/link?url=M-IRJaygbBWptMALrzbYf5ZwBGxj-JOd5yMnZiay_vZtIQJCmihUwjuFjhKR59LlEztFhPHJB5NFwuKIFxaXRv-kQ3BTvPG0ORWeOnAkTY3
发表评论
-
远程操作建立存储过程
2018-07-27 11:10 465存储过程的建立,操作远程数据库的时候表名最好加入数据实例, ... -
存储过程,函数,视图
2017-01-13 09:28 814//存储过程用于操作数据库,函数于返回数据,存储过程中可以 ... -
存储过程调试
2016-10-31 11:55 1133存储过程调用调试跳到了其他无关的函数,或存储过程是由于有相关存 ... -
mybatis调用存储过程及返回值获取
2016-08-08 14:17 2421mybatis调用存储过程及返回值获取 存储过程的返回结 ... -
mybatis等框架调用存存过程
2016-06-23 15:36 933需要一次操作多张表的情况下,为了简化代码,可以直接调用一个 ...
相关推荐
SAP HANA是一个高性能的内存数据库系统,它提供了一系列功能强大的工具来进行数据分析、应用开发、存储过程编写等操作。其中,对于存储过程的调试是开发者日常开发工作中的一个重要环节,SAP HANA为存储过程提供了...
在IT领域,尤其是在数据库管理与优化中,存储过程的解锁是一项关键技能,尤其对于Oracle数据库而言。当存储过程被锁定时,可能会影响系统的性能和稳定性,因此掌握如何解锁存储过程至关重要。以下是对“oracle存储...
SqlServer存储过程及调试指南的知识点如下: 1. 存储过程概念:存储过程是一组为完成特定功能的SQL语句集,这些语句经过编译后存储在数据库中,供用户通过指定存储过程名和参数(如有)来执行。存储过程被称作...
在IT行业中,数据库操作是日常开发中的重要环节,而存储过程是数据库中一种高效、封装性强的预编译语句集合。本问题涉及到的是在PowerBuilder(简称Pb)环境中如何调用Oracle或SQL Server等数据库中的存储过程。以下...
DB2存储过程是一种在数据库管理系统中预编译的SQL代码集合,它允许开发人员封装复杂的业务逻辑和数据处理操作,并可以被多次调用。DB2作为一款强大的关系型数据库管理系统,其存储过程功能强大,提高了应用程序的...
SQL Server存储过程是一种预编译的SQL代码集合,它们允许数据库开发者封装复杂的业务逻辑和数据操作,提高数据库应用的性能和可维护性。然而,由于存储过程的源代码通常是不可见的,对于需要查看或修改这些过程的...
PL/SQL Developer 调试存储过程及调试包中创建的存储过程 PL/SQL Developer 调试存储过程是指使用 PL/SQL Developer 工具来调试 Oracle 数据库中的存储过程。调试存储过程可以帮助开发者快速地定位和解决存储过程...
数据库查询的存储过程 数据库查询的存储过程是数据库管理系统中一种非常重要的概念。它可以将多个SQL语句组合成一个单元,提高数据库的查询效率和性能。 存储过程的优点: 1. 可以在单个存储过程中执行一系列SQL...
### 存储过程的优点 #### 一、提升执行效率 1. **编译优势**:存储过程在创建时仅编译一次,之后每次执行时都无需再次编译。相比之下,一般的SQL语句每次执行都需要重新编译。这种差异使得存储过程能够显著提高...
MySQL存储过程是数据库管理系统中的一种重要功能,它允许开发者预编译一系列SQL语句并封装成一个可重复使用的单元,从而提高数据处理的效率和代码的复用性。本教程将深入探讨MySQL存储过程的创建、调用以及相关概念...
ORACLE 存储过程的异步调用 本文讨论了 ORACLE 存储过程的异步调用方法,旨在解决客户端长时间等待存储过程执行的问题。主要思路是使用 DBMS_JOB 包将主处理存储过程作为任务提交到任务队列中,并通过 DBMS_PIPE 包...
本文将从 Oracle 存储过程的基础知识开始,逐步深入到 Oracle 存储过程的高级应用,包括 Hibernate 调用 Oracle 存储过程和 Java 调用 Oracle 存储过程的方法。 Oracle 存储过程基础知识 Oracle 存储过程是 Oracle...
### Oracle存储过程、函数与DBLink详解 #### 一、Oracle存储过程简介 在Oracle数据库中,存储过程是一种预编译好的SQL代码集合,它可以接受输入参数、返回单个值或多个值,并能够执行复杂的数据库操作。存储过程...
内容概要:简单的C# winform调用存储过程实例,创建存储过程入参,通过SqlConnection对象和SqlCommand对象调用存储过程,获取存储过程的出参并显示出来,详细代码注释,希望对用到C#调用存储过程的小伙伴有帮助 ...
在MySQL中,存储过程是一种预编译的SQL代码集合,它可以执行复杂的操作并提供更好的性能。在编写存储过程时,异常处理是确保程序稳定性和健壮性的重要环节。本实例展示了如何在MySQL存储过程中实现异常处理,以捕获...
《用友R9知识存储过程详解》 在IT行业中,特别是在企业级财务系统中,数据库的高效管理和数据处理是至关重要的。用友R9作为一款先进的财务管理系统,它利用存储过程这一强大的数据库功能来优化账务处理,提高系统...
SQL存储过程是数据库管理系统中一组为了完成特定功能的SQL语句集合,它们被预先编译并存储在数据库中,可以通过一个名称来调用执行。学习SQL存储过程是提升数据库管理和应用开发效率的关键步骤,它可以帮助我们更好...
在数据库管理中,存储过程和触发器是两个重要的概念,它们极大地增强了数据库的功能和效率。存储过程是一组预先编写的SQL语句,可以被多次调用,减少了网络流量,提高了性能,并提供了更好的安全性。而触发器则是一...
### ASP与SQL存储过程详解 #### 一、存储过程简介 存储过程(Stored Procedures)是一种在数据库中预先定义并编译好的SQL语句集合。它能够实现特定功能,并且可以在多处被调用,以此来简化复杂的数据库操作,提高...