Java中调用
//获取
CallableStatement cstmt = null;
String sAutoNo = null;
String sSql = "{call AUTO_No(?,?)}";
try {
cstmt = connDB.getConn().prepareCall(sSql);
cstmt.setString(1, tableName);
cstmt.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR); //注册字符变量
cstmt.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);//注册游标变量
cstmt.execute();
sAutoNo = cstmt.getString(2);//cstmt.getInt(2);
ResultSet rs = ((OracleCallableStatement)call).getCursor(2);
while(rs.next()){
//do something
}
Oracle游标、过程与引用类型(二)
m+v_typenum;
WHEN'004'THEN
ordersum_table.sourcesum:=ordersum_table.sourcesum+v_typenum;
WHEN'005'THEN
ordersum_table.drinksum:=ordersum_table.drinksum+v_typenum;
WHEN'006'THEN
ordersum_table.drinkingsum:=ordersum_table.drinkingsum+v_typenum;
WHEN'007'THEN
ordersum_table.vegetablesum:=ordersum_table.vegetablesum+v_typenum;
WHEN'008'THEN
ordersum_table.fruitsum:=ordersum_table.fruitsum+v_typenum;
WHEN'009'THEN
ordersum_table.moatsum:=ordersum_table.moatsum+v_typenum;
WHEN'010'THEN
ordersum_table.electricsum:=ordersum_table.electricsum+v_typenum;
WHEN'011'THEN
ordersum_table.officesum:=ordersum_table.officesum+v_typenum;
WHEN'012'THEN
ordersum_table.studysum:=ordersum_table.studysum+v_typenum;
WHEN'013'THEN
ordersum_table.diansum:=ordersum_table.diansum+v_typenum;
END CASE;
ordersum_table.allsum := ordersum_table.allsum+v_typenum;
END LOOP;
insert into odreport1 values ordersum_table;
CLOSE ordersum_cur;
END LOOP;
CLOSE branch_cur;
COMMIT;
END "STATISTICS_ORDERSUMxxx";