Oracle PL/SQLストアド・プロシージャとファンクションと例
フィオナ・ブラウン
プロシージャとファンクションは、データベース オブジェクトとして作成してデータベースに保存できるサブプログラムです。 他のブロック内でも呼び出したり参照したりできます。
これとは別に、これら 2 つのサブプログラムの主な違いについても説明します。また、次のことについても議論します。 Oracle 組み込み関数。
PL/SQLサブプログラムの用語
PL/SQL サブプログラムについて学ぶ前に、これらのサブプログラムの一部であるさまざまな用語について説明します。 以下に説明する用語を示します。
パラメータは変数または有効なパラメータのプレースホルダです。 PL/SQLデータ型 これを介して、PL/SQL サブプログラムはメイン コードと値を交換します。 このパラメータを使用すると、サブプログラムに入力を与え、これらのサブプログラムから抽出することができます。
- これらのパラメータは、サブプログラムの作成時に定義する必要があります。
- これらのパラメータは、サブプログラムと値をやり取りするために、これらのサブプログラムの呼び出しステートメントに含まれています。
- サブプログラムと呼び出しステートメントのパラメーターのデータ型は同じである必要があります。
- この型のサイズは動的であるため、パラメーター宣言時にデータ型のサイズを指定する必要はありません。
目的に基づいてパラメータは次のように分類されます。
- INパラメータ
- OUTパラメータ
- IN OUTパラメータ
INパラメータ
- このパラメータはサブプログラムに入力を与えるために使用されます。
- これはサブプログラム内の読み取り専用変数です。 サブプログラム内でそれらの値を変更することはできません。
- 呼び出しステートメントでは、これらのパラメータは変数、リテラル値、または式にすることができます。たとえば、「5*8」や「a/b」（「a」と「b」は変数）のような算術式にすることができます。 。
- デフォルトでは、パラメータは IN タイプです。
OUTパラメータ
- このパラメータは、サブプログラムから出力を取得するために使用されます。
- これはサブプログラム内の読み書き可能な変数です。 それらの値はサブプログラム内で変更できます。
- 呼び出しステートメントでは、これらのパラメーターは常に、現在のサブプログラムからの値を保持する変数である必要があります。
IN OUTパラメータ
- このパラメータは、サブプログラムへの入力と出力の取得の両方に使用されます。
- これはサブプログラム内の読み書き可能な変数です。 それらの値はサブプログラム内で変更できます。
- 呼び出しステートメントでは、これらのパラメーターは常に、サブプログラムからの値を保持する変数である必要があります。
これらのパラメータのタイプは、サブプログラムの作成時に指定する必要があります。
リターン
RETURN は、制御をサブプログラムから呼び出しステートメントに切り替えるようにコンパイラーに指示するキーワードです。 サブプログラムの RETURN は、単にコントロールがサブプログラムから終了する必要があることを意味します。 コントローラーがサブプログラム内で RETURN キーワードを見つけると、これ以降のコードはスキップされます。
通常、親またはメイン ブロックがサブプログラムを呼び出し、その後、制御が親ブロックから呼び出されたサブプログラムに移ります。サブプログラム内の RETURN は、制御を親ブロックに戻します。関数の場合、RETURN ステートメントは値も返します。この値のデータ型は、関数の宣言時に常に指定されます。データ型は、有効な PL/SQL データ型であればどれでもかまいません。
PL/SQLのプロシージャとは何ですか?
A 手順 PL/SQL の は、名前によって呼び出すことができる PL/SQL 文のグループで構成されるサブプログラム単位です。 PL/SQL の各プロシージャには、参照したり呼び出したりできる独自の一意の名前があります。このサブプログラムユニットは、 Oracle データベースはデータベース オブジェクトとして保存されます。
注意： サブプログラムは単なる手続きであり、要件に応じて手動で作成する必要があります。 作成されると、データベース オブジェクトとして保存されます。
PL/SQLのプロシージャ・サブプログラム・ユニットの特徴は次のとおりです。
- プロシージャは、プログラムのスタンドアロン ブロックであり、 データベース.
- これらの PLSQL プロシージャは、その名前を参照することで呼び出して、PL/SQL 文を実行できます。
- 主にPL/SQLで処理を実行するために使用されます。
- ネストされたブロックを含めることも、他のブロックまたはパッケージ内で定義してネストすることもできます。
- これには、宣言部分 (オプション)、実行部分、例外処理部分 (オプション) が含まれます。
- 値を渡すことができます Oracle プロシージャから取得するか、パラメータを介してプロシージャからフェッチします。
- これらのパラメータは呼び出しステートメントに含める必要があります。
- SQL のプロシージャには、呼び出し側ブロックに制御を返す RETURN ステートメントを含めることができますが、RETURN ステートメントを通じて値を返すことはできません。
- プロシージャを SELECT ステートメントから直接呼び出すことはできません。 これらは、別のブロックから、または EXEC キーワードを通じて呼び出すことができます。
構文
CREATE OR REPLACE PROCEDURE <procedure_name> ( <parameterl IN/OUT <datatype> .. . ) [ IS | AS ] <declaration_part> BEGIN <execution part> EXCEPTION <exception handling part> END;
- CREATE PROCEDURE は、コンパイラに新しいプロシージャを作成するように指示します。 Oracle。キーワード「OR REPLACE」は、既存のプロシージャ (存在する場合) を現在のプロシージャで置き換えるようコンパイルに指示します。
- プロシージャ名は一意である必要があります。
- ストアド プロシージャを実行する場合、キーワード 'IS' が使用されます。 Oracle 他のブロックにネストされています。プロシージャがスタンドアロンの場合は、「AS」が使用されます。このコーディング標準以外は、両方とも同じ意味を持ちます。
例1: プロシージャを作成し、EXEC を使用して呼び出す
この例では、 Oracle このプロシージャは、名前を入力として受け取り、ウェルカム メッセージを出力として出力します。 EXECコマンドを使用してプロシージャを呼び出します。
CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2) IS BEGIN dbms_output.put_line (‘Welcome '|| p_name); END; / EXEC welcome_msg (‘Guru99’);
コードの説明：
- コード行 1: 'welcome_msg' という名前と、'IN' タイプの XNUMX つのパラメータ 'p_name' を持つプロシージャを作成しています。
- コード行 4: 入力された名前を連結してウェルカム メッセージを出力します。
- プロシージャは正常にコンパイルされました。
- コード行 7: パラメータ「Guru99」を指定した EXEC コマンドを使用してプロシージャを呼び出します。 手順が実行され、「Welcome Guru99」というメッセージが出力されます。
機能とは？
Functions はスタンドアロンの PL/SQL サブプログラムです。 PL/SQL プロシージャと同様、関数には参照できる一意の名前があります。 これらは、PL/SQL データベース オブジェクトとして保存されます。 以下に関数の特徴をいくつか挙げます。
- 関数は、主に計算目的で使用されるスタンドアロン ブロックです。
- 関数は RETURN キーワードを使用して値を返します。このデータ型は作成時に定義されます。
- Function は値を返すか、例外を発生させる必要があります。つまり、関数では return が必須です。
- DML 文のない関数は SELECT クエリで直接呼び出すことができますが、DML 操作のある関数は他の PL/SQL ブロックからのみ呼び出すことができます。
- ネストされたブロックを含めることも、他のブロックまたはパッケージ内で定義してネストすることもできます。
- これには、宣言部分 (オプション)、実行部分、例外処理部分 (オプション) が含まれます。
- 値は関数に渡すことも、パラメーターを介してプロシージャからフェッチすることもできます。
- これらのパラメータは呼び出しステートメントに含める必要があります。
- PLSQL 関数は、RETURN を使用する以外に、OUT パラメータを通じて値を返すこともできます。
- 常に値を返すため、呼び出しステートメントでは、変数を設定するために常に代入演算子が伴います。
構文
CREATE OR REPLACE FUNCTION <procedure_name> ( <parameterl IN/OUT <datatype> ) RETURN <datatype> [ IS | AS ] <declaration_part> BEGIN <execution part> EXCEPTION <exception handling part> END;
- CREATE FUNCTION は、コンパイラーに新しい関数を作成するように指示します。 キーワード「OR REPLACE」は、既存の関数 (存在する場合) を現在の関数で置き換えるようコンパイラーに指示します。
- 関数名は一意である必要があります。
- RETURN データ型について言及する必要があります。
- プロシージャが他のブロックにネストされている場合、キーワード「IS」が使用されます。 プロシージャがスタンドアロンの場合は、「AS」が使用されます。 このコーディング標準以外は、両方とも同じ意味を持ちます。
例1: 関数を作成し、匿名ブロックを使用して呼び出す
このプログラムでは、名前を入力として受け取り、ウェルカム メッセージを出力として返す関数を作成します。 無名ブロックと select ステートメントを使用して関数を呼び出します。
CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2 IS BEGIN RETURN (‘Welcome ‘|| p_name); END; / DECLARE lv_msg VARCHAR2(250); BEGIN lv_msg := welcome_msg_func (‘Guru99’); dbms_output.put_line(lv_msg); END; SELECT welcome_msg_func(‘Guru99:) FROM DUAL;
コードの説明：
- コード行 1: の作成 Oracle 「welcome_msg_func」という名前と「IN」タイプの 1 つのパラメータ「p_name」を持つ関数。
- コード行 2: 戻り値の型を VARCHAR2 として宣言しています
- コード行 5: 連結値「Welcome」とパラメータ値を返します。
- コード行 8: 上記の関数を呼び出すための匿名ブロック。
- コード行 9: 関数の戻りデータ型と同じデータ型で変数を宣言しています。
- コード行 11: 関数を呼び出し、戻り値を変数 'lv_msg' に設定します。
- コード行 12: 変数の値を出力します。 ここで得られる出力は「Welcome Guru99」です。
- コード行 14: SELECT ステートメントを通じて同じ関数を呼び出します。 戻り値は標準出力に直接出力されます。
プロシージャと関数の類似点
- どちらも他の PL/SQL ブロックから呼び出すことができます。
- サブプログラムで発生した例外がサブプログラムで処理されない場合 例外処理 セクションに到達すると、呼び出し側ブロックに伝播されます。
- どちらも必要な数のパラメータを持つことができます。
- どちらも、PL/SQL ではデータベース オブジェクトとして扱われます。
手順と機能: 主な違い
| 手順 | 演算 |
|---|---|
| 主に特定の処理を実行するために使用されます | 主に何らかの計算を実行するために使用されます |
| SELECT ステートメントで呼び出すことはできません | DML ステートメントを含まない関数を SELECT ステートメントで呼び出すことができます |
| OUT パラメータを使用して値を返します | 値を返すには RETURN を使用します |
| 値を返すことは必須ではありません | 値を返すことは必須です |
| RETURN は単にサブプログラムからコントロールを終了します。 | RETURN はサブプログラムからコントロールを終了し、値も返します |
| 戻り値のデータ型は作成時に指定されません | 戻り値のデータ型は作成時に必須です |
PL/SQLの組み込み関数
PL / SQLの 文字列と日付データ型を操作するためのさまざまな組み込み関数が含まれています。 ここでは、よく使われる関数とその使い方を見ていきます。
変換関数
これらの組み込み関数は、あるデータ型を別のデータ型に変換するために使用されます。
| 関数名 | 使用法 | 例 |
|---|---|---|
| TO_CHAR | 他のデータ型を文字データ型に変換します | TO_CHAR(123); |
| TO_DATE (文字列、形式) | 指定された文字列を日付に変換します。 文字列は形式と一致する必要があります。 |
TO_DATE('2015-JAN-15', 'YYYY-MON-DD'); 出力：1 / 15 / 2015 |
| TO_NUMBER (テキスト、フォーマット) |
テキストを指定された形式の数値型に変換します。 入力形式「9」は桁数を表します |
デュアルから TO_NUMBER('1234','9999') を選択します。
出力: 1234 デュアルから TO_NUMBER('1,234.45','9,999.99') を選択します。 出力: 1234 |
文字列関数
これらは文字データ型で使用される関数です。
| 関数名 | 使用法 | 例 |
|---|---|---|
| INSTR(テキスト、文字列、開始、出現) | 指定された文字列内の特定のテキストの位置を示します。
|
デュアルから INSTR('AEROPLANE','E',2,1) を選択します
出力: 2 デュアルから INSTR('AEROPLANE','E',2,2) を選択します 出力：9（2nd E)の発生 |
| SUBSTR (テキスト、開始、長さ) | メイン文字列のサブ文字列値を与えます。
|
select substr('aeroplane',1,7) from Dual
出力：エアロプラ |
| 上部 (テキスト) | 指定されたテキストの大文字を返します | デュアルからアッパー('guru99')を選択します。
出力: グル99 |
| 下（テキスト） | 指定されたテキストの小文字を返します | デュアルから下側 ('AerOpLane') を選択します。
出力： 飛行機 |
| INITCAP (テキスト) | 指定されたテキストの先頭文字を大文字にして返します。 | デュアルから('guru99')を選択
出力: グル99 デュアルから（「私のストーリー」）を選択してください 出力： 私の物語 |
| 長さ (テキスト) | 指定された文字列の長さを返します | デュアルから LENGTH ('guru99') を選択します。
出力: 6 |
| LPAD (テキスト、長さ、pad_char) | 指定された長さ (合計文字列) の文字列の左側を指定された文字で埋めます。 | デュアルから LPAD('guru99', 10, '$') を選択します。
出力: $$$$グル99 |
| RPAD (テキスト、長さ、pad_char) | 指定された長さ (合計文字列) の文字列の右側を指定された文字で埋めます。 | デュアルから RPAD('guru99',10,'-') を選択します
出力: guru99—- |
| LTRIM (テキスト) | テキストから先頭の空白を削除します。 | デュアルから LTRIM(' Guru99') を選択します。
出力: グル99 |
| RTRIM (テキスト) | テキストの末尾の空白を削除します。 | デュアルから RTRIM('Guru99 ') を選択します。
出力; グル99 |
日付関数
これらは日付を操作するために使用される関数です。
| 関数名 | 使用法 | 例 |
|---|---|---|
| ADD_MONTHS (日付、月数) | 指定された月を日付に追加します | ADD_MONTH('2015-01-01',5);
出力：05 / 01 / 2015 |
| システムデート | サーバーの現在の日付と時刻を返します。 | デュアルから SYSDATE を選択します。
出力: 10/4/2015 2:11:43 |
| TRUNC | 日付変数を可能な限り低い値に丸める | デュアルから sysdate、TRUNC(sysdate) を選択します。
出力: 10/4/2015 午後 2:12:39 10/4/2015 |
| ROUND | 日付を最も近い上限または下限に丸めます | デュアルから sysdate、ROUND(sysdate) を選択
出力: 10/4/2015 午後 2:14:34 10/5/2015 |
| MONTHS_BETWEEN | XNUMX つの日付の間の月数を返します | デュアルから MONTHS_BETWEEN (sysdate+60, sysdate) を選択します
出力: 2 |
製品概要
この章では、次のことを学びました。
- プロシージャの作成方法とさまざまな呼び出し方法
- Function の作成方法とそれを呼び出すさまざまな方法
- プロシージャとファンクションの類似点と相違点
- PL/SQLサブプログラムのパラメータとRETURNの一般的な用語
- 一般的な組み込み関数 Oracle PL / SQLの
