PL/SQLの単一行関数とグループ関数の詳細説明 関数とは、0個以上のパラメータと戻り値を持つプログラムです。 Oracle には SQL に一連の組み込み関数があります。
これらの関数は SQL または PL/SQL ステートメントと呼ばれることがあり、主に次の 2 つのカテゴリに分類されます。
単一行関数のグループ関数 この記事では、単一行関数の使用方法とその使用ルールについて説明します。
SQLの単一行関数
SQL および PL/SQL には、文字、数値、日付、変換、単一行のデータを処理するための混合関数など、さまざまなタイプの関数が付属しています。
したがって、これらをまとめて単一行関数と呼ぶことができます。これらの関数は、SELECT、WHERE、ORDER BY およびその他の句で使用できます。
たとえば、次の例には、TO_CHAR、UPPER、SOUNDEX などの単一行関数が含まれています。
SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')
従業員から
Where UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename)
単一行関数は、更新の SET 句、INSERT の VALUES 句、DELET の WHERE 句など、他のステートメントでも使用できます。
認定試験では、SELECT ステートメントでのこれらの関数の使用に特に注意を払うため、SELECT ステートメントにも注目が集まっています。
NULL と単一行関数 NULL を理解することは最初は難しい場合があり、経験豊富な人でも混乱することがあります。
NULL 値は、不明なデータまたは NULL 値を表し、算術演算子のオペランドはすべて NULL 値であり、結果も NULL 値になります。
このルールは、多くの関数にも適用されます。NULL パラメーターを指定して呼び出された場合、CONCAT、DECODE、DUMP、NVL、および REPLACE のみが非 NULL 値を返すことができます。
このうち、NVL 関数は NULL 値を直接処理できるため、最も重要です。
NVL には 2 つのパラメータがあります: NVL(x1,x2)。x1 と x2 は両方とも式です。x1 が null の場合は X2 が返され、それ以外の場合は x1 が返されます。
emp データテーブルを見てみましょう。給与とボーナスの合計を計算する必要があります。
列名
emp_id 給与ボーナスキーのタイプ
pk nulls/unique nn,u nnfk テーブル データ型 数値 数値 長さ 11.2 11.2
給与とボーナスを単純に合計するのではなく、次の例のように、特定の行に null 値がある場合、結果は null になります。
empset給与を更新=(給与+ボーナス)*1.1
このステートメントでは、従業員の給与とボーナスが新しい値に更新されますが、ボーナスがない場合は、
つまり、給与 + null の場合、誤った結論が導かれます。このとき、nvl 関数を使用して null 値の影響を排除する必要があります。
したがって、正しいステートメントは次のようになります。
empset給与を更新=(給与+nvl(ボーナス,0)*1.1
単一行の文字列関数 単一行の文字列関数は、文字列データを操作するために使用されます。そのほとんどは 1 つ以上のパラメーターを持ち、文字列を返します。
アスキー()
c1 は文字列で、c1 の最初の文字の ASCII コードを返します。その逆関数は CHR() です。
SELECT ASCII('A') BIG_A,ASCII('z') BIG_z
empBIG_A BIG_z65 から 122
CHR(<i>)[NCHAR_CS]
i は数値であり、関数は文字の 10 進表現を返します。
CHR(65)、CHR(122)、CHR(223)を選択
empCHR65 CHR122 CHR223A z B から
CONCAT(,)
c1 と c2 は両方とも文字列です。この関数は c2 を c1 の後ろに接続します。c1 が null の場合は c2 が返され、c2 が null の場合は c1 が返されます。
c1 と c2 の両方が null の場合は、null を返します。演算子 || と同じ結果を返します。
concat('slobo ','Svoboda') ユーザー名を選択します
デュアルユーザー名lobo Shoboda より
INITCAP()
c1 は文字列です。この関数は、各単語の最初の文字を大文字で返し、他の文字を小文字で返します。単語はスペース、制御文字、
句読点の制限。
select INITCAP('veni,vedi,vici') Ceasar
デュアルシーザーからVeni、Vedi、Vici
INSTR(,[,＜i＞[,]])
c1 と c2 は両方とも文字列で、i と j は整数です。この関数は、c1 内で j 番目に出現する c2 の位置を返し、検索は c1 の i 番目の文字から開始されます。
必要な文字が見つからない場合は 0 が返されます。i が負の数の場合、検索は右から左に実行されますが、位置は左から右に計算されます。
i と j のデフォルト値は 1 です。
SELECT INSTR('ミシシッピ','i',3,3)
FROM デュアル INSTR('MISSISSIPPI','I',3,3)
11
select INSTR('ミシシッピ','i',-2,3)
デュアル INSTR('MISSISSIPPI','I',3,3) から
2
INSTRB(,[,i[,j])
INSTR() 関数と同じですが、シングルバイトの INSTRB() は INSTR() と同じです。
長さ（）
c1 は文字列であり、c1 が null の場合は、c1 の長さが返されます。
LENGTH('Ipso Facto') エルゴを選択してください
デュアルエゴ10より
長さb()
LENGTH() と同様に、バイトを返します。
より低い（）
c の小文字を返します。これは、where の部分文字列によく出現します。
下を選択(色名)
商品詳細より
WHERE LOWER(色名) LIKE '%white%'COLORNAMEWinterwhite
LPAD(,＜i＞[,])
c1 と c2 は両方とも文字列で、i は整数です。 c2 文字列を使用して c1 の左側の長さ i を補完します。i が c1 の長さより小さい場合は、これを複数回繰り返すことができます。
次に、i の長さの c1 文字のみが返され、その他は切り捨てられます。 c2 のデフォルト値は単一のスペースです。RPAD を参照してください。
select LPAD(answer,7,'') パディング、アンパディング
質問から;
パッドあり パッドなし はい はい いいえ いいえ多分多分
LTRIM(,)
c2 に最初の文字が含まれないように、c1 の左端の文字を削除します。c2 がない場合、c1 は変更されません。
DualLTRppi から LTRIM('Mississippi','Mis') を選択します
RPAD(,＜i＞[,])
c2 文字列を使用して、c1 の右側の長さ i を補完します。これを複数回繰り返すことができます。i が c1 の長さより小さい場合、i と同じ長さの c1 文字のみが返されます。
その他は切り捨てられます。 c2 のデフォルト値は 1 つのスペースで、その他は LPAD と同様です。
RTRIM(,)
c2 に最後の文字が含まれないように、c1 の右端の文字を削除します。c2 がない場合、c1 は変更されません。
交換する（、[、]）
c1、c2、c3 はすべて文字列です。この関数は、c1 に含まれる c2 を c3 に置き換えて返します。
select REPLACE('アップタウン','アップ','ダウン')
デュアルREPLACEダウンタウンより
STBSTR(,<i>[,])
c1 は文字列、i と j は整数で、長さ j の部分文字列が c1 の i 番目の位置から返されるか、j が空の場合は文字列の終わりまで返されます。
select SUBSTR('メッセージ',1,4)
デュアルSUBSMessから
SUBSTRB(,<i>[,])
I と J がバイト単位で計算される点を除けば、SUBSTR とほぼ同じです。
サウンドデックス()
c1 に似た発音の単語を返します
select SOUNDEX('dawes') ドーズ SOUNDEX('daws') ドーズ、SOUNDEX('dawson')
デュアルから Dawes Daws DawsonD200 D200 D250
翻訳する（、、）
c1 内の c2 と同じ文字を c3 に置き換えます。
DualTEXTramble から TRANSLATE('fumble','uf','ar') テストを選択
TRIM([[]] c3 から)
c3 文字列の最初、最後、または両方を削除します。
select TRIM(' スペースパッド ') デュアル TRIMスペースパッドからトリミング
アッパー（）
c1 の大文字バージョンを返します。これは、where 部分文字列によく現れます。
select name from Dual where UPPER(name) LIKE 'KI%'NAMEKING
単一行の数値関数 単一行の数値関数は、数値データを操作し、数学的および算術演算を実行します。すべての関数は数値パラメータを受け取り、数値を返します。
すべての三角関数のオペランドと値は、角度ではなくラジアンです。Oracle には、ラジアンと角度の組み込み変換関数が提供されていません。
ABS()
nの絶対値を返します。
アコス()
逆余因子関数は、-1 から 1 までの数値を返します。 nはラジアンを表します
ACOS(-1) pi,ACOS(1) ゼロを選択
デュアルPI ZERO3.14159265 から 0
ASIN()
とにかく、謎の関数は-1から1を返します、nはラジアンを表します
あたん()
逆正接関数は、n の逆正接値を返します。ここで、n はラジアンを表します。
CEIL()
n 以上の最小の整数を返します。
COS()
n の共値を返します。n はラジアンです。
コシュ()
n の双曲線余因子を返します。n は数値です。
COSH(<1.4>) を選択します
デュアルCOSH(1.4)2.15089847から
経験値()
e の n 乗、e=2.71828183 を返します。
床（）
N 以下の最大の整数を返します。
LN()
N の自然対数を返します。これは 0 より大きい必要があります。
ログ（、）
n1 の底を n2 とする対数を返します。
MOD()
n1 を n2 で割った余りを返します。
力（、）
n1 の n2 乗を返します
ラウンド（、）
n1 の値を小数点以下 n2 桁に四捨五入して返します。n2 のデフォルト値は 0 です。今回は、小数点に最も近い整数が四捨五入されます。
n2 が負の数の場合、小数点の左側の対応する桁に丸められます。n2 は整数でなければなりません。
select ROUND(12345,-2),ROUND(12345.54321,2)
FROM デュアルROUND(12345,-2) ROUND(12345.54321,2)12300 12345.54
サイン（）
n が負の数の場合は -1 が返され、n が正の数の場合は 1 が返され、n=0 の場合は 0 が返されます。
罪（）
n の正の値を返します。n はラジアンです。
シン()
n の正の双曲線値を返します。n はラジアンです。
SQRT()
n の平方根を返します。n はラジアンです。
タン()
n のタンジェントを返します。n はラジアンです。
タン()
n の双曲線正接を返します。n はラジアンです。
トランク(,)
小数点以下 n2 桁に切り捨てられた n1 の値を返します。n2 のデフォルト設定は 0 です。n2 がデフォルト設定の場合、n1 は整数に切り捨てられます。
n2 が負の値の場合、小数点の左側の対応する位置で切り捨てられます。
単一行の日付関数
単一行の日付関数は DATA データ型で動作し、そのほとんどには DATA データ型のパラメーターがあります。
返される値のほとんどは DATA データ型の値でもあります。
ADD_MONTHS(,＜i＞)
日付 d に i か月を加えた結果を返します。私は任意の整数にすることができます。 i が 10 進数の場合、
その後、データベースはそれを暗黙的に整数に変換し、小数点以下の部分を切り捨てます。
LAST_DAY()
関数は日付 d を含む月の最後の日を返します。
MONTHS_BETWEEN(,)
d1 と d2 の日付が同じであるか、両方とも月の最終日である場合、d1 と d2 の間の月数を返します。
この場合、整数が返されます。それ以外の場合、返される結果には小数が含まれます。
NEW_TIME(,,)
d1 は日付データ型です。タイムゾーン tz1 の日時が d の場合、タイムゾーン tz2 の日時を返します。
tz1 と tz2 は文字列です。
NEXT_DAY(,)
dow で指定された日付 d の次の最初の日を返します。これは、現在のセッションで指定された言語を使用して曜日を指定します。
返される時間コンポーネントは、d の時間コンポーネントと同じです。
select NEXT_DAY('01-Jan-2000','Monday') "第 1 月曜日",
NEXT_DAY('2004 年 11 月 1 日','火曜日')+7 "第 2 火曜日")
デュアルから。
第 1 月曜日 第 2 火曜日 2000 年 1 月 3 日 2004 年 11 月 9 日
ラウンド（[、]）
fmt で指定された形式 (文字列) に従って日付 d を丸めます。
社立
この関数はパラメータをとらず、現在の日付と時刻を返します。
TRUNC([,])
fmt で指定された単位で日付 d を返します。
単行変換関数 複数のデータ型を演算したり、データ型間の変換を行う場合に使用します。
CHARTORWID()
c は文字列を作成し、関数は c を RWID データ型に変換します。
テスト ID を選択します
テストケースから
ここで、rowid=CHARTORWID('AAAA0SAACAAAALiAAA')
変換する（、[、]）
c 末尾文字列、dset および sset は 2 つの文字セットです。この関数は、文字列 c を sset 文字セットから dset 文字セットに変換します。
sset のデフォルト設定は、データベースの文字セットです。
ヘクストロー()
x は 16 進数の文字列であり、関数は 16 進数の x を RAW データ型に変換します。
RAWTOHEX()
x は RAW データ型文字列であり、関数は RAW データ型を 16 進データ型に変換します。
ROWIDTOCHAR()
ROWID データ型を CHAR データ型に変換する関数です。
TO_CHAR([[,)
x はデータまたは数値データ型であり、この関数は x を fmt で指定された形式の char データ型に変換します。
x が日付の場合、nlsparm= NLS_DATE_LANGUAGE は、返される月と日に使用される言語を制御します。
x が数値の場合、nlsparm=NLS_NUMERIC_CHARACTERS を使用して、小数点と千の位の区切り文字、および通貨記号を指定します。
NLS_NUMERIC_CHARACTERS="dg"、NLS_CURRENCY="文字列"
現在まで（[、[、）
c は文字列を表し、fmt は特別な形式の文字列を表します。 fmt 形式で表示された c を返します。nlsparm は使用されている言語を示します。
この関数は、文字列 c を日付データ型に変換します。
TO_MULTI_BYTE()
c は文字列を表し、関数は c の切り捨て文字をマルチバイト文字に変換します。
TO_NUMBER([,[,)
c は文字列、fmt は特殊な形式の文字列を表し、関数の戻り値は fmt で指定された形式で表示されます。
nlsparm は言語を表し、関数は c で表される数値を返します。
TO_SINGLE_BYTE()
文字列 c 内のマルチバイト文字を同等のシングルバイト文字に変換します。
この関数は、データベース文字セットにシングルバイト文字とマルチバイト文字の両方が含まれている場合にのみ使用されます。
その他の単一行関数
BFILENAME(,)
dir はディレクトリ型のオブジェクト、file はファイル名です。この関数は空の BFILE 位置値インジケータを返します。
関数は、BFILE変数またはBFILE列を初期化するために使用されます。
デコード(,,[,,,[])
x は式、m1 は一致する式、x は m1 と比較され、m1 が x と等しい場合は r1 が返され、そうでない場合は x が m2 と比較されます。
結果が返されるまで、m3、m4、m5... と続きます。
ごみ（、[、[、[、]]]）
x は式または文字で、fmt は 8 進数、10 進数、16 進数、または単一の文字を表します。
この関数は、x の内部表現に関する情報を含む VARCHAR2 型の値を返します。
n1,n2 を指定すると、n1 から始まる長さ n2 のバイトが返されます。
EMPTY_BLOB()
この関数にはパラメーターがなく、空の BLOB 位置インジケーターを返します。 BLOB 変数または BLOB 列を初期化するために使用される関数。
EMPTY_CLOB()
この関数にはパラメータがなく、空の CLOB 位置インジケータを返します。 CLOB 変数または CLOB 列を初期化するために関数が使用されます。
最大（）
exp_list は式のリストで、最大の式を返します。各式は暗黙的に最初の式のデータ型に変換されます。
最初の式が文字列データ型の場合、返される結果は varchar2 データ型になります。
同時に使用される比較は、非スペース充填タイプの比較です。
少しでも（）
exp_list は式のリストで、その中の最小の式を返します。各式は暗黙的に最初の式のデータ型に変換されます。
最初の式が文字列データ型の場合、返される結果は varchar2 データ型になります。
同時に使用される比較は、非スペース充填タイプの比較です。
UID
この関数にはパラメータがなく、現在のデータベース ユーザーを一意に識別する整数を返します。
ユーザー
現在のユーザーのユーザー名を返します
USERENV()
opt に基づいて、現在のセッション情報が返されます。 opt のオプションの値は次のとおりです。
SYSDBA ロールは ISDBA セッションで応答し、TRUE を返します。
SESSIONID は監査セッション ID を返します。
ENTRYID は、使用可能な監査エントリ識別子を返します
INSTANCE セッション接続後のインスタンス識別子を返します。
この値は、並列サーバーを実行していて複数のインスタンスがある場合にのみ使用されます。
LANGUAGE は、言語、地域、およびデータベース設定の文字セットを返します。
LANG は、言語名の ISO 略語を返します。
TERMINAL 現在のセッションで使用されている端末またはコンピュータのオペレーティング システム識別子を返します。
VSIZE()
x は式です。 x によって内部的に表されるバイト数を返します。
SQL のグループ関数は集計関数とも呼ばれ、複数の行に基づいて 1 つの結果を返します。正確な行数は決定できません。
クエリが実行され、すべての結果が含まれる場合を除きます。単一行関数とは異なり、解析時にすべての行が認識されます。
この違いにより、グループ関数の要件と動作は単一行関数とは若干異なります。
グループ（複数行）関数 単一行関数と比較して、Oracle は豊富なグループベースの複数行関数を提供します。
これらの関数は、select または select の Hasting 句で使用でき、部分文字列の選択に使用される場合は、GROUP BY とともによく使用されます。
AVG([{DISYINCT|ALL}])
数値の平均を返します。デフォルト設定は「すべて」です。
SELECT AVG(sal)、AVG(ALL sal)、AVG(DISTINCT sal)
FROM scott.empAVG(SAL) AVG(ALL SAL) AVG(DISTINCT SAL)1877.94118 1877.94118 1916.071413
COUNT({*|DISTINCT|ALL} )
クエリ内の行数を返します。デフォルト設定は ALL です。* はすべての行を返すことを意味します。
MAX([{DISTINCT|ALL}])
x が文字列データ型の場合、選択リスト項目の最大値を返します。VARCHAR2 データ型を返します。
X が DATA データ型の場合は日付を返し、X が数値データ型の場合は数値を返します。
[distinct] と [all] は効果がないことに注意してください。最大値は両方の設定で同じである必要があります。
MIN([{DISTINCT|ALL}])
選択リスト項目の最小値を返します。
STDDEV([{DISTINCT|ALL}])
セレクターのリスト項目の標準偏差を返します。標準偏差は分散の平方根です。
SUM([{DISTINCT|ALL}])
選択したリスト項目の数値の合計を返します。
VARIANCE([{DISTINCT|ALL}])
選択したリスト項目の統計的分散を返します。
GROUP BY を使用してデータをグループ化する タイトルが示すように、グループ関数はグループ化されたデータに対して動作します。
SELECT ステートメントの SELECT 句でグループ関数を使用する場合、GROUP BY を使用してデータをグループ化または分類する方法をデータベースに指示します。
group by が特別な処理に使用されない場合は、グループ化された列または非定数列を GROUP BY 句に配置する必要があります。
次に、デフォルトの分類では、結果全体が 1 つのカテゴリに設定されます。
select stat,counter(*) zip_count
zip_codes GROUP BY 状態から。
ST ZIP_COUNT----------AK 360AL 1212AR 1309AZ 768CA 3982
この例では、state フィールドを使用して分類しています。郵便番号に従って結果を並べ替えたい場合は、ORDER BY ステートメントを使用できます。
ORDER BY 句では、列関数またはグループ関数を使用できます。
select stat,counter(*) zip_count
郵便番号から
GROUP BY 状態 ORDER BY COUNT(*) DESC;
ST カウント(*)----------ニューヨーク 4312PA 4297TX 4123CA 3982
HAVING 句を使用してグループ化されたデータを制限する
クエリの SELECT ステートメントと ORDER BY 句で main 関数を使用する方法がわかりました。グループ関数は 2 つの部分文字列に対してのみ使用できます。
グループ関数は WHERE 部分文字列では使用できません。たとえば、次のクエリは間違っています。
間違い
SELECT 販売員,SUN(販売額)
総売上高から
WHERE sales_dept='OUTSIDE' AND SUM(sale_amount)>10000
GROUP BY 店員
このステートメントでは、データベースに行をグループ化し、グループ化された行の出力を制限するように指示する必要がある場合、データベースは SUM() が何であるかを認識しません。
正しい方法は、HAVING ステートメントを使用することです。
SELECT 販売員,SUN(販売額)
総売上高から
WHERE sales_dept='外側'
GROUP BY 店員
SUM(sale_amount)>10000;
ネストされた関数 関数はネストできます。ある関数の出力が別の関数の入力になる場合があります。オペランドには継承可能な実行プロセスがあります。
ただし、機能の優先順位は位置にのみ基づいており、機能は内側から外側、左から右の原則に従います。
ネスト技術は、論理的な判断文 IF...THEN...ELSE で使用できる DECODE などの関数に一般的に使用されます。