PL/SQLプログラムの基礎

PL/SQLプログラムの基礎 
 
わたしは昔勉強しただけであり、このページは勉強のまとめとして作成しました。
よって、実務的なご質問には応じられませんし、もう忘れました。ご了承ください。
------------------------------------------------------------------------
モジュールの基礎 

１．モジュールとは
モジュールには２種類あります。
１つはプロシージャ（PROCEDURE）、俗に言う、サブルーチンの事です。
もう一つはファンクション（FUNCTION）、俗に言う、関数の事です。
サブルーチンは、ある特定の作業を行います。
ファンクションは、ある特定の作業を行った後に、一つの回答を出します。
例えば、「食べる」という処理があるとします。これがサブルーチンなら、食べるとい
う作業を行うだけですが、これがファンクションなら、食べるという作業に対し、その評
価として「うまかった」もしくは「まずかった」などを回答します。

２．モジュールタイプ
モジュールには２つのタイプがあります。
１つはPROCEDURE（プロシージャ）、もう１つはFUNCTION（ファンクション）です。

３．モジュール名称
モジュールは固有の名称を持ちます。
ユーザプログラマがモジュールを扱う場合、このモジュール名称を使います。

４．パラメータリスト（引数）
モジュールは、ゼロ以上複数のパラメータ(引数)を受け取り、基本的に同じ作業を、
毎回微妙に違う感じで行います。
例えば、「なめる」と言うモジュールがあったとします。
モジュール「なめる」に対し、パラメータ「５番」を渡して実行させると、
５番をなめます。
モジュール「なめる」に対し、パラメータ「３番」「７回」を渡して実行させると、
３番を７回もなめます。

５．リターン値（戻り値、返り値）
関数（ファンクション）は、それ自体が１つの値を持ちます。
例えば、関数「なめる」がなめた感想を返す、とします。その関数に対し、パラメータ
「４番」を渡して実行させると、関数「なめる」は「うまかった」もしくは「まずかった」
「好みじゃない」などの戻り値を返します。
------------------------------------------------------------------------
------------------------------------------------------------------------
データ型の基礎 
データ型は、文字や数字を扱う場合に、その種類を明示しておくもので、
主に文字列型と数値型や、特殊なアンカー属性、
ユーザプログラマが作成するユーザ型などがあります。

１．一般的な型
１）VARCHAR2
文字型です。文字列を扱います。最大文字列数は4000文字ぐらいです。
VARCHARのヴァージョンアップ版だから2といいます。
兄弟として、CHAR、VARCHAR、LONGなどがあります。

２）NUMBER
数値型です。数値を扱います。
整数、実数、正の数、負の数、何でもありです。
兄弟として、FLOATなどがあります。

３）BINARY_INTEGER
正の整数のみ扱います。
BINARYは2進数、INTEGERは整数の意味です。

４）BOOLEAN
TRUEまたはFALSEの値を持ちます。TRUEを真、FALSEを偽という事もあります。
TRUEの値は-1（フルビットオン）、FALSEは0（フルビットオフ）です。

５）テーブル型
データベースのテーブルの型です。
テーブル名称%ROWTYPEとして使用します。
 
２．アンカー属性
アンカー属性は、TYPEとROWTYPEの２種類があり、他のデータを参照してそのデータ型
をコピーするという、特殊な方です。どちらも頭に%をつけて使用します。
これを用いれば、「あのテーブルのこの項目を受け取る型」と言う形で
宣言する事ができます。
TYPEは、普通の変数名に用います。
ROWTYPEは、テーブル自体をさすなど、ちょっと変わった使い方をします。
変数名称%TYPEとか、テーブル名称%ROWTYPEと言う形で使います。
 
