Plsql-quick-guide

提供:Dev Guides
移動先:案内検索

PL/SQL-概要

PL/SQLプログラミング言語は、SQLおよびOracleリレーショナル・データベースの手続き型拡張言語として1980年代後半にOracle Corporationによって開発されました。 以下は、PL/SQLに関する特定の注目すべき事実です-

  • PL/SQLは、完全に移植可能な高性能トランザクション処理言語です。
  • PL/SQLは、組み込みの、解釈された、OSに依存しないプログラミング環境を提供します。
  • PL/SQLは、コマンドライン SQL Plusインタフェース*から直接呼び出すこともできます。
  • データベースへの外部プログラミング言語の呼び出しから直接呼び出すこともできます。
  • PL/SQLの一般的な構文は、ADAおよびPascalプログラミング言語の構文に基づいています。
  • Oracleの他に、PL/SQLは* TimesTenインメモリデータベース*および IBM DB2 で利用できます。

PL/SQLの機能

PL/SQLには次の機能があります-

  • PL/SQLはSQLと密接に統合されています。
  • 広範なエラーチェックを提供します。
  • 多数のデータ型を提供します。
  • さまざまなプログラミング構造を提供します。
  • 関数と手順を介して構造化プログラミングをサポートします。
  • オブジェクト指向プログラミングをサポートしています。
  • Webアプリケーションとサーバーページの開発をサポートします。

PL/SQLの利点

PL/SQLには次の利点があります-

  • SQLは標準のデータベース言語であり、PL/SQLはSQLと強力に統合されています。 PL/SQLは、静的SQLと動的SQLの両方をサポートしています。 静的SQLは、PL/SQLブロックからのDML操作とトランザクション制御をサポートします。 ダイナミックSQLでは、SQLを使用してPL/SQLブロックにDDLステートメントを埋め込むことができます。
  • PL/SQLでは、文のブロック全体を一度にデータベースに送信できます。 これにより、ネットワークトラフィックが削減され、アプリケーションのパフォーマンスが向上します。
  • PL/SQLは、データベース内のデータをクエリ、変換、および更新できるため、プログラマに高い生産性を提供します。
  • PL/SQLは、例外処理、カプセル化、データ隠蔽、オブジェクト指向データ型などの強力な機能により、設計とデバッグの時間を節約します。
  • PL/SQLで書かれたアプリケーションは完全に移植可能です。
  • PL/SQLは高いセキュリティレベルを提供します。
  • PL/SQLは、事前定義されたSQLパッケージへのアクセスを提供します。
  • PL/SQLは、オブジェクト指向プログラミングのサポートを提供します。
  • PL/SQLは、Webアプリケーションおよびサーバーページの開発をサポートします。

PL/SQL-環境設定

この章では、PL/SQLの環境設定について説明します。 PL/SQLはスタンドアロンのプログラミング言語ではありません。 Oracleプログラミング環境内のツールです。 SQL Plus *は、コマンドプロンプトでSQLおよびPL/SQLステートメントを入力できる対話型ツールです。 これらのコマンドは、処理のためにデータベースに送信されます。 ステートメントが処理されると、結果が送り返され、画面に表示されます。

PL/SQLプログラムを実行するには、マシンにOracle RDBMSサーバーをインストールする必要があります。 これにより、SQLコマンドの実行が処理されます。 Oracle RDBMSの最新バージョンは11gです。 次のリンクからOracle 11gの試用版をダウンロードできます-

Oracle 11g Express Editionをダウンロード

オペレーティングシステムごとに、32ビットバージョンまたは64ビットバージョンのインストールをダウンロードする必要があります。 通常、2つのファイルがあります。 64ビットバージョンをダウンロードしました。 また、オペレーティングシステムで同様の手順を使用します。LinuxまたはSolarisのどちらでもかまいません。

  • win64_11gR2_database_1of2.zip
  • win64_11gR2_database_2of2.zip

上記の2つのファイルをダウンロードした後、あなたはそれらを単一のディレクトリ*データベース*に解凍する必要があり、その下に次のサブディレクトリがあります-

Oracleサブディレクトリ

ステップ1

ここで、セットアップファイルを使用してOracle Database Installerを起動します。 以下は最初の画面です。 次のスクリーンショットに示すように、メールIDを入力してチェックボックスをオンにすることができます。 [次へ]ボタンをクリックします。

Oracle Install 1

ステップ2

次の画面が表示されます。チェックボックスをオフにし、[続行]ボタンをクリックして続行します。

Oracleインストールエラー

ステップ3

ラジオボタンを使用して最初のオプション[データベースの作成と設定]を選択し、[次へ]ボタンをクリックして続行します。

Oracle Install 2

ステップ4

学習の基本的な目的のためにOracleをインストールし、PCまたはラップトップにインストールすることを想定しています。 したがって、 Desktop Class オプションを選択し、 Next ボタンをクリックして続行します。

Oracle Install 3

ステップ5

Oracleサーバーをインストールする場所を指定します。 Oracle Base を変更するだけで、他の場所が自動的に設定されます。 パスワードも入力する必要があります。これはシステムDBAによって使用されます。 必要な情報を入力したら、[次へ]ボタンをクリックして続行します。

Oracle Install 4

ステップ6

もう一度、[次へ]ボタンをクリックして続行します。

Oracle Install 5

ステップ7

[完了]ボタンをクリックして続行します。これにより、実際のサーバーのインストールが開始されます。

Oracle Install 6

ステップ8

Oracleが必要な構成の実行を開始するまで、これには少し時間がかかります。

Oracle Install 7

ステップ9

ここで、Oracleのインストールにより、必要な構成ファイルがコピーされます。 これには少し時間がかかります-

Oracle構成

ステップ10

データベースファイルがコピーされると、次のダイアログボックスが表示されます。 OK ボタンをクリックして出てください。

Oracle構成

手順11

インストール時に、次の最終ウィンドウが表示されます。

Oracle Install 8

最終段階

ここで、インストールを検証します。 Windowsを使用している場合は、コマンドプロンプトで次のコマンドを使用します-

sqlplus "/as sysdba"

あなたはあなたのPL/SQLコマンドとスクリプトを書くSQLプロンプトを持っている必要があります-

PL/SQLコマンドプロンプト

テキストエディタ

コマンドプロンプトから大きなプログラムを実行すると、不注意で作業の一部が失われる可能性があります。 コマンドファイルを使用することを常にお勧めします。 コマンドファイルを使用するには-

  • メモ帳、メモ帳+、*または *EditPlus などのテキストエディタでコードを入力します。
  • ホームディレクトリに .sql 拡張子を付けてファイルを保存します。
  • PL/SQLファイルを作成したディレクトリから SQL Plusコマンドプロンプト*を起動します。 SQL Plusコマンドプロンプトで @ file_name と入力して、プログラムを実行します。

ファイルを使用してPL/SQLスクリプトを実行していない場合は、PL/SQLコードをコピーして、SQLプロンプトを表示する黒いウィンドウを右クリックします。 paste オプションを使用して、コマンドプロンプトで完全なコードを貼り付けます。 最後に、 Enter を押してコードを実行します(まだ実行されていない場合)。

PL/SQL-基本構文

この章では、*ブロック構造*言語であるPL/SQLの基本構文について説明します。これは、PL/SQLプログラムが分割され、コードの論理ブロックに書き込まれることを意味します。 各ブロックは3つのサブパートで構成されています-

S.No Sections & Description
1

Declarations

このセクションは、キーワード DECLARE で始まります。 これはオプションのセクションであり、プログラムで使用されるすべての変数、カーソル、サブプログラム、およびその他の要素を定義します。

2

Executable Commands

このセクションは、キーワード BEGINEND で囲まれ、必須のセクションです。 プログラムの実行可能なPL/SQL文で構成されます。 少なくとも1行の実行可能なコードが必要です。これは、何も実行しないことを示す* NULLコマンド*である場合があります。

3

Exception Handling

このセクションは、キーワード EXCEPTION で始まります。 このオプションセクションには、プログラムのエラーを処理する* exception(s)*が含まれます。

すべてのPL/SQL文はセミコロン(;)で終わります。 PL/SQLブロックは、 BEGIN および END を使用して他のPL/SQLブロック内にネストできます。 以下は、PL/SQLブロックの基本構造です-

DECLARE
   <declarations section>
BEGIN
   <executable command(s)>
EXCEPTION
   <exception handling>
END;

「Hello World」の例

DECLARE
   message  varchar2(20):= 'Hello, World!';
BEGIN
   dbms_output.put_line(message);
END;
/
*end;* 行は、PL/SQLブロックの終了を示します。 SQLコマンドラインからコードを実行するには、コードの最後の行の後の最初の空白行の先頭に/を入力する必要がある場合があります。 上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-
Hello World

PL/SQL procedure successfully completed.

PL/SQL識別子

PL/SQL識別子は、定数、変数、例外、プロシージャ、カーソルおよび予約語です。 識別子は、オプションでさらに文字、数字、ドル記号、アンダースコア、シャープ記号が続く文字で構成され、30文字を超えてはなりません。

デフォルトでは、識別子は大文字と小文字を区別しません。 したがって、 integer または INTEGER を使用して数値を表すことができます。 予約済みのキーワードを識別子として使用することはできません。

PL/SQLデリミタ

区切り記号は、特別な意味を持つ記号です。 以下は、PL/SQLの区切り文字のリストです-

Delimiter Description
+, -, *,/ Addition, subtraction/negation, multiplication, division
% Attribute indicator
'' Character string delimiter
. Component selector
(,) Expression or list delimiter
: Host variable indicator
, Item separator
" Quoted identifier delimiter
= Relational operator
@ Remote access indicator
; Statement terminator
:= Assignment operator
Association operator
*
* Concatenation operator
** Exponentiation operator
<<, >> Label delimiter (begin and end)
/, / Multi-line comment delimiter (begin and end)
-- Single-line comment indicator
.. Range operator
<, >, ⇐, >= Relational operators
*<>, '=, ~=, ^= * Different versions of NOT EQUAL

PL/SQLのコメント

プログラムのコメントは、記述したPL/SQLコードに含めることができる説明文であり、ソースコードを読む人を支援します。 すべてのプログラミング言語では、何らかの形式のコメントが許可されています。

PL/SQLは、単一行および複数行のコメントをサポートしています。 コメント内で使用可能なすべての文字は、PL/SQLコンパイラによって無視されます。 PL/SQLの単一行コメントは区切り文字-(二重ハイフン)で始まり、複数行コメントは/* および*/で囲まれています。

DECLARE
   -- variable declaration
   message  varchar2(20):= 'Hello, World!';
BEGIN
  /*
  * PL/SQL executable statement(s)
   */
   dbms_output.put_line(message);
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Hello World

PL/SQL procedure successfully completed.

PL/SQLプログラムユニット

PL/SQLユニットは、次のいずれかです-

  • PL/SQLブロック
  • 関数
  • パッケージ
  • パッケージ本体
  • 手順
  • 引き金
  • Type
  • タイプ本体

これらの各ユニットについては、以降の章で説明します。

PL/SQL-データ型

この章では、PL/SQLのデータ型について説明します。 PL/SQL変数、定数およびパラメータには、格納形式、制約および値の有効範囲を指定する有効なデータ型が必要です。 この章では、 SCALAR および LOB データ型に焦点を当てます。 他の2つのデータ型については、他の章で説明します。

S.No Category & Description
1

Scalar

  • NUMBER、DATE、、 *BOOLEAN など、内部コンポーネントのない単一の値。
2

Large Object (LOB)

テキスト、グラフィックイメージ、ビデオクリップ、サウンド波形など、他のデータ項目とは別に保存される大きなオブジェクトへのポインター。

3

Composite

個別にアクセスできる内部コンポーネントを持つデータ項目。 たとえば、コレクションとレコード。

4

Reference

他のデータ項目へのポインター。

PL/SQLスカラーのデータ型とサブタイプ

PL/SQLスカラーデータ型とサブタイプは、次のカテゴリに分類されます-

S.No Date Type & Description
1

Numeric

算術演算が実行される数値。

2

Character

単一の文字または文字列を表す英数字の値。

3

Boolean

論理演算が実行される論理値。

4

Datetime

日付と時刻。

PL/SQLは、データ型のサブタイプを提供します。 たとえば、NUMBERデータ型にはINTEGERというサブタイプがあります。 PL/SQLプログラムのサブタイプを使用して、データ型を他のプログラムのデータ型と互換性を持たせ、PL/SQLコードをJavaプログラムなどの別のプログラムに埋め込むことができます。

PL/SQL数値データ型とサブタイプ

次の表は、PL/SQLの事前定義された数値データ型とそのサブタイプを示しています-

S.No Data Type & Description
1

PLS_INTEGER

32ビットで表される-2,147,483,648から2,147,483,647の範囲の符号付き整数

2

BINARY_INTEGER

32ビットで表される-2,147,483,648から2,147,483,647の範囲の符号付き整数

3

BINARY_FLOAT

単精度IEEE 754形式の浮動小数点数

4

BINARY_DOUBLE

倍精度IEEE 754形式の浮動小数点数

5

NUMBER(prec, scale)

絶対値が1E-130から1.0E126の範囲(ただし、含まない)である固定小数点または浮動小数点数。 NUMBER変数は0を表すこともできます

6

DEC(prec, scale)

38桁の10進数の最大精度を持つANSI固有の固定小数点型

7

DECIMAL(prec, scale)

38桁の10進数の最大精度を持つIBM固有の固定小数点型

8

NUMERIC(pre, secale)

最大精度が10進数38桁の浮動小数点型

9

DOUBLE PRECISION

126桁の最大精度(10進数で約38桁)のANSI固有の浮動小数点型

10

FLOAT

126桁の2進数(約38桁の10進数)の精度を持つANSIおよびIBM固有の浮動小数点型

11

INT

38桁の10進数の最大精度を持つANSI固有の整数型

12

INTEGER

38桁の10進数の最大精度を持つANSIおよびIBM固有の整数型

13

SMALLINT

38桁の10進数の最大精度を持つANSIおよびIBM固有の整数型

14

REAL

最大精度63桁の2進数(10進数で約18桁)の浮動小数点型

以下は有効な宣言です-

DECLARE
   num1 INTEGER;
   num2 REAL;
   num3 DOUBLE PRECISION;
BEGIN
   null;
END;
/

上記のコードをコンパイルして実行すると、次の結果が生成されます-

