資料庫（Oracle）序列（Sequence）的基本使用

来源:https://www.cnblogs.com/codiary/archive/2023/11/09/17819248.html
-Advertisement-
Play Games

在構建數據倉庫或做數據分析時，需要對原始數據的結構進行一定的處理，有時涉及到“行轉列”，有時涉及到“列轉行”，那麼這兩個轉換的方式具體是什麼，有什麼差異，怎麼實現。 ...


1. Sequence 定義

在Oracle中可以用SEQUENCE生成自增欄位。Sequence序列是Oracle中用於生成數字序列的對象，可以創建一個唯一的數字作為主鍵。

2. 為什麼要用 Sequence

你可能有疑問為什麼要使用序列？

不能使用一個存儲主鍵的表並每次遞增嗎？或者將列設置為AUTO INCREMENT？

如果使用一個表來儲存主鍵值的話，也許需要這樣做：

  1. 創建一個新表來儲存單一值。
  2. 每次插入新值到你想要插入的表時，在新表中使用該值。
  3. 然後更新新表（將其加1）。
  4. 這些邏輯都可以加入進存儲過程。

然而這種方法存在以下幾個問題：

  1. 如果你的事務中途失敗，可能會得到重覆的數據（插入的數據有一個ID，但新表沒有增加）。這可以通過先遞增然後插入來部分避免，但它仍然有中途失敗的風險。
  2. 更新表將對其進行鎖定，這可能會導致多個事務或用戶出現問題。如果有一個用戶，這可能是可以的，但如果程式有兩個或多個用戶，那麼這可能會導致問題。
  3. 創建和維護一個表以及執行此操作的代碼是繁重的工作。

Sequence是一個更好的方法，原因如下：

  1. 創建更簡單。
  2. 使用起來更簡單。
  3. 沒有死鎖以及導致其他事務出現問題的風險。

3. 創建（CREATE）Sequence

CREATE SEQUENCE [schema_name.]{sequence_name}    -- schema_name為將存儲序列的模式名，sequence_name 為自定義名稱；
	START WITH      n    -- n 為序列的初始值，預設為1；
	INCREMENT BY    n    -- n 為序列步長（序列增加的幅度），預設為1，如果是負則按此步長遞減；
	[MINVALUE n | NOMINVALUE ]    -- 如果序列遞減，定義序列生成器能產生的最小值,預設為1
	[MAXVALUE n | NOMAXVALUE ]    -- 定義序列生成器能產生的最大值,預設無限制（1e28 - 1）
	[CACHE n | NOCACHE]    -- value 是存放序列的記憶體塊大小，預設20。對序列進行記憶體緩存可以改善序列性能。
	[ORDER | NOORDER]    -- 表示序列號是按照請求的順序生成的。如果使用時間戳的序列號會有用。
	[CYCLE | NOCYCLE]    -- 值達到限制值後是否迴圈，如果不迴圈，達到限制值後，繼續產生新值會發生錯誤 ;

使用緩存創建序列的缺點是，如果發生系統故障，所有未使用的緩存序列值都將“丟失”。這會在指定的序列值中產生“間隙”。當系統恢復時，Oracle將從序列中停止的位置緩存新數字，忽略所謂的“丟失”序列值。要恢復丟失的序列值，您可以始終執行ALTER sequence命令，將計數器重置為正確的值。

4. 使用 Sequence

CURRVAL和NEXTVAL可以在以下幾個地方使用：

  • INSERT語句的VALUES子句。
  • SELECT語句的SELECT列表
  • UPDATE語句的UPDATE子句

它們不能在以下地方使用：

  • CHECK約束的條件
  • 視圖查詢
  • 實體化視圖查詢
  • 子查詢
  • 帶有DISTINCT關鍵字、GROUP BY子句或ORDER BY子句的SELECT語句
  • 使用UNION、INTERSECT或MINUS運算符與另一個SELECT語句組合的SELECT語句
  • SELECT語句的WHERE子句
  • CREATE TABLE或ALTER TABLE語句中列的DEFAULT值

最後一點意味著您不能使用DEFAULT關鍵字的序列將列設置為自動遞增。有一種方法可以創建自動遞增列，那就是使用觸發器和序列。

1）基本Oracle序列示例

以下語句創建一個名為id_seq的升序Sequence，從10開始，遞增10，最小值為10，最大值為100。由於CYCLE選項，序列一旦達到100就返回10。

CREATE SEQUENCE id_seq
    INCREMENT BY 10
    START WITH 10
    MINVALUE 10
    MAXVALUE 100
    CYCLE
    CACHE 2;

使用NEXTVAL偽列獲取序列的下一個值：

SELECT 
    id_seq.NEXTVAL 
FROM 
    dual;
以下是輸出：
    NEXTVAL
----------
        10

一旦通過NEXTVAL偽列獲取序列號，就可以使用CURRVAL偽列重覆訪問它：

SELECT 
    id_seq.CURRVAL 
FROM 
    dual;