３．ユーザ定義型
ユーザプログラマが勝手に作る事のできる型の事です。
SUBTYPE ユーザ定義型名称 IS 型名称 [ := 初期値 ];　と記述します。
ユーザ定義型名称には、型の特徴をあらわす名称を自由に付けます。
型名称には、VARCHAR2などの既存の型のほか、TYPE等のアンカー属性を用いて、
特定の型に合わせる事もできます。
また、書式は違いますがユーザプログラマが任意に作製できるレコードの型があります。
　RECODE ( 項目名 型名 [, 項目名 型名・・・ ] )
レコード型は、任意のレコードの形を作成するものです。
主に、カーソルのリターン値やその受け取り、それらの宣言などに使われます。

------------------------------------------------------------------------
------------------------------------------------------------------------
モジュール定義の基礎 
１．モジュールの構造
モジュールは大きく分けて（1）ヘッダー、（2）宣言部、（３）本体部、（４）例外部
で構成されています。
まずモジュールの識別名称があり、次にモジュールが動作する環境を整え、
そしてモジュールが実際に動作する内容が来て、
最後には、もしエラーが起きた場合の例外処理を、
あらかじめ決めておく、という流れで構成されます。

１）モジュールヘッダー
１／モジュールタイプ
PROCEDUREかFUNCTIONかのどちらかを指定します。
２／モジュール名称
ユーザプログラマが任意に指定します。
名称は他のモジュールと重ならず、機能を表すわかり易い名前をつけます。
３／パラメータリスト（引数）
モジュールが受け取るパラメータの、名称とタイプと型と初期値を、
パラメータの数の分だけ指定します。
名称と型は必須で、タイプ型と初期値は省略できます。
タイプとは、そのパラメータが入力用か出力用か、
その両方かを示し、省略すると入力用として認識されます。
初期値は、タイプが入力用か入出力用のときのみ指定できます。
これを省略すると、初期化はされません。
４／リターン値（返り値、戻り値）
モジュールタイプがFUNCTION（関数）のとき、関数が返す返り値の型を指定します。
ファンクションは、返り値の型を省略できません。

２）モジュールの宣言部
モジュール内で使用する、変数や定数、ユーザ定義型、
カーソルやローカルモジュールを宣言します。
これらは、このモジュール独自のもので、外から参照する事はできません。

３）モジュールの本体部
モジュールの動作を記述します。
仕様で宣言しておいた変数やカーソルを用い、目的の作業をします。

４）モジュールの例外部
モジュールの本体にてエラーが発生した場合、自動的にこのプログラムへ制御が移ります。
エラー時の例外処理を記述しておきます。

２．モジュールの仕様
( FUNCTION | PROCEDURE )
　関数名称(
　　[ 引数名称 [ IN | OUT | IN OUT ] 型 [ ( := | DEFAULT ) 初期値 ]
　　[, 引数名称・・・] ] ) RETURN 型
IS
　関数内で使用する変数やカーソル等の宣言
BEGIN
　関数内部
　RETURN 戻り値
EXCEPTION
　例外(エラー時)の処理
　RETURN 戻り値
END;

３．FUNCTIONとPROCEDUREの違いについて
関数（ファンクション）は、戻り値をもつと言う点だけプロシージャと異なっています。
よって次の２点が、関数（ファンクション）にあってプロシージャにはありません。
１）ヘッダーの最後のRETURN値の型宣言

２）BEGIN区とEXCEPTION区のRETURN文