PL/SQL procedure successfully completed

PL/SQLの文字データ型とサブタイプ

以下は、PL/SQLの事前定義された文字データ型とそのサブタイプの詳細です-

S.No Data Type & Description
1

CHAR

最大サイズが32,767バイトの固定長文字列

2

VARCHAR2

最大サイズが32,767バイトの可変長文字列

3

RAW

PL/SQLで解釈されない最大サイズが32,767バイトの可変長バイナリまたはバイト文字列

4

NCHAR

最大サイズ32,767バイトの固定長国別文字ストリング

5

NVARCHAR2

最大サイズが32,767バイトの可変長国別文字列

6

LONG

最大サイズが32,760バイトの可変長文字列

7

LONG RAW

PL/SQLで解釈されない最大サイズが32,760バイトの可変長バイナリまたはバイト文字列

8

ROWID

物理行識別子、通常のテーブルの行のアドレス

9

UROWID

ユニバーサル行識別子(物理、論理、または外部行識別子)

PL/SQLブールデータ型

*BOOLEAN* データ型には、論理演算で使用される論理値が格納されます。 論理値は、ブール値 *TRUE* および *FALSE* および値 *NULL* です。

ただし、SQLにはBOOLEANに相当するデータ型はありません。 したがって、ブール値を使用することはできません-

  • SQL文
  • 組み込みSQL関数( TO_CHAR など)
  • SQLステートメントから呼び出されるPL/SQL関数

PL/SQLの日時および間隔タイプ

*DATE* データ型は、午前0時からの秒単位の時刻を含む固定長の日付時刻を格納するために使用されます。 有効な日付の範囲は、紀元前4712年1月1日から西暦9999年12月31日までです。

デフォルトの日付形式は、Oracle初期化パラメータNLS_DATE_FORMATによって設定されます。 たとえば、デフォルトは「DD-MON-YY」で、月の日を表す2桁の数字、月の名前の略語、年の下2桁が含まれます。 たとえば、01-OCT-12。

各日付には、世紀、年、月、日、時、分、秒が含まれます。 次の表は、各フィールドの有効な値を示しています-

Field Name Valid Datetime Values Valid Interval Values
YEAR -4712 to 9999 (excluding year 0) Any nonzero integer
MONTH 01 to 12 0 to 11
DAY 01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the calendar for the locale) Any nonzero integer
HOUR 00 to 23 0 to 23
MINUTE 00 to 59 0 to 59
SECOND 00 to 59.9(n), where 9(n) is the precision of time fractional seconds 0 to 59.9(n), where 9(n) is the precision of interval fractional seconds
TIMEZONE_HOUR -12 to 14 (range accommodates daylight savings time changes) Not applicable
TIMEZONE_MINUTE 00 to 59 Not applicable
TIMEZONE_REGION Found in the dynamic performance view V$TIMEZONE_NAMES Not applicable
TIMEZONE_ABBR Found in the dynamic performance view V$TIMEZONE_NAMES Not applicable

PL/SQLラージオブジェクト(LOB)データ型

ラージオブジェクト(LOB)データ型は、テキスト、グラフィックイメージ、ビデオクリップ、音声波形などの大きなデータ項目を指します。 LOBデータ型を使用すると、このデータに効率的かつランダムに区分的にアクセスできます。 以下は、事前定義されたPL/SQL LOBデータ型です-

Data Type Description Size
BFILE Used to store large binary objects in operating system files outside the database. System-dependent. Cannot exceed 4 gigabytes (GB).
BLOB Used to store large binary objects in the database. 8 to 128 terabytes (TB)
CLOB Used to store large blocks of character data in the database. 8 to 128 TB
NCLOB Used to store large blocks of NCHAR data in the database. 8 to 128 TB

PL/SQLユーザー定義サブタイプ

サブタイプは別のデータ型のサブセットであり、そのベース型と呼ばれます。 サブタイプには基本タイプと同じ有効な操作がありますが、有効な値のサブセットのみです。

PL/SQLは、パッケージ STANDARD のいくつかのサブタイプを事前定義します。 たとえば、PL/SQLは次のようにサブタイプ CHARACTERINTEGER を事前定義します-

SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38,0);

独自のサブタイプを定義して使用できます。 次のプログラムは、ユーザー定義のサブタイプの定義と使用を示しています-

DECLARE
   SUBTYPE name IS char(20);
   SUBTYPE message IS varchar2(100);
   salutation name;
   greetings message;
BEGIN
   salutation := 'Reader ';
   greetings := 'Welcome to the World of PL/SQL';
   dbms_output.put_line('Hello ' || salutation || greetings);
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Hello Reader Welcome to the World of PL/SQL

PL/SQL procedure successfully completed.

PL/SQLのNULL

PL/SQL NULL値は*欠落*または*不明データ*を表し、整数、文字、またはその他の特定のデータ型ではありません。 NULL は、空のデータ文字列またはNULL文字値 '\ 0' と同じではないことに注意してください。 nullを割り当てることはできますが、nullを含めて何かと同一視することはできません。

PL/SQL-変数

この章では、Pl/SQLの変数について説明します。 変数は、プログラムが操作できるストレージ領域に付けられた名前に他なりません。 PL/SQLの各変数には、変数のメモリーのサイズとレイアウトを決定する特定のデータ型があります。そのメモリ内に保存できる値の範囲と、変数に適用できる一連の操作。

PL/SQL変数の名前は、オプションでさらに文字、数字、ドル記号、アンダースコア、シャープ記号が続く文字で構成され、30文字を超えてはなりません。 デフォルトでは、変数名は大文字と小文字が区別されません。 予約されたPL/SQLキーワードを変数名として使用することはできません。

PL/SQLプログラミング言語では、日時データ型、レコード、コレクションなど、さまざまなタイプの変数を定義できます。 これについては、以降の章で説明します。 この章では、基本的な変数タイプのみを学習します。

PL/SQLでの変数宣言

PL/SQL変数は、宣言セクションまたはパッケージでグローバル変数として宣言する必要があります。 変数を宣言すると、PL/SQLは変数の値にメモリーを割り当て、ストレージの場所は変数名で識別されます。

変数を宣言するための構文は-

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

ここで、_variable_name_はPL/SQLの有効な識別子であり、_datatype_は有効なPL/SQLデータ型、または前の章ですでに説明したユーザー定義のデータ型でなければなりません。 有効な変数宣言とその定義を以下に示します-

sales number(10, 2);
pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);

データ型にサイズ、スケール、または精度の制限を指定すると、それは*制約付き宣言*と呼ばれます。 制約付き宣言は、制約なし宣言よりも少ないメモリで済みます。 たとえば-

sales number(10, 2);
name varchar2(25);
address varchar2(100);

PL/SQLでの変数の初期化

変数を宣言するたびに、PL/SQLはその変数にデフォルト値のNULLを割り当てます。 あなたがNULL値以外の値で変数を初期化する場合は、次のいずれかを使用して、宣言中に行うことができます-

  • DEFAULT キーワード
  • assignment 演算子

たとえば-

counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Have a Good Day';
*NOT NULL* 制約を使用して、変数が *NULL* 値を持たないように指定することもできます。 NOT NULL制約を使用する場合、その変数の初期値を明示的に割り当てる必要があります。

そうでない場合は、変数を適切に初期化することをお勧めします。プログラムによっては、予期しない結果が生じる場合があります。 さまざまなタイプの変数を利用する次の例を試してください-

DECLARE
   a integer := 10;
   b integer := 20;
   c integer;
   f real;
BEGIN
   c := a + b;
   dbms_output.put_line('Value of c: ' || c);
   f := 70.0/3.0;
   dbms_output.put_line('Value of f: ' || f);
END;
/

上記のコードが実行されると、次の結果が生成されます-

Value of c: 30
Value of f: 23.333333333333333333

PL/SQL procedure successfully completed.

PL/SQLの変数スコープ

PL/SQLでは、ブロックをネストできます。つまり、各プログラムブロックに別の内部ブロックを含めることができます。 変数が内部ブロック内で宣言されている場合、外部ブロックからはアクセスできません。 ただし、変数が宣言され、外部ブロックからアクセスできる場合、ネストされたすべての内部ブロックからもアクセスできます。 変数スコープには2種類あります-

  • ローカル変数-内部ブロックで宣言され、外部ブロックからアクセスできない変数。
  • グローバル変数-最も外側のブロックまたはパッケージで宣言された変数。

次の例は、単純な形式で Local および Global 変数の使用法を示しています-

DECLARE
   -- Global variables
   num1 number := 95;
   num2 number := 85;
BEGIN
   dbms_output.put_line('Outer Variable num1: ' || num1);
   dbms_output.put_line('Outer Variable num2: ' || num2);
   DECLARE
      -- Local variables
      num1 number := 195;
      num2 number := 185;
   BEGIN
      dbms_output.put_line('Inner Variable num1: ' || num1);
      dbms_output.put_line('Inner Variable num2: ' || num2);
   END;
END;
/

上記のコードが実行されると、次の結果が生成されます-

Outer Variable num1: 95
Outer Variable num2: 85
Inner Variable num1: 195
Inner Variable num2: 185

PL/SQL procedure successfully completed.

SQLクエリ結果のPL/SQL変数への割り当て

SQLの SELECT INTO ステートメントを使用して、値をPL/SQL変数に割り当てることができます。 * SELECTリスト*の各アイテムについて、* INTOリスト*に対応するタイプ互換の変数が必要です。 次の例は、概念を示しています。 CUSTOMERSという名前のテーブルを作成しましょう-

SQLステートメントについては、リンクを参照してください:/sql/index [SQLチュートリアル]

