עוד אחד מהפיצ'רים האלה שאנחנו מיישמים בצורה ידנית עד שבאים אורקל ופותרים לנו את הבעיה בהינף פקודה אחת…

עד גרסה 12, אם היינו רוצים עמודה שתקבל ערכים בצורה עצמאית (לדוגמה מספר רץ למספר הזמנה או משהו דומה) היינו צריכים לבוא וליישם את זה בעצמנו. היינו צריכים להגדיר sequence, היינו צריכים להגדיר trigger שידאג לטפל בטבלה או שהיינו מגדירים קוד חיצוני שיטפל בהכנסת ערכים לעמודה (פונקצית הכנסה לטבלה וכו').

החלק מגרסה 12 נוסף פיצ'ר נחמד שמטפל בכל הדברים האלה בעצמו.

אנחנו מגדירים את הטבלה כמו שהיינו רגילים ועל העמודה שנרצה שתקבל ערך בצורה אוטומטית אנחנו נגדיר שהיא מסוג generated identity. אורקל ידאג ליצור לנו sequence (הוא יקושר לטבלה) ויטפל בהכנסה של הנתון לעמודה כאשר זה נדרש וזאת מבלי לייצור אובייקטים נוספים מעבר לטבלה. ניתן להגדיר רק עמודה אחת כזו לכל טבלה והיא חייבת להיות מספרית כדי שזה יעבוד.

יצירת הטבלה:

CREATE TABLE t1
(id NUMBER GENERATED ALWAYS AS IDENTITY,
 first_name varchar2(30));
 
SQL> insert into t1 (first_name) values ('zohar')
  2  /
 
1 row created.
 
SQL> select * From t1;
 
        ID FIRST_NAME
---------- ------------------------------
         1 zohar

ברירת המחדל היא יצירת עמודה שמתעדכנת אך ורק על ידי שימוש בספרור – זאת אומרת שהכנסת נתון באופן ידני (אפילו null) לא תעבוד:

SQL> insert into t1 values (10, 'zohar');
insert into t1 values (10, 'zohar')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
 
SQL> insert into t1 values (null, 'tamar');
insert into t1 values (null, 'tamar')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
 

לעומת זאת, אם נרצה להכניס לפעמים נתונים משלנו לטבלה נוכל להשתמש בהגדרה הבאה:

CREATE TABLE t2
(id NUMBER GENERATED BY DEFAULT AS IDENTITY,
 first_name varchar2(30));
 
  1* insert into t2 values (10, 'zohar')
SQL> /
 
1 row created.
 
SQL> select * from t2;
 
        ID FIRST_NAME
---------- ------------------------------
        10 zohar
 
SQL> insert into t2 (first_name) values ('zohar')
  2  /
 
1 row created.
 
SQL> select * from t2;
 
        ID FIRST_NAME
---------- ------------------------------
        10 zohar
         1 zohar

בכל מקרה לא נוכל להכניס – null לעמודה מכוון שהעמודות של identity נוצרות not null:

SQL> insert into t2 values (null, 'tamar');
insert into t2 values (null, 'tamar')
                       *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ZOHAR"."T2"."ID")

שימו לב – המשך הספרור בטבלה לא ישתנה, זאת אומרת שאם הוא יגיע לרשומה הבאה שצריכה מספר שכבר קיים, הוא לא ידלג על המספר אלא יצור רשומה חדשה עם אותו מספר. אם העמודה מוגדרת כ-PK, הוא יתן הודעת שגיאה (ORA-00001) וידלג למספר הבא. אם נרצה לדלג מספרים ב-sequence, ניתן לבצע את זה בשליפה מה-sequence ישירות (nextval).

כדי להשתמש בפיצ'ר הזה, למשתמש צריכה להיות הרשאה ליצור sequence מכוון שזה מה שקורה בפועל:

SQL> SELECT object_name, object_type
FROM   user_o  2  bjects;
 
OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- -----------------------
T2                                       TABLE
ISEQ$$_91575                             SEQUENCE
ISEQ$$_91571                             SEQUENCE
T1                                       TABLE

ומכוון שזה sequence, ניתן לשנות את הספרור כדי שיהיה משהו מורכב יתר (כמו לדוגמה יקפוץ ב-10 על פעם או יתחיל מ-100 או שיהיה לו cache יותר גדול וכן הלאה):

CREATE TABLE t3
(id NUMBER GENERATED BY DEFAULT AS IDENTITY
              (START WITH 100 INCREMENT BY 10 cache 100),
 first_name varchar2(30));

לא ניתן לשנות ה-sequence אחר כך בצורה ידנית – אולי אפשר דרך הטבלה, אבל לא ניסיתי..

כדי לראות את הפרטים על עמודות ה-identity:

SQL> r
  1  SELECT table_name,
  2         column_name,
  3         generation_type,
  4         identity_options
  5  FROM   all_tab_identity_cols
  6  WHERE  owner = 'ZOHAR'
  7* ORDER BY 1, 2
 
TABLE_NAME           COLUMN_NAME     GENERATION IDENTITY_OPTIONS
-------------------- --------------- ---------- --------------------------------------------------
T1                   ID              ALWAYS     START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999
                                                999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N
                                                , CACHE_SIZE: 20, ORDER_FLAG: N

T2                   ID              BY DEFAULT START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999
                                                999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N
                                                , CACHE_SIZE: 20, ORDER_FLAG: N
 
T3                   ID              BY DEFAULT START WITH: 100, INCREMENT BY: 10, MAX_VALUE: 9999
                                                999999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG
                                                : N, CACHE_SIZE: 100, ORDER_FLAG: N

להתראות ברשומה הבאה!