Oracle 12c 新特性（四）

第四部分包括：

• 如何在子表包含資料的情況下截斷一個主表
• 限制Top-N查詢結果的記錄
• SQL*Plus的各種增強
• 會話級序列
• WITH語句的改善
• 擴充套件資料型別

截斷表CASCADE

在之前的版本中，在子表引用一個主表以及子表存在記錄的情況下，是不提供截斷此主表操作的。而在12c中的帶有CASCADE操作的TRUNCATE TABLE可以截斷主表中的記錄，並自動對子表進行遞迴截斷，並作為DELETE ON CASCADE服從外來鍵引用。由於這是應用到所有子表的，所以對遞迴層級的數量是沒有CAP的，可以是孫子表或是重孫子表等等。

這一增強擯棄了要在截斷一個主表之前先截斷所有子表記錄的前提。新的CASCADE語句同樣也可以應用到表分割槽和子表分割槽等。

SQL> TRUNCATE TABLE CASCADE; SQL> TRUNCATE TABLE PARTITION CASCADE;

如果對於子表的外來鍵沒有定義ON DELETE CASCADE 選項，便會丟擲一個ORA-14705錯誤。

對Top-N查詢結果限制記錄

在之前的版本中有多種間接手段來對頂部或底部記錄獲取Top-N查詢結果。而在12c中，透過新的FETCH FIRST|NEXT|PERCENT語句簡化了這一過程並使其變得更為直接。為了從EMP表檢索排名前10的工資記錄，可以用以下新的SQL語句：

SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC FETCH FIRST 10 ROWS ONLY;

以下示例獲取排名前N的所有相似的記錄。例如，如果第十行的工資值是5000，並且還有其他員工的工資符合排名前N的標準，那麼它們也同樣會由WITH TIES語句獲取。

SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC FETCH FIRST 10 ROWS ONLY WITH TIES;

以下示例限制從EMP表中獲取排名前10%的記錄：

SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC FETCH FIRST 10 PERCENT ROWS ONLY;

以下示例忽略前5條記錄並會顯示錶的下5條記錄：

SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

所有這些限制同樣可以很好的應用於PL/SQL塊。

BEGIN SELECT sal BULK COLLECT INTO sal_v FROM EMP FETCH FIRST 100 ROWS ONLY; END;

對SQL*Plus的各種增強

SQL*Plus的隱式結果：12c中，在沒有實際繫結某個RefCursor的情況下，SQL*Plus從一個PL/SQL塊的一個隱式遊標返回結果。這一新的dbms_sql.return_result過程將會對PL/SQL 塊中由SELECT 語句查詢所指定的結果加以返回並進行格式化。以下程式碼對此用法進行了描述：

SQL> CREATE PROCEDURE mp1 res1 sys_refcursor; BEGIN open res1 for SELECT eno,ename,sal FROM emp; dbms_sql.return_result(res1); END; SQL> execute mp1;

當此過程得以執行，會在SQL*Plus上返回格式化的記錄。

顯示不可見欄位：在本系列文章的第一部分，我已經對不可見欄位的新特性做了相關闡述。當欄位定義為不可見時，在描述表結構時它們將不會顯示。然而，你可以透過在SQL*Plus提示符下進行以下設定來顯示不可見欄位的相關資訊：

SQL> SET COLINVISIBLE ON|OFF

以上設定僅對DESCRIBE 命令有效。目前它還無法對不可見欄位上的SELECT 語句結果產生效果。

會話級序列

在12c中現在可以建立新的會話級資料庫序列來支援會話級序列值。這些序列的型別在有會話級的全域性臨時表上最為適用。

會話級序列會產生一個獨特範圍的值，這些值是限制在此會話內的，而非超越此會話。一旦會話終止，會話序列的狀態也會消失。以下示例解釋了建立一個會話級序列：

SQL> CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1 SESSION; SQL> ALTER SEQUENCE my_seq GLOBAL|SESSION;

對於會話級序列，CACHE, NOCACHE, ORDER 或 NOORDER 語句會予以忽略。

WITH語句的改善

在12c中，你可以用SQL更快的執行PL/SQL函式或過程，這些是由SQL語句的WITH語句加以定義和宣告的。以下示例演示瞭如何在WITH語句中定義和宣告一個過程或函式：

WITH PROCEDURE|FUNCTION test1 (…) BEGIN END; SELECT FROM table_name; /

儘管你不能在PL/SQL單元直接使用WITH語句，但其可以在PL/SQL單元中透過一個動態SQL加以引用。

擴充套件資料型別

在12c中，與早期版本相比，諸如VARCHAR2, NAVARCHAR2以及 RAW這些資料型別的大小會從4K以及2K位元組擴充套件至32K位元組。只要可能，擴充套件字元的大小會降低對LOB資料型別的使用。為了啟用擴充套件字元大小，你必須將MAX_STRING_SIZE的初始資料庫引數設定為EXTENDED。

要使用擴充套件字元型別需要執行以下過程：

1. 關閉資料庫

2. 以升級模式重啟資料庫

3. 更改引數: ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED;

4. 執行 utl32k.sql as sysdba : SQL> @?/rdbms/admin/utl32k.sql

5. 關閉資料庫

6. 以讀寫模式重啟資料庫

對比LOB資料型別，在ASSM表空間管理中，擴充套件資料型別的欄位以SecureFiles LOB加以儲存，而在非ASSM表空間管理中，它們則是以BasciFiles LOB進行儲存的。

注意：一旦更改，你就不能再將設定改回STANDARD。