言語要素

NULL の処理

NOT NULL データ制約は、1 つのフィールドが 1 つのデータ値を取得することを必要とします。値ではなく NULL を指定することはできません。この制約は、空文字列値の使用を妨げません。詳細は、"CREATE TABLE" コマンドを参照してください。

SELECT 文の WHERE 節または HAVING 節内の IS NULL 述語は、NULL 値を選択します。空文字列値は選択しません。

IFNULL 関数は、フィールド値を評価して、フィールドが NULL に評価された場合は 2 つ目の引数に指定されている値を返します。この関数が、空文字列の値を NULL 以外の値として扱うことはありません。

COALESCE 関数は、指定されたデータから最初の NULL でない値を選択します。空文字列は、NULL でない値として処理されます。

CONCAT 関数または連結演算子 (||) が 1 つの文字列と 1 つの NULL を連結すると、結果は NULL になります。詳細は、以下の例を参照してください。

SELECT {fn CONCAT('fred',NULL)} AS FuncCat,   -- returns <null>
       'fred'||NULL AS OpCat                  -- returns <null>

AVG、COUNT、MAX、MIN、および SUM 集約関数は、処理を実行するときに NULL 値を無視します (すべてのフィールドに NULL 値 を持つレコードは存在できないため、COUNT * はすべての行をカウントします)。SELECT 文の DISTINCT キーワードは、その処理に NULL を含みます。指定したフィールドに NULL 値が存在する場合、DISTINCT は NULL 行を返します。

AVG、COUNT、および MIN 集約関数は、空文字列値の影響を受けます。MIN 関数は、ゼロ値を持つ行がある場合でも、空文字列を最小値と判断します。MAX および SUM 集約関数は、空文字列値の影響を受けません。

式内の NULL

ほとんどの SQL 関数で NULL をオペランドとして指定すると、NULL が返されます。

