我要实现的功能查询abc 是否存在若存在则删除,继续执行owners表和owners表查出有用字段放入临时表abc中,再去执行批处理语句返回整型数组
但报有异常
sun.jdbc.odbc.JdbcOdbcBatchUpdateException: SQL Attempt to produce a ResultSet from executeBatch
这句代码是是否有错误?
System.out.println("petId");
String sql1="if exists(select * from abc)";
String sql2="drop table abc ";
String sql3="select owners.name as nam,pets.name into abc from owners inner join pets on owners.id=pets.id";
String sql4="select * from abc ";
Statement stmt=data.getConnectin().createStatement();
stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt.addBatch(sql3);
stmt.addBatch(sql4);
int[] id=stmt.executeBatch();
// System.out.println("长度是"+rs.length);
但报有异常
sun.jdbc.odbc.JdbcOdbcBatchUpdateException: SQL Attempt to produce a ResultSet from executeBatch
这句代码是是否有错误?
System.out.println("petId");
String sql1="if exists(select * from abc)";
String sql2="drop table abc ";
String sql3="select owners.name as nam,pets.name into abc from owners inner join pets on owners.id=pets.id";
String sql4="select * from abc ";
Statement stmt=data.getConnectin().createStatement();
stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt.addBatch(sql3);
stmt.addBatch(sql4);
int[] id=stmt.executeBatch();
// System.out.println("长度是"+rs.length);
解决方案 »
- spring security3.10拦截器不能初始化xml配置文件的bean
- 关于POI导出页面数据到Excel的问题,纠结了快两天了,受不了了,求指教
- Ajax乱码,相当严重。
- 如何通过微博地址获取微博名字
- struts2+spring+ajax为什么status会是404?
- 谁能告诉我数据库表中的-4c8a2e4b:118ea713cc2:-6f46这属于什么编码?
- 【高手请进】用java实现文件的上传下载问题!
- 像www.city8.com这样牛的网站是怎么开发的,高手请教
- java 实现微信微信自定义菜单接口
- 看看你的字典是否丰富。
- CachedRowSet 更新结果集????(在线等)
- 软件架构的问题----信息采集系统
1.oracle表及存储过程
[code=BatchFile]
--Author bluedn
--Description 测试Java 调用Oracle 存储过程返回值
-- 返回列表 建一个程序包
create or replace package Pack_Schlist is type Sch_Cursor is ref cursor;end Pack_Schlist;--它是把游标(可以理解为一个指针),作为一个out 参数来返回值的
create or replace procedure Proc_SearchResultList(p_CURSOR out Pack_Schlist.Sch_Cursor) is
begin
open p_CURSOR for select * from Proc_Table;
end Proc_SearchResultList;
-- Create Proc_SearchResultOne 有返回值的存储过程(非列表)
create or replace procedure Proc_SearchResultOne(PARA1 in VARCHAR2, PARA2 out VARCHAR2) As
begin
select name into PARA2 from Proc_Table where ID = PARA1;
end Proc_SearchResultOne;-- Create Proc_Insert 插入数据:无返回值的存储过程
create or replace procedure Proc_Insert(ID in varchar2, Name in varchar2) is
begin
insert into PROC_TABLE(ID,NAME) VALUES (ID,Name);
end Proc_Insert;-- Create table
create table PROC_TABLE
(
ID VARCHAR2(100),
NAME VARCHAR2(100)
)
tablespace USERS
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);[/code]
2.java调用 // //执行存储过程--插入数据
// public static void main(String args[]) throws SQLException {
// OracleDal orac = new OracleDal();
// Connection conn = orac.connectDatabase();
// String sql = "{call Proc_Insert(?,?)}";
// try {
// CallableStatement cstmt = conn.prepareCall(sql);
// cstmt.setString(1, "1001");
// cstmt.setString(2, "cms2007");
// cstmt.executeUpdate();
// } catch (SQLException ex) {
// System.out.print("writeNote():" + ex.getMessage());
// }
// } // //执行存储过程--有返回值的存储过程(非列表)
// public static void main(String args[]) throws SQLException {
// OracleDal orac = new OracleDal();
// Connection conn = orac.connectDatabase();
// String sql = "{call Proc_SearchResultOne(?,?)}";
// String SearchResult = null;
// try {
// CallableStatement cstmt = conn.prepareCall(sql);
// cstmt.setString(1, "1001");
// cstmt.registerOutParameter(2, Types.VARCHAR);
// cstmt.executeUpdate();
// SearchResult = cstmt.getString(2);
// System.out.println(SearchResult);
// } catch (SQLException ex) {
// System.out.print("writeNote():" + ex.getMessage());
// }
// } //执行存储过程--返回列表
public static void main(String args[]) throws SQLException {
OracleDal orac = new OracleDal();
Connection conn = orac.connectDatabase();
String sql = "{call Proc_SearchResultList(?)}";
ResultSet rs = null;
try {
CallableStatement cstmt = conn.prepareCall(sql);
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.executeUpdate();
rs = (ResultSet) cstmt.getObject(1);
while (rs.next()) { System.out.println(rs.getString(1) +"||"+ rs.getString(2)); }
} catch (SQLException ex) {
System.out.print("writeNote():" + ex.getMessage());
}
}
谢谢前几位有答复
create procedure pNew
@para1 varchar(255),
@para2 int output
AS
declare @strSQL nvarchar(4000)begin
--执行过程
end
CREATE TABLE [dbo].[abc] (
[o_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[p_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[owners] (
[o_id] [int] NOT NULL ,
[o_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[pets] (
[p_id] [int] NOT NULL ,
[p_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[owners] ADD
CONSTRAINT [PK_owners] PRIMARY KEY CLUSTERED
(
[o_id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[pets] ADD
CONSTRAINT [PK_pets] PRIMARY KEY CLUSTERED
(
[p_id]
) ON [PRIMARY]
GO2.存储过程--author bluedn
create procedure pGetLst
@p_id int
asbegin
if exists (select * from abc)
--清空表记录
truncate table abc
--插入临时表
insert into abc(o_name,p_name)
select owners.o_name ,pets.p_name from owners left outer join pets on owners.o_id=pets.p_id and pets.p_id= @p_id
--返回记录集
select * from abc
end
但有一点建议:要先在查询分析器写好代码测试,不要直接在JAVA里面测试