java-oracle 调用程序包

java代码如下：

 1  import  java.sql.CallableStatement;
 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程序包如下：

 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

转载于:https://www.cnblogs.com/kelin1314/archive/2010/08/22/1805841.html