Youtube版【Java】ストアドプロシージャを実行する

解説動画

ストアドプロシージャを使うとSQLのやり取りが減るので良いですね。

■動画はこちら

■Youtube版の解説で使用しているソースコード
動画と一緒にこちらも参考にどうぞ。

Javaソース

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

public class CStmtOraTest {

	public static void main(String[] args) {
		try {
			new CStmtOraTest().execute();
		}catch(Exception e) {
			e.printStackTrace();
		}
	}

	public void execute() throws Exception {

		Connection conn = null;
		CallableStatement cstmt = null;

		//DBに接続(URL,USER_ID,PASSWORD)
		conn = DriverManager.getConnection("jdbc:oracle:thin:@DBYuruFuwa:1521:xe", "YURUKU", "FUWATTO");

		//SQLを発行
		cstmt = conn.prepareCall("CALL SELECT_ITEM(?,?,?,?)");
		cstmt.clearParameters();
		cstmt.setString(1, "00001");						//商品コード
		cstmt.registerOutParameter(2, Types.NUMERIC);	//処理結果
		cstmt.registerOutParameter(3, Types.VARCHAR);	//商品名
		cstmt.registerOutParameter(4, Types.NUMERIC);	//価格
		cstmt.execute();

		//結果を取得
		int result = cstmt.getInt(2);
		String itemName = cstmt.getString(3);
		int price = cstmt.getInt(4);
		
		//後始末
		cstmt.close();
		conn.close();
		
		//標準出力
		System.out.println("商品名:" + itemName);
		System.out.println("価格:" + price);
		System.out.println("処理結果:" + result);

	}
}

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Types;

public class CStmtOraTest {

public static void main(String[] args) {

try {

new CStmtOraTest().execute();

}catch(Exception e) {

e.printStackTrace();

}

public void execute() throws Exception {

Connection conn = null;

CallableStatement cstmt = null;

//DBに接続(URL,USER_ID,PASSWORD)

conn = DriverManager.getConnection("jdbc:oracle:thin:@DBYuruFuwa:1521:xe", "YURUKU", "FUWATTO");

//SQLを発行

cstmt = conn.prepareCall("CALL SELECT_ITEM(?,?,?,?)");

cstmt.clearParameters();

cstmt.setString(1, "00001"); //商品コード

cstmt.registerOutParameter(2, Types.NUMERIC); //処理結果

cstmt.registerOutParameter(3, Types.VARCHAR); //商品名

cstmt.registerOutParameter(4, Types.NUMERIC); //価格

cstmt.execute();

//結果を取得

int result = cstmt.getInt(2);

String itemName = cstmt.getString(3);

int price = cstmt.getInt(4);

//後始末

cstmt.close();

conn.close();

//標準出力

System.out.println("商品名:" + itemName);

System.out.println("価格:" + price);

System.out.println("処理結果:" + result);

}

ストアドプロシージャ

create or replace PROCEDURE SELECT_ITEM 
(
  IN_ITEM_CODE IN VARCHAR2 
, OUT_RESULT OUT VARCHAR2 
, OUT_ITEM_NAME OUT VARCHAR2 
, OUT_PRICE OUT VARCHAR2 
) AS 
BEGIN

    BEGIN
        SELECT ITEM_NAME,PRICE INTO OUT_ITEM_NAME,OUT_PRICE
        FROM PRICE_LIST
        WHERE ITEM_CODE = IN_ITEM_CODE;
        
        OUT_RESULT := 1;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            OUT_RESULT := 0;
    END;
    
END SELECT_ITEM;