我在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 无效
有谁知道该怎么改啊
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也写进存储过程,这个该怎么写啊,它返回的是一个集合啊