JDBC CallableStatement –存储过程IN参数示例

JDBC CallableStatement示例，用于调用接受IN参数的存储过程。

经过Java 8和Oracle数据库19c测试

pom.xml

<dependency>
		<groupId>com.oracle</groupId>
		<artifactId>ojdbc</artifactId>
		<version>8</version>
		<scope>system</scope>
		<systemPath>path.to/ojdbc8.jar</systemPath>
	</dependency>

1. JDBC CallableStatement

1.1一个用于插入行的PL / SQL存储过程。

CREATE OR REPLACE PROCEDURE insert_employee(
	   p_name IN EMPLOYEE.NAME%TYPE,
	   p_salary IN EMPLOYEE.SALARY%TYPE,
	   p_date IN EMPLOYEE.CREATED_DATE%TYPE)
    AS
    BEGIN

      INSERT INTO EMPLOYEE ("NAME", "SALARY", "CREATED_DATE") VALUES (p_name, p_salary, p_date);

      COMMIT;

    END;

1.2 JDBC示例调用上述存储过程。

StoreProcedureInParameter.java

package com.mkyong.jdbc.callablestatement;

import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDateTime;

public class StoreProcedureInParameter {

    public static void main(String[] args) {

        String createSP = "CREATE OR REPLACE PROCEDURE insert_employee( "
                + " p_name IN EMPLOYEE.NAME%TYPE, "
                + " p_salary IN EMPLOYEE.SALARY%TYPE, "
                + " p_date IN EMPLOYEE.CREATED_DATE%TYPE) "
                + " AS "
                + " BEGIN "
                + "     INSERT INTO EMPLOYEE (\"NAME\", \"SALARY\", \"CREATED_DATE\") VALUES (p_name, p_salary, p_date); "
                + "     COMMIT; "
                + " END; ";

        String runSP = "{ call insert_employee(?,?,?) }";

        try (Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:orcl", "system", "Password123");
             Statement statement = conn.createStatement();
             CallableStatement callableStatement = conn.prepareCall(runSP)) {

            // create or replace stored procedure
            statement.execute(createSP);

            //----------------------------------

            callableStatement.setString(1, "mkyong");
            callableStatement.setBigDecimal(2, new BigDecimal("99.99"));
            callableStatement.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));

            // Run insertEmployee() SP
            callableStatement.executeUpdate();

        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

下载源代码

$ git clone https://github.com/mkyong/java-jdbc.git

参考文献

• Oracle参考光标
• CallableStatement JavaDocs
• Java JDBC教程
• 输入和输出变量–有什么区别

标签： callablestatement jdbc oracle 存储过程

翻译自: https://mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-in-parameter-example/