４．引数について
１）引数のタイプ
引数には３つのタイプがあります。
１／入力用　IN
ユーザプログラマからデータが渡されてきます。
この引数には初期値が指定でき、ユーザプログラマがデータを渡さなかったとき
（省略したとき）、この初期値が採用されます。また、この引数は定数です。
モジュール内にてこの引数の値を参照する事はできますが、
別の値を代入する事はできません。
２／出力用　OUT
ユーザプログラマから、回答を格納する変数が渡されてきます。
ユーザプログラマは、この引数を省略できません。
この引数に、初期値を指定する事はできません。
この引数は特殊な定数です。参照できません。
値の代入はできますが、代入した値は、すぐには反映されません。
それが反映されるのは、モジュールが正常に終了し、
制御がユーザプログラマに移ったときです。
モジュールが例外処理で終了した場合は、
事前に代入を行っていても、その値は反映されません。
この引数に代入できるデータ型は、変数のみです。
定数やリテラル値、式の代入はできません。
３／入出力用　IN OUT
ユーザプログラマから、回答を格納する変数が渡されてきます。
ユーザプログラマは、この引数を省略できません。
この引数には、初期値を指定する事ができます。
その場合、ユーザプログラマが指定した回答を格納する
変数にあらかじめ設定されていた値は、初期値によって更新されます。
この引数は、参照可能であり、代入可能であり、代入はすぐに反映されます。
つまりモジュール内にて、普通の変数と同じように扱う事ができます。

２）仮パラメータと実パラメータについて
ユーザの立場からしてみて、ユーザが指定するパラメータを「実パラメータ」、
モジュール内部で扱われるパラメータを「仮パラメータ」といいます。

３）引数の位置の指定について
関数やプロシージャを呼び出す際にカッコの中に指定する変数や値の事を
パラメータといいますが、その並び方について、PL/SQLでは２種類の方法があります。
１／位置指定法
通常、他の言語でも、関数に対する引数の順番は完全に決まっており、
ユーザプログラマがこの位置を変える事はまかりなりません。
この時、「左から何番目は何の引数」という事が位置的にきっちりと決まっており、
このようにパラメータが引き渡される事を「位置指定法」といいます。
例えばモジュール「飲みに行く（誰と、どこへ）」というものがあったとします。
この時、「飲みに行く（かおりちゃん、浅草）」とモジュールを実行した場合、
「浅草へかおりちゃんと飲みに行く」と正しく解釈されますが、その順番を変え、
「飲みに行く（銀座、部長）」とモジュールを実行してしまった場合、
その解釈は「部長へ銀座と飲みに行く」となってしまい、おかしくなります。
「位置指定法」は、パラメータの順番を変えてはいけません。
コンパイルエラーになればまだ運が良く、後でバグとして発覚した場合は大変です。
２／名前指定法
通常、他の言語でも、関数に対する引数の順番は完全に決まっており、
ユーザプログラマがこの位置を変える事はまかりなりません。
ですが、PL/SQLでは、これをユーザプログラマが自由に指定できます。
それを「名前指定法」といいます。
例えば「破壊する（何を、どのように）」というモジュールがあったとします。
この時、「破壊する（無茶苦茶、ハードディスク）」とモジュールを実行した場合、
その解釈は「無茶苦茶をハードディスクに破壊する」というものになり、
訳が分かません。
これを、「破壊する（どのように => 無茶苦茶、何を => ハードディスク）」と
実行すると、「何を＝ハードディスクを、どのように＝無茶苦茶に、破壊する」と
正しく解釈されます。
このように、「名前指定法」を用いれば、ちょっとソースが長くなりますが、
引数がモジュールにどのように解釈されるかが、
ソースを見ただけで分かるようになります。
また、引数がたくさんあるモジュールなどは、
よくその引数ごとにコメントをほどこして分かり易くしてあるものを見かけますが、
「名前指定法」を用いれば、その「分かりづらさ」から解決する事となります。
代入演算子は「=>」です。左辺が仮パラメータで、右辺が実パラメータとなります。

５．宣言部にて宣言するもの
モジュールの宣言部には、モジュールの中で扱う変数などのさまざまな部品を宣言して
おきます。
宣言できるものとしては、変数、定数、カーソル、モジュールなどがあります。