CREATE TABLE CUSTOMERS(
   ID   INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Table Created

テーブルにいくつかの値を挿入しましょう-

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

次のプログラムは、SQLの* SELECT INTO句*を使用して、上記の表の値をPL/SQL変数に割り当てます-

DECLARE
   c_id customers.id%type := 1;
   c_name  customers.name%type;
   c_addr customers.address%type;
   c_sal  customers.salary%type;
BEGIN
   SELECT name, address, salary INTO c_name, c_addr, c_sal
   FROM customers
   WHERE id = c_id;
   dbms_output.put_line
   ('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal);
END;
/

上記のコードが実行されると、次の結果が生成されます-

Customer Ramesh from Ahmedabad earns 2000

PL/SQL procedure completed successfully

PL/SQL-定数とリテラル

この章では、PL/SQLの*定数*および*リテラル*について説明します。 定数は、一度宣言されるとプログラムで変更されない値を保持します。 定数宣言は、その名前、データ型、および値を指定し、それにストレージを割り当てます。 宣言は* NOT NULL制約*を課すこともできます。

定数を宣言する

定数は、 CONSTANT キーワードを使用して宣言されます。 初期値が必要であり、その値を変更することはできません。 たとえば-

PI CONSTANT NUMBER := 3.141592654;
DECLARE
   -- constant declaration
   pi constant number := 3.141592654;
   -- other declarations
   radius number(5,2);
   dia number(5,2);
   circumference number(7, 2);
   area number (10, 2);
BEGIN
   -- processing
   radius := 9.5;
   dia := radius *2;
   circumference := 2.0* pi *radius;
   area := pi* radius * radius;
   -- output
   dbms_output.put_line('Radius: ' || radius);
   dbms_output.put_line('Diameter: ' || dia);
   dbms_output.put_line('Circumference: ' || circumference);
   dbms_output.put_line('Area: ' || area);
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Radius: 9.5
Diameter: 19
Circumference: 59.69
Area: 283.53

Pl/SQL procedure successfully completed.

PL/SQLリテラル

リテラルは、識別子で表されない明示的な数値、文字、文字列、またはブール値です。 たとえば、TRUE、786、NULL、 'finddevguides’はすべてブール型、数値型、または文字列型のリテラルです。 PL/SQL、リテラルでは大文字と小文字が区別されます。 PL/SQLは、次の種類のリテラルをサポートしています-

  • 数値リテラル
  • 文字リテラル
  • 文字列リテラル
  • ブールリテラル *日付と時刻のリテラル

次の表に、リテラル値のこれらすべてのカテゴリの例を示します。

S.No Literal Type & Example
1
  • Numeric Literals*

050 78 -14 0 +32767

6.6667 0.0 -12.0 3.14159 +7800.00

6E5 1.0E-8 3.14159e0 -1E38 -9.5e-3

2

Character Literals

'A' '%' '9' 'z' '('

3

String Literals

'こんにちは世界!'

「チュートリアルポイント」

「19年11月12日」

4

BOOLEAN Literals

TRUE、FALSE、およびNULL。

5

Date and Time Literals

日付 '1978-12-25';

タイムスタンプ '2012-10-29 12:01:01';

文字列リテラル内に一重引用符を埋め込むには、次のプログラムに示すように、2つの一重引用符を並べて配置します-

DECLARE
   message  varchar2(30):= 'That''s finddevguides.com!';
BEGIN
   dbms_output.put_line(message);
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

That's finddevguides.com!

PL/SQL procedure successfully completed.

PL/SQL-演算子

この章では、PL/SQLの演算子について説明します。 演算子は、特定の数学的または論理的な操作を実行するようコンパイラーに指示する記号です。 PL/SQL言語は、組み込み演算子が豊富であり、次の種類の演算子を提供します-

  • 算術演算子
  • 関係演算子
  • 比較演算子
  • 論理演算子
  • 文字列演算子

ここでは、算術演算子、関係演算子、比較演算子、論理演算子を1つずつ理解します。 文字列演算子については、後の章-* PL/SQL-文字列*で説明します。

算術演算子

次の表に、PL/SQLでサポートされているすべての算術演算子を示します。 *変数A *が10を保持し、*変数B *が5を保持すると仮定します-

リンク:/plsql/plsql_arithmetic_operators [例を表示]

Operator Description Example
+ Adds two operands A + B will give 15
- Subtracts second operand from the first A - B will give 5
* Multiplies both operands A* B will give 50
/ Divides numerator by de-numerator A/B will give 2
* * Exponentiation operator, raises one operand to the power of other A* *B will give 100000

関係演算子

関係演算子は、2つの式または値を比較し、ブール結果を返します。 次の表に、PL/SQLでサポートされるすべての関係演算子を示します。* 変数A *が10を保持し、*変数B *が20を保持すると仮定します-

リンク:/plsql/plsql_relational_operators [例を表示]

オペレーター

説明

=

2つのオペランドの値が等しいかどうかを確認し、等しい場合は条件が真になります。

(A = B)は正しくありません。

!=

<>

~=

2つのオペランドの値が等しいかどうかをチェックし、値が等しくない場合は条件が真になります。

(A!= B)は真です。

>

左のオペランドの値が右のオペランドの値よりも大きいかどうかをチェックし、そうであれば条件が真になります。

(A> B)は正しくありません。

<

左のオペランドの値が右のオペランドの値よりも小さいかどうかを確認し、そうであれば条件が真になります。

(A <B)は真です。

>=

左のオペランドの値が右のオペランドの値以上かどうかをチェックし、はいの場合は条件が真になります。

(A> = B)は正しくありません。

左のオペランドの値が右のオペランドの値以下かどうかをチェックし、そうであれば条件が真になります。

(A ⇐ B)はtrue

比較演算子

比較演算子は、ある式を別の式と比較するために使用されます。 結果は常に TRUE、FALSE または NULL のいずれかです。

リンク:/plsql/plsql_comparison_operators [例を表示]

Operator Description Example
LIKE The LIKE operator compares a character, string, or CLOB value to a pattern and returns TRUE if the value matches the pattern and FALSE if it does not. If 'Zara Ali' like 'Z% A_i' returns a Boolean true, whereas, 'Nuha Ali' like 'Z% A_i' returns a Boolean false.
BETWEEN The BETWEEN operator tests whether a value lies in a specified range. x BETWEEN a AND b means that x >= a and x ⇐ b. If x = 10 then, x between 5 and 20 returns true, x between 5 and 10 returns true, but x between 11 and 20 returns false.
IN The IN operator tests set membership. x IN (set) means that x is equal to any member of set. If x = 'm' then, x in ('a', 'b', 'c') returns Boolean false but x in ('m', 'n', 'o') returns Boolean true.
IS NULL The IS NULL operator returns the BOOLEAN value TRUE if its operand is NULL or FALSE if it is not NULL. Comparisons involving NULL values always yield NULL. If x = 'm', then 'x is null' returns Boolean false.

論理演算子

次の表に、PL/SQLでサポートされる論理演算子を示します。 これらの演算子はすべてブール値オペランドで機能し、ブール値の結果を生成します。 *変数A *がtrueを保持し、*変数B *がfalseを保持すると仮定してみましょう-

リンク:/plsql/plsql_logical_operators [例を表示]

Operator Description Examples
and Called the logical AND operator. If both the operands are true then condition becomes true. (A and B) is false.
or Called the logical OR Operator. If any of the two operands is true then condition becomes true. (A or B) is true.
not Called the logical NOT Operator. Used to reverse the logical state of its operand. If a condition is true then Logical NOT operator will make it false. not (A and B) is true.

PL/SQL演算子の優先順位

演算子の優先順位は、式内の用語のグループ化を決定します。 これは、式の評価方法に影響します。 特定の演算子は、他の演算子よりも優先順位が高くなっています。たとえば、乗算演算子は加算演算子よりも優先順位が高くなります。

たとえば、 x = 7 + 3 2 ;ここでは、演算子*の優先順位が+より高いため、 *x には20ではなく 13 が割り当てられます。したがって、最初に 3 2 で乗算され、 *7 に加算されます。

ここでは、優先順位が最も高い演算子が表の上部に表示され、優先順位が最も低い演算子が下部に表示されます。 式内では、優先順位の高い演算子が最初に評価されます。

演算子の優先順位は、=、<、>、⇐、> =、<>、!=、〜=、^ =、IS NULL、LIKE、BETWEEN、INです。

リンク:/plsql/plsql_operators_precedence [例を表示]

Operator Operation
** exponentiation
+, - identity, negation
*,/ multiplication, division
+, -,
addition, subtraction, concatenation
comparison
NOT logical negation
AND conjunction
OR inclusion

PL/SQL-条件

この章では、PL/SQLの条件について説明します。 意思決定構造では、プログラマーが、プログラムによって評価またはテストされる1つ以上の条件を、条件が真であると判断された場合に実行されるステートメント、およびオプションで、条件は偽と判断されます。

以下は、ほとんどのプログラミング言語で見られる典型的な条件付き(つまり、意思決定)構造の一般的な形式です-

PL/SQLでの意思決定文

PL/SQLプログラミング言語は、次のタイプの意思決定文を提供します。 詳細を確認するには、次のリンクをクリックしてください。

S.No Statement & Description
1

IF - THEN statement

  • IFステートメント*は、キーワード THEN および END IF で囲まれた一連のステートメントに条件を関連付けます。 条件が真の場合、ステートメントが実行され、条件が偽またはNULLの場合、IFステートメントは何もしません。
2

IF-THEN-ELSE statement

  • IFステートメント*は、キーワード ELSE の後にステートメントの代替シーケンスを追加します。 条件がfalseまたはNULLの場合、ステートメントの代替シーケンスのみが実行されます。 これにより、ステートメントのシーケンスのいずれかが実行されます。
3

IF-THEN-ELSIF statement

いくつかの選択肢から選択できます。

4

Case statement

IFステートメントと同様に、* CASEステートメント*は、実行するステートメントのシーケンスを1つ選択します。

ただし、シーケンスを選択するために、CASEステートメントは複数のブール式ではなくセレクターを使用します。 セレクターは、値がいくつかの選択肢の1つを選択するために使用される式です。

5

Searched CASE statement

検索されたCASEステートメントにはセレクタがありません*。WHEN句にはブール値を生成する検索条件が含まれています。

6

nested IF-THEN-ELSE

1つの IF-THEN または IF-THEN-ELSIF ステートメントを別の IF-THEN または IF-THEN-ELSIF ステートメント内で使用できます。

PL/SQL-ループ

この章では、PL/SQLのループについて説明します。 コードのブロックを数回実行する必要がある場合があります。 一般に、ステートメントは順番に実行されます。関数の最初のステートメントが最初に実行され、次に2番目のステートメントが実行されます。

プログラミング言語は、より複雑な実行パスを可能にするさまざまな制御構造を提供します。

ループステートメントを使用すると、ステートメントまたはステートメントのグループを複数回実行できます。ほとんどのプログラミング言語では、ループステートメントの一般的な形式は次のとおりです-

ループアーキテクチャ

PL/SQLには、ループ要件を処理するために次のタイプのループが用意されています。 詳細を確認するには、次のリンクをクリックしてください。

S.No Loop Type & Description
1

PL/SQL Basic LOOP

このループ構造では、一連のステートメントがLOOPステートメントとEND LOOPステートメントで囲まれています。 各反復で、一連のステートメントが実行され、ループの先頭から制御が再開されます。

2

PL/SQL WHILE LOOP

特定の条件が真の間、ステートメントまたはステートメントのグループを繰り返します。 ループ本体を実行する前に条件をテストします。

3

PL/SQL FOR LOOP

一連のステートメントを複数回実行し、ループ変数を管理するコードを短縮します。

4

Nested loops in PL/SQL

別の基本ループ、while、またはforループ内で1つ以上のループを使用できます。

PL/SQLループのラベル付け

PL/SQLループにはラベルを付けることができます。 ラベルは二重山括弧(???)で囲まれ、LOOPステートメントの先頭に表示される必要があります。 ラベル名は、LOOPステートメントの末尾にも表示できます。 EXITステートメントでラベルを使用して、ループを終了できます。

次のプログラムは、概念を示しています-

DECLARE
   i number(1);
   j number(1);
BEGIN
   << outer_loop >>
   FOR i IN 1..3 LOOP
      << inner_loop >>
      FOR j IN 1..3 LOOP
         dbms_output.put_line('i is: '|| i || ' and j is: ' || j);
      END loop inner_loop;
   END loop outer_loop;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

i is: 1 and j is: 1
i is: 1 and j is: 2
i is: 1 and j is: 3
i is: 2 and j is: 1
i is: 2 and j is: 2
i is: 2 and j is: 3
i is: 3 and j is: 1
i is: 3 and j is: 2
i is: 3 and j is: 3

PL/SQL procedure successfully completed.

ループ制御ステートメント

ループ制御ステートメントは、通常のシーケンスから実行を変更します。 実行がスコープを離れると、そのスコープで作成されたすべての自動オブジェクトが破棄されます。

PL/SQLは、次の制御文をサポートしています。 ループにラベルを付けることは、ループ外で制御を行うのにも役立ちます。 詳細を確認するには、次のリンクをクリックしてください。

S.No Control Statement & Description
1

EXIT statement

Exitステートメントはループを完了し、END LOOPの直後に制御がステートメントに渡されます。

2

CONTINUE statement

ループがその本体の残りをスキップし、反復する前にその状態をすぐに再テストします。

3

GOTO statement

ラベル付きステートメントに制御を移します。 ただし、プログラムでGOTOステートメントを使用することはお勧めしません。

PL/SQL-文字列

PL/SQLの文字列は、実際にはオプションのサイズ指定のある文字列です。 文字は、数字、文字、空白、特殊文字、またはすべての組み合わせです。 PL/SQLは3種類の文字列を提供します-

  • 固定長文字列-そのような文字列では、プログラマは文字列を宣言しながら長さを指定します。 文字列は、指定された長さまでスペースで右詰めされます。
  • 可変長文字列-そのような文字列では、文字列の最大長が32,767まで指定され、パディングは行われません。
  • 文字ラージオブジェクト(CLOB)-これらは最大128テラバイトの可変長文字列です。

PL/SQL文字列は、変数またはリテラルのいずれかです。 文字列リテラルは引用符で囲まれています。 例えば、

'This is a string literal.' Or 'hello world'

文字列リテラル内に一重引用符を含めるには、2つの一重引用符を並べて入力する必要があります。 例えば、

'this isn''t what it looks like'

文字列変数の宣言

Oracleデータベースは、CHAR、NCHAR、VARCHAR2、NVARCHAR2、CLOB、NCLOBなどの多数の文字列データ型を提供します。 'N' が前に付いたデータ型は '国別文字セット' データ型で、Unicode文字データを格納します。

可変長文字列を宣言する必要がある場合は、その文字列の最大長を指定する必要があります。 たとえば、VARCHAR2データ型。 次の例は、いくつかの文字列変数の宣言と使用を示しています-

DECLARE
   name varchar2(20);
   company varchar2(30);
   introduction clob;
   choice char(1);
BEGIN
   name := 'John Smith';
   company := 'Infotech';
   introduction := ' Hello! I''m John Smith from Infotech.';
   choice := 'y';
   IF choice = 'y' THEN
      dbms_output.put_line(name);
      dbms_output.put_line(company);
      dbms_output.put_line(introduction);
   END IF;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

John Smith
Infotech
Hello! I'm John Smith from Infotech.

PL/SQL procedure successfully completed

固定長文字列を宣言するには、CHARデータ型を使用します。 ここでは、固定長変数の最大長を指定する必要はありません。 長さの制約を省略すると、Oracle Databaseは必要な最大長を自動的に使用します。 次の2つの宣言は同一です-

red_flag CHAR(1) := 'Y';
 red_flag CHAR   := 'Y';

PL/SQL文字列関数と演算子

PL/SQLには、2つの文字列を結合するための連結演算子*(||)*が用意されています。 次の表は、PL/SQLが提供する文字列関数を提供します-

S.No Function & Purpose
1

ASCII(x);

文字xのASCII値を返します。

2

CHR(x);

ASCII値xの文字を返します。

3

CONCAT(x, y);

文字列xとyを連結し、追加された文字列を返します。

4

INITCAP(x);

xの各単語の最初の文字を大文字に変換し、その文字列を返します。

5

INSTR(x, find_string [, start] [, occurrence]);

xで find_string を検索し、それが発生する位置を返します。

6

INSTRB(x);

別の文字列内の文字列の位置を返しますが、値をバイト単位で返します。

7

LENGTH(x);

xの文字数を返します。

8

LENGTHB(x);

シングルバイト文字セットの文字列の長さをバイト単位で返します。

9

LOWER(x);

xの文字を小文字に変換し、その文字列を返します。

10

LPAD(x, width [, pad_string]) ;

  • x* を左側にスペースで埋めて、文字列の合計長さをwidth文字までにします。
11

LTRIM(x [, trim_string]);

  • x* の左から文字を切り取ります。
12

NANVL(x, value);

xがNaNの特別な値(数値ではない)と一致する場合は値を返し、そうでない場合は x を返します。

13

NLS_INITCAP(x);

NLSSORTで指定された異なるソート方法を使用できることを除いて、INITCAP関数と同じです。

14

NLS_LOWER(x) ;

NLSSORTで指定された異なるソート方法を使用できることを除いて、LOWER関数と同じです。

15

NLS_UPPER(x);

NLSSORTで指定された異なるソート方法を使用できることを除いて、UPPER関数と同じです。

16

NLSSORT(x);

文字のソート方法を変更します。 NLS関数の前に指定する必要があります。そうでない場合は、デフォルトのソートが使用されます。

17

NVL(x, value);

  • x* がnullの場合、値を返します。それ以外の場合、xが返されます。
18

NVL2(x, value1, value2);

xがnullでない場合、value1を返します。 xがnullの場合、value2が返されます。

19

REPLACE(x, search_string, replace_string);

search_stringの x を検索し、replace_stringに置き換えます。

20

RPAD(x, width [, pad_string]);

右側に x を埋め込みます。

21

RTRIM(x [, trim_string]);

右から x を切り取ります。

22

SOUNDEX(x) ;

  • x* の音声表現を含む文字列を返します。
23

SUBSTR(x, start [, length]);

startで指定された位置から始まる x の部分文字列を返します。 サブストリングのオプションの長さを指定できます。

24

SUBSTRB(x);

パラメータがシングルバイト文字システムの文字ではなくバイトで表されることを除いて、SUBSTRと同じです。

25

TRIM([trim_char FROM) x);

  • x* の左右の文字を切り取ります。
26

UPPER(x);

xの文字を大文字に変換し、その文字列を返します。

概念を理解するために、いくつかの例を試してみましょう-

例1

DECLARE
   greetings varchar2(11) := 'hello world';
BEGIN
   dbms_output.put_line(UPPER(greetings));

   dbms_output.put_line(LOWER(greetings));

   dbms_output.put_line(INITCAP(greetings));

  /*retrieve the first character in the string*/
   dbms_output.put_line ( SUBSTR (greetings, 1, 1));

  /*retrieve the last character in the string*/
   dbms_output.put_line ( SUBSTR (greetings, -1, 1));

  /*retrieve five characters,
      starting from the seventh position.*/
   dbms_output.put_line ( SUBSTR (greetings, 7, 5));

  /*retrieve the remainder of the string,
      starting from the second position.*/
   dbms_output.put_line ( SUBSTR (greetings, 2));

  /*find the location of the first "e"*/
   dbms_output.put_line ( INSTR (greetings, 'e'));
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

HELLO WORLD
hello world
Hello World
h
d
World
ello World
2

PL/SQL procedure successfully completed.

例2

DECLARE
   greetings varchar2(30) := '......Hello World.....';
BEGIN
   dbms_output.put_line(RTRIM(greetings,'.'));
   dbms_output.put_line(LTRIM(greetings, '.'));
   dbms_output.put_line(TRIM( '.' from greetings));
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

......Hello World
Hello World.....
Hello World

PL/SQL procedure successfully completed.

PL/SQL-配列

この章では、PL/SQLの配列について説明します。 PL/SQLプログラミング言語は、 VARRAY と呼ばれるデータ構造を提供します。これは、同じタイプの要素の固定サイズの順次コレクションを格納できます。 VARRAYは、順序付けられたデータのコレクションを格納するために使用されますが、配列を同じタイプの変数のコレクションと考える方が良い場合がよくあります。

すべてのVARRAYは、連続したメモリー位置で構成されています。 最下位アドレスは最初の要素に対応し、最上位アドレスは最後の要素に対応します。

PL/SQLのVARRAY

配列はコレクション型データの一部であり、可変サイズの配列を表します。 他のコレクションタイプについては、後の章「* PL/SQLコレクション」*で学習します。

*varray* の各要素には、インデックスが関連付けられています。 また、動的に変更できる最大サイズもあります。

Varrayタイプの作成

*CREATE TYPE* ステートメントを使用して、VARRAY型が作成されます。 VARRAYに格納される要素の最大サイズとタイプを指定する必要があります。

スキーマレベルでVARRAY型を作成するための基本的な構文は次のとおりです-

CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>

どこで、

  • _varray_type_name_は有効な属性名です。

  • _n_は、VARRAYの要素数(最大)です。

  • _element_type_は、配列の要素のデータ型です。

    *ARTER TYPE* ステートメントを使用して、VARRAYの最大サイズを変更できます。

例えば、

CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10);
/

Type created.

PL/SQLブロック内にVARRAY型を作成するための基本的な構文は次のとおりです-

TYPE varray_type_name IS VARRAY(n) of <element_type>

たとえば-

TYPE namearray IS VARRAY(5) OF VARCHAR2(10);
Type grades IS VARRAY(5) OF INTEGER;

概念を理解するために、いくつかの例を試してみましょう-

例1

次のプログラムは、VARRAYの使用を示しています-

DECLARE
   type namesarray IS VARRAY(5) OF VARCHAR2(10);
   type grades IS VARRAY(5) OF INTEGER;
   names namesarray;
   marks grades;
   total integer;
BEGIN
   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
   marks:= grades(98, 97, 78, 87, 92);
   total := names.count;
   dbms_output.put_line('Total '|| total || ' Students');
   FOR i in 1 .. total LOOP
      dbms_output.put_line('Student: ' || names(i) || '
      Marks: ' || marks(i));
   END LOOP;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Total 5 Students
Student: Kavita  Marks: 98
Student: Pritam  Marks: 97
Student: Ayan  Marks: 78
Student: Rishav  Marks: 87
Student: Aziz  Marks: 92

PL/SQL procedure successfully completed.

注意してください-

  • Oracle環境では、VARRAYの開始インデックスは常に1です。
  • VARRAYと同じ名前のVARRAYタイプのコンストラクタ・メソッドを使用して、VARRAY要素を初期化できます。
  • Varrayは1次元配列です。 *VARRAYは、宣言されると自動的にNULLになり、要素を参照する前に初期化する必要があります。

例2

VARRAYの要素は、任意のデータベース表の%ROWTYPEまたは任意のデータベース表フィールドの%TYPEにすることもできます。 次の例は、概念を示しています。

データベースに保存されているCUSTOMERSテーブルを次のように使用します-

Select* from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+

次の例では、*カーソル*を使用しています。これについては、別の章で詳しく学習します。

DECLARE
   CURSOR c_customers is
   SELECT  name FROM customers;
   type c_list is varray (6) of customers.name%type;
   name_list c_list := c_list();
   counter integer :=0;
BEGIN
   FOR n IN c_customers LOOP
      counter := counter + 1;
      name_list.extend;
      name_list(counter)  := n.name;
      dbms_output.put_line('Customer('||counter ||'):'||name_list(counter));
   END LOOP;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal

PL/SQL procedure successfully completed.

PL/SQL-プロシージャ

この章では、PL/SQLのプロシージャについて説明します。 *サブプログラム*は、特定のタスクを実行するプログラム単位/モジュールです。 これらのサブプログラムは結合されて、より大きなプログラムを形成します。 これは基本的に「モジュール設計」と呼ばれます。 サブプログラムは、*呼び出しプログラム*と呼ばれる別のサブプログラムまたはプログラムによって呼び出すことができます。

サブプログラムを作成することができます-

  • スキーマレベルで
  • パッケージ内
  • PL/SQLブロック内

スキーマレベルでは、サブプログラムは*スタンドアロンサブプログラム*です。 CREATE PROCEDUREまたはCREATE FUNCTIONステートメントで作成されます。 データベースに保存され、DROP PROCEDUREまたはDROP FUNCTIONステートメントで削除できます。

パッケージ内に作成されたサブプログラムは、パッケージ化されたサブプログラム*です。 データベースに保存され、DROP PACKAGEステートメントでパッケージが削除された場合にのみ削除できます。 パッケージについては、 *'PL/SQL-パッケージ' の章で説明します。

PL/SQLサブプログラムは、一連のパラメータで起動できるPL/SQLブロックという名前です。 PL/SQLは2種類のサブプログラムを提供します-

  • 関数-これらのサブプログラムは単一の値を返します。主に値を計算して返すために使用されます。
  • 手順-これらのサブプログラムは値を直接返しません。主にアクションを実行するために使用されます。

この章では、* PL/SQLプロシージャ*の重要な側面について説明します。 次の章で* PL/SQL関数*について説明します。

PL/SQLサブプログラムの一部

各PL/SQLサブプログラムには名前があり、パラメータ・リストもある場合があります。 無名PL/SQLブロックのように、名前付きブロックには次の3つの部分もあります-

S.No Parts & Description
1

Declarative Part

これはオプションの部分です。 ただし、サブプログラムの宣言部はDECLAREキーワードで始まりません。 型、カーソル、定数、変数、例外、ネストされたサブプログラムの宣言が含まれています。 これらの項目はサブプログラムに対してローカルであり、サブプログラムの実行が完了すると存在しなくなります。

2

Executable Part

これは必須の部分であり、指定されたアクションを実行するステートメントが含まれています。

3

Exception-handling

これもオプションの部分です。 実行時エラーを処理するコードが含まれています。

プロシージャを作成する

プロシージャは CREATE OR REPLACE PROCEDURE ステートメントで作成されます。 CREATE OR REPLACE PROCEDUREステートメントの簡略化された構文は次のとおりです-

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
  < procedure_body >
END procedure_name;

どこで、

  • _procedure-name_は、プロシージャの名前を指定します。
  • [OR REPLACE]オプションを使用すると、既存のプロシージャを変更できます。 オプションのパラメーターリストには、パラメーターの名前、モード、およびタイプが含まれます。 IN *は外部から渡される値を表し、OUTはプロシージャの外部で値を返すために使用されるパラメーターを表します。
  • _procedure-body_には実行可能部分が含まれます。
  • スタンドアロンプ​​ロシージャを作成するには、ISキーワードの代わりにASキーワードを使用します。

次の例では、文字列「Hello World!」を表示する簡単なプロシージャを作成します実行時に画面に表示されます。

CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
   dbms_output.put_line('Hello World!');
END;
/

上記のコードがSQLプロンプトを使用して実行されると、次の結果が生成されます-

Procedure created.

スタンドアロンプ​​ロシージャの実行

スタンドアロンプ​​ロシージャは、2つの方法で呼び出すことができます-

  • EXECUTE キーワードを使用する

  • PL/SQLブロックからプロシージャの名前を呼び出す

    *'greetings'* という名前の上記のプロシージャは、EXECUTEキーワードを指定して呼び出すことができます-
EXECUTE greetings;

上記の呼び出しは表示されます-

Hello World

PL/SQL procedure successfully completed.

プロシージャは、別のPL/SQLブロックから呼び出すこともできます-

BEGIN
   greetings;
END;
/

上記の呼び出しは表示されます-

Hello World

PL/SQL procedure successfully completed.

スタンドアロンプ​​ロシージャの削除

スタンドアロンプ​​ロシージャは、 DROP PROCEDURE ステートメントで削除されます。 プロシージャを削除するための構文は-

DROP PROCEDURE procedure-name;

あなたは、次の文を使用して、グリーティングの手順をドロップすることができます-

DROP PROCEDURE greetings;

PL/SQLサブプログラムのパラメータ・モード

次の表は、PL/SQLサブプログラムのパラメータモードを示しています-

S.No Parameter Mode & Description
1

IN

INパラメーターを使用すると、サブプログラムに値を渡すことができます。 これは読み取り専用パラメーターです。 サブプログラム内では、INパラメーターは定数のように機能します。 値を割り当てることはできません。 INパラメーターとして定数、リテラル、初期化された変数、または式を渡すことができます。 デフォルト値に初期化することもできます。ただし、その場合、サブプログラム呼び出しからは省略されます。 これは、パラメーターを渡すデフォルトのモードです。 パラメーターは参照によって渡されます

2

OUT

OUTパラメーターは、呼び出し元プログラムに値を返します。 サブプログラム内では、OUTパラメーターは変数のように機能します。 値を変更し、割り当てた後に値を参照できます。 実際のパラメーターは変数でなければならず、値で渡されます

3

IN OUT

  • IN OUT* パラメーターは、初期値をサブプログラムに渡し、更新された値を呼び出し元に返します。 値を割り当てて、その値を読み取ることができます。

IN OUT仮パラメータに対応する実際のパラメータは、定数または式ではなく、変数でなければなりません。 仮パラメータには値を割り当てる必要があります。 *実際のパラメーターは値で渡されます。 *

IN&OUTモードの例1

このプログラムは、2つの値の最小値を見つけます。 ここでは、プロシージャはINモードを使用して2つの数値を取り、OUTパラメーターを使用して最小値を返します。

DECLARE
   a number;
   b number;
   c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
   IF x < y THEN
      z:= x;
   ELSE
      z:= y;
   END IF;
END;
BEGIN
   a:= 23;
   b:= 45;
   findMin(a, b, c);
   dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Minimum of (23, 45) : 23

PL/SQL procedure successfully completed.

IN&OUTモードの例2

このプロシージャは、渡された値の値の2乗を計算します。 この例は、同じパラメーターを使用して値を受け入れ、別の結果を返す方法を示しています。

DECLARE
   a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
  x := x* x;
END;
BEGIN
   a:= 23;
   squareNum(a);
   dbms_output.put_line(' Square of (23): ' || a);
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Square of (23): 529

PL/SQL procedure successfully completed.

パラメータを渡す方法

実際のパラメータは3つの方法で渡すことができます-

  • 位置表記
  • 名前付き表記
  • 混合表記

位置表記

位置表記法では、次のようにプロシージャを呼び出すことができます-

findMin(a, b, c, d);

位置表記では、最初の実パラメータが最初の仮パラメータに置き換えられます。 2番目の実パラメータが2番目の仮パラメータに置き換えられ、以下同様に続きます。 したがって、 axに、byに、cz に、 dm に置換されます。

名前付き表記

名前付き表記では、実際のパラメーターは*矢印記号(⇒)*を使用して仮パラメーターに関連付けられます。 プロシージャコールは次のようになります-

findMin(x => a, y => b, z => c, m => d);

混合表記

混合表記では、プロシージャ呼び出しで両方の表記を混在させることができます。ただし、位置表記法は名前表記法の前に置く必要があります。

次の呼び出しは合法です-

findMin(a, b, c, m => d);

ただし、これは違法です。

findMin(x => a, b, c, d);

PL/SQL-関数

この章では、PL/SQLの関数について説明します。 関数は、値を返すことを除いてプロシージャと同じです。 したがって、前の章のすべての議論は関数にも当てはまります。

関数を作成する

スタンドアロン関数は、 CREATE FUNCTION ステートメントを使用して作成されます。 CREATE OR REPLACE PROCEDURE ステートメントの簡略化された構文は次のとおりです-

CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
   < function_body >
END [function_name];

どこで、

  • _function-name_は、関数の名前を指定します。
  • [OR REPLACE]オプションを使用すると、既存の関数を変更できます。
  • オプションのパラメーターリストには、パラメーターの名前、モード、およびタイプが含まれます。 INは外部から渡される値を表し、OUTはプロシージャの外部で値を返すために使用されるパラメーターを表します。
  • 関数には return ステートメントが含まれている必要があります。
  • _RETURN_句は、関数から返すデータ型を指定します。
  • _function-body_には実行可能部分が含まれます。 *スタンドアロン機能を作成するには、ISキーワードの代わりにASキーワードが使用されます。

次の例は、スタンドアロン関数を作成して呼び出す方法を示しています。 この関数は、顧客テーブルの顧客の総数を返します。

リンクで作成したCUSTOMERSテーブルを使用します:/plsql/plsql_variable_types [PL/SQL変数]章-

Select* from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
   total number(2) := 0;
BEGIN
   SELECT count(*) into total
   FROM customers;

   RETURN total;
END;
/

上記のコードがSQLプロンプトを使用して実行されると、次の結果が生成されます-

Function created.

関数を呼び出す

関数を作成するときに、関数が何をする必要があるかを定義します。 関数を使用するには、その関数を呼び出して定義済みのタスクを実行する必要があります。 プログラムが関数を呼び出すと、プログラム制御は呼び出された関数に転送されます。

呼び出された関数は定義されたタスクを実行し、returnステートメントが実行されるか、* last endステートメント*に到達すると、プログラム制御をメインプログラムに戻します。

関数を呼び出すには、必要なパラメーターと関数名を渡すだけでよく、関数が値を返す場合、戻り値を保存できます。 次のプログラムは、匿名ブロックから関数 totalCustomers を呼び出します-

DECLARE
   c number(2);
BEGIN
   c := totalCustomers();
   dbms_output.put_line('Total no. of Customers: ' || c);
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Total no. of Customers: 6

PL/SQL procedure successfully completed.

次の例は、最大2つの値を計算して返す単純なPL/SQL関数の宣言、定義および起動を示しています。

DECLARE
   a number;
   b number;
   c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
    z number;
BEGIN
   IF x > y THEN
      z:= x;
   ELSE
      Z:= y;
   END IF;
   RETURN z;
END;
BEGIN
   a:= 23;
   b:= 45;
   c := findMax(a, b);
   dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Maximum of (23,45): 45

PL/SQL procedure successfully completed.

PL/SQL再帰関数

プログラムまたはサブプログラムが別のサブプログラムを呼び出す場合があることを確認しました。 サブプログラムがそれ自体を呼び出すとき、それは再帰呼び出しと呼ばれ、プロセスは*再帰*と呼ばれます。

概念を説明するために、数値の階乗を計算しましょう。 数nの階乗は次のように定義されます-

n! = n*(n-1)!
   = n*(n-1)*(n-2)!
      ...
   = n*(n-1)*(n-2)*(n-3)... 1

次のプログラムは、それ自体を再帰的に呼び出すことにより、所定の数の階乗を計算します-

DECLARE
   num number;
   factorial number;

FUNCTION fact(x number)
RETURN number
IS
   f number;
BEGIN
   IF x=0 THEN
      f := 1;
   ELSE
      f := x * fact(x-1);
   END IF;
RETURN f;
END;

BEGIN
   num:= 6;
   factorial := fact(num);
   dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Factorial 6 is 720

PL/SQL procedure successfully completed.

PL/SQL-カーソル

この章では、PL/SQLのカーソルについて説明します。 Oracleは、SQL文を処理するためのコンテキスト領域と呼ばれるメモリ領域を作成します。SQL領域には、文の処理に必要なすべての情報が含まれます。たとえば、処理された行の数など。

  • カーソル*は、このコンテキスト領域へのポインタです。 PL/SQLは、カーソルを介してコンテキスト領域を制御します。 カーソルは、SQLステートメントによって返された行(1つ以上)を保持します。 カーソルが保持する行のセットは、*アクティブセット*と呼ばれます。

カーソルに名前を付けると、プログラムで参照できるようになり、SQLステートメントによって返される行を一度に1つずつフェッチして処理できます。 カーソルには2種類あります-

  • 暗黙カーソル
  • 明示カーソル

暗黙カーソル

暗黙カーソルは、SQL文が実行されるたびに、文に明示的なカーソルがない場合、Oracleによって自動的に作成されます。 プログラマは、暗黙カーソルとその中の情報を制御できません。

DMLステートメント(INSERT、UPDATE、DELETE)が発行されるたびに、暗黙的なカーソルがこのステートメントに関連付けられます。 INSERT操作の場合、カーソルは挿入する必要のあるデータを保持します。 UPDATEおよびDELETE操作の場合、カーソルは影響を受ける行を識別します。

PL/SQLでは、最新の暗黙カーソルを* SQLカーソル*として参照できます。* SQLカーソル*には、常に*%FOUND、%ISOPEN、%NOTFOUND および%ROWCOUNT などの属性があります。 SQLカーソルには、 *FORALL ステートメントで使用するために設計された追加の属性*%BULK_ROWCOUNT および%BULK_EXCEPTIONS *があります。 次の表は、最も使用される属性の説明を提供します-

S.No Attribute & Description
1

%FOUND

INSERT、UPDATE、またはDELETEステートメントが1つ以上の行に影響した場合、またはSELECT INTOステートメントが1つ以上の行を返した場合、TRUEを返します。 それ以外の場合は、FALSEを返します。

2

%NOTFOUND

%FOUNDの論理的な反対。 INSERT、UPDATE、またはDELETEステートメントが行に影響しない場合、またはSELECT INTOステートメントが行を返さない場合、TRUEを返します。 それ以外の場合は、FALSEを返します。

3

%ISOPEN

暗黙カーソルの場合は常にFALSEが返されます。これは、関連するSQL文の実行後にOracleがSQLカーソルを自動的に閉じるためです。

4

%ROWCOUNT

INSERT、UPDATE、またはDELETEステートメントによって影響を受けた行、またはSELECT INTOステートメントによって返された行の数を返します。

以下の例に示すように、SQLカーソル属性はすべて sql%attribute_name としてアクセスされます。

前の章で作成して使用したCUSTOMERSテーブルを使用します。

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+

次のプログラムは、テーブルを更新し、各顧客の給与を500増やし、 SQL%ROWCOUNT 属性を使用して、影響を受ける行の数を決定します-

DECLARE
   total_rows number(2);
BEGIN
   UPDATE customers
   SET salary = salary + 500;
   IF sql%notfound THEN
      dbms_output.put_line('no customers selected');
   ELSIF sql%found THEN
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected ');
   END IF;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

6 customers selected

PL/SQL procedure successfully completed.

顧客テーブルのレコードを確認すると、行が更新されていることがわかります-

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2500.00 |
|  2 | Khilan   |  25 | Delhi     |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2500.00 |
|  4 | Chaitali |  25 | Mumbai    |  7000.00 |
|  5 | Hardik   |  27 | Bhopal    |  9000.00 |
|  6 | Komal    |  22 | MP        |  5000.00 |
+----+----------+-----+-----------+----------+

明示カーソル

明示カーソルは、*コンテキスト領域*をより詳細に制御するためのプログラマ定義のカーソルです。 PL/SQLブロックの宣言セクションで明示カーソルを定義する必要があります。 複数の行を返すSELECTステートメントで作成されます。

明示カーソルを作成するための構文は次のとおりです-

CURSOR cursor_name IS select_statement;

明示カーソルの操作には、次の手順が含まれます-

  • メモリを初期化するためのカーソルを宣言する
  • メモリを割り当てるためにカーソルを開く
  • データを取得するためのカーソルの取得
  • カーソルを閉じて、割り当てられたメモリを解放する

カーソルの宣言

カーソルを宣言すると、名前と関連付けられたSELECTステートメントでカーソルが定義されます。 たとえば-

CURSOR c_customers IS
   SELECT id, name, address FROM customers;

カーソルを開く

カーソルを開くと、カーソルのメモリが割り当てられ、SQLステートメントによって返された行をフェッチする準備が整います。 たとえば、次のように上記で定義したカーソルを開きます-

OPEN c_customers;

カーソルの取得

カーソルをフェッチするには、一度に1行ずつアクセスする必要があります。 たとえば、次のように上で開いたカーソルから行をフェッチします-

FETCH c_customers INTO c_id, c_name, c_addr;

カーソルを閉じる

カーソルを閉じるとは、割り当てられたメモリを解放することを意味します。 たとえば、次のように上で開いたカーソルを閉じます-

CLOSE c_customers;

以下は、明示カーソルの概念を説明する完全な例です&minua;

DECLARE
   c_id customers.id%type;
   c_name customer.name%type;
   c_addr customers.address%type;
   CURSOR c_customers is
      SELECT id, name, address FROM customers;
BEGIN
   OPEN c_customers;
   LOOP
   FETCH c_customers into c_id, c_name, c_addr;
      EXIT WHEN c_customers%notfound;
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
   END LOOP;
   CLOSE c_customers;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP

PL/SQL procedure successfully completed.

PL/SQL-レコード

この章では、PL/SQLのレコードについて説明します。 *レコード*は、さまざまな種類のデータ項目を保持できるデータ構造です。 レコードは、データベーステーブルの行に似たさまざまなフィールドで構成されます。

たとえば、図書館で本を追跡したいとします。 タイトル、著者、件名、書籍IDなど、各書籍に関する次の属性を追跡できます。 これらの各項目のフィールドを含むレコードにより、BOOKを論理ユニットとして扱うことができ、より適切な方法でその情報を整理および表現できます。

PL/SQLは、次のタイプのレコードを処理できます-

  • テーブルベース
  • カーソルベースのレコード
  • ユーザー定義のレコード

テーブルベースのレコード

%ROWTYPE属性により、プログラマは table-based および cursorbased レコードを作成できます。

次の例は、 table-based レコードの概念を示しています。 前の章で作成して使用したCUSTOMERSテーブルを使用します-

DECLARE
   customer_rec customers%rowtype;
BEGIN
   SELECT * into customer_rec
   FROM customers
   WHERE id = 5;
   dbms_output.put_line('Customer ID: ' || customer_rec.id);
   dbms_output.put_line('Customer Name: ' || customer_rec.name);
   dbms_output.put_line('Customer Address: ' || customer_rec.address);
   dbms_output.put_line('Customer Salary: ' || customer_rec.salary);
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Customer ID: 5
Customer Name: Hardik
Customer Address: Bhopal
Customer Salary: 9000

PL/SQL procedure successfully completed.

カーソルベースのレコード

次の例は、*カーソルベース*レコードの概念を示しています。 前の章で作成して使用したCUSTOMERSテーブルを使用します-

DECLARE
   CURSOR customer_cur is
      SELECT id, name, address
      FROM customers;
   customer_rec customer_cur%rowtype;
BEGIN
   OPEN customer_cur;
   LOOP
      FETCH customer_cur into customer_rec;
      EXIT WHEN customer_cur%notfound;
      DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name);
   END LOOP;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

1 Ramesh
2 Khilan
3 kaushik
4 Chaitali
5 Hardik
6 Komal

PL/SQL procedure successfully completed.

ユーザー定義のレコード

PL/SQLには、様々なレコード構造を定義できるユーザー定義のレコード型が用意されています。 これらのレコードは異なるフィールドで構成されています。 図書館で本を追跡したいとします。 あなたは、各本に関する次の属性を追跡することができます-

  • タイトル
  • 著者
  • 件名
  • ブックID

レコードを定義する

レコードタイプは次のように定義されています-

TYPE
type_name IS RECORD
  ( field_name1  datatype1  [NOT NULL]  [:= DEFAULT EXPRESSION],
   field_name2   datatype2   [NOT NULL]  [:= DEFAULT EXPRESSION],
   ...
   field_nameN  datatypeN  [NOT NULL]  [:= DEFAULT EXPRESSION);
record-name  type_name;

本の記録は次のように宣言されています-

DECLARE
TYPE books IS RECORD
(title  varchar(50),
   author  varchar(50),
   subject varchar(100),
   book_id   number);
book1 books;
book2 books;

フィールドへのアクセス

レコードの任意のフィールドにアクセスするには、ドット*(。)*演算子を使用します。 メンバーアクセス演算子は、レコード変数名とアクセスするフィールドの間のピリオドとしてコーディングされます。 以下は、レコードの使用法を説明する例です-

DECLARE
   type books is record
      (title varchar(50),
      author varchar(50),
      subject varchar(100),
      book_id number);
   book1 books;
   book2 books;
BEGIN
   -- Book 1 specification
   book1.title  := 'C Programming';
   book1.author := 'Nuha Ali ';
   book1.subject := 'C Programming Tutorial';
   book1.book_id := 6495407;
   -- Book 2 specification
   book2.title := 'Telecom Billing';
   book2.author := 'Zara Ali';
   book2.subject := 'Telecom Billing Tutorial';
   book2.book_id := 6495700;

  -- Print book 1 record
   dbms_output.put_line('Book 1 title : '|| book1.title);
   dbms_output.put_line('Book 1 author : '|| book1.author);
   dbms_output.put_line('Book 1 subject : '|| book1.subject);
   dbms_output.put_line('Book 1 book_id : ' || book1.book_id);

   -- Print book 2 record
   dbms_output.put_line('Book 2 title : '|| book2.title);
   dbms_output.put_line('Book 2 author : '|| book2.author);
   dbms_output.put_line('Book 2 subject : '|| book2.subject);
   dbms_output.put_line('Book 2 book_id : '|| book2.book_id);
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Book 1 title : C Programming
Book 1 author : Nuha Ali
Book 1 subject : C Programming Tutorial
Book 1 book_id : 6495407
Book 2 title : Telecom Billing
Book 2 author : Zara Ali
Book 2 subject : Telecom Billing Tutorial
Book 2 book_id : 6495700

PL/SQL procedure successfully completed.

サブプログラムパラメータとしてのレコード

他の変数を渡すのと同じように、サブプログラムのパラメーターとしてレコードを渡すことができます。 また、上記の例でアクセスしたのと同じ方法でレコードフィールドにアクセスできます-

DECLARE
   type books is record
      (title  varchar(50),
      author  varchar(50),
      subject varchar(100),
      book_id   number);
   book1 books;
   book2 books;
PROCEDURE printbook (book books) IS
BEGIN
   dbms_output.put_line ('Book  title :  ' || book.title);
   dbms_output.put_line('Book  author : ' || book.author);
   dbms_output.put_line( 'Book  subject : ' || book.subject);
   dbms_output.put_line( 'Book book_id : ' || book.book_id);
END;

BEGIN
   -- Book 1 specification
   book1.title  := 'C Programming';
   book1.author := 'Nuha Ali ';
   book1.subject := 'C Programming Tutorial';
   book1.book_id := 6495407;

   -- Book 2 specification
   book2.title := 'Telecom Billing';
   book2.author := 'Zara Ali';
   book2.subject := 'Telecom Billing Tutorial';
   book2.book_id := 6495700;

   -- Use procedure to print book info
   printbook(book1);
   printbook(book2);
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Book  title : C Programming
Book  author : Nuha Ali
Book subject : C Programming Tutorial
Book  book_id : 6495407
Book title : Telecom Billing
Book author : Zara Ali
Book subject : Telecom Billing Tutorial
Book book_id : 6495700

PL/SQL procedure successfully completed.

PL/SQL-例外

この章では、PL/SQLの例外について説明します。 例外は、プログラム実行中のエラー状態です。 PL/SQLは、プログラマがプログラム内の EXCEPTION ブロックを使用してこのような状態をキャッチすることをサポートし、エラー状態に対して適切なアクションが実行されます。 例外には2種類あります-

  • システム定義の例外
  • ユーザー定義の例外

例外処理の構文

例外処理の一般的な構文は次のとおりです。 ここでは、処理できる限り多くの例外をリストできます。 デフォルトの例外は、 _ WHENその他THEN_ を使用して処理されます-

DECLARE
   <declarations section>
BEGIN
   <executable command(s)>
EXCEPTION
   <exception handling goes here >
   WHEN exception1 THEN
      exception1-handling-statements
   WHEN exception2  THEN
      exception2-handling-statements
   WHEN exception3 THEN
      exception3-handling-statements
   ........
   WHEN others THEN
      exception3-handling-statements
END;

概念を説明するコードを書きましょう。 前の章で作成して使用したCUSTOMERSテーブルを使用します-

DECLARE
   c_id customers.id%type := 8;
   c_name customerS.Name%type;
   c_addr customers.address%type;
BEGIN
   SELECT  name, address INTO  c_name, c_addr
   FROM customers
   WHERE id = c_id;
   DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);
   DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);

