| Oracle Rdb SQLリファレンス・マニュアル リリース7.2 E06178-01 |
|
 戻る |
 次へ |
付録G
Oracle RDBMSの互換性G.1 Oracle RDBMSファンクション
Oracle SQLとの収束のために、OpenVMS Oracle Rdb SQLインタフェースにはSQLファンクションが追加されています。これらのファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
G.1.1 オプションのOracle SQLファンクション
オプションで、次の例に示すように、表G-1のファンクションを対話型SQLからデータベースにインストールできます。
ファイル名は、SQL_FUNCTIONSnn.SQLです（nnはバージョン番号）。たとえば、次の文を使用します。
SQL> ATTACH 'FILENAME mydatabase'; SQL> @SYS$LIBRARY:SQL_FUNCTIONS71.SQL |
インストール可能なファンクションでDEC_MCS以外のキャラクタ・セットを使用する場合は、前述の文を実行する前に、そのキャラクタ・セットを使用するキャラクタ・タイプとしてRDB$ORACLE_SQLFUNC_VCHAR_DOMドメインを定義する必要があります。同様に、インストール可能なファンクションでDATE VMS以外のDATEデータ型を使用する場合は、前述の文を実行する前に、DATEデータ型としてRDB$ORACLE_SQLFUNC_DATE_DOMドメインを定義する必要があります。
次に例を示します。
SQL> ATTACH 'FILENAME mydatabase'; SQL> CREATE DOMAIN RDB$ORACLE_SQLFUNC_VCHAR_DOM VARCHAR(2000) cont> CHARACTER SET KANJI; SQL> CREATE DOMAIN RDB$ORACLE_SQLFUNC_DATE_DOM DATE ANSI; SQL> @SYS$LIBRARY:SQL_FUNCTIONS71.SQL |
インストール可能なファンクションは、後で不要になった場合にデータベースから削除できます。ただし、ファンクションを削除する前に、そのファンクションを参照する動的SQL文を解放し、セッションを切断する必要があります。インストール可能なファンクションをデータベースから削除する場合は、対話型SQLから次の文を使用します。
SQL> ATTACH 'FILENAME mydatabase'; SQL> @SYS$LIBRARY:SQL_FUNCTIONS_DROP71.SQL |
ファイル名は、SYS$LIBRARY:SQL_FUNCTIONS_DROPnn.SQLです（nnはバージョン番号）。
表G-1に、オプションでデータベースにインストールできる各ファンクションの簡単な説明を示します。
| ファンクション名 | 説明 | 制限 |
|---|---|---|
| ADD_MONTHS (d,n) | 日付dにnか月を加算して返す。 | dはRDB$ORACLE_SQLFUNC_DATE_DOMドメインと同じDATEデータ型である必要があり、このデータ型はOracle SQLファンクションのインストール時にバインドされる。 |
| ASCII (str) | 引数の最初の文字の10進表現を返す。 | strはRDB$ORACLE_SQLFUNC_VCHAR_DOMドメインと同じキャラクタ・セットである必要があり、このキャラクタ・セットはOracle SQLファンクションのインストール時にバインドされる。 |
| CEIL (n) | n以上の最小の整数を返す。 | |
| CHR (n) | nに等しいバイナリを持つ文字を返す。 | 戻り値の型はRDB$ORACLE_SQLFUNC_VCHAR_DOMであり、そのキャラクタ・セットはOracle SQLファンクションのインストール時にバインドされる。また、1オクテット（バイト）のデータのみがエンコードされる。 |
| COS (n) | nの余弦（ラジアンで表した角度）を返す。 | |
| COSH (n) | nの双曲線余弦（ラジアンで表した角度）を返す。 | |
| EXP (n) | eのn乗を返す（e=2.71828183...）。 | |
| FLOOR (n) | n以下の最大の整数を返す。 | |
| HEXTORAW (str) | 16進数を含む引数をRAW文字値に変換する。 | strはRDB$ORACLE_SQLFUNC_VCHAR_DOMドメインと同じキャラクタ・セットである必要があり、このキャラクタ・セットはOracle SQLファンクションのインストール時にバインドされる。戻り値の型はRDB$ORACLE_SQLFUNC_VCHAR_DOM。 |
| INITCAP (str) | 文字列引数の各単語の先頭文字を大文字、他の文字をすべて小文字にして返す。単語は英数以外の文字で区切られる。 | strはRDB$ORACLE_SQLFUNC_VCHAR_DOMドメインと同じキャラクタ・セットである必要があり、このキャラクタ・セットはOracle SQLファンクションのインストール時にバインドされる。戻り値の型はRDB$ORACLE_SQLFUNC_VCHAR_DOM。 |
| INSTR (s1,s2[,n[,m]]) | s1のn番目の文字から検索を開始し、s2がm回目に発生する文字位置を返す。s2がm回発生しない場合は0を返す。n < 0の場合、検索はs1の末尾から開始される。 | s1およびs2はRDB$ORACLE_SQLFUNC_VCHAR_DOMドメインと同じキャラクタ・セットである必要があり、このキャラクタ・セットはOracle SQLファンクションのインストール時にバインドされる。nまたはmが省略された場合、デフォルトは1になる。 |
| INSTRB (s1,s2[,n[,m]]) | s1のn番目のオクテットから検索を開始し、s2がm回目に発生するオクテットの位置を返す。s2がm回発生しない場合は0を返す。n < 0の場合、検索はs1の末尾から開始される。 | s1およびs2はRDB$ORACLE_SQLFUNC_VCHAR_DOMドメインと同じキャラクタ・セットである必要があり、このキャラクタ・セットはOracle SQLファンクションのインストール時にバインドされる。nまたはmが省略された場合、デフォルトは1になる。 |
| LAST_DAY (d) | dを含む月の末日を返す。 | dはRDB$ORACLE_SQLFUNC_DATE_DOMドメインと同じDATEデータ型である必要があり、このデータ型はOracle SQLファンクションのインストール時にバインドされる。戻り値の型はRDB$ORACLE_SQLFUNC_DATE_DOM。 |
| LN (n) | nの自然対数を返す。ここで、nは0より大きい値。 | |
| LOG (m,n) | nの対数の底mを返す。底mには0または1以外の任意の正の数、nには任意の正の数を指定できる。 | |
| LPAD (s,l,p) | sの長さがlになるまで、sの左側にpの文字を連続的に埋めて返す。sがlより長い場合は、lに収まるsの部分を返す。 | sおよびpはRDB$ORACLE_SQLFUNC_VCHAR_DOMドメインと同じキャラクタ・セットである必要があり、このキャラクタ・セットはOracle SQLファンクションのインストール時にバインドされる。戻り値の型はRDB$ORACLE_SQLFUNC_VCHAR_DOM。Oracleと異なり、pのデフォルトはない。 |
| LTRIM (s1[,s2]) | s1の先頭（左）から、s2に含まれない最初の文字までを削除する。 | s1およびs2はRDB$ORACLE_SQLFUNC_VCHAR_DOMドメインと同じキャラクタ・セットである必要があり、このキャラクタ・セットはOracle SQLファンクションのインストール時にバインドされる。戻り値の型はRDB$ORACLE_SQLFUNC_VCHAR_DOM。s2が省略された場合、デフォルトは空白になる。 |
| MOD (m,n) | mをnで割った余りを返す。nが0の場合はmを返す。 | |
| MONTHS_BETWEEN (d1,d2) | 日付d1とd2の間の月数を返す。 | d1およびd2はRDB$ORACLE_SQLFUNC_DATE_DOMドメインと同じDATEデータ型である必要があり、このデータ型はOracle SQLファンクションのインストール時にバインドされる。 |
| NEW_TIME (d1,z1,z2) | タイムゾーンz1のdの日時に対応するタイムゾーンz2の日時を返す。タイムゾーンz1およびz2に指定できる値は次のとおり: AST、ADT、BST、BDT、CST、CDT、EST、EDT、GMT、HST、HDT、MST、MDT、NST、PST、PDT、YST、YDT。 | d1はRDB$ORACLE_SQLFUNC_DATE_DOMドメインと同じDATEデータ型である必要があり、このデータ型はOracle SQLファンクションのインストール時にバインドされる。z1およびz2はRDB$ORACLE_SQLFUNC_VCHAR_DOMドメインと同じキャラクタ・セットである必要があり、このキャラクタ・セットもOracle SQLファンクションのインストール時にバインドされる。戻り値の型はRDB$ORACLE_SQLFUNC_DATE_DOM。 |
| NEXT_DAY (d,dayname) | 日付dより後の、daynameで指定された曜日の最初の日付を返す。 | dはRDB$ORACLE_SQLFUNC_DATE_DOMドメインと同じDATEデータ型である必要があり、このデータ型はOracle SQLファンクションのインストール時にバインドされる。daynameはRDB$ORACLE_SQLFUNC_VCHAR_DOMドメインと同じキャラクタ・セットである必要があり、このキャラクタ・セットもOracle SQLファンクションのインストール時にバインドされる。戻り値の型はRDB$ORACLE_SQLFUNC_DATE_DOM。 |
| POWER (m,n) | mのn乗を返す。底mと指数nには任意の数値を指定できるが、mが負の数の場合はnを整数にする必要がある。 | |
| RAWTOHEX (str) | RAW引数を、それに相当する16進数を含む文字値に変換する。 | strはRDB$ORACLE_SQLFUNC_VCHAR_DOMドメインと同じキャラクタ・セットである必要があり、このキャラクタ・セットもOracle SQLファンクションのインストール時にバインドされる。戻り値の型はRDB$ORACLE_SQLFUNC_VCHAR_DOM。 |
| REPLACE (s1[,s2[,s3]]) | s2が発生するたびにs3に置き換えてs1を返す。 | s1、s2およびs3はRDB$ORACLE_SQLFUNC_VCHAR_DOMドメインと同じキャラクタ・セットである必要があり、このキャラクタ・セットはOracle SQLファンクションのインストール時にバインドされる。戻り値の型はRDB$ORACLE_SQLFUNC_VCHAR_DOM。s2およびs3が省略された場合、デフォルトは空の文字列になる。 |
| RPAD (s[,l[,p]]) | sの長さがlになるまで、sの右側にpの文字を連続的に埋めて返す。sがlより長い場合は、lに収まるsの部分を返す。 | sおよびpはRDB$ORACLE_SQLFUNC_VCHAR_DOMドメインと同じキャラクタ・セットである必要があり、このキャラクタ・セットもOracle SQLファンクションのインストール時にバインドされる。戻り値の型はRDB$ORACLE_SQLFUNC_VCHAR_DOM。pが省略された場合、デフォルトは空白になる。 |
| RTRIM (s1[,s2]) | s2にはない最後の文字の後に最後の文字を追加してs1を返す。 | s1およびs2はRDB$ORACLE_SQLFUNC_VCHAR_DOMドメインと同じキャラクタ・セットである必要があり、このキャラクタ・セットもOracle SQLファンクションのインストール時にバインドされる。戻り値の型はRDB$ORACLE_SQLFUNC_VCHAR_DOM。s2が省略された場合、デフォルトは空白になる。 |
| SIGN (n) | n < 0の場合は-1を、n = 0の場合は0を、n > 0の場合は1を返す。 | |
| SIN (n) | nの正弦（ラジアンで表した角度）を返す。 | |
| SINH (n) | nの双曲線正弦（ラジアンで表した角度）を返す。 | |
| SQRT (n) | nの平方根を返す。nには負の値は指定できない。返される結果は倍精度。 | |
| SUBSTR (s[,p[,l]]) | sの一部を、文字位置pからl文字分返す。pが負の場合、sの末尾から逆方向にカウントする。 | sはRDB$ORACLE_SQLFUNC_VCHAR_DOMドメインと同じキャラクタ・セットである必要があり、このキャラクタ・セットもOracle SQLファンクションのインストール時にバインドされる。戻り値の型はRDB$ORACLE_SQLFUNC_VCHAR_DOM。lが省略された場合、デフォルトはゼロ（0）になる。 |
| SUBSTRB (s[,p[,l]]) | SUBSTRと同様であるが、pおよびlは文字ではなくオクテット（バイト）で表現される。 | sはRDB$ORACLE_SQLFUNC_VCHAR_DOMドメインと同じキャラクタ・セットである必要があり、このキャラクタ・セットもOracle SQLファンクションのインストール時にバインドされる。戻り値の型はRDB$ORACLE_SQLFUNC_VCHAR_DOM。lが省略された場合、デフォルトはゼロ（0）になる。 |
| TAN (n) | nの正接（ラジアンで表した角度）を返す。 | |
| TANH (n) | nの双曲線正接（ラジアンで表した角度）を返す。 |
G.2 Oracleスタイルの外部結合
Oracle Rdbでは、2つ以上の表の外部結合を実行するSQLデータベース言語規格構文（すなわちLEFT OUTER JOIN句、RIGHT OUTER JOIN句およびFULL OUTER JOIN句）がサポートされています。また、Oracle RDMS SQL言語で使用できる構文やセマンティクスに準拠した代替構文と代替セマンティクスもサポートされているため、この2つの製品間の互換性が向上します。特殊演算子（+）をWHERE句に指定すると、表は外部結合セマンティクスを使用して結合されます。
外部結合は、単純結合の結果を拡張します。外部結合では、結合条件を満たすすべての行に加えて、一方の表で結合条件を満たしていない行も他方の表から返されます。このような行は、単純結合では返されません。表AとBの外部結合を実行し、Aからすべての行を返す問合せを記述するには、外部結合演算子（+）を結合条件内のBのすべての列に適用します。一致する行がBにないAのすべての行に対しては、Bの列を含む選択リストの式にNULLが返されます。
外部結合問合せには、次のルールと制限が適用されます。
WHERE句に表Bの列と定数を比較する条件が含まれている場合は、（+）演算子をその列に適用し、この列に対してNULLが生成された行が表Aから返されるようにする必要があります。これを適用しない場合、単純結合の結果のみが返されます。
3つ以上の表の外部結合を実行する問合せでは、1つの表は、他の1つの表のみに対してNULLが生成される表として指定できます。このため、AとBの結合条件と、BとCの結合条件においては、Bの列に（+）演算子は適用できません。
G.2.1 外部結合の例
この項の例では、EMP表とDEPT表の次の内部結合（等価結合）の結果を拡張します。
SQL> SELECT ename, job, dept.deptno, dname cont> FROM emp, dept cont> WHERE emp.deptno = dept.deptno; EMP.ENAME EMP.JOB DEPT.DEPTNO DEPT.DNAME King President 10 Accounting Blake Manager 30 Sales Clark Manager 10 Accounting Jones Manager 20 Research Ford Analyst 20 Research Smith Clerk 20 Research Allen Salesman 30 Sales Ward Salesman 30 Sales Martin Salesman 30 Sales Scott Analyst 20 Research Turner Salesman 30 Sales Adams Clerk 20 Research James Clerk 30 Sales Miller Clerk 10 Accounting 14 rows selected |
次の問合せでは、外部結合を使用して、前述の等価結合の例の結果を拡張します。
SQL> SELECT ename, job, dept.deptno, dname cont> FROM emp, dept cont> WHERE emp.deptno (+) = dept.deptno; EMP.ENAME EMP.JOB DEPT.DEPTNO DEPT.DNAME King President 10 Accounting Clark Manager 10 Accounting Miller Clerk 10 Accounting Jones Manager 20 Research Ford Analyst 20 Research Smith Clerk 20 Research Scott Analyst 20 Research Adams Clerk 20 Research Blake Manager 30 Sales Allen Salesman 30 Sales Ward Salesman 30 Sales Martin Salesman 30 Sales Turner Salesman 30 Sales James Clerk 30 Sales NULL NULL 40 Operations 15 rows selected |
この外部結合では、OPERATIONS部門に従業員がいなくても、この部門を含む行が返されます。この行のENAME列とJOB列にはNULLが返されます。この例の結合問合せでは、従業員がいる部門のみが選択されます。
次の問合せでは、外部結合を使用して、前述の例の結果を拡張します。
SQL> SELECT ename, job, dept.deptno, dname cont> FROM emp, dept cont> WHERE emp.deptno (+) = dept.deptno cont> AND job (+) = 'Clerk'; EMP.ENAME EMP.JOB DEPT.DEPTNO DEPT.DNAME Miller Clerk 10 Accounting Smith Clerk 20 Research Adams Clerk 20 Research James Clerk 30 Sales NULL NULL 40 Operations 5 rows selected |
この外部結合では、OPERATIONS部門に事務員がいなくても、この部門を含む行が返されます。JOB列に（+）演算子が指定されているため、JOB列がNULLの行も返されます。この（+）が省略されると、JOBの値が'CLERK'ではないため、OPERATIONS部門を含む行は返されません。
SQL> SELECT ename, job, dept.deptno, dname cont> FROM emp, dept cont> WHERE emp.deptno (+) = dept.deptno cont> AND job = 'Clerk'; EMP.ENAME EMP.JOB DEPT.DEPTNO DEPT.DNAME Miller Clerk 10 Accounting Smith Clerk 20 Research Adams Clerk 20 Research James Clerk 30 Sales 4 rows selected |
この例では、CUSTOMERS表、ORDERS表、LINEITEMS表およびPARTS表に対する4つの外部結合問合せを示します。この4つの表は次のとおりです。
SQL> SELECT custno, custname
cont> FROM customers
cont> ORDER BY custno;
CUSTNO CUSTNAME
1 Angelic Co
2 Believable Co
3 Cables R Us
3 rows selected
SQL>
SQL> SELECT orderno, custno, orderdate
cont> FROM orders
cont> ORDER BY orderno;
ORDERNO CUSTNO ORDERDATE
9001 1 1999-10-13
9002 2 1999-10-13
9003 1 1999-10-20
9004 1 1999-10-27
9005 2 1999-10-31
5 rows selected
SQL>
SQL> SELECT orderno, lineno, partno, quantity
cont> FROM lineitems
cont> ORDER BY orderno, lineno;
ORDERNO LINENO PARTNO QUANTITY
9001 1 101 15
9001 2 102 10
9002 1 101 25
9002 2 103 50
9003 1 101 15
9004 1 102 10
9004 2 103 20
7 rows selected
SQL>
SQL> SELECT partno, partname
cont> FROM parts
cont> ORDER BY partno;
PARTNO PARTNAME
101 X-Ray Screen
102 Yellow Bag
103 Zoot Suit
3 rows selected
|
顧客Cables R Usからはまだ注文がなく、注文番号9005には明細項目がありません。
次の外部結合では、すべての顧客と、顧客による注文日が返されます。（+）演算子が指定されているため、注文していない顧客も返されます。
SQL> SELECT custname, orderdate cont> FROM customers, orders cont> WHERE customers.custno = orders.custno (+) cont> ORDER BY customers.custno, orders.orderdate; CUSTOMERS.CUSTNAME ORDERS.ORDERDATE Angelic Co 1999-10-13 Angelic Co 1999-10-20 Angelic Co 1999-10-27 Believable Co 1999-10-13 Believable Co 1999-10-31 Cables R Us NULL 6 rows selected |
次の外部結合では、前述の結果に対し、LINEITEMS表をFROM句に追加し、この表の列を選択リストに追加し、この表をORDERS表に結合する結合条件をwhere_clauseに追加します。この問合せでは、前述の問合せの結果をLINEITEMS表に結合すると、すべての顧客、顧客による注文日、顧客が注文した各部品の部品番号および数量が返されます。最初の（+）演算子は、前述の問合せと同様に機能します。また、2番目の（+）演算子によって、明細項目がない注文も返されます。
SQL> SELECT custname, orderdate, partno, quantity cont> FROM customers, orders, lineitems cont> WHERE customers.custno = orders.custno (+) cont> AND orders.orderno = lineitems.orderno (+) cont> ORDER BY customers.custno, orders.orderdate, lineitems.partno; CUSTOMERS.CUSTNAME ORDERS.ORDERDATE LINEITEMS.PARTNO LINEITEMS.QUANTITY Angelic Co 1999-10-13 101 15 Angelic Co 1999-10-13 102 10 Angelic Co 1999-10-20 101 15 Angelic Co 1999-10-27 102 10 Angelic Co 1999-10-27 103 20 Believable Co 1999-10-13 101 25 Believable Co 1999-10-13 103 50 Believable Co 1999-10-31 NULL NULL Cables R Us NULL NULL NULL 9 rows selected |
次の外部結合では、前述の結果に対し、PARTS表をFROM句に追加し、この表のPARTNAME列を選択リストに追加し、この表をLINEITEMS表に結合する結合条件をwhere_clauseに追加します。この問合せでは、前述の問合せの結果をPARTS表に結合すると、すべての顧客、顧客による注文日、顧客が注文した各部品の数量および名前が返されます。最初の2つの（+）演算子は、前述の問合せと同様に機能します。また、3番目の（+）演算子によって、部品番号がNULLの行も返されます。
SQL> SELECT custname, orderdate, quantity, partname cont> FROM customers, orders, lineitems, parts cont> WHERE customers.custno = orders.custno (+) cont> AND orders.orderno = lineitems.orderno (+) cont> AND lineitems.partno = parts.partno (+) cont> ORDER BY customers.custno, orders.orderdate, parts.partno; CUSTOMERS.CUSTNAME ORDERS.ORDERDATE LINEITEMS.QUANTITY PARTS.PARTNAME Angelic Co 1999-10-13 15 X-Ray Screen Angelic Co 1999-10-13 10 Yellow Bag Angelic Co 1999-10-20 15 X-Ray Screen Angelic Co 1999-10-27 10 Yellow Bag Angelic Co 1999-10-27 20 Zoot Suit Believable Co 1999-10-13 25 X-Ray Screen Believable Co 1999-10-13 50 Zoot Suit Believable Co 1999-10-31 NULL NULL Cables R Us NULL NULL NULL 9 rows selected |
