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&NBSP;AC) { if (ac != null) { try { ac.close (); } catch (exception e) &NBSP;{&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