EXCEPTION
   WHEN no_data_found THEN
      dbms_output.put_line('No such customer!');
   WHEN others THEN
      dbms_output.put_line('Error!');
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

No such customer!

PL/SQL procedure successfully completed.

上記のプログラムは、IDが与えられている顧客の名前と住所を表示します。 データベースにID値8の顧客がいないため、プログラムは実行時例外 NO_DATA_FOUND を発生させます。これは* EXCEPTIONブロック*でキャプチャされます。

例外を発生させる

内部データベースエラーが発生すると、データベースサーバーによって例外が自動的に発生しますが、プログラマは RAISE コマンドを使用して例外を明示的に発生させることができます。 以下は、例外を発生させるための簡単な構文です-

DECLARE
   exception_name EXCEPTION;
BEGIN
   IF condition THEN
      RAISE exception_name;
   END IF;
EXCEPTION
   WHEN exception_name THEN
   statement;
END;

上記の構文を使用して、Oracle標準例外またはユーザー定義の例外を発生させることができます。 次のセクションでは、ユーザー定義の例外を発生させる例を示します。 同様の方法でOracle標準例外を発生させることができます。

ユーザー定義の例外

PL/SQLでは、プログラムのニーズに応じて独自の例外を定義できます。 RAISEステートメントまたはプロシージャ DBMS_STANDARD.RAISE_APPLICATION_ERROR を使用して、ユーザー定義の例外を宣言し、明示的に発生させる必要があります。

