ORACLE PL/SQLとは　〜初学者向け入門編〜

Last updated at 2024-07-27Posted at 2024-07-20

【背景】

ネット上でのPL/SQLについての文献が少ない為、現場で培ってきたPL/SQLについての知識をまとめようと思います。
PL/SQLを使用している現場に参画して１年以上が経過し、ある程度ノウハウが蓄積されたので改めて知識共有として
自身の知識と+αネット上に転がっている数少ない文献とを組み合わせて初学者でも取り組みやすい資料にしました。

私の経歴だけ前提として記載しておきます。間違った記載があればご指導ご鞭撻の程、宜しくお願い致します。

【職務経歴・言語】

言語	言語歴	業界
PL/SQL	１年３か月	銀行系システム・リース決済システム
Java	２年０ヶ月	保険系業務システム等

１.PLSQLとは何か

形式的に説明すると「オラクル社が開発した手続き型言語で、非手続き型言語のSQLを拡張したもの」になります。
ざっくり簡単に説明すると

=====================================================

「複数のSQLを同時に扱え、分岐処理など複雑な処理を組めるもの」

=====================================================

になります。
ですのでデータベースと大量にやりとりするシステムに向いていると言えます。

【PLSQLが良く使用されているシステム】
・銀行系システム
・決算システム

数字を扱う大規模システムでは重宝されているイメージです。

【出来ることイメージ】

SQL：一般的なSQLイメージ

SELECT * FROM TEST WHERE no = '1';

↓PLSQLにて上記SQL文をIF文を使用して分岐処理させることが可能

PLSQL：分岐処理

--IF ELSE を使用してプログラム内で処理を分岐させることが可能
--※CASE WHEN句なども使用可能（後述にて記載）
IF TRUE THEN
    SELECT * FROM TEST WHERE no = '1';
ENO IF;

複数のSQL文にも勿論対応

PLSQL：SQLが複数処理

--複数のSQLも処理が可能
IF TRUE THEN
    SELECT * FROM TEST WHERE no = '1';
    SELECT * FROM TEST WHERE no = '2';
ENO IF;

SELECT文以外のDML（UPDATE,INSERT,DELETE）の実行についても可能

PLSQL：SELECTや他DML（UPDATE,INSERT,DELETE）の処理

--複数のSQLや他DML（UPDATE,INSERT,DELETE）も処理が可能
IF TRUE THEN
    SELECT * FROM TEST WHERE no = '1';
    --UPDATE実行前取得用
    SELECT * FROM TEST WHERE no = '2';
    UPDATE test_name = 'test' SET TEST WHERE no = '2';
    --UPDATE実行後取得用
    SELECT * FROM TEST WHERE no = '2';
ENO IF;

２.何が出来るのか

PL/SQLを用いて実際に何が出来るのかというと実際全てできます。JavaやC言語などの処理ロジックと何ら変わりなく処理を組むことができ、「１.PLSQLとは何か」でも記載したようにIFやFor使用して処理を分岐させたり、ループさせたりすることが可能です。
基本的な使用方法としてはカーソルやプロシージャを作成し、実行します。
※基本構造については後述を参照・・・「４.基本構造」

カーソル：SQL文（SELECT,INSERT,UPDATE,DELETE,文など）
プロシージャ：一連の処理の塊（複数のSQL文）

種別	とは	使用用途
カーソル	SQL文（SELECT,INSERT,UPDATE,DELETE,文など）	SQL文を作成して「カーソル」として用意しておく事によりプログラムの意図したタイミングでカーソル（SQL文）を処理実行可能。
プロシージャ	IF~ELSE などの分岐処理やSQL文を用いた処理の塊（「５.ストアドプログラム」参照）	処理の塊として実行する事が可能。引数を作成し、値を渡す事（IN）や戻り値として値を返す事（OUT）が可能。

３.実務的にどう使うか

「PL/SQLを使用して実際どの処理などに使用しているか」というところに関して言うと下記のようになります（私の経験上では）

処理内容	処理用途
画面	業務システムの画面から画面操作した際の処理を実施（DBへの登録、エラーチェック）
ファンクション	処理の塊として定義し、引数などを持たせ処理実行や戻り値を設定し特定のタイミングで処理を実施「５.ストアドプログラム」参照
バッチ	日次や週次、月次バッチなど基本的なバッチプログラムもPL/SQLを使用して処理を実施（プログラムの中身はファンクションやプロシージャが定義されており、上記の「ファンクション」の説明とあまり変わらない）

