我在Oracle里面这样定义了一个存储过程:
CREATE OR REPLACE Procedure insstandard( p_stdno IN ordersub.standno%type,
v_intTotale OUT int,
v_intViewNumber OUT int,
v_intPrintNumber OUT int,
v_intDownNumber OUT int,
p_ret OUT varchar(20))Begin
select sum(mount) into v_intTotale from ordersub o where o.standno ='p_stdno' AND status='1';
select sum(mount) into v_intViewNumber from ordersub o where o.standno ='p_stdno' AND mediumtype='0' AND status='1';
select sum(mount) into v_intPrintNumber from ordersub o where o.standno ='p_stdno' AND mediumtype='1' AND status='1';
select sum(mount) into v_intDownNumber from ordersub o where o.standno ='p_stdno' AND mediumtype='2' AND status='1';
COMMIT;
p_ret:= ‘Sucess!’;Exception
when others then
ROLLBACK;
p_ret := ‘fail’;
End;在JAVA中我这样去调用它:
public int[] getPreRs(String standNo) throws Exception {
int[] arrInt = new int[4];
CallableStatement cstmt = conn.prepareCall("{call insstandard(?,?,?,?,?)}");
cstmt.setString(1,standNo);
cstmt.registerOutParameter(2,Types.INTEGER);
cstmt.registerOutParameter(3,Types.INTEGER);
cstmt.registerOutParameter(4,Types.INTEGER);
cstmt.registerOutParameter(5,Types.INTEGER);
cstmt.execute();
arrInt[0] = cstmt.getInt(2);
arrInt[1] = cstmt.getInt(3);
arrInt[2] = cstmt.getInt(4);
arrInt[3] = cstmt.getInt(5);
return arrInt;
}出现错误:对象 GB_STD.INSSTANDARD 无效
有谁知道该怎么改啊
CREATE OR REPLACE Procedure insstandard( p_stdno IN ordersub.standno%type,
v_intTotale OUT int,
v_intViewNumber OUT int,
v_intPrintNumber OUT int,
v_intDownNumber OUT int,
p_ret OUT varchar(20))Begin
select sum(mount) into v_intTotale from ordersub o where o.standno ='p_stdno' AND status='1';
select sum(mount) into v_intViewNumber from ordersub o where o.standno ='p_stdno' AND mediumtype='0' AND status='1';
select sum(mount) into v_intPrintNumber from ordersub o where o.standno ='p_stdno' AND mediumtype='1' AND status='1';
select sum(mount) into v_intDownNumber from ordersub o where o.standno ='p_stdno' AND mediumtype='2' AND status='1';
COMMIT;
p_ret:= ‘Sucess!’;Exception
when others then
ROLLBACK;
p_ret := ‘fail’;
End;在JAVA中我这样去调用它:
public int[] getPreRs(String standNo) throws Exception {
int[] arrInt = new int[4];
CallableStatement cstmt = conn.prepareCall("{call insstandard(?,?,?,?,?)}");
cstmt.setString(1,standNo);
cstmt.registerOutParameter(2,Types.INTEGER);
cstmt.registerOutParameter(3,Types.INTEGER);
cstmt.registerOutParameter(4,Types.INTEGER);
cstmt.registerOutParameter(5,Types.INTEGER);
cstmt.execute();
arrInt[0] = cstmt.getInt(2);
arrInt[1] = cstmt.getInt(3);
arrInt[2] = cstmt.getInt(4);
arrInt[3] = cstmt.getInt(5);
return arrInt;
}出现错误:对象 GB_STD.INSSTANDARD 无效
有谁知道该怎么改啊
解决方案 »
- 大哥大姐帮忙写个sql语句,嘿嘿!
- 求大牛们来帮帮忙!测试after触发器的时候出错oracle触发器行级的问题!!!万分感谢
- 求助:急!Oracle中使用javasource的问题(dropjava时卡住)
- oracle 9i 备份
- 100分求助:ASP.net的web网页连不上oracle10g数据库,怎么办?
- 求助!Linux下Oracle数据库误删表空间后恢复出现问题!
- oracle 8i安装时,setup线程自动消失?
- 请问我在WINDOWS平台上开发的基于ORACAL的网站可以放到LINUX上运行吗?
- 哪里有下Oracle的FTP?
- 在odi 执行 sql loader 装数的时候出错
- 请教一个事务处理的问题
- 高手指点 , 如何在packages 创建 type
CallableStatement cstmt = conn.prepareCall("{call std.insstandard(?,?,?,?,?)}");PLS-00302: 必须说明 'INSSTANDARD' 组件
ResultSet rs = db.getRs(sql);
while (rs.next()) {
String a100 = rs.getString("a100");
int num[] = db.getPreRs(a100);
}
方法为:
public int[] getPreRs(String standNo) throws Exception {
int[] arrInt = new int[4];
CallableStatement cstmt = conn.prepareCall("{call insstandard (?,?,?,?,?,?)}");
cstmt.setString(1,standNo);
cstmt.registerOutParameter(2,Types.INTEGER);
cstmt.registerOutParameter(3,Types.INTEGER);
cstmt.registerOutParameter(4,Types.INTEGER);
cstmt.registerOutParameter(5,Types.INTEGER);
cstmt.registerOutParameter(6,Types.VARCHAR);
cstmt.execute();
arrInt[0] = cstmt.getInt(2);
arrInt[1] = cstmt.getInt(3);
arrInt[2] = cstmt.getInt(4);
arrInt[3] = cstmt.getInt(5);
return arrInt;
}
这样速度很慢,我想要把模糊查询的SQL也写进存储过程,这个该怎么写啊,它返回的是一个集合啊