ตอนที่ 21 : Java เรียกใช้ Oracle Stored Procedure ด้วย (JDBC)

{CALL procedure_name(agr1,agr2,...)}

CREATE OR REPLACE PROCEDURE GET_CUSTOMER
  (pCountryCode IN VARCHAR2, pRowFound OUT NUMBER, pCustomer OUT SYS_REFCURSOR)
AS
BEGIN
  
  -- OUT pCustomer
  OPEN pCustomer FOR
       SELECT *
         FROM CUSTOMER WHERE COUNTRY_CODE = pCountryCode;
         
  -- OUT pRowFound       
  SELECT COUNT(*) INTO pRowFound
         FROM CUSTOMER WHERE COUNTRY_CODE = pCountryCode;
END;

VAR  pRowFound NUMBER;
VAR  pCustomer REFCURSOR;
EXEC GET_CUSTOMER('US',:pRowFound,:pCustomer)
PRINT pRowFound;
PRINT pCustomer;

package com.java.myapp;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import oracle.jdbc.OracleTypes;

public class MyClass {
	public static void main(String[] args) {
		
		Connection connect = null;
		
		try {

			Class.forName("oracle.jdbc.driver.OracleDriver");
			connect =  DriverManager.getConnection("" +
					"jdbc:oracle:thin:@//localhost:1521/tcdb", "myuser","mypassword");
			
			// Stored Procedure
			String command = "{CALL GET_CUSTOMER(?,?,?)}"; 
			CallableStatement stmt = connect.prepareCall (command);
			
			// Parameters
			String CountryCode = "US";
			stmt.setString(1, CountryCode);  // IN
			stmt.registerOutParameter(2, OracleTypes.VARCHAR); // OUT 		
			stmt.registerOutParameter(3, OracleTypes.CURSOR); // OUT
				
			// Execute
			stmt.execute();
			
			// Get return parameters (pRowFound NUMBER)
			Integer pRowFound = stmt.getInt(2);			
			System.out.println("pRowFound : " + pRowFound.toString());
			
			// Get return parameters (pCustomer SYS_REFCURSOR)
			ResultSet rec  = (ResultSet) stmt.getObject(3);
			System.out.println("pCustomer : ");
			while((rec!=null) && (rec.next()))
			{
				System.out.print(rec.getString("CUSTOMER_ID"));
				System.out.print(" - ");
				System.out.print(rec.getString("NAME"));
				System.out.print(" - ");
				System.out.print(rec.getString("EMAIL"));
				System.out.print(" - ");
				System.out.print(rec.getString("COUNTRY_CODE"));
				System.out.print(" - ");
				System.out.print(rec.getFloat("BUDGET"));
				System.out.print(" - ");
				System.out.print(rec.getFloat("Used"));
				System.out.println("");
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			connect.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

CREATE OR REPLACE PROCEDURE INSERT_CUSTOMER
(
  pCustomerID IN VARCHAR2,
  pName IN VARCHAR2,
  pEmail IN VARCHAR2,
  pCountryCode IN VARCHAR2,
  pBudget IN DECIMAL,
  pUsed IN DECIMAL,
  pResult OUT NUMBER,
  pMessage OUT VARCHAR2
)
AS
BEGIN

  -- Insert Statement 1
  INSERT INTO CUSTOMER (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
    VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
 
  -- Return row effected  
  pResult := SQL%ROWCOUNT;
  pMessage := 'Insert Data Successfully';
  
  -- Return error exception
   EXCEPTION 
    WHEN OTHERS THEN
      BEGIN
        pResult := 0;
        pMessage := 'Error - ' || SQLERRM; 
      END;  
    
END;

VAR pResult NUMBER;
VAR pMessage VARCHAR2;
EXEC INSERT_CUSTOMER('C005','Fun Wipa','[email protected]','TH','100000','0',:pResult,:pMessage);
PRINT pResult;
PRINT pMessage;

package com.java.myapp;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import oracle.jdbc.OracleTypes;

public class MyClass {
	public static void main(String[] args) {
		
		Connection connect = null;
		
		try {

			Class.forName("oracle.jdbc.driver.OracleDriver");
			connect =  DriverManager.getConnection("" +
					"jdbc:oracle:thin:@//localhost:1521/tcdb", "myuser","mypassword");
			
			// Stored Procedure
			String command = "{CALL INSERT_CUSTOMER(?,?,?,?,?,?,?,?)}"; 
			CallableStatement stmt = connect.prepareCall (command);
			
			// Parameters
			String strCustomerID = "C005";
			String strName = "Fun Wipa";
			String strEmail = "[email protected]";
			String strCountryCode = "TH";
			Double dBudget = 100000.00;
			Double dUsed = 0.00;
            
			stmt.setString(1, strCustomerID);  // IN
			stmt.setString(2, strName);  // IN
			stmt.setString(3, strEmail);  // IN
			stmt.setString(4, strCountryCode);  // IN
			stmt.setDouble(5, dBudget);  // IN
			stmt.setDouble(6, dUsed);  // IN
			stmt.registerOutParameter(7, OracleTypes.INTEGER); // OUT 		
			stmt.registerOutParameter(8, OracleTypes.VARCHAR); // OUT
				
			// Execute
			stmt.execute();
			
			// Get return parameters (pResult NUMBER)
			Integer pResult = stmt.getInt(7);			
			System.out.println("pResult : " + pResult.toString());

			// Get return parameters (pMessage VARCHAR2)
			String pMessage = stmt.getString(8);			
			System.out.println("pMessage : " + pMessage);
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			connect.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}