在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

Oracle 如何實(shí)現(xiàn)一個(gè)只有一行數(shù)據(jù)的表

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專(zhuān)欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        基于虛擬列的唯一約束
        基于函數(shù)的唯一索引
        通過(guò)觸發(fā)器禁止數(shù)據(jù)刪除
        總結(jié)

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。今天我們來(lái)討論一個(gè)有趣的話(huà)題：如何在 Oracle 中實(shí)現(xiàn)一個(gè)只能存儲(chǔ)一行數(shù)據(jù)的表。

    ??如果你使用的是 MySQL，可以通過(guò)這篇文章了解如何在 MySQL 中實(shí)現(xiàn)一個(gè)只有一行數(shù)據(jù)的表。
    ??如果你使用的是 PostgreSQL，可以通過(guò)這篇文章了解如何在 PostgreSQL 中實(shí)現(xiàn)一個(gè)只有一行數(shù)據(jù)的表

假如我們有一個(gè)表 t_version，用于記錄應(yīng)用系統(tǒng)的版本信息：

create table t_version(version varchar2(100) not null, update_at timestamp not null);



在第一次安裝應(yīng)用程序時(shí)需要生成一條記錄，以后升級(jí)系統(tǒng)時(shí)需要更新版本信息，但不允許用戶(hù)刪除該記錄。這種需求該如何實(shí)現(xiàn)？
基于虛擬列的唯一約束

Oracle 11g 增加了虛擬列（Virtual Column）的支持，也就是基于一個(gè)表達(dá)式的虛擬字段。我們可以為 t_version 表增加一個(gè)虛擬列：

alter table t_version add only_one_row generated always as (1);



虛擬列 only_one_row 的值永遠(yuǎn)只能等于 1，然后我們?cè)倩谠撟侄蝿?chuàng)建一個(gè)唯一索引。例如：

alter table t_version add constraint uk_t_version unique (only_one_row);



以上語(yǔ)句限制了字段 only_one_row 中的值具有唯一性，加上取值只能等于 1，所以表 t_version 中最多只能存在一條記錄。

初始化安裝時(shí)插入數(shù)據(jù)的效果如下：

-- 初始化安裝
insert into t_version(version, update_at) values ('XYZ 系統(tǒng)版本 1.0.0', current_timestamp);

select * from t_version;
VERSION          |UPDATE_AT          |ONLY_ONE_ROW|
-----------------|-------------------|------------|
XYZ 系統(tǒng)版本 1.0.0|2020-07-19 17:02:05|           1|



第一次插入數(shù)據(jù)時(shí)可以使用 INSERT 語(yǔ)句，但是隨后升級(jí)應(yīng)用程序時(shí)繼續(xù)插入數(shù)據(jù)就會(huì)返回錯(cuò)誤：

-- 升級(jí)軟件版本
insert into t_version(version, update_at) values ('XYZ 系統(tǒng)版本 2.0.0', current_timestamp);
ORA-00001: unique constraint (TONY.UK_T_VERSION) violated


第二次插入數(shù)據(jù)時(shí)返回了唯一約束沖突。顯然，系統(tǒng)升級(jí)時(shí)需要使用 UPDATE 語(yǔ)句更新版本信息：

update t_version
set version = 'XYZ 系統(tǒng)版本 2.0.0',
    update_at = current_timestamp;

select * from t_version;
VERSION          |UPDATE_AT          |ONLY_ONE_ROW|
-----------------|-------------------|------------|
XYZ 系統(tǒng)版本 2.0.0|2020-07-19 17:06:42|           1|



但是我們需要判斷表中是否已經(jīng)存在數(shù)據(jù)，然后執(zhí)行不同的語(yǔ)句。為了解決這個(gè)問(wèn)題，可以使用 MERGE 語(yǔ)句，它可以同時(shí)實(shí)現(xiàn)插入和更新操作：

-- 清除數(shù)據(jù)
truncate table t_version;

-- 初始化安裝
merge into t_version dst
using (select 1 from dual) src
on (only_one_row = 1)
when matched then
  update set dst.version = 'XYZ 系統(tǒng)版本 1.0.0',
             dst.update_at = current_timestamp
when not matched then
  insert (dst.version, dst.update_at)
  values ('XYZ 系統(tǒng)版本 1.0.0', current_timestamp);
 
select * from t_version;
VERSION          |UPDATE_AT          |ONLY_ONE_ROW|
-----------------|-------------------|------------|
XYZ 系統(tǒng)版本 1.0.0|2020-07-19 17:17:46|           1|

