官方SQL語言參考筆記重點整理之表達式.md

表達式

表達式是一個或多個值、運算符和 SQL 函數的組合, 計算結果為值。表達式通常假定其組件的數據類型。

可以在中使用表達式:

select 語句的選擇列表SELECT

WHERE子句和HAVING子句的條件

BY子句CONNECT、START WITH和ORDER BY

INSERT語句的VALUES子句

UPDATE語句的SET子句

簡單表達式

簡單表達式指定列、偽列、常量、序列號或 null

例子:

複合表達式

複合表達式指定其他表達式的組合。

例子:

CASE表達式

CASE表達式允許使用IF ...THEN...ELSE SQL 語句中的邏輯, 而不必調用過程。

CASE表達式中的最大參數數為65535。

可以使用嵌套

返回類型必須保持一致

對於示例oe.customers" 表中的每個客戶, 以下語句將信用限額列出為 "低", 如果它等於 $ 100, "高", 如果它等於 5000, 和 "中等", 如果它等於其他任何東西。

下面的語句查找示例表oe中僱員的平均工資oe.employees使用最低工資2000元:

列表達式

列表達式 (在後面的語法圖中被指定為column_expression ) 是一個有限的expr形式。

列表達式可以是簡單表達式、複合表達式、函數表達式或表達式列表, 但只能包含下列表達式:

主題表的列-正在創建、更改或索引的表

常量 (字元串或數字)

確定性函數-SQL 內置函數或用戶定義函數

使用PRIOR的關鍵字的複合表達式不受支持, 也不是聚合函數。

可以為一下目的使用列表達式:

創建基於函數的索引。

以顯式或隱式定義虛列。定義虛擬列時, 定義的column_expression必須僅引用在當前語句或前面的語句中已定義的主題表的列。

列表達式的組合組件必須是確定性的。即, 同一組輸入值必須返回同一組輸出值。

游標表達式

CURSOR表達式返回嵌套游標。這種形式的表達式等效於 PL/SQL REF CURSOR, 可以作為REF CURSOR參數傳遞給函數。

在計算游標表達式時, 將隱式打開嵌套游標。

例如, 如果游標表達式出現在選擇列表中, 則會為查詢提取的每一行打開一個嵌套游標。

只有在以下情況下, 嵌套游標才會關閉:

嵌套游標由用戶顯式關閉

父游標為重新執行

父游標已關閉

父游標被取消

在讀取其父游標之一時出現錯誤 (它作為清理的一部分關閉)

游標表達式的限制 下列限制適用於CURSOR表達式:

如果封閉語句不是SELECT語句, 則嵌套游標只能作為過程的REF CURSOR參數出現。

如果封閉語句是SELECT語句, 則嵌套游標也可以出現在查詢規範的最外層選擇列表中, 或者顯示在另一個嵌套游標的最外層選擇列表中。

嵌套游標不能出現在視圖中。

不能對嵌套游標執行BIND和EXECUTE操作。 例子 下面的示例演示如何在查詢的選擇列表中使用CURSOR表達式:

演示如何將CURSOR表達式用作函數參數。該示例首先在示例OE架構中創建一個可以接受REF CURSOR參數的函數。

該函數接受游標和日期。該函數期望游標是返回一組日期的查詢。下面的查詢使用該函數在 "示例employees" 表中查找這些管理器, 其中大部分僱員是在經理之前僱用的。

日期時間格式

datetime 表達式生成日期時間數據類型之一的值。

初始expr是除標量子查詢表達式外的任何表達式, 計算結果為數據類型TIMESTAMP的值、 WITH TIME ZONE時區的時間TIMESTAMP或時間TIMESTAMPWITHLOCALTIMEZONE。 不支持DATE數據類型。如果此expr本身是datetime_expression, 則必須用圓括弧括起來。 Datetimes 和間隔可以根據表 3-5中定義的規則進行組合。產生日期時間值的三組合在 datetime 表達式中有效。 如果ATLOCAL指定, 則 Oracle 將使用當前會話時區。

時區的設置AT TIME ZONE解釋如下:

字元串"[+|-]hh:mi " 指定一個時區作為與 UTC 的偏移量。對於hh, 指定小時數。對於mi, 指定分鐘數。

DBTIMEZONE: Oracle 在創建資料庫期間使用建立的資料庫時區 (顯式或默認)。

SESSIONTIMEZONE: Oracle 使用默認情況下建立的會話時區或最近的ALTERSESSION語句。

time_zone_name: Oracle 返回time_zone_name指示的時區中的datetime_value_expr 。 有關有效時區區域名稱的列表, 請查詢V$TIMEZONE_NAMES動態性能視圖。