４.基本構造

基本構造は以下になります。

処理	和名	処理内容
DECLARE	宣言部分	定数や変数、カーソルを宣言（定義）する箇所
BEGIN	実行部分	実際の実行部分。SQL文の実行やDECLAREで宣言した定数や変数、カーソルを使用することも可能
EXCEPTION	例外処理	BEGINで実行している際にエラー例外が発生した場合に処理される。ユーザー独自のエラー定義を設定することも可能
END	-	-

基本構造

DECLARE
--宣言部分（任意）
BIGIN
--実行部分
END;

SQL文を実行する

基本構造 + SQL文を実行

DECLARE
--宣言部分（任意）
BIGIN
--実行部分
SELECT COUNT(*) FROM TEST
END;

変数宣言をしてその変数にSQL文を実行した結果を代入する

仮にTESTテーブルというものがあり、その中に存在するレコード数をカウントするSQLを実行部分に記載しているので上記のプログラムを実行した場合はTESTテーブルのレコード数をカウントすることができます。

基本構造 + SQL文を実行 + INTO句を使用して値を代入

DECLARE
--宣言部分（任意）
test_count NUMBER(4)
BIGIN
--実行部分
SELECT COUNT(*) INTO test_count FROM TEST
--↑INTO句を使用して宣言部で宣言しているtest_count(変数)に代入している
END;

カーソルを作成してカーソルを実行する

基本構造 + カーソル作成 + カーソル実行（実行部分）

DECLARE
--宣言部分（任意）

--カーソルを作成
CURSOR test_cursor IS
    SELECT name, nenrei FROM MEIBO;
TYPE test_cursor_rec_type IS RECORD (
    name NUMBER(4),
    nenrei VARCHAR(10)
);
test_cursor_rec test_cursor_rec_type;

BIGIN
--実行部分
--カーソルをオープンする
OPEN test_cursor
LOOP
FETCH test_cursor INTO test_cursor_rec
EXIT WHEN test_cursor_rec%NOTFOUND;
DBMS_OUTPUT.PUT(test_cursor_rec.NAME);
--name
DBMS_OUTPUT.PUT(test_cursor_rec.nenrei);
--nenrei
END LOOP
--カーソルをクローズする
CLOSE test_cursor
END;

５.データ型

PL/SQLには通常の変数定義とは別に汎用性の高い変数定義の方法が存在します。

型	型参照する対象	使用用途
%TYPE	〇〇テーブル.〇〇カラム	特定のテーブルの特定のカラムの型を参照し、その型を「%TYPE」を指定した変数の型とする・・・①
%ROWTYPE	カーソル・テーブル	カーソル定義やテーブル定義を参照し、その型を「%ROWTYPE」を指定した変数の型とする・・・②、③

上記２つを変数定義として定義することにより、参照先の型の変数が変更された場合にもその変更に合わせて型が定義される。

①：%TYPE

DECLARE
--宣言部分（任意）

--カーソルを作成
CURSOR test_cursor IS
    SELECT name, nenrei FROM MEIBO;
TYPE test_cursor_rec_type IS RECORD (
    --①・・・〇〇テーブル.〇〇カラム
    name MEIBO.name%TYPE,
    nenrei MEIBO.nenrei%TYPE
);
test_cursor_rec test_cursor_rec_type;
BIGIN
--実行部分　省略
END;

②：%ROWTYPE「カーソル定義」参照

DECLARE
--宣言部分（任意）

--カーソルを作成
CURSOR test_cursor IS
    SELECT name, nenrei FROM MEIBO;
    --②・・・カーソル定義「test_cursor」を参照して変数定義をしている
    test_cursor_rec test_cursor%ROWTYPE;
BIGIN
--実行部分　省略
END;

③：%ROWTYPE「テーブル定義」参照

DECLARE
--宣言部分（任意）

--カーソルを作成
CURSOR test_cursor IS
    SELECT name, nenrei FROM MEIBO;
    --③・・・テーブル定義「MEIBO」を参照して変数定義をしている
    test_cursor_rec MEIBO%ROWTYPE;
BIGIN
--実行部分　省略
END;

６.ストアドプログラム

PL/SQLで記述している一連の処理の手続き（かたまり）のことを指す

大きく２つに分けられる
・プロシージャ
・ファンクション

