Oracle Series: (+) JDBC access to Oracle's stored procedures and storage functions

1. Stored Procedures
1.1. Prepare SQL

--  Defining stored Procedure Create or replace procedure get_rax (salary in number,rax out  number) as    --need to pay taxes     bal number;begin     bal := salary - 3500;    if bal<=1500 then        rax := bal * 0.03 - 0;     elsif bal<=4500 then       rax := bal *  0.1 - 105;    elsif bal<=9000 then        rax := bal * 0.2 - 555;    elsif bal <=35000 then       rax := bal * 0.25 -  1005;    elsif bal<=55000 then        rax := bal * 0.3 - 2755;    elsif bal<=80000 then        rax := bal * 0.35 - 5505;    else        rax := bal * 0.45 - 13505;     end if;end;/set serveroutput on;--  Calling stored procedure declare    sal  Number := &salary;    rax number;begin    get_rax ( Sal,rax);     dbms_output.put_line (sal | |   ' yuan salary should be taxed '  | |  rax | |   ' Yuan '); end;/

1.2. Preparing the JAR Package

Oracle	Ojdbc5.jar
C3p0	C3p0-0.9.1.2.jar C3p0-config.xml

C3p0-config.xml

<c3p0-config>    <default-config>         <property name= "Jdbcurl" >jdbc:oracle:thin:@127.0.0.1:1521 : Orcl</property>        <property name= "DriverClass" > Oracle.jdbc.driver.oracledriver</property>        <property  name= "User" >scott</property>        <property  Name= "Password" >tiger</property>        <property  Name= "Initialpoolsize" >3</property>        <property  Name= "Maxpoolsize" >6</property>        <property name = "MaxIdleTime" >1000</property>    </default-config></c3p0-config> 

1.3. Writing tool classes

Jdbcutils.java

package com.rk.utils;import java.sql.connection;import  com.mchange.v2.c3p0.combopooleddatasource;public class jdbcutils {     Private static combopooleddatasource datasource = new combopooleddatasource ();     public static connection getconnection ()  throws Exception{         return datasource.getconnection ();     }        public static void closequietly ( AUTOCLOSEABLE AC) {        if (ac != null) {             try {                 ac.close ();             } catch  (exception e)  {&NBSp;               e.printstacktrace ();            }         }    }}

1.4. The JDBC program calls the stored procedure

Callproc.java

package com.rk.test;import java.sql.callablestatement;import java.sql.connection;import  java.sql.types;import com.rk.utils.jdbcutils;/** *  Demo JAVA-JDBC Call Oracle Process  */public  Class callproc {    public static void main (String[] args)  throws Exception{        String sql =  "{Call   get_rax (?,?)} ";         connection conn = jdbcutils.getconnection ();         callablestatement cstmt = conn.preparecall (SQL) ;         //set the value for the first number, starting with 1          cstmt.setint (1, 7000);         //for the second one? Registered output Type         cstmt.registeroutparameter (2, types.integer);         //Execution Call Procedure         cstmt.execute ();         //The return value of the receive process, which is the second one? No.         int rax = cstmt.getint (2);         //Display         system.out.println (" 7000 yuan salary should pay tax "+rax+" Yuan ");         jdbcutils.closequietly (cstmt);         jdbcutils.closequietly (conn);     }}

2. Store function 2.1, prepare SQL

--Define function Create or Replace function findempnameandjobandsal (pempno in Number,pjob out varchar2,psal out number) return Varcha    R2as pename Emp.ename%type;begin Select Ename,job,sal into Pename,pjob,psal from emp where empno = Pempno;    Return pename;end;/--call function declare Pename emp.ename%type;    Pjob Emp.job%type;    Psal emp.sal%type;begin Pename: = Findempnameandjobandsal (7788,pjob,psal); Dbms_output.put_line (' 7788 ' | | ' --' | | pename| | ' --' | | pjob| | ' --' | | PSAL); end;/

2.2. The JDBC program calls the store function

package com.rk.test;import java.sql.callablestatement;import java.sql.connection;import  java.sql.types;import com.rk.utils.jdbcutils;/** *  Demo JAVA-JDBC Call Oracle Functions  */public  Class callfunc {    public static void main (String[] args)  throws Exception {        String sql =  "{?  = call findempnameandjobandsal (?,?,?)}";         connection conn = jdbcutils.getconnection ();         callablestatement cstmt = conn.preparecall (SQL) ;                 //as the first? Register output type         cstmt.registeroutparameter (1, types.varchar);         //for the second one? Injected value     &Nbsp;   cstmt.setint (2, 7788);         //as a third? Registered output Type         cstmt.registeroutparameter (3, types.varchar);         //for the fourth one? Register output Type          Cstmt.registeroutparameter (4, types.integer);                 //Execute function call         cstmt.execute ();                 //get 1, respectively, The value of the 3,4 placeholder is         string ename = cstmt.getstring (1);         string job = cstmt.getstring (3);         int sal = cstmt.getint (4);         //Display    &Nbsp;    system.out.println ("7788--" +ename+ "--" +job+ "--" +sal ";    )     jdbcutils.closequietly (cstmt);         jdbcutils.closequietly (conn);     }}

Oracle Series: (+) JDBC access to Oracle's stored procedures and storage functions