After learning the stored procedures and stored functions, this paper will explain how to access the stored procedures and stored functions of oracle through jdbc.

1 precondition

• oracle has been installed, scott users have also unlocked [the author uses oracle 11g]
• jdk installed and environment variables configured
• Proficient in using idea
• Proficient in SQL developer
• Familiar with stored procedures and functions

2 operation

• 1. Log in as scott to open sqldeveloper and create stored procedures and functions

# Create a stored procedure. The input parameter is the user number and the output parameter is the user name
create or replace procedure pro_demo (no in emp.empno%type,username out emp.ename%type)
is 
begin
  select ename into username from emp where empno=no;
end;
# Create a storage function, enter 10, and return the result
create or replace function fun_demo(num number) return number
is
  v_num number(10);
begin
  v_num:=num+10;
  return v_num;
end;  

• 2 create a project in idea, add oracle driver package and test class

# TestProcedure.java, stored procedure test class
import java.sql.*;
public class TestProcedure {
    public static void main(String[] args) throws Exception{
        //Driver identifier
        String driver = "oracle.jdbc.OracleDriver";
        //url links
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        //User name of the database
        String user = "scott";
        //Password for database
        String password = "tiger";
        //Load driver
        Class.forName("oracle.jdbc.OracleDriver");
        //Create connection
        Connection connection = DriverManager.getConnection(url,user,password);
        //Create execution engine
        Statement statement = connection.createStatement();
        CallableStatement cs = connection.prepareCall("{call pro_demo(?,?)}");  //Call format {call stored procedure name (parameter, parameter)}
        cs.setObject(1, 7499);//Set input parameter value
        cs.registerOutParameter(2,Types.VARCHAR);//Set parameter type
        cs.execute();//implement
        Object objRtn = cs.getObject(2);//Get the reference value
        System.out.println(objRtn);
        //close resource
        cs.close();
        statement.close();
        connection.close();
    }
}
# TestFunction.java, store function test class
import java.sql.*;

public class TestFunction {
    public static void main(String[] args) throws Exception{
        //Driver identifier
        String driver = "oracle.jdbc.OracleDriver";
        //url links
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        //User name of the database
        String user = "scott";
        //Password for database
        String password = "tiger";
        //Load driver
        Class.forName("oracle.jdbc.OracleDriver");
        //Create connection
        Connection connection = DriverManager.getConnection(url,user,password);
        //Create execution engine
        PreparedStatement ps = connection.prepareStatement("select fun_demo(?) from dual");
        //Set input parameter value
        ps.setObject(1, 10);
        //implement
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            //Get execution results
            System.out.println(rs.getObject(1));
        }
        ps.close();
        connection.close();
    }
}

The above is to access the stored procedures and functions in oracle through jdbc.