-- 升級(jí)軟件版本
merge into t_version dst
using (select 1 from dual) src
on (only_one_row = 1)
when matched then
  update set dst.version = 'XYZ 系統(tǒng)版本 2.0.0',
             dst.update_at = current_timestamp
when not matched then
  insert (dst.version, dst.update_at)
  values ('XYZ 系統(tǒng)版本 2.0.0', current_timestamp);

select * from t_version;
VERSION          |UPDATE_AT          |ONLY_ONE_ROW|
-----------------|-------------------|------------|
XYZ 系統(tǒng)版本 2.0.0|2020-07-19 17:18:35|           1|


MERGE 語(yǔ)句使用 only_one_row = 1 作為數(shù)據(jù)沖突的判斷條件，如果已經(jīng)存在則更新數(shù)據(jù)，如果不存在則插入數(shù)據(jù)。
基于函數(shù)的唯一索引

除了上面的方法之外，我們還可以利用 Oracle 函數(shù)索引（Function-Based Index）直接基于常量值創(chuàng)建一個(gè)唯一的索引。例如：

-- 刪除并重建 t_version 表
drop table t_version;
create table t_version(version varchar2(100) not null, update_at timestamp not null);

-- 創(chuàng)建一個(gè)唯一的函數(shù)索引
create unique index uk_t_version on t_version ( (1) );


索引 uk_t_version 是一個(gè)基于常量表達(dá)式 (1) 的函數(shù)索引，并且具有唯一性。也就是說(shuō)，表中任何數(shù)據(jù)行對(duì)應(yīng)的索引值都是 1，而唯一索引只允許一個(gè) 1，因此該表中最多只能存儲(chǔ)一行數(shù)據(jù)。函數(shù)索引和虛擬列索引類(lèi)似，但是不需要?jiǎng)?chuàng)建額外的字段。

接下來(lái)的操作和上一節(jié)類(lèi)似，但是需要將 MERGE 語(yǔ)句修改如下：

merge into t_version dst
using (select 1 from dual) src
on (1 = 1)
when matched then
  update set dst.version = 'XYZ 系統(tǒng)版本 1.0.0',
             dst.update_at = current_timestamp
when not matched then
  insert (dst.version, dst.update_at)
  values ('XYZ 系統(tǒng)版本 1.0.0', current_timestamp);



由于不存在字段 only_one_row，我們使用 1=1 作為數(shù)據(jù)沖突的判斷條件。
通過(guò)觸發(fā)器禁止數(shù)據(jù)刪除

最后一個(gè)問(wèn)題就是需要避免版本信息被誤刪除，這個(gè)可以通過(guò)觸發(fā)器來(lái)實(shí)現(xiàn)。

首先為 t_version 表創(chuàng)建一個(gè)刪除觸發(fā)器：

create or replace trigger tri_disable_version_del
    before delete on t_version
begin
    raise_application_error(-20000, '禁止刪除版本信息！');
end;



該觸發(fā)器是一個(gè)語(yǔ)句級(jí) BEFORE 觸發(fā)器，在任何刪除語(yǔ)句之前返回錯(cuò)誤信息。

我們執(zhí)行以下語(yǔ)句刪除版本信息：

delete
from t_version;
ORA-20000: 禁止刪除版本信息！
ORA-06512: at "TONY.TRI_DISABLE_VERSION_DEL", line 2
ORA-04088: error during execution of trigger 'TONY.TRI_DISABLE_VERSION_DEL'

select * from t_version;
VERSION          |UPDATE_AT          |ONLY_ONE_ROW|
-----------------|-------------------|------------|
XYZ 系統(tǒng)版本 2.0.0|2020-07-19 17:30:16|           1|



以上刪除語(yǔ)句返回了錯(cuò)誤信息，t_version 中的數(shù)據(jù)仍然存在。不過(guò)需要注意的是，TRUNCATE TABLE 語(yǔ)句仍然可以清除表中的數(shù)據(jù)，因?yàn)樗粫?huì)觸發(fā) DML 觸發(fā)器。
總結(jié)

本文介紹了在 Oracle 中利用虛擬列加唯一約束，以及函數(shù)索引兩種方式實(shí)現(xiàn)一個(gè)只能存儲(chǔ)一行數(shù)據(jù)的表，同時(shí)介紹了通過(guò)觸發(fā)器禁止數(shù)據(jù)刪除的方法。

除了本文介紹的方法之外，你還有沒(méi)有其他的實(shí)現(xiàn)方法？歡迎關(guān)注??、評(píng)論??、點(diǎn)贊??