NULL をオペランドとして持つ任意の SQL 算術演算は、NULL 値を返します。したがって、7+NULL=NULL となります。これには、二項演算子の加算 (+)、減算 (-)、乗算 (*)、除算 (/)、整数除算 (\)、モジュロ (#)、および単項演算子符号のプラス (+) およびマイナス (-) が含まれます。

算術演算で指定された空文字列は、0 (ゼロ) 値として処理されます。6/'' など、空の文字列による除算 (/)、整数除算 (\)、またはモジュロ (#) では、<DIVIDE> エラーが返されます。

NULL の長さ

SQL 内では、NULL の長さは未定義です (<null> を返します)。しかし、空文字列の長さは、長さゼロとして定義されています。詳細は、以下の例を参照してください。

SELECT LENGTH(NULL) AS NullLen,   -- returns <null>
       LENGTH('') AS EmpStrLen    -- returns 0

この例で示すように、SQL LENGTH 関数は SQL の長さを返します。

以下の例に示すように、ASCII 関数を使用して、長さゼロの SQL 文字列を NULL に変換できます。

SELECT LENGTH(NULL) AS NullLen,                  -- returns <null> 
       LENGTH({fn ASCII('')}) AS AsciiEmpStrLen, -- returns <null>
       LENGTH('') AS EmpStrLen                   -- returns 0

ただし、標準 SQL に対する特定の InterSystems IRIS の拡張は、NULL と空文字列のそれぞれの長さを処理します。$LENGTH 関数は、これらの値の InterSystems IRIS 内部表現を返します。NULL は、長さゼロの定義済みの値として表され、SQL の空文字列は、長さゼロの文字列として表されます。この機能は、ObjectScript と互換性があります。

SELECT $LENGTH(NULL) AS NullLen,    -- returns 0
$LENGTH('') AS EmpStrLen,           -- returns 0
$LENGTH('a') AS OneCharStrLen,      -- returns 1
$LENGTH(CHAR(0)) AS CharZero        -- returns 0 

これらの値の内部表現が有効な別の場所は、%STRING、%SQLSTRING、および %SQLUPPER 関数内です。これらの関数は、空白スペースを値に追加します。NULL は実際には値を持たないため、それに空白を追加すると、長さが 1 の文字列が作成されます。一方、空文字列は文字値を持つため、それに空白を追加すると、長さが 2 の文字列が作成されます。以下の例を参照してください。

SELECT CHAR_LENGTH(%STRING(NULL)) AS NullLen,  -- returns 1
CHAR_LENGTH(%STRING('')) AS EmpStrLen          -- returns 2

この例では、LENGTH ではなく CHAR_LENGTH が使用されることに注意してください。LENGTH 関数は末尾の空白を削除するため、LENGTH(%STRING(NULL)) は長さ 0 を返します。LENGTH(%STRING('')) は、%STRING が末尾の空白ではなく先頭の空白を付加するため、長さ 2 を返します。

ObjectScript と SQL

SQL の NULL が ObjectScript に出力された場合、その SQL の NULL は、ObjectScript の空文字列 ("")、つまり長さゼロの文字列で表されます。

長さゼロの SQL 文字列データが ObjectScript に出力された場合、そのデータは、長さ 1 の文字列である $CHAR(0) を含む文字列で表されます。

   &sql(SELECT NULL,''
        INTO :a,:b)

  WRITE !,"NULL length: ",$LENGTH(a)         // returns 0
  WRITE !,"empty string length: ",$LENGTH(b) // returns 1

ObjectScript では、通常、値が存在しないことを空文字列 ("") で示します。この値が埋め込み SQL に渡された場合は、以下の例に示すように、NULL 値として処理されます。

  set x=""
  set myquery="SELECT NULL As NoVal,:x As EmpStr"
  set tStatement=##class(%SQL.Statement).%New()
  
  set qStatus = tStatement.%Prepare(myquery)
  if $$$ISERR(qStatus) {write "%Prepare failed:" do $System.Status.DisplayError(qStatus) quit}
  
  set rset = tStatement.%Execute()
  if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  while rset.%Next()
  {
    write "NoVal:",rset.%Get("NoVal")," length ",$LENGTH(rset.%Get("NoVal")),! // length 0
    write "EmpStr:",rset.%Get("EmpStr")," length ",$LENGTH(rset.%Get("EmpStr")),! // length 0
  }
  if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}

  write "End of data"

定義されていない入力ホスト変数を指定した場合、埋め込み SQL はその値を NULL として処理します。

以下の例では、空白を追加された SQL 空文字列は、文字列長 2 として渡されます。

   &sql(SELECT %SQLUPPER('')
        INTO :y )
  WRITE !,"SQL empty string length: ",$LENGTH(y)

結果のデータ型

データ型が異なる 2 つの数値に対して算術演算を実行した場合、結果のデータ型は以下のように決定されます。

加算 (+)、減算 (-)、整数除算 (\)、およびモジュロ (#) の場合 ：

乗算 (*) または除算 (/) の場合 ：

任意のデータ型の 2 つの数値を連結すると、VARCHAR 文字列になります。

ダイナミック SQL では、SQL 列のメタデータを使用して、結果セット・フィールドのデータ型を確認できます。数値データ型の詳細は、SQL データ型を参照してください。

演算子の優先順位

SQL-92 標準は演算子の優先順位に関して不正確です。このことについての前提は SQL 実装間で異なります。InterSystems SQL は、以下のいずれかのタイプの優先順位をサポートするよう構成できます。

• InterSystems IRIS 2019.1 以降の場合、InterSystems SQL では、既定で算術演算子の ANSI 優先順位がサポートされます。これは、システム全体の構成設定です。ANSI の優先順位を構成した場合、演算子 "*"、"\"、"/"、および "#" は、演算子 "+"、"-"、および "||" よりも優先順位が高くなります。優先順位の高い演算子は、優先順位の低い演算子より先に実行されます。したがって 3+3*5 は 18 になります。この優先順位は、必要に応じて括弧を使用することでオーバーライドできます。したがって (3+3)*5 は 30 になります。

  既定の ANSI 優先順位は、InterSystems IRIS 2019.1 のクリーン・インストールの場合にサポートされます。InterSystems IRIS 2018.1 を InterSystems IRIS 2019.1 にアップグレードした場合は、演算子の優先順位はそのまま InterSystems IRIS 2018.1 の既定である厳密な左から右の順序に構成されます。

• InterSystems IRIS 2018.1 の場合、InterSystems SQL では、既定で算術演算子に優先順位はありません。既定では、InterSystems SQL には演算子の優先順位はなく、算術式は必ず左から右の順番で実行されます。これは、ObjectScript で使用される規則と同じです。したがって 3+3*5 は 30 になります。演算順位を指定するには、括弧を使用します。したがって 3+(3*5) は 18 に等しくなります。開発者は、注意深く括弧を使用して、意図を明示する必要があります。

$SYSTEM.SQL.Util.SetOption()Opens in a new tab メソッドを次のように使用して、システム全体でどちらのタイプの SQL 演算子の優先順位も構成できます。SET status=$SYSTEM.SQL.Util.SetOption("ANSIPrecedence",1,.oldval) は、ANSI の優先順位を設定し、SET status=$SYSTEM.SQL.Util.SetOption("ANSIPrecedence",0,.oldval) は、厳密な左から右への評価を設定します。現在の設定を確認するには $SYSTEM.SQL.CurrentSettings()Opens in a new tab を呼び出します。これにより、[ANSI の演算子優先順位を適用する] オプションが表示されます。この SQL オプションの変更内容は、すぐにシステム全体で有効になります。このオプションを変更すると、すべてのクエリ・キャッシュがシステム全体で削除されます。

SQL の優先順位を変更しても、ObjectScript には影響しません。ObjectScript は常に、厳密に左から右へ算術演算子を実行します。

精度と小数桁数

NUMERIC 結果の 精度 (数値内の最大桁数) は、以下のようになります。

• 加算または減算は、resultprecision=max(scale1, scale2) + max(precision1–scale1, precision2–scale2)+1 というアルゴリズムによって決定されます。計算値 resultprecision が 36 より大きい場合、有効桁数の値は 36 に設定されます。

• 乗算は、resultprecision=min(36, precision1+precision2+1) というアルゴリズムによって決定されます。

• 除算 (value1 / value2) は、resultprecision=min(36, precision1–scale1+scale2+max(6, scale1+precision2+1)) というアルゴリズムによって決定されます。

NUMERIC 結果の スケール (小数部の最大桁数) は、以下のようになります。

• 加算または減算は、resultscale=max(scale1, scale2) というアルゴリズムによって決定されます。

• 乗算は、resultscale=min(17, scale1+scale2) というアルゴリズムによって決定されます。

• 除算 (value1 / value2) は、resultscale=min(17, max(6, scale1+precision2+1)) というアルゴリズムによって決定されます。

データ型、有効桁数、および小数桁数の詳細は、SQL の "データ型" を参照してください。

算術関数と三角関数

InterSystems SQL は以下の算術関数をサポートします。

InterSystems SQL は以下の三角関数をサポートします。

InterSystems SQL は以下の角度変換関数もサポートします。

包含関係演算子と後続関係演算子

InterSystems SQL では、Contains 比較演算子と Follows 比較演算子もサポートされています。

• 包含関係演算子は、指定された文字または文字列が値に含まれているかどうかを判断します。大文字と小文字が区別されます。

• %STARTSWITH 述語条件は、指定された文字または文字列で値が始まるかどうかを判断します。大文字と小文字は区別されません。

• InterSystems SQL Search を使用して、指定された単語や語句が値に含まれているかどうかを判断できます。SQL Search は、コンテキスト認識のマッチングを実行します。大文字と小文字は区別されません。

例えば、SELECT Age FROM MyTable WHERE Age ] 88 は 89 以上を返しますが、9 も返します。9 は照合シーケンスでは 88 の後であるためです。SELECT Age FROM MyTable WHERE Age > 88 は 89 以上を返し、9 は返しません。‘ABC’ などの文字列オペランドは、‘ABCA’ など、追加の文字を含む文字列の前に照合されます。このため、オペランド文字列を ] 演算子または > 演算子から除外するには、文字列全体を指定する必要があります。Name ] ‘Smith,John’ は、‘Smith,John’ を除外しますが、‘Smith,John P’ は除外しません。