例外を宣言するための構文は次のとおりです-

DECLARE
   my-exception EXCEPTION;

次の例は、概念を示しています。 このプログラムは顧客IDを要求します。ユーザーが無効なIDを入力すると、例外 invalid_id が発生します。

DECLARE
   c_id customers.id%type := &cc_id;
   c_name customerS.Name%type;
   c_addr customers.address%type;
   -- user defined exception
   ex_invalid_id  EXCEPTION;
BEGIN
   IF c_id <= 0 THEN
      RAISE ex_invalid_id;
   ELSE
      SELECT  name, address INTO  c_name, c_addr
      FROM customers
      WHERE id = c_id;
      DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);
      DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
   END IF;

EXCEPTION
   WHEN ex_invalid_id THEN
      dbms_output.put_line('ID must be greater than zero!');
   WHEN no_data_found THEN
      dbms_output.put_line('No such customer!');
   WHEN others THEN
      dbms_output.put_line('Error!');
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Enter value for cc_id: -6 (let's enter a value -6)
old  2: c_id customers.id%type := &cc_id;
new  2: c_id customers.id%type := -6;
ID must be greater than zero!

PL/SQL procedure successfully completed.

事前定義の例外

PL/SQLは多くの事前定義の例外を提供します。これらの例外は、プログラムによってデータベース規則に違反したときに実行されます。 たとえば、SELECT INTOステートメントが行を返さない場合、事前定義の例外NO_DATA_FOUNDが発生します。 次の表は、重要な事前定義の例外のいくつかを示しています-

Exception Oracle Error SQLCODE Description
ACCESS_INTO_NULL 06530 -6530 It is raised when a null object is automatically assigned a value.
CASE_NOT_FOUND 06592 -6592 It is raised when none of the choices in the WHEN clause of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL 06531 -6531 It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
DUP_VAL_ON_INDEX 00001 -1 It is raised when duplicate values are attempted to be stored in a column with unique index.
INVALID_CURSOR 01001 -1001 It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER 01722 -1722 It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
LOGIN_DENIED 01017 -1017 It is raised when a program attempts to log on to the database with an invalid username or password.
NO_DATA_FOUND 01403 +100 It is raised when a SELECT INTO statement returns no rows.
NOT_LOGGED_ON 01012 -1012 It is raised when a database call is issued without being connected to the database.
PROGRAM_ERROR 06501 -6501 It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH 06504 -6504 It is raised when a cursor fetches value in a variable having incompatible data type.
SELF_IS_NULL 30625 -30625 It is raised when a member method is invoked, but the instance of the object type was not initialized.
STORAGE_ERROR 06500 -6500 It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS 01422 -1422 It is raised when a SELECT INTO statement returns more than one row.
VALUE_ERROR 06502 -6502 It is raised when an arithmetic, conversion, truncation, or sizeconstraint error occurs.
ZERO_DIVIDE 01476 1476 It is raised when an attempt is made to divide a number by zero.

PL/SQL-トリガー

この章では、PL/SQLのトリガーについて説明します。 トリガーは保存されたプログラムで、イベントが発生すると自動的に実行または起動されます。 トリガーは、実際には、次のイベントのいずれかに応答して実行されるように書かれています-

  • *データベース操作(DML)*ステートメント(DELETE、INSERT、またはUPDATE)
  • *データベース定義(DDL)*ステートメント(CREATE、ALTER、またはDROP)。
  • データベース操作(SERVERERROR、LOGON、LOGOFF、STARTUP、またはSHUTDOWN)。

トリガーは、イベントが関連付けられているテーブル、ビュー、スキーマ、またはデータベースで定義できます。

トリガーの利点

トリガーは、次の目的のために書くことができます-

  • いくつかの派生列値を自動的に生成する
  • 参照整合性の強制
  • イベントログとテーブルアクセスに関する情報の保存
  • 監査
  • テーブルの同期複製
  • セキュリティ許可を課す
  • 無効なトランザクションの防止

トリガーの作成

トリガーを作成するための構文は次のとおりです-

CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
   Declaration-statements
BEGIN
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;

どこで、

  • CREATE [OR REPLACE] TRIGGER trigger_name-既存のトリガーを作成するか、_trigger_name_で置き換えます。
  • \ {前|後| INSTEAD OF}-これは、トリガーがいつ実行されるかを指定します。 INSTEAD OF句は、ビューでトリガーを作成するために使用されます。
  • \ {INSERT [OR] |更新[または] | DELETE}-これはDML操作を指定します。
  • [OF col_name]-これは、更新される列名を指定します。
  • [ON table_name]-これは、トリガーに関連付けられたテーブルの名前を指定します。
  • [古いASを新しいASとして参照]-これにより、INSERT、UPDATE、DELETEなどのさまざまなDMLステートメントの新しい値と古い値を参照できます。
  • [FOR EACH ROW]-これは行レベルのトリガーを指定します。つまり、トリガーは影響を受ける各行に対して実行されます。 それ以外の場合、SQLステートメントの実行時にトリガーは1回だけ実行されます。これは、テーブルレベルトリガーと呼ばれます。 *WHEN(条件)-これは、トリガーが起動する行の条件を提供します。 この句は、行レベルのトリガーに対してのみ有効です。

まず、前の章で作成して使用したCUSTOMERSテーブルを使用します-

Select* from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+

次のプログラムは、CUSTOMERSテーブルで実行されたINSERTまたはUPDATEまたはDELETE操作に対して起動するcustomersテーブルの*行レベル*トリガーを作成します。 このトリガーは、古い値と新しい値の給与の違いを表示します-

CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
   sal_diff number;
BEGIN
   sal_diff := :NEW.salary  - :OLD.salary;
   dbms_output.put_line('Old salary: ' || :OLD.salary);
   dbms_output.put_line('New salary: ' || :NEW.salary);
   dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Trigger created.

ここで次の点を考慮する必要があります-

  • OLDおよびNEW参照は、テーブルレベルトリガーでは使用できませんが、レコードレベルトリガーで使用できます。
  • 同じトリガーでテーブルをクエリする場合は、AFTERキーワードを使用する必要があります。トリガーは、最初の変更が適用され、テーブルが一貫した状態に戻った後にのみ、テーブルをクエリまたは再度変更できるためです。
  • 上記のトリガーは、テーブルに対するDELETEまたはINSERTまたはUPDATE操作の前に起動するように記述されていますが、単一または複数の操作(たとえば、BEFORE DELETE)でトリガーを記述できます。テーブルでDELETE操作を使用して削除されます。

トリガーのトリガー

CUSTOMERSテーブルでいくつかのDML操作を実行してみましょう。 これは、テーブルに新しいレコードを作成する1つのINSERTステートメントです-

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

CUSTOMERSテーブルにレコードが作成されると、上記の作成トリガー display_salary_changes が起動され、次の結果が表示されます-

Old salary:
New salary: 7500
Salary difference:

これは新しいレコードであるため、古い給与は使用できず、上記の結果はnullになります。 CUSTOMERSテーブルでもう1つのDML操作を実行してみましょう。 UPDATEステートメントは、テーブル内の既存のレコードを更新します-

UPDATE customers
SET salary = salary + 500
WHERE id = 2;

CUSTOMERSテーブルでレコードが更新されると、上記の作成トリガー display_salary_changes が起動され、次の結果が表示されます-

Old salary: 1500
New salary: 2000
Salary difference: 500

PL/SQL-パッケージ

この章では、PL/SQLのパッケージについて説明します。 パッケージは、論理的に関連するPL/SQLタイプ、変数、およびサブプログラムをグループ化するスキーマオブジェクトです。

パッケージには2つの必須部分があります-

  • パッケージ仕様
  • パッケージ本体または定義

パッケージ仕様

仕様はパッケージへのインターフェースです。 パッケージの外部から参照できる型、変数、定数、例外、カーソル、およびサブプログラムを DECLARES します。 つまり、パッケージのコンテンツに関するすべての情報が含まれていますが、サブプログラムのコードは除外されています。

仕様に含まれるすべてのオブジェクトは、 public オブジェクトと呼ばれます。 パッケージ仕様にはないが、パッケージ本体にコード化されたサブプログラムは、 private オブジェクトと呼ばれます。

次のコードスニペットは、1つのプロシージャを持つパッケージ仕様を示しています。 多くのグローバル変数を定義し、パッケージ内で複数のプロシージャまたは関数を使用できます。

CREATE PACKAGE cust_sal AS
   PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Package created.

パッケージ本体

パッケージ本体には、パッケージ仕様で宣言されたさまざまなメソッドのコードと、パッケージ外部のコードから隠されている他のプライベート宣言があります。

*CREATE PACKAGE BODY* ステートメントは、パッケージ本体の作成に使用されます。 次のコードスニペットは、上記で作成された *_cust_sal_* パッケージのパッケージ本体宣言を示しています。 link:/plsql/plsql_variable_types [PL/SQL-変数]の章で述べたように、データベースにCUSTOMERSテーブルがすでに作成されていると想定しました。
CREATE OR REPLACE PACKAGE BODY cust_sal AS

   PROCEDURE find_sal(c_id customers.id%TYPE) IS
   c_sal customers.salary%TYPE;
   BEGIN
      SELECT salary INTO c_sal
      FROM customers
      WHERE id = c_id;
      dbms_output.put_line('Salary: '|| c_sal);
   END find_sal;
END cust_sal;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Package body created.

パッケージ要素の使用

パッケージ要素(変数、プロシージャまたは関数)は、次の構文でアクセスされます-

package_name.element_name;

データベーススキーマで上記のパッケージを既に作成していることを考慮してください。次のプログラムは cust_sal パッケージの find_sal メソッドを使用します-

DECLARE
   code customers.id%type := &cc_id;
BEGIN
   cust_sal.find_sal(code);
END;
/

上記のコードがSQLプロンプトで実行されると、顧客IDの入力を求められ、IDを入力すると、対応する給与が次のように表示されます-

Enter value for cc_id: 1
Salary: 3000

PL/SQL procedure successfully completed.

次のプログラムは、より完全なパッケージを提供します。 私たちは、データベースに保存されたCUSTOMERSテーブルを次のレコードとともに使用します-

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  3000.00 |
|  2 | Khilan   |  25 | Delhi     |  3000.00 |
|  3 | kaushik  |  23 | Kota      |  3000.00 |
|  4 | Chaitali |  25 | Mumbai    |  7500.00 |
|  5 | Hardik   |  27 | Bhopal    |  9500.00 |
|  6 | Komal    |  22 | MP        |  5500.00 |
+----+----------+-----+-----------+----------+

パッケージ仕様

CREATE OR REPLACE PACKAGE c_package AS
   -- Adds a customer
   PROCEDURE addCustomer(c_id   customers.id%type,
   c_name  customerS.No.ame%type,
   c_age  customers.age%type,
   c_addr customers.address%type,
   c_sal  customers.salary%type);

   -- Removes a customer
   PROCEDURE delCustomer(c_id  customers.id%TYPE);
   --Lists all customers
   PROCEDURE listCustomer;

END c_package;
/

上記のコードがSQLプロンプトで実行されると、上記のパッケージが作成され、次の結果が表示されます-

Package created.

パッケージ本体の作成

CREATE OR REPLACE PACKAGE BODY c_package AS
   PROCEDURE addCustomer(c_id  customers.id%type,
      c_name customerS.No.ame%type,
      c_age  customers.age%type,
      c_addr  customers.address%type,
      c_sal   customers.salary%type)
   IS
   BEGIN
      INSERT INTO customers (id,name,age,address,salary)
         VALUES(c_id, c_name, c_age, c_addr, c_sal);
   END addCustomer;

   PROCEDURE delCustomer(c_id   customers.id%type) IS
   BEGIN
      DELETE FROM customers
      WHERE id = c_id;
   END delCustomer;

   PROCEDURE listCustomer IS
   CURSOR c_customers is
      SELECT  name FROM customers;
   TYPE c_list is TABLE OF customers.Name%type;
   name_list c_list := c_list();
   counter integer :=0;
   BEGIN
      FOR n IN c_customers LOOP
      counter := counter +1;
      name_list.extend;
      name_list(counter) := n.name;
      dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter));
      END LOOP;
   END listCustomer;

