java-oracle 调用程序包
java代码如下：
2 import java.sql.Connection;
3 import java.sql.DriverManager;
4 import java.sql.SQLException;
5
6 import oracle.jdbc.OracleDriver;
7
8 public class Test {
9
10 /**
11 * @param args
12 */
13 public static void main(String[] args) {
14 Connection conn = null;
15 CallableStatement callstmt = null;
16 //String sql="{? = call MyPack.getAvgHisal()}";
17 String sql="{? = call MyPack.getHisalAccordingGrade(?)}";
18 try {
19 conn = getConnection();
20 if (conn != null) {
21 callstmt = conn.prepareCall(sql);
22 //the first parameter
23 callstmt.registerOutParameter(1, java.sql.Types.DECIMAL);
24 //the second parameter
25 callstmt.setString(2, "1");
26 callstmt.execute();
27 System.out.println(callstmt.getInt(1));
28 }
29 } catch (Exception e) {
30 e.printStackTrace();
31 }
32 }
33
34 static Connection getConnection() throws SQLException {
35 DriverManager.registerDriver(new OracleDriver());
36 String cs = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
37 return DriverManager.getConnection(cs, "scott", "tiger");
38 }
39 }
40
Oracle程序包如下：
2 create or replace package MyPack
3 as
4 function getHisalAccordingGrade(inGrade in salgrade.hisal%type)
5 return salgrade.hisal%type;
6 procedure getAvgHisal
7 (v_average out number);
8 end;
9
10 --package body
11 create or replace package body MyPack
12 AS
13 procedure getAvgHisal
14 (v_average out number)
15 as
16 begin
17 select AVG(hisal) into v_average
18 from salgrade;
19 end getAvgHisal;
20
21 function getHisalAccordingGrade(inGrade in salgrade.hisal%type)
22 return salgrade.hisal%type
23 as
24 outHisal salgrade.hisal%type;
25 begin
26 select hisal into outHisal
27 from salgrade where grade = inGrade;
28 return outHisal;
29 end getHisalAccordingGrade;
30 end MyPack;
问题：
1. oracle集函数AVG()的返回值类型总是不匹配
2. 当sql文件中有多条sql语句的时候，可以拆成一句句的sql批量执行；当sql里面定义的是有具体定义的procedure,function或者package的时候也可以用call来调用；但是是不是所有的sql文件都可以执行，这个需要测试。。
Java-Oracle类型转换：
Oracle与java.sql.Types的对应
Oracle java.sql.Types
blob blob
char char
clob clob
date date
number decimal
long varbinary
nclob,nvarchar2 other
smallint smallint
timestamp timstamp
raw varbinary
varchar2 varchar
Sql server与java.sql.Types的对应
Sql server java.sql.Types
bigint (2005,2008) bigint
timstamp,binary binary
bit bit
char,nchar,unqualified char
datetime date
money,smallmoney,decimal decimal
float (2005,2008) double
float(2000) float
int integer
image longvarbinary
text,ntext,xml longvarchar
numeric numeric
real real
smallint smallint
datetime,smalldatetime timestamp
tinyint tinyint
varbinary varbinay
nvarchar,varchar varchar
DB2与java.sql.Types的对应
bigint bigint
blob blob
character,graphic char
clob clob
date date
decimal decimal
double double
integer integer
longvargraphic longvarchar
longvarchar
real real
smallint smallint
time time
timestamp timestamp
vargraphic varchar
varchar
MySQL与java.sql.Types的对应
MySQL java.sql.Types
bigint bigint
tinyblob binary
bit bit
enum,set,char char
date,year date
decimal,numeric decimal
double,real double
mediumint,int integer
blob,mediumblob blob
longblob
float real
smallint smallint
time time
timestamp,datetime timestamp
tinyint tinyint
varbinary,binary varbinay
varchar,tinytext,text varchar
Sybase与java.sql.Types的对应
Sybase java.sql.Types
binary binary
bit bit
char,nchar, char
money,smallmoney,decimal decimal
float double
int integer
image longvarbinary
text longvarchar
numeric numeric
real real
smallint smallint
datetime,smalldatetime timestamp
tinyint tinyint
varbinar,timestamp varbinay
nvarchar,varchar ,sysname varchar