単項否定演算子

単項否定論理演算子を使用して、論理的に逆の条件を指定します。以下に例を示します。

SELECT Name,Age FROM Sample.Person
WHERE NOT Age>21
ORDER BY Age

SELECT Name,Age FROM Sample.Person
WHERE NOT Name %STARTSWITH('A')
ORDER BY Name

NOT 演算子を条件の前に配置できます (上記のとおり)。または、NOT を単一文字演算子の直前に配置できます。例えば、NOT<、NOT[ などです。NOT とそれによって否定する単一文字演算子の間には、スペースを入れてはなりません。

AND 演算子および OR 演算子

一連の複数条件で 2 つのオペランド間に AND 論理演算子および OR 論理演算子を使用できます。これらの論理演算子は、キーワードまたは記号で指定できます。

記号演算子とオペランドの間にスペースは必要ありません (ただし読みやすくするためにスペースを入れることをお勧めします)。キーワード演算子の前後にはスペースが必要です。

これらの論理演算子は、単項否定論理演算子と組み合わせることができます。WHERE Age<65 & NOT Age=21 のように使用します。

以下の 2 つの例では、論理演算子を使用して、年齢に基づいた課税をスケジュールします。年齢が 20 ～ 40 歳の人は 3 年ごとに、40 ～ 64 歳の人は 2 年ごとに、そして 65 歳以上の人は毎年課税します。以下の例では結果が同じになります。最初の例はキーワードを使用し、2 番目の例は記号を使用しています。

SELECT Name,Age FROM Sample.Person
WHERE Age>20
      AND Age<40 AND (Age # 3)=0 
      OR Age>=40 AND (Age # 2)=0 
      OR Age>=65
ORDER BY Age

SELECT Name,Age FROM Sample.Person
WHERE Age>20
      & Age<40 & (Age # 3)=0 
      ! Age>=40 & (Age # 2)=0 
      ! Age>=65
ORDER BY Age

括弧を使用すると論理演算子をグループにできます。これにより、グループ・レベルを設定できます。評価は、最下位のグループから最上位のグループの順で行われます。以下の最初の例では、AND 条件は 2 番目の OR 条件にのみ適用されます。MA からすべての年齢の人物を返し、NY から 25 歳未満の人物を返します。

SELECT Name,Age,Home_State FROM Sample.Person
WHERE Home_State='MA' OR Home_State='NY' AND Age < 25
ORDER BY Age

グループ条件で括弧を使用すると、異なる結果になります。以下の例は、MA または NY から、年齢が 25 歳未満の人物を返します。

SELECT Name,Age,Home_State FROM Sample.Person
WHERE (Home_State='MA' OR Home_State='NY') AND Age < 25
ORDER BY Age

• SQL の実行は、簡易版のロジックを使用します。条件が偽になると、残りの AND 条件はテストされません。条件が真になると、残りの OR 条件はテストされません。

• ただし、SQL は、WHERE 節の実行を最適化するため、同じグループ・レベルでの複数条件の実行順序は予測できず、有効ではありません。

1 行コメント

1 行のコメントを指定するには、行頭に 2 つのハイフンを記述します。コメントは独立した行として記述できるほか、SQL コードと同じ行に記述することもできます。SQL コードの後にコメントを続ける場合は、コードの末尾に空白を 1 つ以上置き、その後にハイフン 2 つのコメント演算子を記述します。コメントには、ハイフン、アスタリスク、スラッシュも含め、あらゆる文字を使用できます。その行の最後までがコメントです。

以下の例では、複数の 1 行コメントが記述されています。

-- This is a simple SQL query
-- containing -- (double hyphen) comments
SELECT TOP 10 Name,Age, -- Two columns selected
Home_State -- A third column
FROM Sample.Person -- Table name
-- Other clauses follow
WHERE Age > 20 AND -- Comment within a clause
Age < 40
ORDER BY Age,  -- Comment within a clause
Home_State
-- End of query

複数行コメント

複数行のコメントを指定するには、/* 開始区切り文字と */ 終了区切り文字を使用します。コメントは、1 行または複数の別々の行として記述することも、SQL コードと同じ行で開始または終了することもできます。少なくとも 1 つの空白を使用して、コメント区切り文字を SQL から区切る必要があります。コメントには、ハイフン、アスタリスク、スラッシュも含め、あらゆる文字を記述できます。ただし、*/ 文字の組み合わせは明らかに例外です。

以下の例では、複数の複数行コメントが記述されています。

/* This is 
   a simple 
   SQL query. */
SELECT TOP 10 Name,Age /* Two fields selected */
FROM Sample.Person  /* Other clauses 
could appear here */ ORDER BY Age
/* End of query */

埋め込み SQL コードをコメントアウトする場合、必ずコメントを &sql 指示文の前または括弧内で始めます。以下の例では、2 つの埋め込み SQL コード・ブロックが正しくコメントアウトされています。

   SET a="default name",b="default age"
   WRITE "(not) Invoking Embedded SQL",!
   /*&sql(SELECT Name INTO :a FROM Sample.Person) */
   WRITE "The name is ",a,!
   WRITE "Invoking Embedded SQL (as a no-op)",!
   &sql(/* SELECT Age INTO :b FROM Sample.Person */)
   WRITE "The age is ",b

コメントとして保持される SQL コード

埋め込み SQL 文は、ルーチンの .INT コード・バージョンのコメントとして保持することができます。これをシステム全体で行うには、$SYSTEM.SQL.Util.SetOption()Opens in a new tab メソッドを SET status=$SYSTEM.SQL.Util.SetOption("RetainSQL",1,.oldval) のように設定します。現在の設定を確認するには、$SYSTEM.SQL.CurrentSettings()Opens in a new tab を呼び出します。これにより、[.INT コード内で SQL 文をコメントとして残す] の設定が表示されます。既定は 1 (“はい”) です。

このオプションを [はい] に設定すると、ルーチンの .INT コード・バージョンで SQL 文をコメントとして維持できます。また、“はい” に設定することで、コメントのテキストの中でその SQL 文によって使用されている非 % 変数をすべてリストにできます。このリストの変数を ObjectScript プロシージャの PUBLIC 変数リストにもリストし、NEW コマンドで再び初期化する必要があります。詳細は、このドキュメントの “埋め込み SQL” の章の "ホスト変数" を参照してください。