END c_package;
/

上記の例では、*ネストされたテーブル*を使用しています。 ネストした表の概念については、次の章で説明します。

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Package body created.

パッケージの使用

次のプログラムは、パッケージ_c_package_で宣言および定義されたメソッドを使用します。

DECLARE
   code customers.id%type:= 8;
BEGIN
   c_package.addcustomer(7, 'Rajnish', 25, 'Chennai', 3500);
   c_package.addcustomer(8, 'Subham', 32, 'Delhi', 7500);
   c_package.listcustomer;
   c_package.delcustomer(code);
   c_package.listcustomer;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish
Customer(8): Subham
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish

PL/SQL procedure successfully completed

PL/SQL-コレクション

この章では、PL/SQLのコレクションについて説明します。 コレクションは、同じデータ型を持つ要素の順序付けられたグループです。 各要素は、コレクション内での位置を表す一意の添え字によって識別されます。

PL/SQLは3つのコレクションタイプを提供します-

  • インデックスバイテーブルまたは連想配列
  • 入れ子になったテーブル
  • 可変サイズの配列またはVarray

Oracleのドキュメントは、コレクションの種類ごとに次の特性を提供します-

Collection Type Number of Elements Subscript Type Dense or Sparse Where Created Can Be Object Type Attribute
Associative array (or index-by table) Unbounded String or integer Either Only in PL/SQL block No
Nested table Unbounded Integer Starts dense, can become sparse Either in PL/SQL block or at schema level Yes
Variablesize array (Varray) Bounded Integer Always dense Either in PL/SQL block or at schema level Yes
*'PL/SQL配列'* の章でVARRAYについて既に説明しました。 この章では、PL/SQL表について説明します。