１）変数と定数の宣言について
変数名 [ CONSTANT ] 型名 [ := 初期値 ];
モジュールの宣言部で宣言された変数や定数は、モジュールの本体部にて使用できます。
変数名は、ユーザプログラマが自由につけます。
CONSTANTを指定すると、「定数」の定義になります。
型名は、VARCHAR2などの既存の型のほか、テーブル名．項目名%TYPEや、
テーブル%ROWTYPEなどのアンカー属性を後いて指定する事もできます。
初期つをつけるかどうかはユーザプログラマの自由です。
ただし、定数の場合は省略不可です。
定数としてあらかじめ決められた値の事をマジック値といいます。
それに対し、プログラム上に直接記述される値の事をリテラル値といいます。
プログラムの常識として、リテラル値の仕様は極力避け、
定数（マジック値）として宣言しておきます。

２）カーソルの宣言について
宣言
CURSOR カーソル名（
 ［引数名称 [ ( IN | OUT | IN OUT ) ] 型、引数名称・・・］
 ） [ RETURN 型名 ]
IS
 
 SQL文
開く
OPEN カーソル名（［引数名称、引数名称・・・］）;
取得
FETCH カーソル名　[ INTO 返り値受け取り変数 ];
閉じる
CLOSE カーソル名
カーソルとは、SQL文をあらかじめ定義しておくものです。
主に、本体部のソースをすっきりさせるために使用します。
OPENしたとき、宣言されていたSQLが実行され、その回答群がメモリ上に作成されます。
ユーザプログラマはその回答を、FETCH（取ってくる、の意）で取得します。
オープン時には引数を指定する事ができます。
カーソルの定義時にその引数をどのように使うかあらかじめ宣言しておき、
汎用的なカーソルを作成する事ができます。
回答群の数だけFETCHを実行する事ができ、
そのすべての回答をメモリ上から回収し終わったとき、
CLOSEを実行してカーソルの処理を終了します。
回答群の終了は、カーソル名%FOUND が偽（FALSE = IF文の判定に引っかからない）
となるときです。
OPENしていないカーソルをCLOSEしてはいけません。
パッケージ仕様にカーソルを宣言する時、カーソルの仕様を示さねばならないため、
リターン値が必要です。モジュールや使用時に気にする必要はありません。

３）ローカルモジュールの宣言について
モジュールの中にモジュールを作る事ができます。
この時の下位のモジュールのことをローカルモジュールといいます。
モジュールの宣言部に宣言する以外、普通にモジュールを宣言するのと変わりません。
モジュールの中からしか参照できないという点以外、宣言および使用法は
普通のモジュールと変わりありません。

４）モジュールのオーバーロードについて
モジュールは、いくつか同じ名称を持つものを作成する事ができます。
同じモジュール名称で動作がそれぞれ違うという事ですが、
その使用目的は、例えば、「あいさつ（誰に）」「あいさつ（何回）」
「あいさつ（どんな）」というモジュールがあったとします。
この時、「あいさつ（部長）、あいさつ（１）、あいさつ（丁寧）」という感じで
実行した場合、「部長にあいさつをする、１回あいさつをする、丁寧にあいさつをする」
と解釈されます。
外見上は同じ「あいさつ」というモジュールが、パラメータを見て違う動作をして
いるように見えますが、実際にその振分を行っているのはコンパイラであり、
プログラマは、ご苦労にも３種類の、
それぞれパラメータの異なる「あいさつ」というモジュールを作成しているのです。
このように、同じ名称のモジュールが複数あって、それぞれ動作が違っているもの
を「モジュールのオーバーロード」といいます。

１／パラメータのデータ型グループの相違
コンパイラは、これらの同じ名称を持つモジュールを、パラメータで区別します。
よって、オーバーロードされているモジュールのパラメータは、少なくともその
１つの型が違っていなくてはいけません。
VARCHARとVARCHAR2という違いはだめです。
パラメータに文字列が指定された場合、
その文字列がCHAR型なのかVARCHAR型なのかVARCHAR2型なのか、LONG型なのか、
コンパイラが判断しかねるからです。
同等の事が、数値を指定した場合にもいえます。
「オーバーロードされているモジュールの間で、
最低１つのパラメータのデータ型グループが異なっていなければいけません。」
ちなみに、INやOUTのタイプの違いは、データ型の識別には考慮されません。
したがってそれが違うからといって、パラメータの相違とはなりません。
また、RETURNの型が違うというのも却下です。
あくまでパラメータの型がぜんぜん違うというのが前提です。

