java-oracle 调用程序包_JAVA_编程开发_程序员俱乐部

 import java.sql.CallableStatement;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.SQLException;
 
 import oracle.jdbc.OracleDriver;
 
 public class Test {
 
     /**
      * @param args
      */
     public static void main(String[] args) {
         Connection conn = null;
         CallableStatement callstmt = null;
         //String sql="{? = call MyPack.getAvgHisal()}";
         String sql="{? = call MyPack.getHisalAccordingGrade(?)}";
         try {
             conn = getConnection();
             if (conn != null) {
                 callstmt = conn.prepareCall(sql);
                 //the first parameter
                 callstmt.registerOutParameter(1, java.sql.Types.DECIMAL);
                 //the second parameter
                 callstmt.setString(2, "1");
                 callstmt.execute();
                 System.out.println(callstmt.getInt(1));
             }
         } catch (Exception e) {
             e.printStackTrace();
         }
     }
 
     static Connection getConnection() throws SQLException {
         DriverManager.registerDriver(new OracleDriver());
         String cs = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
         return DriverManager.getConnection(cs, "scott", "tiger");
     }
 }
 

1 --package
 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