以下是輸出：
    CURRVAL
----------
        10

註意！當調用NEXTVAL偽列時，它會為正在選擇或插入的每一行數據返回一個新值。：

SELECT 
    id_seq.NEXTVAL 
FROM 
    dual;
以下是輸出：
    NEXTVAL
----------
        20

但如果我使用幾個NEXTVAL命令運行此查詢的話：

SELECT id_seq.NEXTVAL AS nv1,
id_seq.NEXTVAL AS nv2,
id_seq.NEXTVAL AS nv3
FROM dual;
以下是輸出：
       NV1         NV2         NV3
----------  ----------  ----------
        30          30          30

這表明NEXTVAL只為此記錄調用過一次，並且每次的值都是相同的。

2) 在表列中使用 Sequence 的例子

在Oracle12c之前，只能在插入時將序列與表列間接關聯。

首先，創建一個名為tasks的新表：

CREATE TABLE tasks(
    id NUMBER PRIMARY KEY,
    title VARCHAR2(255) NOT NULL
);

其次，為tasks表的id列創建一個序列：

CREATE SEQUENCE task_id_seq;

第三，在任務表中插入數據：

INSERT INTO tasks(id, title)
VALUES(task_id_seq.NEXTVAL, 'Create Sequence in Oracle');

INSERT INTO tasks(id, title)
VALUES(task_id_seq.NEXTVAL, 'Examine Sequence Values');

最後，從任務表中查詢數據：

SELECT  
    id, title
FROM
    tasks;

添加圖片註釋，不超過 140 字（可選）

在本例中，tasks表與task_id_seq序列沒有直接關聯。

3）通過標識列使用序列的例子

在Oracle12c中，您可以通過標識列將序列與表列相關聯。

首先，用id列的identity列創建tasks表。

CREATE TABLE tasks(
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title VARCHAR2(255) NOT NULL
);

Oracle創建了一個與 tasks表 的 id列 關聯的序列。

由於Oracle自動為 id列 生成序列，因此在您的Oracle實例中，序列的名稱可能不同。

Oracle創建序列-標識列

添加圖片註釋，不超過 140 字（可選）

Oracle使用 sys.idnseq$ 來存儲表和序列之間的鏈接。

此查詢返回任務表和 ISEQ$$_74366序列 的關聯：

SELECT 
    a.name AS table_name,
    b.name AS sequence_name
FROM   
    sys.idnseq$ c
    JOIN obj$ a ON c.obj# = a.obj#
    JOIN obj$ b ON c.seqobj# = b.obj#
WHERE 
    a.name = 'TASKS';

第二，在任務表中插入一些行：

INSERT INTO tasks(title)
VALUES('Learn Oracle identity column in 12c');

INSERT INTO tasks(title)
VALUES('Verify contents of the tasks table');

最後，從任務表中查詢數據：

SELECT
    id, title
FROM
    tasks;

添加圖片註釋，不超過 140 字（可選）

5. 刪除（DROP）Sequence

要刪除序列，它必須在您的模式中，或者您必須具有drop ANY sequence許可權才能刪除其他模式中的序列。

DROP SEQUENCE seq_name;

6. 更改（Alter）Sequence

創建序列後，可以更改序列的大多數特性。唯一不能更改的是序列的起始編號和序列的當前值。要更改的話需要刪除並重新創建序列。

ALTER SEQUENCE seq02
INCREMENT BY 20
MAXVALUE 100000
CYCLE;

7. Sequence 原理

Oracle用Sequence來按需提供（幾乎）無限的數字序列，這些序列保證是唯一的。最簡單的方法就是保持一個全局可見的計數器，併在每次查詢sequence_name.nextval的值時遞增。

但Oracle必須跟蹤提供的最後一個數字，以避免兩次提供相同數字的風險——這對資料庫系統來說很容易：只需將最新的數字保存在表中即可。然而，如果每次有人需要下一個號碼時都必須更新一個表格，這流程會非常緩慢。這時應該使用一個自主事務，這樣就不需要等待第一個會話提交其當前事務，然後才能生成下一個數字。因此，Oracle使用遞歸的、自主的事務來處理表更新，並且它通過在記憶體中計數並記住下一次需要更新表的時機來避免每次調用nextval都更新表。

自製”序列的一種常見策略是，在一個表中包含（序列名，最近使用的序列號）的列，然後編寫一個函數，從表中選擇相關行進行更新，增加編號，更新表，並將編號提供給最終用戶，這正是Oracle內在的機制。

您可以通過查詢視圖 user_sequences 來查看序列的資料庫映像。假設我剛剛創建了一個名為s1的序列，圖3.1是一個查詢創建序列後立即給出的結果，和一次調用獲取 s1.nextval 後得到的結果：

select
        min_value, max_value, 
        increment_by, cache_size, last_number, 
        cycle_flag, order_flag
from 
        user_sequences
where 
        sequence_name = 'S1'
/

圖7.1