２／有効範囲が同じである事
たとえ、オーバーロードのパラメータのルールに完全にのっとっていても、
それが有効範囲の異なる場所で宣言されていてはいけません。
例えば、ネストの中にローカルモジュールを宣言しておき、
さらにその下の階層のネストにて同じ名称のモジュールを宣言しようとすると、
コンパイラエラーが発生します。
なぜならば、オーバーロードされるモジュールは同じ階層にあるものという制限があり、
当然階層の違うモジュールの間では、オーバーロードは実現しません。
にも関わらず同じ名称のモジュールを宣言したために、
「オーバーロード関数でもないのに同じモジュール名称が使われた」といって、
コンパイラが怒るのです。

５）ネストについて
モジュールの本体部には、さらに独立した本体部を作成する事ができます。
更にその本体部の中に、更なる本体部を作成する事ができます。
このように、プログラムの中であたかも階層が降りているようなコーディングを
ネストといいます。
PL/SQLにおいて、本体部のネストは、BEGIN～ENDの中にBEGIN～ENDを入れるという
形になります。
PL/SQLでBEGIN～ENDのネストを行う利点として、その中にEXCEPTION（例外処理）
を記述できるという事があります。
「たとえ途中でエラーが起きても次の処理へ移りたい」という場合、
もしもエラーが発生したらその制御は強制的にEXCEPTIONへ、
それがない場合はモジュールが終了してしまいます。
しかしモジュールの中にネストを作り、
そのネストの中にEXCEPTIONを指定してやれば、
エラー時の例外処理がそのネストの内部のみで処理され、
それが終われば次に進むような仕組みを作る事ができます。

------------------------------------------------------------------------
------------------------------------------------------------------------
パッケージの基礎 
パッケージは、モジュールの１ランク上の存在であり、複数の変数や定数、
カーソルやモジュールを含み、それらを管理するものです。
パッケージは、１つのシステムであり、１つのプログラムであり、１つの家です。

１．情報の公開と隠蔽
パッケージが所有するデータには、大きく分けて公開用と秘密用の２種類があります。
複数のデータやファンクションを要し、それぞれに公開用と秘密用があるというものは、
「オブジェクト指向プログラム」と呼ばれるプログラム技法のものであり、
大規模なプログラムアプリケーションの開発で発生する問題に対する解決法です。

１）大規模なアプリケーション開発で発生する問題
不特定多数のプログラマが、同一上にある複数のプログラムまたは同じプログラム
を触るため、データの破壊によるバグが発生します。（良くあります）

２）そこで情報の公開と隠蔽
すべてのデータを公開という形でほうっておくのではなく、適度に隠蔽し、
そのデータに触れる事のできるのは特定のプログラムのみ、ユーザプログラマは、
直接データに触れるのではなくその特定のぷとグラムを通して行うという形を徹底します。
そうする事によって、３日徹夜した挙げ句に「ここ、ここ、ゼロが一個多い」などという
悲惨なバグ修正が、少しは減らす事ができます。

３）めんどくさい
はい、そうです。
手続きというものはそういうものです。
下手に上場しているような会社に行くと、名前と会社名（学校名）と作業場所を
受付に告げ、バッチなどをもらわなければ、不法侵入です。
不法侵入によるデータの破壊を防ぐというのがインターフェースの目的なのです。

２．パッケージの概要
パッケージの構成は、大きく２つに別れます。
１つは、関数などを宣言する「仕様」部分。パッケージ仕様といいます。
もう１つは、仕様で宣言された関数などを実際に記述する「本体」部分です。
パッケージ仕様は、パブリック要素がないなど特に必要のない場合は、省略できます。