注意： 時區區域名稱由夏時制功能所需。這些名稱存儲在兩種類型的時區文件中: 一個是大的, 一個是小的。 這些文件之一是默認文件, 具體取決於您所使用的環境和 Oracle 資料庫的發布。 有關動態性能視圖信息的 Oracle 資料庫引用 expr: 如果expr返回具有有效時區格式的字元串, 則 Oracle 返回該時區中的輸入。否則, Oracle 將返回一個錯誤。

例子 下面的示例將一個時區的日期時間值轉換為另一個時區:

函數表達式

可以將任何內置 SQL 函數或用戶定義函數用作表達式。一些有效的內置函數表達式為:

對用戶定義函數表達式的限制:不能將對象類型或XMLType的參數傳遞給遠程函數和過程。

用戶定義的函數表達式指定對以下內容的調用:

oracle 提供的包中的函數

用戶定義的包或類型或獨立用戶定義函數中的函數

用戶定義的函數或運算符

某些有效的用戶定義函數表達式為:

在用作表達式的用戶定義函數中, 支持位置、命名和混合表示法。例如, 下列所有符號都是正確的:

間隔表達式

間隔表達式將INTERVALYEAR的值生成TOMONTH或INTERVALDAYTOSECOND。.

表達式expr1和expr2可以是任何計算數據類型DATE、時間TIMESTAMP、TIME ZONETIMESTAMPWITH時區值的表達式 , 或者TIMESTAMPWITHLOCALTIMEZONE.

Datetimes 和間隔可以根據表 3-5中定義的規則進行組合。產生間隔值的六組合在間隔表達式中有效。

leading_field_precision和fractional_second_precision都可以是從0到9的任意整數。

如果省略leading_field_precision的任何DAY或YEAR, 則 Oracle 資料庫將使用默認值2。

如果省略第二個fractional_second_precision , 則資料庫使用默認值6。

如果查詢返回的值包含默認精度的更多位數, 則 Oracle 資料庫將返回錯誤。

因此, 最好的做法是指定您知道的精度至少與查詢返回的任何值一樣大。

例如, 以下語句從系統時間戳 (另一個 datetime 值) 中減去示例表orders(datetime 值) 中 的order_date列的值, 以產生間隔值表達式。尚不知道最早的訂單是在多少天前被放置的, 因此指定了DAY前導欄位精度的最大值 9:

模型表達式

模型表達式僅在SELECT語句的查詢中使用, 然後僅用於模型規則的右側。 它為先前在查詢中定義的度量值列中的單元格生成值。

在模型表達式中指定度量值列時, 指定的任何條件和表達式都必須解析為單個值。

在模型表達式中指定聚合函數時, 函數的參數是以前在查詢中定義的度量值列。 聚合函數只能在模型規則的右側使用。

在模型規則的右側指定一個解析函數, 可以直接在查詢中表達複雜計算。

在模型表達式中使用解析函數時, 會應用以下限制:

解析函數只能在UPDATE規則中使用。

如果規則的左側包含FOR循環或ORDER BY by 子句, 則不能在模型規則的右側指定解析函數.

解析函數的OVER子句中的參數不能包含聚合。

解析函數的OVER子句之前的參數不能包含單元格引用。

當expr本身是模型表達式時, 它稱為嵌套單元格引用。以下限制適用於嵌套單元格引用:

只允許一個級別的嵌套。

嵌套單元格引用必須是單單元格引用。

當在model_rules_clause中指定AUTOMATICORDER時, 只有在嵌套單元格引用中使用的度量值保持靜態時, 才能在模型規則的左側使用嵌套單元格引用。 下面顯示的模型表達式基於以下SELECT語句的查詢:

模型表達式表示使用符號表示法的單個單元格引用。它代表老鼠墊的銷售為年2000。

下面的模型表達式使用CV函數表示使用位置表示法的多單元格引用。它代表2001年維度列的當前值的銷售額prod 。

下面的模型表達式表示聚合函數。它表示在維度列的當前值小於二和維度列的當前值之間的年份year中, 滑鼠墊的銷售額減去一年的總和year 。

對象訪問表達式

對象訪問表達式指定屬性引用和方法調用。 列參數可以是對象或REF列。如果指定expr, 則它必須解析為對象類型。

當在 SQL 語句的上下文中調用類型的成員函數時, 如果SELF變數為 null, 則 Oracle 返回 null, 並且不調用該函數。

例子

