Oracleのストアドプロシージャ

ストアドプロシージャの作成

CREATE PROCEDURE（CREATE OR REPLACE PROCEDURE）を使用する。
例）userqueries の sqlstatement に値(sqlVal)を追加し、query_id を出力として返す。

CREATE OR REPLACE PROCEDURE add_sql_query(sqlVal IN VARCHAR2,id OUT NUMBER) IS
BEGIN
    INSERT INTO userqueries (sqlstatement) VALUES (sqlVal)
    RETURNING userqueries.query_id INTO id;
END;
.
/

javaからのストアドプロシージャの呼び出し

javaプログラム中でストアドプロシージャを呼び出す。

例）PROCEDURE add_sql_query(sqlVal IN VARCHAR2,id OUT NUMBER)を呼び出し、実行する。

// Oracle Thin Driverを用いて接続
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection　con = DriverManager.getConnection( "jdbc:oracle:thin:@[ホスト名]:[ポート番号]/[接続するDBのSID]", "[ユーザー名]", "[パスワード]");
// 以下のように指定しなければ動かない場合もある（原因不明）。
//Connection　con = DriverManager.getConnection( "jdbc:oracle:thin:@[ホスト名]:[ポート番号]:[接続するDBのSID]", "[ユーザー名]", "[パスワード]");

// ストアドプロシージャの呼び出し
CallableStatement cstmt = connection.prepareCall(add_sql_query);

// 入力パラメーターの設定
cstmt.setString(1,usersql);
// 出力パラメーターの登録
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);

// 実行
cstmt.executeUpdate();
// 出力パラメーターを受け取る
int id = cstmt.getInt(2);

cstmt.close();

ストアドプロシージャを調べる

静的データディクショナリALL_SOURCE、DBA_SOURCE、USER_SOURCEを調べる。

ALL_SOURCE

ログインしているユーザーがアクセスできる全てのストアド・オブジェクトのテキストソース。

DBA_SOURCE

データベース内のすべてのストアド・オブジェクトのテキストソース。

USER_SOURCE

ログインしているユーザーが所有する全てのストアド・オブジェクトのテキストソース。

主要項目一覧

列名	説明
OWNER	オブジェクトの所有者
NAME	オブジェクト名
TYPE	オブジェクト型: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY
LINE	このソースの行番号
TEXT	ストアド・オブジェクトのテキスト・ソース

DESC user_source;
Name                Null?    Type
------------------- -------- ------------------
NAME                         VARCHAR2(30)
TYPE                         VARCHAR2(12)
LINE                         NUMBER
TEXT                         VARCHAR2(4000)

例）ストアドプロシージャのテキスト・ソースを表示する。

SELECT text FROM user_source WHERE name = 'add_sql_query' ORDER BY LINE;