３．パッケージの構文
仕様部分に宣言するカーソルや関数、プロシージャは、単なる宣言のみで、
プログラム本体は含みません。
仕様の変数と本体の変数では、同じ名前は使用できません。
本体にあるBEGIN区は、パッケージ自体の実行部であり、
パッケージ自身の初期化として変数の初期化などに使用します。
パッケージ仕様
PACKAGE パッケージ名称
IS
　[ 変数館の宣言 ]
　[ カーソルやモジュールの宣言（仕様の宣言のみ） ]
END [ パッケージ名称 ];
パッケージ本体
PACKAGE BODY パッケージ名称
IS
　[ 変数と型の宣言 ]
　[ カーソルの宣言(本体) ]
　[ BEGIN
　　実行文 ]
[ EXCEPTION
　　例外ハンドラ ]
END [ パッケージ名称 ];

４．パブリック要素とプライベート要素
パッケージの外から参照できるものをパブリック要素、
パッケージの中の関数等からしか参照できないものをプライベート要素と呼びます。
パブリック要素は公開され、プライベート要素は非公開です。
パッケージにおいて、仕様にて宣言されたものがパブリック要素として外に公開され、
本体にて宣言されたものがプライベート要素として外には公開されません。
ただし、関数やプロシージャは、パブリックだろうがプライベートだろうが、
その実体は共にパッケージの本体にあります。

------------------------------------------------------------------------
------------------------------------------------------------------------
アプリケーションの構成 

１．全体図
 

２．各データの有効範囲
アプリケーションの中で、変数やモジュールなどの有効範囲は、４階層に分かれます。
１）第１階層　パッケージ仕様
ここで宣言されたデータを、「グローバルなパブリックデータ」といいます。
アプリケーション内のすべての階層から参照する事ができます。

２）第２階層　パッケージ本体
パッケージの仕様と本体は１体１の関係ですが、
仕様で宣言した場合と違って本体で宣言した場合は、
「グローバルなプライベートデータ」といいます。
第２階層以下の同じパッケージ本体にあるすべてのプログラムから参照できます。

３）第３階層　モジュール
モジュールの宣言部で宣言されたデータを、「プライベートデータ」といいます。
第３階層以下の同じモジュールに属するすべてのプログラムから参照できます。

４）第４階層以下　無名ブロック（と、そのネスト達）
無名ブロックの宣言部で宣言されたデータは、「ローカルデータ」といいます。
第４階層以下の同じブロックに属するすべてのプログラムから参照できます。

３．データアクセスルーチン
階層や所属の違うデータにアクセスする時は、要注意です。
下の階層へは元々アクセスできないので関係ないですが、上の階層のデータへは、
簡単にできてしまいます。
しかし、そのデータがいつまでも同じ姿でいるという保証はありません。
プログラムはヴァージョンアップするものです。アプリケーションというのは、
常に変更され続ける生物です。
そこで、そのデータが宣言されている同じ所属同じ階層に、
特定のデータを操作する為の専用のファンクション（関数）を用意し、
データへのアクセスはこの受付を通すようにします。
この様なプログラムの事を、プログラムインターフェースといいますが、
特にこの様なファンクションを、「データアクセスルーチン」（受付のお姉さん）
といいます。
たとえデータの仕様が変わろうとも、「お姉さんを修正すればそれで終わり」という
状況を作る事ができます。
　もしも・・・

１）「お姉さん」を作っておらず、さまざまなプログラムが勝手にデータに
直接アクセスしていたとします。

２）データの仕様が変わらざるをえなくなったとします。

３）その修正個所が１０００を超えるとします。
（大きなものならば、これぐらいはありえます。）

４）さらに、２万行のプログラムを３つ渡されて、「見といて」と気軽に言われるかも
知れません。

５）「部長、プログラマの○○君が出社してきません・・・」

------------------------------------------------------------------------
------------------------------------------------------------------------