下面的示例根據oe.order_item_typ對象類型創建一個表, 然後顯示如何從對象列屬性中更新和選擇。

佔位符表達式

佔位符表達式提供 SQL 語句中的位置, 第三代語言綁定變數將提供一個值。 可以使用可選的指示器變數指定佔位符表達式。 這種形式的表達式只能出現在 Oracle 調用介面 (保監) 程序中處理的嵌入式 sql 語句或 sql 語句中。

某些有效的佔位符表達式為:

:employee_name INDICATOR :employee_name_indicator_var

:department_location

Scalar Subquery查詢表達式

Scalar Subquery表達式是一個子查詢, 它只從一行返回一列值。

標量子查詢表達式的值是子查詢的選擇列表項的值。

如果子查詢返回0行, 則標量子查詢表達式的值為NULL。

如果子查詢返回多個行, 則 Oracle 將返回一個錯誤。

可以在調用表達式 (expr) 的大多數語法中使用標量子查詢表達式。 在所有情況下, 標量子查詢必須括在其自己的圓括弧中, 即使其語法位置已經將其定位在括弧內 (例如, 當標量子查詢用作內置函數的參數時)。

標量子查詢在以下位置不是有效的表達式:

作為列的默認值

作為群集的哈希表達式

在 DML 語句的RETURNING子句中

作為基於函數的索引的基礎

在CHECK約束中

在 "GROUPBY" 子句中

在與查詢無關的語句中, 如CREATE PROFILE

類型構造函數表達式

類型構造函數表達式指定對構造函數方法的調用。類型構造函數的參數是任何表達式。 可以在調用函數的任何位置調用類型構造函數。 對類型構造函數調用的限制

在對類型構造函數方法的調用中, 即使對象類型具有超過999個屬性, 指定的參數 (expr) 數也不能超過999。 此限制僅在從 SQL 調用構造函數時適用。對於來自 pl/sql 的調用, 將應用 pl/sql 限制。

NEW關鍵字適用於對象類型的構造函數, 但不適用於集合類型。 它指示 Oracle 通過調用適當的構造函數來構造一個新對象。NEW關鍵字的使用是可選的, 但是指定它是很好的做法。

如果type_name是對象類型, 則表達式必須是有序列表, 其中第一個參數是其類型與對象類型的第一個屬性匹配的值, 第二個參數是一個值, 其類型與第二個對象類型的屬性, 等等。 構造函數的參數總數必須與對象類型的屬性總數匹配。

如果type_name是varray或嵌套表類型, 則表達式列表可以包含零個或多個參數。 零參數意味著構造空集合。否則, 每個參數對應於其類型為集合類型的元素類型的元素值。

表達式示例

本示例使用示例oe架構中的cust_address_typ類型在對構造函數方法的調用中顯示錶達式的用法 (PL/SQL 以斜體顯示):

子查詢示例

本示例使用示例架構oe中的warehouse_typ類型說明子查詢在對構造函數方法的調用中的使用。

表達式列表

表達式列表是其他表達式的組合

表達式列表可以顯示在比較和成員條件中, 並BY查詢和子查詢的子句GROUP。 比較或成員條件中的表達式列表有時稱為行值構造函數或行構造函數.

比較和成員條件出現在WHERE子句的條件中。 它們可以包含一個或多個逗號分隔的表達式或一個或多個表達式集合, 其中每個集合包含一個或多個逗號分隔表達式。 在後一種情況下 (多組表達式):

每個集合都由圓括弧限定

每個集合必須包含相同數量的表達式

每個集合中的表達式數必須與比較條件中運算符之前的表達式數和成員條件IN的 in 關鍵字之前的 表示形式匹配。

以逗號分隔的表達式列表可以包含不超過1000個表達式。以逗號分隔的表達式集列表可以包含任意數量的集合, 但每個集合可以包含不超過1000個表達式。

下面是某些條件下的有效表達式列表:

"比較條件"和在條件條件下 在簡單GROUP BY子句中, 可以使用表達式列表的上或下形式:

在 "ROLLUP"、"CUBE" 和 "GROUPING SETS方式"GROUPBY子句中, 可以將單個表達式與同一表達式列表中的表達式集組合在一起。 下面的示例在一個 SQL 語句中顯示幾個有效的分組集表達式列表:

本人是一枚程序猿,如果覺得整理的不錯,請關注個人微信公眾號(掃一掃):

讓程序猿能共同成長起來

讓程序猿傳播和共享技術

讓程序猿養成做筆記習慣

讓程序猿持續的增長知識

讓程序猿寫代碼更加規範

讓程序猿職場上學會低調

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！