ポイント: Db2 12の言語要素は、特別なレジスタ、ホスト変数、関数、および式などの基本的なプログラミングコンポーネントをカバーし、適切な構文の使用例を提供します。
- 特別なレジスタとその応用を説明します。
- 構文の例を含むさまざまな関数と式の詳細を示します。
特別レジスタ
>>__ _CURRENT APPLICATION ENCODING SCHEME_______________________ ______><
ǀ_CURRENT CLIENT_ACCTING____________________________________ǀ
ǀ_CURRENT CLIENT_APPLNAME___________________________________ǀ
ǀ_CURRENT CLIENT_USERID_____________________________________ǀ
ǀ_CURRENT CLIENT_WRKSTNNAME_________________________________ǀ
ǀ_ _CURRENT DATE_____ _____________________________________ǀ
ǀ ǀ ǀ ǀ
ǀ ǀ_CURRENT DATE___ǀ ǀ
ǀ_CURRENT DEBUG MODE________________________________________ǀ
ǀ_CURRENT DECFLOAT ROUNDING MODE____________________________ǀ
ǀ_CURRENT DEGREE____________________________________________ǀ
ǀ_CURRENT EXPLAIN MODE______________________________________ǀ
ǀ_CURRENT GET_ACCEL_ARCHIVE_________________________________ǀ
ǀ _LOCALE_ ǀ
ǀ_ _CURRENT__ǀ________ǀ__LC_CTYPE_ _________________________ǀ
ǀ ǀ_CURRENT_LC_CTYPE______________ǀ ǀ
ǀ _TABLE_ _FOR OPTIMIZATION_ ǀ
ǀ_CURRENT MAINTAINED_ǀ________ǀ__TYPES_ǀ_________________ǀ__ǀ
ǀ_CURRENT MEMBER____________________________________________ǀ
ǀ_CURRENT OPTIMIZATION HINT_________________________________ǀ
ǀ_CURRENT PACKAGE PATH______________________________________ǀ
ǀ_CURRENT PACKAGESET________________________________________ǀ
ǀ_ _CURRENT PATH_ __________________________________________ǀ
ǀ ǀ_CURRENT PATH_ǀ ǀ
ǀ_CURRENT PRECISION_________________________________________ǀ
ǀ_ CURRENT QUERY ACCELERATION ______________________________ǀ
ǀ_CURRENT REFRESH AGE_______________________________________ǀ
ǀ_CURRENT ROUTINE VERSION __________________________________ǀ
ǀ_CURRENT RULES_____________________________________________ǀ
ǀ_ _CURRENT SCHEMA____ _____________________________________ǀ
ǀ ǀ ǀ ǀ
ǀ ǀ_CURRENT SCHEMA____ǀ ǀ
ǀ_CURRENT SERVER____________________________________________ǀ
ǀ_CURRENT SQLID_____________________________________________ǀ
ǀ ___(_6_)___ _WITHOUT TIME ZONE_ ǀ
ǀ_ _CURRENT TIMESTAMP__ǀ___________ǀ_ǀ__________________ǀ___ǀ
ǀ ǀ ǀ ǀ_(integer)_ǀ_ǀ_WITH TIME ZONE___ǀ ǀ
ǀ ǀ_CURRENT TIMESTAMP_ǀ ǀ
ǀ_ _CURRENT TIME__________ _________________________________ǀ
ǀ ǀ ǀ ǀ
ǀ ǀ_CURRENT TIME__________ǀ ǀ
ǀ_CURRENT TIME ZONE_________________________________________ǀ
ǀ_SESSION TIME ZONE_________________________________________ǀ
ǀ_ENCRYPTION PASSWORD_______________________________________ǀ
ǀ_SESSION_USER______________________________________________ǀ
ǀ ǀ_USER______ǀ ǀ
ǀ_CURRENT TEMPORAL SYSTEM_TIME______________________________ǀ
ǀ_CURRENT TEMPORAL BUSINESS_TIME____________________________ǀ
ホスト変数
>__:host-identifier_____________________________________________________>
ǀ _INDICATOR_ ǀ
ǀ__ǀ___________ǀ_:host-identifier_ǀ
Javaでは、ホスト変数の構文は:
>__:__________java-identifier___________________________________________>
ǀ_IN____ǀ ǀ_(java_expression_)_ǀ ǀ _INDICATOR_ ǀ
ǀ_OUT___ǀ ǀ_ǀ_________ǀ_:_Java-identifier_ǀ
ǀ_INOUT_ǀ
PL/I、C、およびCOBOLでは、ホスト変数の構文は:
>__:_________________host-identifier____________________________________>
ǀ_host-identifier._ǀ
>_______________________________________________________________________>
ǀ _INDICATOR_ ǀ
ǀ___ǀ_____________ǀ_:________________________host-identifier_ǀ
ǀ_host-identifier._ǀ
関数
>__function-name__(__________________________________________________)_____>
ǀ_ALL______ǀ ǀ <_,_________________________________ ǀ
ǀ_DISTINCT_ǀ ǀ_____expression_____________________ǀ_ǀ
|_TABLE__transition-table-name_ǀ
テーブル関数
>_TABLE_(function_name__(________________________________))_correlation_clause__>
ǀ <_,____________________________ ǀ
ǀ_ǀ_expression__________________ǀ_ǀ
ǀ_TABLE_transition_table_name_ǀ
式
<_operator________________________
>______ǀ_________function-invocation____ǀ________________________________>
ǀ_+_ǀ ǀ_(expression)____________ ǀ
ǀ_-_ǀ ǀ_constant________________ ǀ
ǀ_column-name_____________ ǀ
ǀ_variable________________ ǀ
ǀ_special-register________ ǀ
ǀ_scalar-fullselect_______ ǀ
ǀ_time-zone-expression____ ǀ
ǀ_labeled-duration________ ǀ
ǀ_case-expression_________ ǀ
ǀ_cast-specification______ ǀ
ǀ_XMLCAST-specification___ ǀ
ǀ_sequence-reference______ ǀ
ǀ_row-change-expression___ ǀ
ǀ_OLAP-specfication_______ ǀ
>________CONCAT_________________________________________________________________>
ǀ_ǀǀ_____ǀ
ǀ_/______ǀ
ǀ_*______ǀ
ǀ_+______ǀ
ǀ_-______ǀ
ラベル付き期間
>_____function-invocation____YEAR______________________________________>
ǀ_(expression)___ǀ ǀ_YEARS_________ǀ
ǀ_constant_______ǀ ǀ_MONTH_________ǀ
ǀ_column-name____ǀ ǀ_MONTHS________ǀ
ǀ_variable_______ǀ ǀ_DAY___________ǀ
ǀ_DAYS__________ǀ
ǀ_HOUR__________ǀ
ǀ_HOURS_________ǀ
ǀ_MINUTE________ǀ
ǀ_MINUTES_______ǀ
ǀ_SECOND________ǀ
ǀ_SECONDS_______ǀ
ǀ_MICROSECOND___ǀ
ǀ_MICROSECONDS__ǀ
CASE式
_ELSE NULL_______________
>__CASE___searched-when-clause___ǀ_________________________ǀ___________>
ǀ_simple-when-clause___ǀ ǀ_ELSE__result-expression_ǀ
>__END_________________________________________________________________>
searched-when-clause:
<_________________________________________________________
>______WHEN____search-condition__THEN____result-expression____ǀ________>
simple-when-clause:
<______________________________________________
>_____expression______WHEN__expression__THEN____result-expression__ǀ___>
キャスト
>__CAST__(____expression___________AS__data-type__)____________________>
ǀ_NULL_____________ǀ
ǀ_parameter-marker_ǀ
データ型:
>_______built-in-data-type_____________________________________________>
ǀ_distinct-type-name_ǀ
ǀ_array-type_________ǀ
組み込みデータ型:
>__SMALLINT_______________________________________________________________________ ><
ǀ ǀ_INTEGER__ǀ ǀ
ǀ ǀ ǀ INT__ǀ ǀ ǀ
ǀ ǀ_BIGINT___ǀ ǀ
ǀ ______(5,0)_________________ ǀ
ǀ__DECIMAL____ǀ____________________________ǀ______________________________________ ǀ
ǀǀ_DEC_____ǀ ǀ_(integer_______________)___ǀ ǀ
ǀǀ_NUMERIC_ǀ ǀ_, integer_ǀ ǀ
ǀ _(34)_ ǀ
ǀ_DECFLOAT_ ǀ______ǀ______________________________________________________________ ǀ
ǀ ǀ_(16)_ǀ ǀ
ǀ _(53)____________ ǀ
ǀ___FLOAT___ǀ_________________ǀ___________________________________________________ ǀ
ǀ ǀ ǀ_(_integer_)_ǀ ǀ
ǀ_ǀ_REAL______________________ǀ ǀ
ǀ ǀ _PRECISION_ ǀ ǀ
ǀ_ǀ_DOUBLE____ǀ___________ǀ ǀ ǀ
ǀ _(1 OCTETS)____ ǀ
ǀ_______CHARACTER_____ǀ___________________________________________________________ ǀ
ǀ ǀ ǀ ǀ_CHAR______ǀ ǀ_(_length_)______ǀ ǀ ǀǀ_CCSID__ASCII_ǀ ǀ_FOR__SBCS__DATA_ǀ ǀ ǀ
ǀ ǀ ǀ___CHARACTER__VARYING___(_length_)__ǀ ǀ ǀ_EBCDIC_ǀ ǀ_MIXED_ǀ ǀ ǀ ǀ
ǀ ǀ ǀ ǀ_CHAR____ǀ ǀ ǀ ǀ_UNICODE_ǀ ǀ_BIT___ǀ ǀ ǀ ǀ
ǀ ǀ ǀ__VARCHAR___________ǀ ǀ_CCSID_integer_____________________ǀ ǀ ǀ
ǀ ǀ ǀ ǀ
ǀ ǀ _(IM OCTETS)__ ǀ ǀ
ǀ_ǀ__CHARACTER_LARGE_OBJECT_ǀ______________ǀ____________________________________ ǀ ǀ
ǀ ǀ ǀ_CHAR____ǀ ǀǀ_(lob length)_ ǀ ǀǀ_CCSID__ASCII__ǀǀ_FOR__SBCS__DATA_ǀ ǀ ǀ
ǀ ǀ_CLOB_________________ǀ ǀ ǀ_EBCDIC__ǀ ǀ_MIXED_ǀ ǀ ǀ
ǀ ǀ ǀ_UNICODE_ǀ ǀ_BIT___ǀ ǀ ǀ
ǀ ǀ_CCSID_integer_____________________ ǀ ǀ
ǀ _(1 CODEUNITES16)_ ǀ
ǀ____GRAPHIC_ǀ__________________ǀ__________________________________________________ǀ
ǀ ǀ ǀ_(__length___)____ǀ ǀ ǀ_CCSID__ASCII___ǀ ǀ
ǀ ǀ_VARGRAPHIC__(__length__)_________ǀ ǀ_EBDIC___ǀ ǀ
ǀ ǀ __(1M CODEUNITS16)_ ǀUNICODE__ǀ ǀ
ǀ ǀ_DBCLOB_ǀ___________________ǀ ǀ_integer_ǀ ǀ
ǀ ǀ_(lob length)______ǀ ǀ
ǀ _(1)________ ǀ
ǀ_ _BINARY__ǀ____________ǀ________________________________________________________ ǀ
ǀ ǀ ǀ_(integer)_ ǀ ǀ ǀ
ǀ ǀ__BINARY VARYING _(integer)______________________________ ǀ ǀ
ǀ ǀ ǀ_VARBINARY_____ǀ________________________________________ǀ ǀ
ǀ ǀ _(1M)_________________ ǀ ǀ
ǀ ǀ_BINARY LARGE OBJECT______ǀ______________________ǀ________ǀ ǀ
ǀ ǀ_BLOB_________________ǀ ǀ_(__integer______)____ǀ ǀ
ǀ ǀ_K_ǀ ǀ
ǀ ǀ_M_ǀ ǀ
ǀ ǀ_G_ǀ ǀ
ǀ__DATE____________________________________________________________________________ǀ
ǀ ǀ_TIME______ǀ ǀ
ǀ ǀ __(_6_)____ _WITHOUT TIME ZONE_ ǀ
ǀ ǀ_TIMESTAMP_ǀ___________ǀ____ǀ___________________ǀ_______________________________ǀ
ǀ ǀ_(integer)_ǀ ǀ_WITH TIME ZONE____ǀ ǀ
ǀ_ROWID____________________________________________________________________________ǀ
ǀ XML______________________________________________________________________________ǀ
長さ:
>_integer_______________________________________________________________________>
ǀ_CODEUNITS16_ǀ
ǀ_CODEUNITS32_ǀ
ǀ_OCTETS______ǀ
LOBの長さ:
>_integer_______________________________________________________________________>
ǀ_K_ǀ ǀ_CODEUNITS16_ǀ
ǀ_M_ǀ ǀ_CODEUNITS32_ǀ
ǀ_G_ǀ ǀ_OCTETS______ǀ
XMLCAST仕様
>__XMLCAST_(__expression____________AS__data-type_______________________________>
ǀ_NULL_____________ǀ
ǀ_parameter-marker_ǀ
配列式
>__array-expression__(array-index)__________________________________________>
配列コンストラクター
>__ARRAY_[_______________________________]__________________________________>
ǀ_fullselect_______________ǀ
ǀ _,___________________ ǀ
ǀ ǀ ǀ ǀ
ǀ V ǀ ǀ
ǀ___element-expression__ǀ__ǀ
ǀ_NULL______________ǀ
NEXT VALUE式
>__NEXT VALUE FOR sequence-name_____________________________________________>
PREVIOUS VALUE式
>__PREVIOUS VALUE FOR sequence-name_________________________________________>
ROW CHANGE式
>_ROW CHANGE____TIMESTAMP____FOR__table-designator__________________________>
ǀ_TOKEN_____ǀ
OLAP
>____ordered-OLAP-specification_____________________________________________>
ǀ_numbering-specification___ǀ
ǀ_aggregation-specification_ǀ
ordered-OLAP-specification
>__RANK________(_)_OVER_(_____________________________window-order-clause_)_>
ǀ_DENSE_RANK_(_)ǀ ǀ_window-partition-clause_ǀ
番号指定:
>__ROW_NUMBER___()_OVER_(_____________________________window-order-clause_)_>
ǀ_window-partition-clause_ǀ
集約指定:
>_aggregate_function_()_OVER_(________________________window-order-clause_)_>
ǀ_window-partition-clause_ǀ
__RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUND FOLLOWING____________
>___ǀ_____________________________________________________________________ǀ__>
ǀ _RANGE BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW_ ǀ
ǀ_window-order_ǀ___________________________________________________ǀ_)ǀ
ǀ_ window-aggregation-group-clause__________________ǀ
ウィンドウパーティション句:
<_,_____________________
>_PARTITION BY__partition-expression__ǀ____________________________________>
ウィンドウ順序句:
<_,_________________________________________________
_NULLS LAST_ ǀ
_ASC__ǀ____________ǀ___ ǀ
>_ORDER BY____sort-key-expression_______ǀ_______________________ǀ_ǀ________>
ǀ_ASC NULLS FIRST______ǀ
ǀ _NULLS FIRST_ ǀ
ǀ_DESC_ǀ_____________ǀ_ǀ
ǀ_DESC NULLS LAST______ǀ
集約関数:
>___AVG FUNCTION___________________________________________________________>
ǀ_CORRELATION function_ǀ
ǀ_COUNT function_______ǀ
ǀ_COUNT_BIG function___ǀ
ǀ_COVARIANCE function__ǀ
ǀ_MAX function_________ǀ
ǀ_MIN function_________ǀ
ǀ_STDDEV function______ǀ
ǀ_SUM function_________ǀ
ǀ_VARIANCE function____ǀ
ウィンドウ集約グループ句
>___ROWS______________group-start_________________________________________>
ǀ_RANGE_ǀ ǀ_group-between_ǀ
ǀ_group-end_____ǀ
グループ開始
>___UNBOUNDED PRECEDING___________________________________________________>
ǀ_unsigned-constant_PRECEDING_ǀ
ǀ_CURRENT ROW_________________ǀ
グループ間
>___BETWEEN__group-bound-1__AND__group-bound-2____________________________>
グループ境界-1
>___UNBOUNDED PRECEDING___________________________________________________>
ǀ_unsigned-constant_PRECEDING_ǀ
ǀ_unsigned-constant_FOLLOWING_ǀ
ǀ_CURRENT ROW_________________ǀ
グループ終了
>___UNBOUNDED FOLLOWING___________________________________________________>
ǀ_unsigned-constant_FOLLOWING_ǀ
述語
>________basic predicate__________________________________________________>
ǀ_quantified predicate___ǀ
ǀ_ARRAY_EXISTS predicate_ǀ
ǀ_BETWEEN predicate______ǀ
ǀ_DISTINCT predicate_____ǀ
ǀ_EXISTS predicate_______ǀ
ǀ_IN predicate___________ǀ
ǀ_LIKE predicate_________ǀ
ǀ_NULL predicate_________ǀ
ǀ_XMLEXISTS predicate____ǀ
基本述語
>>__ _expression_ _=______ _expression__________________________ __________><
ǀ ǀ_<>_____ǀ ǀ
ǀ ǀ_<______ǀ ǀ
ǀ ǀ_>______ǀ ǀ
ǀ ǀ_<=_____ǀ ǀ
ǀ ǀ_>=_____ǀ ǀ
ǀ ǀ
ǀ__row-value-expression___ _=____ ___row-value-expression___ǀ
ǀ_<>___ǀ
ǀ_<____ǀ
ǀ_>____ǀ
ǀ_<=___ǀ
ǀ_>=___ǀ
量的述語
>>__ _expression__ _=______ __ _SOME_ __(fullselect1)____ _______________><
ǀ ǀ ǀ ǀ_ANY__ǀ ǀ
ǀ ǀ_<>_____ǀ ǀ_ALL__ǀ ǀ
ǀ ǀ_<______ǀ ǀ
ǀ ǀ_>______ǀ ǀ
ǀ ǀ_<=_____ǀ ǀ
ǀ ǀ_>=_____ǀ ǀ
ǀ_( row-value-expression)=_ _SOME_ _(fullselect2)___ ǀ
ǀ ǀ_ANY_ǀ ǀ
ǀ_( row-value-expression)___<>______ALL(fullselect2)_ǀ
ARRAY_EXISTS述語
>__ARRAY_EXISTS_(array-expression, array-index)___________________________><
BETWEEN述語
>__expression___________BETWEEN__expression__AND__expression______________><
ǀ_NOT_ǀ
異なる述語
>__expression_IS________DISTINCT FROM__expression_________________________><
ǀ ǀ_NOT_ǀ ǀ
ǀ_(row-value-expression)_IS______DISTINCT FROM_(row-value-expression)__ǀ
ǀ_NOT_ǀ
存在する述語
>__EXISTS(fullselect)_____________________________________________________><
IN述語
>>__ _expression1__ _____ __IN_ _(fullselect1)_________ __ _____________><
ǀ ǀ_NOT_ǀ ǀ <_,_____________ ǀ ǀ
ǀ ǀ_(____expression2__ǀ_)_ǀ ǀ
ǀ_(row-value-expression)__ _____ __IN__(fullselect2)__ǀ
ǀ_NOT_ǀ
LIKE述語
>__match-expression___________LIKE__pattern-expression____________________>
ǀ_NOT_ǀ
>_________________________________________________________________________>
ǀ_ESCAPE__escape-expression_ǀ
NULL述語
>__expression__IS_____________NULL________________________________________>
ǀ_NOT_ǀ
XMLEXISTS
>_XMLEXISTS_(_xquery-expression-constant__________________________________>
>__________________________________________________)______________________><
ǀ _BY REF_ ǀ
ǀ_PASSING_ǀ______ǀxquery-argumentǀ__ǀ
xquery-argument
>__xquery-context-item-expression_________________________________________><
ǀ_xquery-context-item-expression_AS_identifier_ǀ
検索条件
>__________predicate______________________________________________________>
ǀ_NOT_ǀ ǀ ǀ_SELECTIVITY_numeric-constant_ǀ ǀ
ǀ_(search-condition)_______________________________ǀ
<____________________________________________
>________________________________________________ǀ_______________________>
ǀ___AND_______________predicate__________ǀ
ǀ_OR__ǀ ǀ_NOT_ǀ ǀ_(search-conditon)_ǀ
免責事項: この Db2® 12 for z/OS リファレンスガイドは、Db2 for z/OS の管理およびプログラミングにおけるユーザーの日常活動を支援するために作成されました。このガイドの内容に関しては、明示または暗示の保証はありません。ユーザーのために質の高い有用なリファレンスを提供したいと考えています。このリファレンスガイドの誤りやエラーについては、blogs@bmc.com までご連絡ください。Db2はIBM Corporationの登録商標です。
Db2ガイド
- z/OS用Db2 12: はじめに
- Db2 12のスカラー関数
- Db2 12のIBM<sup>®</sup>構文図の読み方
- Db2 12の言語要素
- Db2 12の集約関数
- Db2 12のテーブルおよび行関数
- Db2 12のクエリ
- Db2 12のコマンド
- Db2 12のSQL制御文
- Db2 12の説明テーブル
- Db2 12のDSNZPARM
- Db2 12のバインドパラメータ
- Db2 12のDb2制限
- Db2 12のSQL通信領域(SQLCA)
- Db2 12のREXX SQLCA
- Db2 12の診断を取得
- Db2 12のIFCIDS
- Db2 12のカタログテーブル
- Db2 12の例外
- Db2 12のXMLスキーマリポジトリテーブル
- Db2 12の更新可能なカタログ統計
- Db2 12のIBMユーティリティ
- Db2 12のSQL正の戻りコード
- Db2 12のSQLエラー戻りコード
これらの投稿は私自身の意見であり、必ずしもBMCの立場、戦略、または意見を表すものではありません。