ORACLE PL/SQLとは 〜パッケージ作成・ファイル実行〜

【背景】

ネット上でのPL/SQLについての文献が少ない為、現場で培ってきたPL/SQLについての知識をまとめようと思います。

PL/SQLについて、３つ目の資料になります！

作成資料について誤記等がございましたらご指導ご鞭撻のほど宜しく御願い致します。
　
前回の資料↓

---

目次

１３.トランザクション
１４.実行
１５.制御構造
１６.資料３についてのまとめ

１３.トランザクション

トランザクションとは

PL/SQLプログラムの一連の処理のまとまりをトランザクションと呼びます。

PL/SQLの処理とは、1つ以上のSQL文のことです。

PL/SQLでは、トランザクションの単位でデータベース操作の確定（コミット）や取消（ロールバック）が行われます。

トランザクション	処理内容
コミット	トランザクション単位にデータベース操作を確定させること
ロールバック	トランザクション単位にデータベース操作を取消させること

コミット（COMMIT）

トランザクション単位にデータベース操作を確定させることをコミット（COMMIT）と呼びます。

例えば、あるテーブルに50件のレコードをINSERTした後に確定する場合はコミットを実行します。

コミット

COMMIT;

コミット例

SQL> BEGIN
  2      EXECUTE IMMEDIATE 'INSERT INTO test(id) VALUES(' || CHR(10) || '00001' || CHR(10) || ')';
  3      COMMIT;
  4  END;
  5  /

--PL/SQLプロシージャが正常に完了しました。

SQL> select * from test;

ID         NAME             NENREI
---------- ---------------- ---------- 
00001

　
ロールバック（ROLLBACK）

トランザクション単位にデータベース操作を取消させることをロールバック（ROLLBACK）と呼びます。

例えば、あるテーブルに50件のレコードをINSERTした後に、取消する場合はロールバックを実行します。

ただし、一度コミットした操作は取り消しすることが出来ないので注意が必要がです。

ロールバック

ROLLBACK;

ロールバック例

SQL> BEGIN
  2      EXECUTE IMMEDIATE 'INSERT INTO test(id) VALUES(' || CHR(10) || '00001' || CHR(10) || ')';
  3      ROLLBACK;
  4  END;
  5  /

--PL/SQLプロシージャが正常に完了しました。

SQL> select * from test;

--レコードが選択されませんでした

自律型トランザクション

まず、自律型トランザクションを宣言したテストプロシージャを作成します。

test_procはtestテーブルに1件レコードを登録します

自律型トランザクション①

CREATE OR REPLACE PROCEDURE test_proc
IS
      PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
      INSERT INTO test VALUES('00001','Yoshida',28);
      COMMIT;
END;

次にtest_procでtest_proc2を呼び出します。

このとき、EXECUTEで実行したINSERT文は構文エラーとなり例外処理部に遷移します。

例外処理部でtest_procを呼び出した後、ROLLBACKを実行します。

自律型トランザクション②

CREATE OR REPLACE PROCEDURE test_proc2
IS
BEGIN
      EXECUTE IMMEDIATE 'INSERT INTO test VALUES(' || CHR(10) || '00002' || CHR(10) || ')';
      COMMIT;
EXCEPTION WHEN OTHERS THEN
      test_proc;
      ROLLBACK;
END;
/

自律型トランザクション②　実行結果

SQL> EXECUTE test_proc2;

--PL/SQLプロシージャが正常に完了しました。

SQL> select * from test;

ID         NAME                 NENREI
---------- -------------------- ----------
00001      Yoshida              28        

補足

トランザクションの基本概念

トランザクションは、一連のデータベース操作を単一の論理単位として扱うものです。トランザクションの主な特性は以下の通りになります。

• 原子性 (Atomicity): トランザクション内のすべての操作は一つの単位として扱われ、すべて成功するか、すべて失敗する。
• 一貫性 (Consistency): トランザクションが終了すると、データベースの状態が一貫したものになる。
• 独立性 (Isolation): トランザクションは互いに独立して実行され、同時に実行される場合でも結果は個々のトランザクションが順番に実行された場合と同じになる。
• 耐久性 (Durability): トランザクションが完了すると、その結果は永続的に保存される。

１４.実行

PL/SQLでパッケージを作成（後述参照）したら実際に実行をしてみます。

※sqlplusがインストールされていることが前提

コマンドプロンプトでの実行例

sqlplusへのログイン

$ sqlplus username/password@connect_identifier
※一般的なユーザーで入る場合

username・・・ユーザーID
password・・・パスワード
connect_identifier・・・ログインしたい対象のスキーマ等

sqlファイルを実行

SQL> @test_pkg.sql
※ 「@ + sqlファイル名（+ 拡張子）」

　

パッケージ作成例

パッケージ

論理的に関連するPL/SQLの型、変数、定数、サブプログラム、カーソルおよび例外をグループにまとめたスキーマ・オブジェクトのこと

パッケージボディ

パッケージ内での実際の処理内容
　

パッケージ

CREATE OR REPLACE PACKAGE test_pkg IS (
    PROCEDURE t (test_1 IN VARCHAR2)
    
    FUNCTION e (test_2 IN VARCHAR2)
    
    FUNCTION s (test_3 IN NUMBER)
    RETURN NUMBER;
    
    FUNCTION t_test (test_4 IN NUMBER)
    RETURN NUMBER;
END;

パッケージの中身

パッケージボディ

CREATE OR REPLACE PACKAGE BODY test_pkg_body IS (
    PROCEDURE t (test_1 IN VARCHAR2) IS
    BEGIN 
        DBMS_OUTPUT.PUT_LINE(test_1);
    END t;
    
    FUNCTION e (test_2 IN VARCHAR2) IS
    BEGIN 
        test_2 := test_2 * 2;
        DBMS_OUTPUT.PUT_LINE(test_2);
    END e;
    
    FUNCTION s (test_3 IN NUMBER) IS
    BEGIN 
        RETURN test_3;
    END s;
    
    FUNCTION t_test (test_4 IN NUMBER) IS
    BEGIN
        RETURN test_4;
    END t_test;
END;

１６.資料３についてのまとめ

３つの資料に分けてPL/SQLについてまとめてみました。

・１つ目はPL/SQLとは何か
・２つ目は実際にどう実装するか
・３つ目はsqlファイル（パッケージ）が作成できたらどう実行するか

今更ですがメモ程度にPL/SQLについての説明書きなるものを残しておきます。

歴史

いつから	処理内容
1988年より	ORACLE DATABASEに追加された

他の言語と比較

採番	メリット
1	sqlとの親和性が高い
2	大量のデータのやり取りが得意
3	PL/SQLをパッケージとして作成し、Oracleデータベースに格納可能
4	ユーザー定義レコードの作成

PL/SQLは名前に「SQL」と入っているだけありSQLとの親和性が高い為、大量のデータをやり取りするシステムに向いています。
世間的にあまり使用されていない言語みたいですが、古くからのシステムであったり大量のデータを使用するようなシステムでは今現在もかなり重宝されているようです。
私の現場ではたまたま使用しており今回このような資料を作成することになりましたが
PL/SQLについて少しでも興味を持っていただけましたでしょうか。
導入資料として少しでも役に立てばよいですがこれからもPL/SQLを学習していく中で学んだことを本資料に追記していければと思っています。

以上になりますがここまで閲覧いただき有難うございます！