両方のタイプのPL/SQL表、つまり、索引付き表とネストした表は同じ構造を持ち、行は添字表記を使用してアクセスされます。 ただし、これら2つのタイプのテーブルは1つの側面が異なります。ネストした表はデータベース列に格納できますが、索引付き表は格納できません。

インデックステーブル

*index-by* テーブル(*連想配列*とも呼ばれる)は、*キーと値*のペアのセットです。 各キーは一意であり、対応する値を見つけるために使用されます。 キーは整数または文字列のいずれかです。

インデックス付きテーブルは、次の構文を使用して作成されます。 ここでは、 table_name という名前の index-by テーブルを作成しています。そのキーはsubscript_typeであり、関連する値は_element_type_です

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;

table_name type_name;

次の例は、名前とともに整数値を保存するテーブルを作成し、後で同じ名前のリストを出力する方法を示しています。

DECLARE
   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
   salary_list salary;
   name   VARCHAR2(20);
BEGIN
   -- adding elements to the table
   salary_list('Rajnish') := 62000;
   salary_list('Minakshi') := 75000;
   salary_list('Martin') := 100000;
   salary_list('James') := 78000;

   -- printing the table
   name := salary_list.FIRST;
   WHILE name IS NOT null LOOP
      dbms_output.put_line
      ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name)));
      name := salary_list.NEXT(name);
   END LOOP;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Salary of James is 78000
Salary of Martin is 100000
Salary of Minakshi is 75000
Salary of Rajnish is 62000

PL/SQL procedure successfully completed.

インデックス付きテーブルの要素は、任意のデータベーステーブルの*%ROWTYPE または任意のデータベーステーブルフィールドの%TYPE にすることもできます。 次の例は、概念を示しています。 データベースに保存されている *CUSTOMERS テーブルを次のように使用します-

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+
DECLARE
   CURSOR c_customers is
      select name from customers;

   TYPE c_list IS TABLE of customers.Name%type INDEX BY binary_integer;
   name_list c_list;
   counter integer :=0;
BEGIN
   FOR n IN c_customers LOOP
      counter := counter +1;
      name_list(counter) := n.name;
      dbms_output.put_line('Customer('||counter||'):'||name_lis t(counter));
   END LOOP;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal

PL/SQL procedure successfully completed

入れ子になったテーブル

  • ネストされたテーブル*は、任意の数の要素を持つ1次元配列のようなものです。 ただし、ネストされたテーブルは、次の点で配列とは異なります-
  • 配列には宣言された数の要素がありますが、ネストされたテーブルにはありません。 ネストした表のサイズは動的に増加する場合があります。
  • 配列は常に密です。つまり、常に連続した添え字を持っています。 ネストされた配列は最初は密ですが、要素が削除されると疎になる可能性があります。

ネストされたテーブルは、次の構文を使用して作成されます-

TYPE type_name IS TABLE OF element_type [NOT NULL];

table_name type_name;

この宣言は index-by テーブルの宣言に似ていますが、 INDEX BY 句はありません。

ネストした表は、データベース列に格納できます。 さらに、単一列のテーブルをより大きなテーブルに結合するSQL操作を簡素化するために使用できます。 連想配列はデータベースに保存できません。

次の例は、ネストされたテーブルの使用を示しています-

DECLARE
   TYPE names_table IS TABLE OF VARCHAR2(10);
   TYPE grades IS TABLE OF INTEGER;
   names names_table;
   marks grades;
   total integer;
BEGIN
   names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
   marks:= grades(98, 97, 78, 87, 92);
   total := names.count;
   dbms_output.put_line('Total '|| total || ' Students');
   FOR i IN 1 .. total LOOP
      dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i));
   end loop;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Total 5 Students
Student:Kavita, Marks:98
Student:Pritam, Marks:97
Student:Ayan, Marks:78
Student:Rishav, Marks:87
Student:Aziz, Marks:92

PL/SQL procedure successfully completed.

ネストされたテーブル*の要素は、データベーステーブルの%ROWTYPE *またはデータベーステーブルフィールドの%TYPEでもあります。 次の例は、概念を示しています。 データベースに保存されているCUSTOMERSテーブルを次のように使用します-

Select *from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+
DECLARE
   CURSOR c_customers is
      SELECT  name FROM customers;
   TYPE c_list IS TABLE of customerS.No.ame%type;
   name_list c_list := c_list();
   counter integer :=0;
BEGIN
   FOR n IN c_customers LOOP
      counter := counter +1;
      name_list.extend;
      name_list(counter)  := n.name;
      dbms_output.put_line('Customer('||counter||'):'||name_list(counter));
   END LOOP;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal

PL/SQL procedure successfully completed.

収集方法

PL/SQLは、コレクションを使いやすくする組み込みのコレクションメソッドを提供します。 次の表は、メソッドとその目的を示しています-

S.No Method Name & Purpose
1
  • EXISTS(n)*

コレクション内のn番目の要素が存在する場合、TRUEを返します。そうでない場合はFALSEを返します。

2

COUNT

コレクションに現在含まれている要素の数を返します。

3

LIMIT

コレクションの最大サイズを確認します。

4

FIRST

整数の添字を使用するコレクション内の最初の(最小の)インデックス番号を返します。

5

LAST

整数の添え字を使用するコレクション内の最後の(最大の)インデックス番号を返します。

6

PRIOR(n)

コレクション内のインデックスnの前にあるインデックス番号を返します。

7

NEXT(n)

インデックスnに続くインデックス番号を返します。

8

EXTEND

1つのnull要素をコレクションに追加します。

9

EXTEND(n)

コレクションにn個のnull要素を追加します。

10

EXTEND(n,i)

i ^ th ^要素の n コピーをコレクションに追加します。

11

TRIM

コレクションの末尾から1つの要素を削除します。

12

TRIM(n)

コレクションの末尾から n 個の要素を削除します。

13

DELETE

COUNTを0に設定して、コレクションからすべての要素を削除します。

14

DELETE(n)

数値キーまたはネストされたテーブルを使用して、連想配列から n ^ th ^ 要素を削除します。 連想配列に文字列キーがある場合、キー値に対応する要素が削除されます。 n がヌルの場合、* DELETE(n)*は何もしません。

15

DELETE(m,n)

連想配列またはネストされたテーブルから m..n の範囲のすべての要素を削除します。 mn よりも大きい場合、または m または n がヌルの場合、* DELETE(m、n)*は何もしません。

コレクションの例外

次の表は、コレクションの例外とそれらが発生した場合を提供します-

Collection Exception Raised in Situations
COLLECTION_IS_NULL You try to operate on an atomically null collection.
NO_DATA_FOUND A subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNT A subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMIT A subscript is outside the allowed range.
VALUE_ERROR A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.

PL/SQL-トランザクション

この章では、PL/SQLのトランザクションについて説明します。 データベースの*トランザクション*は、1つ以上の関連するSQLステートメントで構成される可能性があるアトミックな作業単位です。 トランザクションを構成するSQLステートメントによってもたらされるデータベースの変更は、一括してコミット、つまりデータベースに永続的にするか、データベースからロールバック(元に戻す)できるため、アトミックと呼ばれます。

正常に実行されたSQLステートメントとコミットされたトランザクションは同じではありません。 SQLステートメントが正常に実行された場合でも、そのステートメントを含むトランザクションがコミットされない限り、それをロールバックし、ステートメントによって行われたすべての変更を元に戻すことができます。

トランザクションの開始と終了

トランザクションには beginningend があります。 次のイベントのいずれかが発生すると、トランザクションが開始されます-

  • 最初のSQLステートメントは、データベースに接続した後に実行されます。
  • トランザクションの完了後に発行される新しいSQLステートメントごと。

トランザクションは、次のイベントのいずれかが発生したときに終了します-

  • COMMIT または ROLLBACK ステートメントが発行されます。
  • CREATE TABLE ステートメントなどの DDL ステートメントが発行されます。その場合、COMMITが自動的に実行されるためです。
  • GRANT ステートメントなどの DCL ステートメントが発行されます。その場合、COMMITが自動的に実行されるためです。
  • ユーザーがデータベースから切断します。
  • ユーザーは EXIT コマンドを発行して SQL PLUS を終了すると、COMMITが自動的に実行されます。 *SQL Plusは異常終了し、 ROLLBACK が自動的に実行されます。
  • DML ステートメントは失敗します。その場合、そのDMLステートメントを取り消すためにROLLBACKが自動的に実行されます。

トランザクションをコミットする

トランザクションは、SQLコマンドCOMMITを発行することにより永続的になります。 COMMITコマンドの一般的な構文は次のとおりです-

COMMIT;

例えば、

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

COMMIT;

トランザクションのロールバック

COMMITなしでデータベースに加えられた変更は、ROLLBACKコマンドを使用して元に戻すことができます。

ROLLBACKコマンドの一般的な構文は次のとおりです-

ROLLBACK [TO SAVEPOINT < savepoint_name>];

システム障害などの前例のない状況によりトランザクションが中止されると、コミット以降のトランザクション全体が自動的にロールバックされます。 あなたが*セーブポイント*を使用していない場合は、単に次のステートメントを使用して、すべての変更をロールバックします-

ROLLBACK;

セーブポイント

セーブポイントは、いくつかのチェックポイントを設定することにより、長いトランザクションを小さな単位に分割するのに役立つ一種のマーカーです。 長いトランザクション内でセーブポイントを設定することにより、必要に応じてチェックポイントにロールバックできます。 これは、 SAVEPOINT コマンドを発行することにより行われます。

SAVEPOINTコマンドの一般的な構文は次のとおりです-

SAVEPOINT < savepoint_name >;

例えば

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Rajnish', 27, 'HP', 9500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (8, 'Riddhi', 21, 'WB', 4500.00 );
SAVEPOINT sav1;

UPDATE CUSTOMERS
SET SALARY = SALARY + 1000;
ROLLBACK TO sav1;

UPDATE CUSTOMERS
SET SALARY = SALARY + 1000
WHERE ID = 7;
UPDATE CUSTOMERS
SET SALARY = SALARY + 1000
WHERE ID = 8;

COMMIT;
*ROLLBACK TO sav1* -このステートメントは、セーブポイントsav1をマークしたポイントまでのすべての変更をロールバックします。

その後、新しい変更が開始されます。

自動トランザクション制御

*INSERT、UPDATE* 、または *DELETE* コマンドが実行されるたびに *COMMIT* を自動的に実行するには、 *AUTOCOMMIT* 環境変数を次のように設定できます-
SET AUTOCOMMIT ON;

あなたは、次のコマンドを使用して自動コミットモードをオフにすることができます-

SET AUTOCOMMIT OFF;

PL/SQL-日付と時刻

この章では、PL/SQLの日付と時刻について説明します。 PL/SQLには日付と時刻に関連するデータ型の2つのクラスがあります-

  • 日時データ型
  • 間隔データ型

日時データ型は-

  • DATE
  • タイムスタンプ
  • タイムゾーン付きタイムスタンプ
  • 現地時間帯のタイムスタンプ

間隔データ型は-

  • 間隔月
  • 間隔日から2日

日時および間隔データ型のフィールド値

*datetime* および *interval* データ型は両方とも *fields* で構成されています。 これらのフィールドの値により、データ型の値が決まります。 次の表に、日付時刻と間隔のフィールドと可能な値を示します。
Field Name Valid Datetime Values Valid Interval Values
YEAR -4712 to 9999 (excluding year 0) Any nonzero integer
MONTH 01 to 12 0 to 11
DAY 01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the calendar for the locale) Any nonzero integer
HOUR 00 to 23 0 to 23
MINUTE 00 to 59 0 to 59
SECOND

00 to 59.9(n), where 9(n) is the precision of time fractional seconds

9(n)部分はDATEには適用されません。

0 to 59.9(n), where 9(n) is the precision of interval fractional seconds
TIMEZONE_HOUR

-12 to 14 (range accommodates daylight savings time changes)

DATEまたはTIMESTAMPには適用されません。

Not applicable
TIMEZONE_MINUTE

00 to 59

DATEまたはTIMESTAMPには適用されません。

Not applicable
TIMEZONE_REGION Not applicable for DATE or TIMESTAMP. Not applicable
TIMEZONE_ABBR Not applicable for DATE or TIMESTAMP. Not applicable

日時のデータ型と関数

以下は、日時データ型です-

DATE

文字と数字の両方のデータ型で日付と時刻の情報を保存します。 世紀、年、月、日付、時、分、秒に関する情報で構成されています。 次のように指定されます-

タイムスタンプ

これはDATEデータ型の拡張です。 DATEデータ型の年、月、日、および時、分、秒の値を保存します。 正確な時間値を保存するのに便利です。

タイムゾーン付きタイムスタンプ