如果你有DBA許可權，你可以進一步找到序列的 object_id，然後查詢視圖所基於的字典表（seq$）。以下運行了兩次查詢，一次在第一次調用 s1.nextval 之前，另一次是剛剛調用 s1 之後：

select 
        minvalue, maxvalue, increment$, cache, highwater, cycle#, order$ 
from 
        seq$ 
where
        obj# = 124874
;

圖7.2

如果你的賬戶已被授權，可以通過動態性能視圖 v$_sequences 查看記憶體中的信息：

select
        min_value, max_value,
        increment_by, cache_size, highwater,
        cycle_flag, order_flag, nextvalue
from
        v$_sequences
where
        object# = 124874
/

第一次調用s1.nextval之後，我只查詢了一次視圖（在第一次調用nextval時，序列才會出現在視圖中）：

圖7.3

動態性能視圖的列 nextvalue 保存了下次用戶調用 s1.nextval 時將提供的值。

三個視圖中都有一個“highwater”值（在圖7.1視圖user_sequences中稱為“last_number”）。當對s1.nextval 的調用返回 highwater 中顯示的值（highwater和nextvalue相同）時，進行調用的會話將執行並提交遞歸事務，將增量值添加到當前 highwater 並更新 seq$ 表。

“緩存”不是傳統意義上的緩存，它只是一對數字（無論您設置的序列緩存大小有多大）：當前值和提升highwater 後的值。為序列設置大型緩存不會造成資源損失，所以在大多數情況下，您應該儘可能使用大的cache。

引用

  1. Oracle sequences: The basics - Simple Talk
  2. https://www.databasestar.com/oracle-sequence/#What_is_a_Sequence_in_Oracle
  3. Oracle CREATE SEQUENCE Explained By Practical Examples

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Python 既是解釋型語言，也是編譯型語言
    哈嘍大家好，我是鹹魚 不知道有沒有小伙伴跟我一樣，剛開始學習 Python 的時候都聽說過 Python 是一種解釋型語言，因為它在運行的時候會逐行解釋並執行，而 C++ 這種是編譯型語言 不過我今天看到了一篇文章，作者提出 Python 其實也有編譯的過程，解釋器會先編譯再執行 不但如此，作者還認 ...
  • Welcome to YARP - 5.身份驗證和授權
    目錄 Welcome to YARP - 1.認識YARP並搭建反向代理服務 Welcome to YARP - 2.配置功能 2.1 - 配置文件（Configuration Files） 2.2 - 配置提供者（Configuration Providers） 2.3 - 配置過濾器（Confi ...
  • Net 高級調試之六：對象檢查之值類型、引用類型、數組和異常的轉儲
    一：背景 1. 講故事 這段時間分析了幾個和網路故障有關的.NET程式之後，真的越來越體會到電腦基礎課的重要，比如 電腦網路 課，如果沒有對 tcpip協議 的深刻理解，解決這些問題真的很難，因為你只能在高層做黑盒測試，你無法看到 tcp 層面的握手和psh通訊。 這篇我們通過兩個小例子來理解一 ...
  • Linux 搭建FTP服務
    FTP 介紹 FTP是File Transfer Protocol的縮寫，譯為文件傳輸協議，是基於CS結構的應用層協議。其主要作用是在網路上的兩台電腦之間傳輸文件。 FTP傳輸層使用的是TCP協議，它有一個很大的特點是採用雙埠的工作模式，即客戶端和服務端的通信是通過兩個通道進行的 命令通道用於服 ...
  • 如何查看MySQL會話連接方式？
    MySQL總共支持四種連接方式訪問資料庫，如下表格所示，其中Linux平臺環境下，主要有兩種連接方式，一種是TCP/IP連接方式，另外一種是socket方式。Name pipe和Share memory是Windows平臺下獨有的連接方式。 那麼，MySQL資料庫中，我們能否查看會話具體使用的連接方 ...
  • 恭喜我同事的論文被IEEE HPCC收錄！
    近日，由天翼雲科技有限公司雲網產品事業部天璣實驗室撰寫的《關於公有雲區分負載QoS感知的記憶體資源動態超分管理優化》（Thoth：Provisioning Overcommitted Memory Resource with Differentiated QoS in Public Clouds）論文... ...
  • Proxy下的Prepare透傳，讓GaussDB(for MySQL)更穩固，性能更卓越
    本文分享自華為雲社區《Proxy下的Prepare透傳，讓GaussDB(for MySQL)更穩固，性能更卓越》，作者： GaussDB 資料庫 。 1.引言 在很多業務場景下，資料庫應用程式處理大量相同的SQL語句——只需更改SQL語句中的文字或變數值。例如：使用相同的SQL模板進行WHERE查 ...
  • 從 SQL 查詢優化技巧去看 h2 資料庫查詢原理
    本文目標是：瞭解查詢的核心原理，對比 SQL 查詢優化技巧在 h2database 中的落地實現。前提：為了貼近實際應用，本文 Code Insight 基於 BTree 存儲引擎。 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類