上記２つは機能としてはほとんど同一のものだが大きな違いとしては

戻り値があるかないか

である。

ストアドプログラム	戻り値
プロシージャ	なし
ファンクション	あり※なくても良い

【プロシージャ】

プロシージャ

CREATE OR REPLACE PROCEDURE test_procedure (
    test_proc_a IN NUMBER, test_proc_b IN NUMBER)
test_proc_c NUMBER;
--宣言部分（任意）
IS
--実行部分
BEGIN
  test_proc_c := test_proc_a * test_proc_b;
  DBMS_OUTPUT.PUT_LINE(test_proc_c);
END test_procedure;

【ファンクション】

引数なしファンクション

CREATE OR REPLACE FUNCTION test_function
--宣言部分（任意）
IS
  test_name VARCHAR2(10);
--実行部分
BEGIN
  SELECT name
  INTO test_name
  FROM TEST;

  DBMS_OUTPUT.PUT_LINE(test_name);
END test_function;

上記を実行するsql

実行sql

--sqlplusで実行する場合
EXECUTE test_function;
--test_name　の中身が出力される

引数ありファンクション

CREATE OR REPLACE FUNCTION test_function (
    test_name IN VARCHAR, test_nenrei IN NUMBER)
--宣言部分（任意）
IS
--実行部分
BEGIN
    DBMS_OUTPUT.PUT_LINE(test_name || TO_CHAR(test_nenrei));
END test_function;

実行sql

-- 宣言部
DECLARE
  name VARCHAR2;
  nenrei NUMBER;
-- 処理部
BEGIN
  test_function('テスト', 20);
END;

実行結果

--「テスト」と「２０」が出力
テスト20

※年齢*1000円　お年玉がいくら貰えるか計算する処理

引数あり・戻り値ありプロシージャ

CREATE OR REPLACE PROCEDURE test_procedure (
    test_nenrei IN NUMBER, test_out_money OUT NUMBER)
--宣言部分（任意）
IS
--実行部分
BEGIN
    test_out_money := test_nenrei * 1000;
END test_function;

実行sql

-- 宣言部
DECLARE
  money NUMBER;
-- 処理部
BEGIN
  test_function(10, money);
  DBMS_OUTPUT.PUT_LINE(money);
END;

実行結果

--10*1000
10000

７.パッケージ

パッケージ

論理的に関連する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;

実行

--コマンドプロンプトなどでsqlplusでの実行の場合
SQL>execute test_pkg.t('test')
--実行結果
test

８.資料についてのまとめ

ここまで閲覧頂きありがとうございます。私自身がPL/SQL初学者の頃に良く調べたことやよく理解出来なかった点を思い出しながらまとめてみました。改めてですが間違っている箇所等ございましたらご指導ご鞭撻の程宜しくお願い致します。

今更ですが下記の言語を習得、使用されている方はPL/SQLに取っ掛かりやすいのではと思っております。
・Java
・C言語
・SQL各種

PL/SQLはあまり人気がないですがデータベースとのやりとりをどの言語よりも頻繁にする言語だと思っているのでデータベースについてもっと学びたい方（DBA）や今までの現場でデータベースを沢山触れてきた方はPL/SQLにすぐ馴染めると考えていますし、率先して現場参画してみて欲しいです。
一緒にPL/SQL人口増やしましょう！

参考文献資料

https://www.oracle.com/jp/a/tech/docs/technical-resources/b-11-plsql.pdf
※一応載せておきます、公式ですが初見だととっつきにくいと思います

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

ORACLE PL/SQLとは 〜初学者向け入門編〜

【背景】

【職務経歴・言語】

目次

１.PLSQLとは何か

【出来ることイメージ】

↓PLSQLにて上記SQL文をIF文を使用して分岐処理させることが可能

複数のSQL文にも勿論対応

SELECT文以外のDML（UPDATE,INSERT,DELETE）の実行についても可能

２.何が出来るのか

３.実務的にどう使うか

４.基本構造

SQL文を実行する

変数宣言をしてその変数にSQL文を実行した結果を代入する

カーソルを作成してカーソルを実行する

５.データ型

６.ストアドプログラム

【プロシージャ】

プロシージャ

【ファンクション】

引数なしファンクション

上記を実行するsql

引数ありファンクション

７.パッケージ

パッケージ

パッケージボディ

８.資料についてのまとめ

参考文献資料

ORACLE PL/SQLとは　〜初学者向け入門編〜