これは、タイムゾーン地域名またはタイムゾーンオフセットを値に含むTIMESTAMPのバリアントです。 タイムゾーンオフセットは、現地時間とUTCの差(時間と分)です。 このデータ型は、地理的領域全体で日付情報を収集および評価するのに役立ちます。

現地時間帯のタイムスタンプ

値にタイムゾーンオフセットを含むTIMESTAMPの別のバリアントです。

次の表は、日時関数を提供します(xは日時値を持ちます)-

S.No Function Name & Description
1

ADD_MONTHS(x, y);

  • y* 月を *x* に追加します。
2

LAST_DAY(x);

月の最後の日を返します。

3

MONTHS_BETWEEN(x, y);

  • x* と *y* の間の月数を返します。
4

NEXT_DAY(x, day);

  • x* の後の次の_day_の日時を返します。
5

NEW_TIME;

ユーザーが指定したタイムゾーンから時刻/日の値を返します。

6

ROUND(x [, unit]);

  • x* を丸めます。
7

SYSDATE();

現在の日時を返します。

8

TRUNC(x [, unit]);

  • x* を切り捨てます。

タイムスタンプ関数(xはタイムスタンプ値を持ちます)-

S.No Function Name & Description
1

CURRENT_TIMESTAMP();

現在のセッション時間とセッションタイムゾーンを含むTIMESTAMP WITH TIME ZONEを返します。

2 *EXTRACT(\{ YEAR
MONTH DAY
HOUR MINUTE
SECOND } \{ TIMEZONE_HOUR
TIMEZONE_MINUTE } \{ TIMEZONE_REGION

} TIMEZONE_ABBR ) FROM x)*

x から年、月、日、時間、分、秒、またはタイムゾーンを抽出して返します。

3

FROM_TZ(x, time_zone);

TIMESTAMP xとtime_zoneで指定されたタイムゾーンをTIMESTAMP WITH TIMEZONEに変換します。

4

LOCALTIMESTAMP();

セッションタイムゾーンの現地時間を含むTIMESTAMPを返します。

5

SYSTIMESTAMP();

現在のデータベース時刻とデータベースタイムゾーンを含むTIMESTAMP WITH TIME ZONEを返します。

6

SYS_EXTRACT_UTC(x);

TIMESTAMP WITH TIMEZONE xをUTCの日付と時刻を含むTIMESTAMPに変換します。

7

TO_TIMESTAMP(x, [format]);

文字列xをTIMESTAMPに変換します。

8

次のコードスニペットは、上記の関数の使用を示しています-

例1

SELECT SYSDATE FROM DUAL;

出力-

08/31/2012 5:25:34 PM

例2

SELECT TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH:MI:SS') FROM DUAL;

出力-

31-08-2012 05:26:14

例3

SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;

出力-

01/31/2013 5:26:31 PM

例4

SELECT LOCALTIMESTAMP FROM DUAL;

出力-

8/31/2012 5:26:55.347000 PM

間隔のデータ型と関数

間隔データ型は次のとおりです-

  • IINTERVAL YEAR TO MONTH-YEARおよびMONTH日時フィールドを使用して期間を保存します。 *INTERVAL DAY TO SECOND-日、時間、分、秒の観点から一定期間を保存します。

インターバル関数

S.No Function Name & Description
1
  • NUMTODSINTERVAL(x, interval_unit);*

数値xをINTERVAL DAY TO SECONDに変換します。

2

NUMTOYMINTERVAL(x, interval_unit);

数値xをINTERVAL YEAR TO MONTHに変換します。

3

TO_DSINTERVAL(x);

文字列xをINTERVAL DAY TO SECONDに変換します。

4

TO_YMINTERVAL(x);

文字列xをINTERVAL YEAR TO MONTHに変換します。

PL/SQL-DBMS出力

この章では、PL/SQLでのDBMS出力について説明します。 DBMS_OUTPUT は、出力、デバッグ情報の表示、およびPL/SQLブロック、サブプログラム、パッケージ、トリガーからのメッセージの送信を可能にする組み込みパッケージです。 このパッケージは、チュートリアル全体で既に使用しています。

データベース内のすべてのユーザーテーブルを表示する小さなコードスニペットを見てみましょう。 データベースで試して、すべてのテーブル名をリストダウンします-

BEGIN
   dbms_output.put_line  (user || ' Tables in the database:');
   FOR t IN (SELECT table_name FROM user_tables)
   LOOP
      dbms_output.put_line(t.table_name);
   END LOOP;
END;
/

DBMS_OUTPUTサブプログラム

DBMS_OUTPUTパッケージには、次のサブプログラムがあります-

S.No Subprogram & Purpose
1

DBMS_OUTPUT.DISABLE;

メッセージ出力を無効にします。

2

DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000);

メッセージ出力を有効にします。 buffer_size のNULL値は、無制限のバッファーサイズを表します。

3

DBMS_OUTPUT.GET_LINE (line OUT VARCHAR2, status OUT INTEGER);

バッファリングされた情報の1行を取得します。

4

DBMS_OUTPUT.GET_LINES (lines OUT CHARARR, numlines IN OUT INTEGER);

バッファーから行の配列を取得します。

5

DBMS_OUTPUT.NEW_LINE;

行末マーカーを配置します。

6

DBMS_OUTPUT.PUT(item IN VARCHAR2);

バッファーに部分的な行を配置します。

7

DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2);

バッファーに行を配置します。

DECLARE
   lines dbms_output.chararr;
   num_lines number;
BEGIN
   -- enable the buffer with default size 20000
   dbms_output.enable;

   dbms_output.put_line('Hello Reader!');
   dbms_output.put_line('Hope you have enjoyed the tutorials!');
   dbms_output.put_line('Have a great time exploring pl/sql!');

   num_lines := 3;

   dbms_output.get_lines(lines, num_lines);

   FOR i IN 1..num_lines LOOP
      dbms_output.put_line(lines(i));
   END LOOP;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Hello Reader!
Hope you have enjoyed the tutorials!
Have a great time exploring pl/sql!

PL/SQL procedure successfully completed.

PL/SQL-オブジェクト指向

この章では、オブジェクト指向のPL/SQLについて説明します。 PL/SQLでは、オブジェクト型を定義できます。これにより、Oracleでオブジェクト指向データベースを設計できます。 オブジェクト型を使用すると、複合型を作成できます。 オブジェクトを使用すると、特定のデータ構造とそれを操作するためのメソッドを持つ実世界のオブジェクトを実装できます。 オブジェクトには属性とメソッドがあります。 属性はオブジェクトのプロパティであり、オブジェクトの状態を保存するために使用されます。メソッドはその動作をモデル化するために使用されます。

オブジェクトは、CREATE [OR REPLACE] TYPEステートメントを使用して作成されます。 以下は、いくつかの属性で構成される単純な address オブジェクトを作成する例です-

CREATE OR REPLACE TYPE address AS OBJECT
(house_no varchar2(10),
 street varchar2(30),
 city varchar2(20),
 state varchar2(10),
 pincode varchar2(10)
);
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Type created.

もう1つのオブジェクト customer を作成して、オブジェクト指向の感覚を持たせるために attributesmethods を一緒にラップします。

CREATE OR REPLACE TYPE customer AS OBJECT
(code number(5),
 name varchar2(30),
 contact_no varchar2(12),
 addr address,
 member procedure display
);
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Type created.

オブジェクトのインスタンス化

オブジェクトタイプを定義すると、オブジェクトの青写真が提供されます。 このオブジェクトを使用するには、このオブジェクトのインスタンスを作成する必要があります。 次のようにインスタンス名と*アクセス演算子(。)*を使用して、オブジェクトの属性とメソッドにアクセスできます-

DECLARE
   residence address;
BEGIN
   residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301');
   dbms_output.put_line('House No: '|| residence.house_no);
   dbms_output.put_line('Street: '|| residence.street);
   dbms_output.put_line('City: '|| residence.city);
   dbms_output.put_line('State: '|| residence.state);
   dbms_output.put_line('Pincode: '|| residence.pincode);
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

House No: 103A
Street: M.G.Road
City: Jaipur
State: Rajasthan
Pincode: 201301

PL/SQL procedure successfully completed.

メンバーメソッド

  • メンバーメソッド*は、オブジェクトの*属性*を操作するために使用されます。 オブジェクト型を宣言しながら、メンバーメソッドの宣言を提供します。 オブジェクト本体は、メンバーメソッドのコードを定義します。 オブジェクト本体は、CREATE TYPE BODYステートメントを使用して作成されます。
  • コンストラクタ*は、値として新しいオブジェクトを返す関数です。 すべてのオブジェクトには、システム定義のコンストラクターメソッドがあります。 コンストラクターの名前はオブジェクトタイプと同じです。 たとえば-
residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301');
  • 比較方法*はオブジェクトの比較に使用されます。 オブジェクトを比較するには2つの方法があります-

マップ方式

  • Mapメソッド*は、値が属性の値に依存するように実装された関数です。 たとえば、顧客オブジェクトの場合、2人の顧客の顧客コードが同じであれば、両方の顧客が同じになる可能性があります。 したがって、これら2つのオブジェクト間の関係は、コードの値に依存します。

注文方法

  • Orderメソッド*は、2つのオブジェクトを比較するためのいくつかの内部ロジックを実装します。 たとえば、長方形オブジェクトの場合、長方形の両側が大きい場合、長方形は別の長方形よりも大きくなります。

Mapメソッドを使用する

次の長方形オブジェクトを使用して上記の概念を理解してみましょう-

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 member function enlarge( inc number) return rectangle,
 member procedure display,
 map member function measure return number
);
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Type created.

型本体の作成-

CREATE OR REPLACE TYPE BODY rectangle AS
   MEMBER FUNCTION enlarge(inc number) return rectangle IS
   BEGIN
      return rectangle(self.length + inc, self.width + inc);
   END enlarge;
   MEMBER PROCEDURE display IS
   BEGIN
      dbms_output.put_line('Length: '|| length);
      dbms_output.put_line('Width: '|| width);
   END display;
   MAP MEMBER FUNCTION measure return number IS
   BEGIN
      return (sqrt(length*length + width*width));
   END measure;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Type body created.

今長方形オブジェクトとそのメンバー関数を使用して-

DECLARE
   r1 rectangle;
   r2 rectangle;
   r3 rectangle;
   inc_factor number := 5;
BEGIN
   r1 := rectangle(3, 4);
   r2 := rectangle(5, 7);
   r3 := r1.enlarge(inc_factor);
   r3.display;
   IF (r1 > r2) THEN -- calling measure function
      r1.display;
   ELSE
      r2.display;
   END IF;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Length: 8
Width: 9
Length: 5
Width: 7

PL/SQL procedure successfully completed.

Orderメソッドを使用する

現在、注文方法を使用して*同じ効果を達成できます*。 順序メソッドを使用して長方形オブジェクトを再作成しましょう-

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 member procedure display,
 order member function measure(r rectangle) return number
);
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Type created.

型本体の作成-

CREATE OR REPLACE TYPE BODY rectangle AS
   MEMBER PROCEDURE display IS
   BEGIN
      dbms_output.put_line('Length: '|| length);
      dbms_output.put_line('Width: '|| width);
   END display;
   ORDER MEMBER FUNCTION measure(r rectangle) return number IS
   BEGIN
      IF(sqrt(self.length*self.length + self.width*self.width)>
         sqrt(r.length*r.length + r.width*r.width)) then
         return(1);
      ELSE
         return(-1);
      END IF;
   END measure;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Type body created.

長方形オブジェクトとそのメンバー関数を使用して-

DECLARE
   r1 rectangle;
   r2 rectangle;
BEGIN
   r1 := rectangle(23, 44);
   r2 := rectangle(15, 17);
   r1.display;
   r2.display;
   IF (r1 > r2) THEN -- calling measure function
      r1.display;
   ELSE
      r2.display;
   END IF;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Length: 23
Width: 44
Length: 15
Width: 17
Length: 23
Width: 44

PL/SQL procedure successfully completed.

PL/SQLオブジェクトの継承

PL/SQLでは、既存のベースオブジェクトからオブジェクトを作成できます。 継承を実装するには、ベースオブジェクトを NOT FINAL として宣言する必要があります。 デフォルトは FINAL です。

次のプログラムは、PL/SQLオブジェクトの継承を示しています。 TableTop という名前の別のオブジェクトを作成してみましょう。これはRectangleオブジェクトから継承されます。 このために、我々はベース_rectangle_オブジェクトを作成する必要があります-

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 member function enlarge( inc number) return rectangle,
 NOT FINAL member procedure display) NOT FINAL
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Type created.

基本型本体の作成-

CREATE OR REPLACE TYPE BODY rectangle AS
   MEMBER FUNCTION enlarge(inc number) return rectangle IS
   BEGIN
      return rectangle(self.length + inc, self.width + inc);
   END enlarge;
   MEMBER PROCEDURE display IS
   BEGIN
      dbms_output.put_line('Length: '|| length);
      dbms_output.put_line('Width: '|| width);
   END display;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Type body created.

子オブジェクト_tabletop_の作成-

CREATE OR REPLACE TYPE tabletop UNDER rectangle
(
   material varchar2(20),
   OVERRIDING member procedure display
)
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Type created.

子オブジェクト_tabletop_のタイプボディの作成

CREATE OR REPLACE TYPE BODY tabletop AS
OVERRIDING MEMBER PROCEDURE display IS
BEGIN
   dbms_output.put_line('Length: '|| length);
   dbms_output.put_line('Width: '|| width);
   dbms_output.put_line('Material: '|| material);
END display;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Type body created.

_tabletop_オブジェクトとそのメンバー関数を使用して-

DECLARE
   t1 tabletop;
   t2 tabletop;
BEGIN
   t1:= tabletop(20, 10, 'Wood');
   t2 := tabletop(50, 30, 'Steel');
   t1.display;
   t2.display;
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Length: 20
Width: 10
Material: Wood
Length: 50
Width: 30
Material: Steel

PL/SQL procedure successfully completed.

PL/SQLの抽象オブジェクト

*NOT INSTANTIABLE* 句を使用すると、抽象オブジェクトを宣言できます。 抽象オブジェクトをそのまま使用することはできません。そのようなオブジェクトのサブタイプまたは子タイプを作成して、その機能を使用する必要があります。

例えば、

CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
 width number,
 NOT INSTANTIABLE NOT FINAL MEMBER PROCEDURE display)
 NOT INSTANTIABLE NOT FINAL
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Type created.

Plsql-questions-answers