SELECTの構文は比較的複雑ですが、このセクションではSIMPLE SELECTの構文構造について説明します。
説明
このステートメントは、テーブルまたはビューからデータをクエリするために使用します。
構文
simple_select:
SELECT [ hint_options ] [ DISTINCT | UNIQUE | ALL] select_expr_list
FROM from_list
[WHERE condition]
[hierarchical_query_clause]
[GROUP BY group_expression_list
[{ROLLUP | CUBE | GROUPING SETS} group_expression_list]
[HAVING condition]
]
[ORDER BY order_expression_list]
[FOR UPDATE [OF column] [ {NOWAIT | WAIT integer | SKIP LOCKED } ] ]
[row_limiting_clause ]
select_expr_list:
table_name.*
| table_alias_name.*
| expr [[AS] column_alias_name]
| sequence_name.{ CURRVAL|NEXTVAL }@dblink_name
from_list:
table_reference [, table_reference...]
url_external_table_references
table_reference:
simple_table
| joined_table
| pivot_clause
| unpivot_clause
| table_name@dblink_name
url_external_table_references:
location_url | table_function
location_url:
'file_path'
(
{FORMAT = (
TYPE = 'CSV',
LINE_DELIMITER = '<string>' | <expr>,
FIELD_DELIMITER = '<string>' | <expr>,
ESCAPE = '<character>' | <expr>,
FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>,
ENCODING = 'charset',
NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...),
SKIP_HEADER = <int>,
SKIP_BLANK_LINES = { TRUE | FALSE },
PARSE_HEADER = { TRUE | FALSE },
TRIM_SPACE = { TRUE | FALSE },
EMPTY_FIELD_AS_NULL = { TRUE | FALSE }
)
| FORMAT = ( TYPE = 'PARQUET' | 'ORC' )
}
[PATTERN = '<regex_pattern>']
)
table_function:
{
FILES (
LOCATION = {'file_path' | @location_name['/path']},
{
FORMAT = (
TYPE = 'CSV',
LINE_DELIMITER = '<string>' | <expr>,
FIELD_DELIMITER = '<string>' | <expr>,
ESCAPE = '<character>' | <expr>,
FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>,
ENCODING = 'charset',
NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...),
SKIP_HEADER = <int>,
SKIP_BLANK_LINES = { TRUE | FALSE },
PARSE_HEADER = { TRUE | FALSE },
TRIM_SPACE = { TRUE | FALSE },
EMPTY_FIELD_AS_NULL = { TRUE | FALSE }
)
| FORMAT = ( TYPE = 'PARQUET' | 'ORC' )
},
[PATTERN = '<regex_pattern>']
)
| SOURCE (
TYPE = 'ODPS',
ACCESSID = '<string>',
ACCESSKEY = '<string>',
ENDPOINT = '<string>',
TUNNEL_ENDPOINT = '<string>',
PROJECT_NAME = '<string>',
SCHEMA_NAME = '<string>',
TABLE_NAME = '<string>',
QUOTA_NAME = '<string>',
COMPRESSION_CODE = '<string>'
)
}
simple_table:
(table_factor [partition_option])[table_alias_name]
| (select_stmt) table_alias_name
| (table_reference_list)
joined_table:
table_reference [INNER] JOIN simple_table [join_condition]
| table_reference outer_join_type JOIN simple_table join_condition
partition_option:
PARTITION (partition_name_list)
partition_name_list:
partition_name [, partition_name...]
outer_join_type:
{LEFT | RIGHT | FULL} [OUTER]
join_condition:
ON expression
condition:
expression
group_expression_list:
group_expression [, group_expression...]
group_expression:
expression [ASC | DESC]
order_expression_list:
order_expression [, order_expression...]
order_expression:
expression [ASC | DESC]
row_limiting_clause:
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]
pivot_clause：
PIVOT
(aggregate_function ( expr ) [[AS] alias ]
[, aggregate_function ( expr ) [[AS] alias ]... ]
pivot_for_clause
pivot_in_clause
)
pivot_for_clause：
FOR { column| ( column [, column... ]) }
pivot_in_clause
IN
( { { expr| ( expr [, expr...] ) } [ [ AS] alias]... }
[, { { expr| ( expr [, expr...] ) } [ [ AS] alias] ...} ]
)
unpivot_clause ：
UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]
( { column | ( column [, column... ]) }
pivot_for_clause
unpivot_in_clause
)
unpivot_in_clause：
IN
( { column | ( column [, column... ]) }[ AS { literal | ( literal [, literal... ]) } ]
[, { column | ( column [, column... ] ) }[ AS {literal | ( literal [, literal... ]) } ]]
)
hierarchical_query_clause:
[START WITH start_expression] CONNECT BY [NOCYCLE]
{PRIOR child_expr = parent_expr
| parent_expr = PRIOR child_expr} [ORDER SIBLINGS BY ...]
パラメータ説明
フィールド |
説明 |
|
|---|---|---|
| hint_options | ヒントオプションを指定します。オプションです。 | |
| DISTINCT | UNIQUE | ALL | クエリ対象のデータには重複行が含まれる可能性があります。
|
|
| select_expr_list | データベースからクエリする式または列名をカンマ（,）で区切って指定します。\*はすべての列を表します。
|
|
| FROM table_references | データを選択するオブジェクトを指定します。 | |
| url_external_table_references | オプションです。URLを通じて外部テーブルデータを直接読み取ります。現在のURL外部テーブルは2つの構文形式をサポートしています。詳細については以下を参照してください：
|
|
| PARTITION(partition_list) | クエリ対象のテーブルのパーティション情報を指定します。例：partition(p0,p1...)。 |
|
| table_factor | テーブル名（ベーステーブル、更新可能なビュー）、特殊なサブクエリなどを指定し、直接クエリ関数をサポートします。 | |
| table_alias_name | データオブジェクトを選択するエイリアスを指定します。 | |
| joined_table | 複数テーブルクエリの結合方法を指定します。
|
|
| ON expression | 複数テーブル結合の結合条件を指定します。 | |
| WHERE where_conditions | 絞り込み条件を指定します。クエリ結果には条件を満たすデータのみが含まれます。オプションです。where_conditions は式です。 |
|
| hierarchical_query_clause | オプションです。階層クエリオプションを指定します。詳細については以下の hierarchical_query_clause を参照してください。 | |
| GROUP BY group_by_list | グループ化するフィールドを指定します。通常は集約関数と組み合わせて使用します。説明 SELECT 句の後の列に集約関数を使用していない場合、グループ化クエリでは、SELECT 句の後の列は GROUP BY 句に含まれている必要があります。 |
|
| ROLLUP group_expression_list | Group Byのグループを統合し、統計値を生成します。 | |
| CUBE group_expression_list | 式のリスト内のすべての項目の各配列に基づいて生成されたグループで集計を行い、GROUP BY のグループを結合して統計値を生成します。説明：
|
|
| GROUPING SETS group_expression_list | 1つのクエリで複数のデータグループを指定し、それぞれに対して統計値を生成して集計表示します。GROUPING SETS では、単一のフィールドまたはフィールドのリストを指定できます。 |
|
| HAVING search_confitions | グループ化後の各データグループをフィルタリングします。HAVING 句は WHERE 句と似ていますが、累積関数（例：SUM、AVG など）を使用できます。 |
|
| ORDER BY order_list | 結果セットを1列または複数列に基づいて ASC または DESC で表示する列を指定します。ASC または DESC を指定しない場合、デフォルトは ASC です。
|
|
| row_limiting_clause | クエリが返す行数を制限し、ページネーションクエリ機能を実現します。オフセット量、および返す行数または行の割合を指定できます。ORDER BY 句と組み合わせてソート順序を指定することで、一貫性のある結果を得ることができます。 |
|
| OFFSET | ページネーションクエリの開始前にスキップする行数を指定します。offset は数値、または数値を計算する式である必要があります。
|
|
| ROW | ROWS | 単一行または複数行を表します。行数に応じてキーワードを選択することで、意味の明確さを保証できます。 | |
| FETCH | 返す行数または行の割合を指定します。この句を指定しない場合、offset+1から始まるすべての行が返されます。 |
|
| FIRST | NEXT | 最初に返す行数または行の割合、または次に返す行数または行の割合を表します。 | |
| rowcount | percent PERCENT | rowcount を使用して、返す行数を指定します。rowcount は数値または数値として評価される式でなければなりません。負の数を指定した場合、rowcount は 0 と見なされます。rowcount が rowcount + 1 から始まる利用可能な行数より大きい場合、利用可能なすべての行を返します。rowcount に小数部が含まれる場合、その小数部は切り捨てられます。rowcount が NULL の場合、0 行を返します。percent PERCENT を使用して、返す指定された行の総数の割合を指定します。percent は数値または数値として評価される式でなければなりません。負の数を指定した場合、percent は 0 と見なされます。percent が NULL の場合、0 行を返します。rowcount または percent PERCENT が指定されていない場合、1 行を返します。 |
|
| ONLY | WITH TIES | ONLY を指定すると、指定された行数または行の割合を返します。WITH TIES を指定すると、前回取得した行と同じソートキーを持つ他の行も返します。WITH TIES を使用する場合は、ORDER BY 句を指定する必要があります。ORDER BY 句を指定しない場合、他の行は返されません。 |
|
| FOR UPDATE | オプションです。クエリ結果のすべての行に排他的ロックをかけ、他のトランザクションによる並行変更や、特定のトランザクション分離レベルでの並行読み取りを防ぎます。
|
|
| pivot_clause | 行を列にピボットする句。
注意
|
|
| aggregate_function | 集計関数を指定します。 | |
| expr | 計算結果が定数値となる式を指定します。 pivot_in_clause は定数式のみをサポートします。 |
|
| unpivot_clause | 列を行にピボットする句。
注意
|
|
| dblink_name | アクセスするデータベースリンク（つまり dblink）の名前を指定します。 | |
| sequence_name | dblinkを使用して、リモートデータベース（OceanBaseデータベースおよびOracleデータベースを含む）のシーケンス値にアクセスします。SEQUENCEオブジェクトのNEXTVALおよびCURRVAL値の計算も含まれます。 |
|
hierarchical_query_clause
階層クエリでは、SELECT ステートメントの列に特殊な擬似列（pseudo column）LEVEL を使用して、階層、つまりノードの階層を表すことができます。クエリの開始点から数え始め、開始点は1となり、以降同様に続きます。このフィールドは階層クエリでのみ使用できます。階層クエリにおける擬似列の詳細については、階層クエリの擬似列を参照してください。
START WITH start_expression：オプションです。階層クエリ内のルート行（Root Row）を指定します。CONNECT BY：親子関係をどのように特定するかを指定します。通常は等価式を使用しますが、他の式もサポートされています。NOCYCLE：このキーワードを指定すると、実行結果にサイクルが含まれていても返却できます。サイクルが発生した場所はCONNECT_BY_ISCYCLE仮想列を使用して指定できます。指定しない場合、クライアントにエラーが返されます。PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr：PRIORは一元演算子で、パラメータ内の列が親行（Parent Row）から来たことを示します。一元の + および - と同じ優先順位を持ちます。ORDER SIBLINGS BY：同一階層の行間の並べ替え順序を指定します。
注意
階層クエリに FOR UPDATE 句が含まれる場合、以下のユースケースはサポートされません：
- サブクエリで
DISTINCTキーワードや集計を使用している場合、この場合はFOR UPDATEと併用できません。 - 共通テーブル式（Common Table Expressions、CTE）を含むシナリオはすべてサポートされていません。つまり、
WITH ... AS ...句を含むSELECTクエリはFOR UPDATEと同時に使用できません。
階層クエリの使用方法の詳細については、階層クエリを参照してください。
table_function
LOCATION句は、外部テーブルファイルの保存パスを指定するために使用されます。通常、外部テーブルのデータファイルは単独のディレクトリに保存され、そのフォルダ内にはサブディレクトリを含むことができます。テーブル作成時、外部テーブルはそのディレクトリ内のすべてのファイルを自動的に収集します。取り得る値は以下の通りです：
file_path：具体的な外部テーブルファイルのパス情報を表します。詳細は以下の通りです：- ローカルLOCATIONの形式は
LOCATION = '[file://] local_file_path'で、local_file_pathは相対パスまたは絶対パスにすることができます。相対パスを指定する場合、現在のディレクトリはOceanBaseデータベースのインストールディレクトリである必要があります。secure_file_privはOBServerノードがアクセス権限を持つファイルパスを設定するために使用されます。local_file_pathはsecure_file_privパスのサブパスである必要があります。 - リモートLOCATIONの形式は
LOCATION = '{oss | s3}://$ACCESS_ID:$ACCESS_KEY@$HOST：s3_region/remote_file_path'で、$ACCESS_ID、$ACCESS_KEY、$HOSTはOSSおよびS3へのアクセス時に設定が必要なアクセス情報です。s3_regionはS3使用時に選択するリージョン情報で、これらの機密アクセス情報は暗号化されてデータベースのシステムテーブルに保存されます。
- ローカルLOCATIONの形式は
@location_name['/path']：Locationオブジェクトを参照して外部テーブルを作成することを表します。['/path']はオプションで、サブディレクトリを表します。Locationオブジェクトの作成に関する詳細は、CREATE LOCATIONを参照してください。説明
OceanBaseデータベースV4.4.x系では、V4.4.1バージョンから
@location_name['/path']パラメータがサポートされています。
FORMAT句は、読み取りファイル形式に関連するプロパティを指定するために使用され、CSV/PARQUET/ORCの3種類のファイル形式をサポートしています。
- TYPE = 'CSV' の場合、以下のフィールドが含まれます：
LINE_DELIMITER：CSVファイルの行区切り文字を指定します。デフォルトはLINE_DELIMITER='\n'です。FIELD_DELIMITER：オプションで、CSVファイルの列区切り文字を指定します。デフォルトはFIELD_DELIMITER='\t'です。ESCAPE：CSVファイルのエスケープ文字を指定します。1バイトのみ指定可能で、デフォルトはESCAPE ='\'です。FIELD_OPTIONALLY_ENCLOSED_BY：オプションで、CSVファイル内でフィールド値を囲む記号を指定します。デフォルトは空です。このオプションを使用すると、一部の型のフィールド（例：CHAR、VARCHAR、TEXT、JSONなど）にのみ囲み文字が付加されます。ENCODING：ファイルの文字セットエンコーディング形式を指定します。指定しない場合、デフォルト値はUTF8MB4です。NULL_IF：NULLとして処理される文字列を指定します。デフォルト値は空です。SKIP_HEADER：ファイルヘッダーをスキップし、スキップする行数を指定します。SKIP_BLANK_LINES：空白行をスキップするかどうかを指定します。デフォルト値はFALSEで、空白行はスキップされません。TRIM_SPACE：ファイル内のフィールドの先頭と末尾のスペースを削除するかどうかを指定します。デフォルト値はFALSEで、ファイル内のフィールドの先頭と末尾のスペースは削除されません。EMPTY_FIELD_AS_NULL：空文字列をNULLとして処理するかどうかを指定します。デフォルト値はFALSEで、空文字列はNULLとして処理されません。PARSE_HEADER:この設定を行うと、CSVファイルの最初の行の情報を直接取得し、それを各列の列名として使用します。注意
PARSE_HEADERとSKIP_HEADERは同時に使用できません。意味的に矛盾します。
- TYPE = 'PARQUET/ORC' の場合、他の追加フィールドはありません。
- TYPE = 'CSV' の場合、以下のフィールドが含まれます：
PATTERN句は、LOCATIONディレクトリ内のファイルをフィルタリングするための正規表現パターン文字列を指定します。各LOCATIONディレクトリ内のファイルパスがこのパターン文字列に一致する場合、外部テーブルはそのファイルにアクセスします。一致しない場合、外部テーブルはそのファイルをスキップします。このパラメータを指定しない場合、デフォルトではLOCATIONディレクトリ内のすべてのファイルにアクセスできます。
ODPS形式の場合、データの取得方法がファイルを介さないため、意味のあるURLパスが存在しないため、sourceという1種類のtable_functionの形式のみをサポートしています。
- TYPE = 'ODPS' には以下のフィールドが含まれます：
ACCESSID：ODPSユーザーのIDを指定します。ACCESSKEY：ODPSユーザーのパスワードを指定します。ENDPOINT：ODPSサービスの接続アドレスを指定します。TUNNEL_ENDPOINT：Tunnelデータ伝送サービスの接続アドレスを指定します。PROJECT_NAME：クエリ対象のテーブルが存在するProjectを指定します。SCHEMA_NAME：オプションで、クエリ対象のテーブルのSCHEMAを指定します。TABLE_NAME：クエリ対象のテーブル名を指定します。QUOTA_NAME：オプションで、指定したQuotaを使用するかどうかを選択します。COMPRESSION_CODE：オプションで、データソースの圧縮形式を指定します。ZLIB、ZSTD、LZ4、ODPS_LZ4の4種類の圧縮形式をサポートしており、設定しない場合は圧縮は有効になりません。
location_url
FORMAT句は、読み取りファイル形式に関連するプロパティを指定するために使用されます。CSV、PARQUET、ORCの3種類のファイル形式をサポートしています。そのうち、CSVファイルでは、ファイルのヘッダー行を解析するかどうかを指定するためにparse_headerを設定し、TYPEを使用してエクスポートファイル形式を指定する必要があります。ODPSのデータについては、source句を使用する必要があります。クエリ例：
SELECT * FROM FILES( location = '/data/', format (TYPE = 'csv', field_delimiter = ',', parse_header = true), pattern = 'datafiles$';
例
テーブル
tbl1からnameデータを読み取ります。obclient> CREATE TABLE tbl1 (id INT,name VARCHAR(10),num INT); Query OK, 0 rows affected obclient> INSERT INTO tbl1 VALUES (1, 'a',100),(2, 'b',200),(3, 'a',50); Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0 obclient> SELECT * FROM tbl1; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 1 | a | 100 | | 2 | b | 200 | | 3 | a | 50 | +------+------+------+ 3 rows in set obclient> SELECT name FROM tbl1; +------+ | NAME | +------+ | a | | b | | a | +------+ 3 rows in setテーブル
tbl1からnameデータを読み取り、重複を除去します。obclient> SELECT DISTINCT name FROM tbl1; +------+ | NAME | +------+ | a | | b | +------+ 2 rows in setテーブル
tbl1からid、name、numを照会し、num列を2で割って出力します。出力列名はavgです。obclient> SELECT id, name, num/2 AS avg FROM tbl1; +------+------+------+ | ID | NAME | AVG | +------+------+------+ | 1 | a | 50 | | 2 | b | 100 | | 3 | a | 25 | +------+------+------+ 3 rows in setテーブル
tbl1からフィルター条件name = 'a'に基づいて、対応するid、name、numを出力します。obclient> SELECT id, name, num FROM tbl1 WHERE name = 'a'; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 1 | a | 100 | | 3 | a | 50 | +------+------+------+ 2 rows in setテーブル
tbl1からnameを照会し、nameでグループ化してnumの合計を求めます。numの合計が160より小さい行を照会し、出力します。obclient> SELECT name,SUM(num) sum FROM tbl1 GROUP BY name HAVING SUM(num) < 160; +------+------+ | NAME | SUM | +------+------+ | a | 150 | +------+------+ 1 row in setテーブル
tbl1からid、name、numを照会し、numの昇順でクエリ結果を出力します。obclient> SELECT * FROM tbl1 ORDER BY num ASC; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 3 | a | 50 | | 1 | a | 100 | | 2 | b | 200 | +------+------+------+ 3 rows in setテーブル
tbl1からすべての列を照会し、nameの降順、numの昇順でクエリ結果を出力します。obclient> SELECT * FROM tbl1 ORDER BY name DESC,num ASC; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 2 | b | 200 | | 3 | a | 50 | | 1 | a | 100 | +------+------+------+ 3 rows in setテーブル
tbl1から指定されたidの行を照会し、FOR UPDATE句を使用してクエリ結果行をロックします。/* セッション1でテーブルtbl1のid=1の行を照会してロック */ obclient> SELECT * FROM tbl1 WHERE id=1 FOR UPDATE; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 1 | a | 100 | +------+------+------+ 1 row in set /* セッション2でテーブルtbl1のid=1またはid=2の行を照会してロック */ obclient> SELECT * FROM tbl1 WHERE id=1 or id=2 FOR UPDATE; OBE-30006: resource busy; acquire with WAIT timeout expired obclient> SELECT * FROM tbl1 WHERE id=1 or id=2 FOR UPDATE SKIP LOCKED; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 2 | b | 200 | +------+------+------+ 1 row in setテーブル
group_tbl1を作成し、データを挿入します。CUBEを含むGROUP BYクエリステートメントを実行します。obclient> CREATE TABLE group_tbl1 (group_id INT,job VARCHAR2(10),name VARCHAR2(10),salary INT); Query OK, 0 rows affected obclient> INSERT INTO group_tbl1 VALUES(10,'Coding','Bruce',1000), (10,'Programmer','Clair',1000), (20,'Coding','Jason',2000), (20,'Programmer','Joey',2000), (30,'Coding','Rebecca',3000), (30,'Programmer','Rex',3000); Query OK, 6 rows affected Records: 6 Duplicates: 0 Warnings: 0 obclient> SELECT * FROM group_tbl1; +----------+------------+---------+--------+ | GROUP_ID | JOB | NAME | SALARY | +----------+------------+---------+--------+ | 10 | Coding | Bruce | 1000 | | 10 | Programmer | Clair | 1000 | | 20 | Coding | Jason | 2000 | | 20 | Programmer | Joey | 2000 | | 30 | Coding | Rebecca | 3000 | | 30 | Programmer | Rex | 3000 | +----------+------------+---------+--------+ 6 rows in set obclient> SELECT group_id, salary, SUM(salary) FROM group_tbl1 GROUP BY CUBE (group_id, salary); +----------+--------+-------------+ | GROUP_ID | SALARY | SUM(SALARY) | +----------+--------+-------------+ | NULL | NULL | 12000 | | NULL | 1000 | 2000 | | NULL | 2000 | 4000 | | NULL | 3000 | 6000 | | 10 | NULL | 2000 | | 20 | NULL | 4000 | | 30 | NULL | 6000 | | 10 | 1000 | 2000 | | 20 | 2000 | 4000 | | 30 | 3000 | 6000 | +----------+--------+-------------+ 10 rows in setテーブル
tbl1からnameとnumでグループ化し、各グループの数を照会して集計します。obclient> SELECT name, num, COUNT(*) from tbl1 GROUP BY GROUPING SETS(name, num); +------+------+----------+ | NAME | NUM | COUNT(*) | +------+------+----------+ | a | NULL | 2 | | b | NULL | 1 | | NULL | 100 | 1 | | NULL | 200 | 1 | | NULL | 50 | 1 | +------+------+----------+ 5 rows in setテーブル
emp_phoneの行を列に変換し、その後列を行に変換します。obclient> CREATE TABLE emp(name VARCHAR2(50), num CHAR, phone VARCHAR2(50)); Query OK, 0 rows affected obclient> INSERT INTO emp VALUES('ZhangSan', '1', '1234-5678'),('ZhangSan', '2', '3219-6066'),('ZhangSan', '3', '5365-9583'); Query OK, 3 rows affected Records: 3 Duplicates: 0 Warnings: 0 obclient> SELECT * FROM emp; +----------+------+-----------+ | NAME | NUM | PHONE | +----------+------+-----------+ | ZhangSan | 1 | 1234-5678 | | ZhangSan | 2 | 3219-6066 | | ZhangSan | 3 | 5365-9583 | +----------+------+-----------+ 3 rows in set /* テーブルempの行を列に変換 */ obclient> SELECT * FROM emp PIVOT(MAX(phone) FOR num IN (1 AS home, 2 AS office, 3 AS mobile)); +----------+-----------+-----------+-----------+ | NAME | HOME | OFFICE | MOBILE | +----------+-----------+-----------+-----------+ | ZhangSan | 1234-5678 | 3219-6066 | 5365-9583 | +----------+-----------+-----------+-----------+ 1 row in set /* テーブルempの列を行に変換 */ obclient> CREATE VIEW v_emp AS SELECT * FROM emp PIVOT(MAX(phone) FOR num IN (1 AS home, 2 AS office, 3 AS mobile)); Query OK, 0 rows affected obclient> SELECT * FROM v_emp UNPIVOT(phone FOR num IN (home AS 1, office AS 2, mobile AS 3)); +----------+-----+-----------+ | NAME | NUM | PHONE | +----------+-----+-----------+ | ZhangSan | 1 | 1234-5678 | | ZhangSan | 2 | 3219-6066 | | ZhangSan | 3 | 5365-9583 | +----------+-----+-----------+ 3 rows in setリモートデータベースのテーブルからデータを照会します。
/*リモートのOceanBaseデータベースにアクセスしてデータを照会*/ obclient> SELECT ID FROM tbl2@ob_dblink; +------+ | ID | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set obclient> SELECT * FROM tbl2@ob_dblink; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 1 | a | 100 | | 2 | b | 200 | | 3 | a | 50 | +------+------+------+ 3 rows in set /*リモートのOracleデータベースにアクセスしてデータを照会*/ obclient> SELECT ID FROM tbl2@ora_dblink; +------+ | ID | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set obclient> SELECT * FROM tbl2@ora_dblink; +------+------+------+ | ID | NAME | NUM | +------+------+------+ | 1 | a | 100 | | 2 | b | 200 | | 3 | a | 50 | +------+------+------+ 3 rows in set /*ローカルデータベースとリモートデータベースのデータを同時に照会*/ obclient> SELECT t4.col1,t5.col2 FROM tbl1 t4, tbl2@ob_dblink t5 WHERE t1.col3=t2.col3; /*異なるリモートデータベースのデータを同時に照会*/ obclient> SELECT * FROM tbl2@ob_dblink t_remote1,tbl2@ora_dblink t_remote2 WHERE t_remote1.col1 = t_remote2.col1;ページネーションクエリの例
従業員番号
empnoが最も低い上位3名の従業員を照会します。obclient> CREATE TABLE emp( empno NUMBER(4,0), empname VARCHAR(10), job VARCHAR(9), mgr NUMBER(4,0), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2,0), CONSTRAINT PK_emp PRIMARY KEY (empno) ); Query OK, 0 rows affected obclient> INSERT INTO emp VALUES (1839,'KING','PRESIDENT',null,'17-DEC-81',5000,null,10) ,(1698,'BLAKE','MANAGER',1839,'01-MAY-81',2850,null,30) ,(1782,'CLARK', 'MANAGER', 1839, '09-JUN-81', 2450, null, 10) ,(1566,'JONES','MANAGER',1839, '02-APR-81',2975,null,40) ,(1788,'SCOTT','ANALYST',1566, '15-JUL-87',3000,null,20) ,(1902,'FORD','ANALYST',1566, '05-OCT-81',3000,null,40) ,(1369,'SMITH','CLERK',1902, '17-NOV-80',800,null,20) ,(1499,'ALLEN','SALESMAN',1698, '20-FEB-81',1600,300,30); Query OK, 8 rows affected Records: 8 Duplicates: 0 Warnings: 0 obclient> SELECT empno, empname FROM emp ORDER BY empno FETCH FIRST 3 ROWS ONLY; +-------+---------+ | EMPNO | EMPNAME | +-------+---------+ | 1369 | SMITH | | 1499 | ALLEN | | 1566 | JONES | +-------+---------+ 3 rows in set次に、従業員番号
empnoが最も低い上位3名の従業員を照会します。obclient> SELECT empno, empname FROM emp ORDER BY empno FETCH NEXT 3 ROWS ONLY;最低給与の従業員のうち、上位25%の従業員を照会します。
obclient> SELECT empno, empname sal FROM emp ORDER BY sal FETCH FIRST 25 PERCENT ROWS ONLY; +-------+-------+ | EMPNO | SAL | +-------+-------+ | 1499 | ALLEN | | 1698 | BLAKE | +-------+-------+ 2 rows in set最低給与の従業員のうち、上位25%の従業員と、前の例で取得した最後の従業員の給与と同じ給与を持つ他のすべての従業員を照会します。
obclient> SELECT empno, empname sal FROM emp ORDER BY sal FETCH FIRST 25 PERCENT ROWS WITH TIES; +-------+-------+ | EMPNO | SAL | +-------+-------+ | 1499 | ALLEN | | 1698 | BLAKE | +-------+-------+ 2 rows in set
テーブル
t1のデータを照会し、テーブルエイリアスを指定します。obclient> CREATE TABLE t1 (c1 INT, c2 INT ); Query OK, 0 rows affected obclient> INSERT INTO t1 VALUES ('1','2'); Query OK, 1 row affected obclient> SELECT * FROM (t1) a; +------+------+ | C1 | C2 | +------+------+ | 1 | 2 | +------+------+ 1 row in setRANDOM()を使用してランダム数を生成します。FROM句の後に直接GENERATOR()関数を照会します。obclient> SELECT RANDOM(4) FROM GENERATOR(3); +---------------------+ | RANDOM(4) | +---------------------+ | 5267436225003336391 | | -851690886662571060 | | 1738617244330437274 | +---------------------+ 3 rows in setリモートデータベース内のシーケンス値にアクセスします。
1.ローカルのOceanBaseデータベースにログインし、リモートのOceanBaseデータベースOracleテナントのdblinkを作成します。
obclient> CREATE DATABASE LINK seq_link CONNECT TO test@oracle IDENTIFIED BY test HOST '127.xxx.xxx.xxx:2828'; Query OK, 0 rows affected2.リモートのOceanBaseデータベースで、シーケンス
my_seqを作成します。obclient> CREATE SEQUENCE my_seq START WITH 1 MINVALUE 1 MAXVALUE 10 INCREMENT BY 2 NOCYCLE NOORDER CACHE 30; Query OK, 0 rows affected3.ローカルでリモートのOceanBaseデータベースのシーケンス値にアクセスします。
obclient> SELECT my_seq.NEXTVAL@seq_link FROM DUAL; +---------+ | NEXTVAL | +---------+ | 1 | +---------+ 1 row in set obclient> SELECT my_seq.CURRVAL@seq_link FROM DUAL; +---------+ | CURRVAL | +---------+ | 1 | +---------+ 1 row in set obclient> SELECT my_seq.NEXTVAL@seq_link FROM DUAL; +---------+ | NEXTVAL | +---------+ | 3 | +---------+ 1 row in set