jdbc连接数据库 调用存储过程
存储过程:
CREATE OR REPLACE TYPE quarters_type is table of VARCHAR(10);
/
CREATE OR REPLACE TYPE years_type is table of VARCHAR(10);
/
CREATE OR REPLACE TYPE ordernum_type is table of NUMBER;
/
CREATE OR REPLACE TYPE sales_type is table of NUMBER;
/
CREATE OR REPLACE PROCEDURE quarsales( quarters out quarters_type,years out years_type,
ordernum out ordernum_type, sales out sales_type )is
BEGIN
select to_char(orderdate,'Q'),to_char(orderdate,'YYYY'),count(distinct orders.orderid),sum(orderdetails.price*num*(1-discount))
bulk collect into quarters,years,ordernum,sales
from orders,orderdetails
where orders.orderid=orderdetails.orderid
group by to_char(orderdate,'Q'),to_char(orderdate,'YYYY')
order by to_char(orderdate,'Q');
END;
/
declare years years_type;
ordernum ordernum_type;
sales sales_type;
quarters quarters_type;
i integer;
j integer;
q integer;
begin quarsales(quarters ,years,ordernum, sales);
j:=1; i:=1;
dbms_output.put_line('季度'||' 年度'||' 订单数'||' 销售额');
for q in 1..quarters.count loop
dbms_output.put_line( quarters(i));
while(quarters(i)=quarters(j)) loop
dbms_output.put_line(' '||years(j)||' '||ordernum(j)||' '||sales(j));
j:=j+1;
if(j>quarters.count) then exit;
end if;
end loop;
i:=j;
end loop;
end;
/
java 调用存储过程的代码:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import
java.sql.SQLException;
import java.sql.Statement;
import sun.jdbc.odbc.ee.ConnectionPool;
public class procedure
{
public static void main(String[] args) throws
InstantiationException,
IllegalAccessException,
SQLException,
ClassNotFoundException {
Connection conn=null;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
}
catch(Class
NotFoundException e)
{
System.out.println("Error message="+e.getMessage());
}
try
{
conn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.163.131:1521:XE","oracle","oracle123");
CallableStatement cstmt=conn.prepareCall("{call CATEGORY_STO( ?,?,?,?)}");
System.out.println(" 年度 季度 订单数 销售额" );
cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.VARCHAR,10);
cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR,10);
cstmt.registerOutParameter(3, oracle.jdbc.OracleTypes.NUMBER);
cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.NUMBER);
int i=1;
cstmt.execute();
while(cstmt.getString(i)!=null)
{
System.out.println(cstmt.getString(i));
i++;
}
cstmt.close();
}
catch(SQLException E)
{
System.out.println("Error code = "+E.getErrorCode());
System.out.println("Error message = "+E.getMessage());
}
finally
{
try
{
if(conn!=null)
{
conn.close();
}
}
catch(SQLException E)