همانطور که می دانید در دیتابیس اوراکل OBJECT های مختلفی وجود دارند که یکی از این OBJECT ها SEQUENCE است که در این متن معرفی می شود و روش ساخت و بکارگیری آن را به همراه مثال توضیح می دهیم.

SEQUENCE چیست؟

گاهی اوقات در دیتابیس نیاز به تولید اعداد جدید و ذخیره آن اعداد است. برای مثال می خواهیم برای هر کارمند که استخدام می شود یک عدد به عنوان شماره پرسنلی تولید شود و در دیتایس ذخیره شود. برای این منظور می توان از SEQUENCE استفاده کرد.

هر SEQUENCE یک عدد صحیح تولید می کند و می تواند به صورت اشتراکی توسط چندین کاربر مورد استفاده قرار گیرد. بعضی از ویژگی های SEQUENCE عبارتند از:

1.اعداد تولیدی توسط یک SEQUENCE می تواند صعودی یا نزولی باشد. برای مثال می تواند نزولی باشد و از 1000 شروع شود و در 2000- تمام شود.

2.می تواند اعداد واحد (UNIQUE) تولید کند.

3.می توان مقادیری که توسط SEQUENCE ایجاد می شود را به عنوان PRIMARY KEY یک جدول در نظر گرفت.

4.زمانی که در دیتابیس از یک SEQUENCE استفاده می شود کد نویسی در سطح APPLICATION کاهش می یابد و در زمان صرفه جویی می شود.

5.تولید و ذخیره سازی اعداد توسط SEQUENCE ها مستقل از OBJECT های دیگر است بنابراین می توان از یک SEQUENCE برای چند جدول استفاده نمود.

در شکل زیر سینتکس دستور ساخت SEQUENCE را می بینید.

––استفاده از قسمت هایی که در داخل علامت [ ] نوشته شده اند اختیاری است.

—START WITH n : شماره اولین SEQUENCE که تولید می شود برابر با n خواهد بود. اگر از این عبارت در دستور ساخت استفاده نشود به صورت پیش فرض n برابر با یک است.

—INCREMENT BY n : اختلاف میان هر عدد تولیدی برابر با n خواهد بود. مقدار n به طور پیش فرض برابر با یک است.

—MAXVALUE n یا NOMAXVALUE : اگر از عبارت MAXVALUE n استفاده شود بزرگترین عددی که یک SEQUENCE می تواند تولید کند برابر با n خواهد بود. ولی اگر از عبارت NOMAXVALUE استفاده شود مقدار حداکثر برای SEQUENCE ها درنظر گرفته می شود. به طور پیش فرض اگر از هیچ کدام از این عبارات استفاده نشود NOMAXVALUE در نظر گرفته می شود.

—MINVALUE n یا NOMINVALUE : اگر از عبارت MINVALUE n استفاده شود کوچکترین عددی که یک SEQUENCE می تواند تولید کند برابر با n خواهد بود. ولی اگر از عبارت NOMINVALUE استفاده شود مقدار حداقل برای SEQUENCE ها درنظر گرفته می شود. به طور پیش فرض اگر از هیچ کدام از این عبارات استفاده نشود NOMINVALUE در نظر گرفته می شود.

—CYCLE یا NOCYCLE : اگر از CYCLE استفاده شود زمانی که یک SEQUENCE به آخرین مقدار خود می رسد می تواند مجدد از اولین عدد شروع کند.

—CACHE n یا NOCHACHE : اگر از CACHE n استفاده شود اوراکل به تعداد n عدد را برای SEQUENCE از قبل تولید می کند و در حافظه نگه می دارد. در دستور ایجاد SEQUENCE به صورت پیش فرض از CACHE 20 استفاده می شود.

—ORDER یا NOORDER : با عبارت ORDER تضمین شود که همیشه مقدار اعداد تولیدی به ترتیب درخواست ها است. برای زمانی مناسب است اعداد به عنوان TIMESTAMP استفاده می شوند. به صورت پیش فرض NOORDER است.

بنابراین اگر از دستور زیر استفاده شود یک SECUENCE به نام S1 ساخته می شود که از 1 شروع شده و هربار یک عدد اضافه می شود. مقدار CACHE هم 20 خواهد بود.

CREATE sequence s1;

مثال: می خواهیم شماره هر دپارتمان را با استفاده از یک SEQUENCE تولید کنیم. این شماره از 280 شروع می شود و هربار 10 واحد اضافه می شود. حداکثر شماره دپارتمان نیز می تواند 9999 باشد. این شماره ها به صورت غیرتکراری (NOCYCLE) تولید می شوند. SEQUENCE مورد نظر را بسازید.

نکته: اگر اعداد تولیدی یک SEQUENCE در یک ستون از جدول ذخیره می شوند و می خواهیم آن ستون PRIMARY KEY باشد باید آن SEQUENCE را به صورت NOCYCLE ایجاد کنیم. مگر اینکه مکانیزمی داشته باشیم که سطرهای با شماره قدیمی را پاک میکند.

 

روش استفاده از SEQUENCE

بعد از ساختن یک SEQUENCE می توانیم از شبه ستون های CURRVAL و NEXTVAL استفاده کنیم تا بتوانیم یک عدد جدید توسط آن SEQUENCE تولید کنیم و در جدول ها ذخیره کنیم. CURRVAL و NEXTVAL ستون های SEQUENCE نیستند ولی ویژگی های آنها شبیه ستونی از جدول است. به همین دلیل به آنها شبه ستون می گویند.

زمانی که در یک دستور SQL از شبه ستون NEXTVAL استفاده شود یک عدد جدید برای SEQUENCE تولید می شود که این عدد با استفاده از CURRVAL قابل مشاهده است. برای اینکه بتوانیم مقدار عددی SEQUENCE را با استفاده از CURRVAL مشاهدا کنیم باید در آن SESSION یک عمل NEXTVAL انجام شده باشد وگرنه خطای اوراکل دریافت می کنیم. در ادامه با یک مثال فرایند تولید و نمایش عدد جدید با CRRVAL و NEXTVAL را توضیح می دهیم.

نکته: کاربری که می خواهد برای SEQUENCE کاربر دیگر مقدار تولید کند (NEXTVAL اجرا کند) یا مقدار CURVAL را ببیند باید مجوز SELECT ANY SEQUENCE داشته باشد.

مثال:

ابتدا یک SEQUENCE به نام seq1 برای کاربر milad ایجاد می کنیم.

SQL> create sequence seq1;

Sequence created.

حال اگر بخواهیم مقدار CURRVAL این SEQUENCE را مشاهده کنیم پیغام خطا دریافت می کنیم زیرا در این SESSION هنوز NEXTVAL اجرا نشده است که مقدار SEQUENCE تولید شود.

SQL> select seq1.currval from dual; 

select seq1.currval from dual

             *

ERROR at line 1:

ORA-08002

ORA-08002: sequence SEQ1.CURRVAL is not yet defined in this session

بنابراین ابتدا مقدار NEXTVAL را تولید می کنیم و سپس CURRVAL را مشاهده می کنیم.

SQL> select seq1.nextval from dual;

   NEXTVAL

———-

             1

SQL> select seq1.currval from dual;            

   CURRVAL

———-

             1

در ادامه با همان کاربر milad و در یک SESSION جدید مقدار CURRVAL را درخواست می کنیم ولی از آنجایی که در این SESSION هنوز NEXTVAL انجام نشده است خطا دریافت می کنیم. بنابراین ابتدا یک مقدار NEXTVAL تولید می کنیم.

SQL> select seq1.currval from dual;

select seq1.currval from dual

*

ERROR at line 1:

ORA-08002: sequence SEQ1.CURRVAL is not yet defined in this session

SQL> select seq1.nextval from dual;

   NEXTVAL

———-

             2

SQL> select seq1.currval from dual;

   CURRVAL

———-

             2

حال با کاربر ahmad به دیتابیس متصل می شویم و مقدار currval برای SEQUENCE متعلق به کاربر milad را درخواست می کنیم. ولی خطا دریافت می کنیم. بنابراین ابتدا از nextval استفاده می کنیم.

SQL> select milad.seq1.currval from dual;

select milad.seq1.currval from dual

*

ERROR at line 1:

ORA-08002: sequence milad.SEQ1.CURRVAL is not yet defined in this session

SQL> select milad.seq1.nextval from dual;

   NEXTVAL

———-

             3

QL> select milad.seq1.currval from dual;

   CURRVAL

———-

             3

 

نکته: در بخش های زیر از یک دستور SQL می توان از NEXTVAL و CURRVAL استفاده نمود:

—در لیست SELECT یک دستور SELECT که خودش یک SUBQUERY نیست.

—در لیست SELECT یک SUBQUERY به شرطی که بخشی ازیک دستور INSERT باشد.

—در قسمت VALUES یک دستور INSERT.

—در قسمت SET یک دستور UPDATE.

نکته: در بخش های زیر از یک دستور SQL نمی توان از NEXTVAL و CURRVAL استفاده نمود:

—در لیست یک دستور SELECT که در داخل یک VIEW استفاده شده باشد.

—در داخل یک دستور SELECT که از کلمه کلیدی DISTINCT استفاده کرده است.

—در داخل یک دستور SELECT که از عبارت های GROUP BY، HAVING و ORDER BY استفاده می کند.

—هر SUBQUERY که داخل یک دستور DELETE، UPDATE و یا SELECT اجرا شود.

مثال: یک دپارتمان جدید به جدول DEPARTMENTS اضافه کنید که شماره دپارتمان آن توسط یک SEQUENCE به نام dept_deptid_seq ایجاد می شود.

مثال: مقدار فعلی SEQUENCE مثال قبل را نمایش دهید.

مثال: در ادامه مثال قبل یک کارمند جدیدالورود به جدول EMPLOYEES اضافه کنید. این کارمند در دپارتمان جدید مشغول به کار می شود(برای تولید شماره پرسنلی این کارمند جدیدالورورد از یک SEQUENCE به نام employees_seq استفاده شود).      

INSERT INTO employees (employee_id, department_id, …)

VALUES (employees_seq.NEXTVAL, dept_deptid_seq.CURRVAL, …);

 

استفاده از SEQUENCE به عنوان مقدار DEFAULT

همانطور که می دانید زمانی که در دستور INSERT هیچ مقداری برای یک ستون خاص تعیین نشود، اگر برای آن ستون یک مقدار DEFAULT تعریف شده باشد آن مقدار درج خواهد شد. در اوراکل نگارش 12c و بالاتر می توان از مقدارهای NEXVAL و CURRVAL مربوط به یک SEQUENCE به عنوان یک مقدار DEFAULT برای ستون های جدول استفاده نمود. البته به شرطی که یک مقدار برای آن SEQUENCE تولید شده باشد و مجوز لازم برای دسترسی به SEQUENCE را داشته باشیم.

نکته: اگر یک SEQUENCE که به عنوان مقدار DEFAULT برای یک ستون بکار رفته است DROP شود در زمان درج دستورات DML در جدول هایی که به مقدار DEFAULT از آن SEQUENCE احتیاج دارند خطای اوراکل دریافت می کنیم و آن عمل درج انجام نخواهد شد.

مثال: ستون a1 از جدول emp مقدار DEFAULT خودش را از NEXTVAL مربوط به یک SEQUENCE به نام s1 دریافت کند.

نکته: زمانی که مقدارهای SEQUENCE در حافظه CACHE می شوند سرعت دسترسی افزایش می یابد.

نکته: در یکی از شرایط زیر ممکن است فاصله یا GAP بین اعداد تولید شده از یک SEQUENCE رخ دهد.

—دستوری که برای آن یک SEQUNCE تولید شده است ROLLBACK شود.

—سیستم CRASH کند و عملیات ROLLBACK و RECOVERY انجام شود.

—یک SEQUENCE توسط جدول یا OBJECT دیگر مورد استفاده قرار گرفته باشد.

 

تغییر دادن مقدار یک SEQUENCE

فرض کنید اعداد تولید شده توسط یک SEQUENCE به مقدار تعریف شده برای MAXVALUE رسیده است و SEQUENCE نیز از نوع NOCYCLE است. بنابراین در زمان تولید مقدار جدید برای آن SEQUENCE، خطای اوراکل دریافت می کنیم. می توان با استفاده از دستور ALTER مقدار MAXVALUE برای آن SEQUENCE را تغییر داد.

نکته: برای ALTER کردن یک SEQUENCE باید مجوز لازم را داشته باشیم و یا مالک آن SEQUENCE باشیم.

نکته: بعد از هرگونه ALTER کردن SEQUENCE فقط می توان روند اعداد بعدی که می خواهند تولید شوند را تغییر داد و اعداد قبلی به همان روال خواهند ماند.

نکته: اگر می خواهیم یک SEQUENCE از ابتدا، اعداد جدید تولید کند باید آن SEQUENCE را DROP کنیم و دوباره با شروط جدید ایجاد کنیم. با دستور ALTER مقدار عبارت START WITH قابل تغییر نیست.

 

مثال: SEQUENCE به نام dept_deptid_seq را DROP کنید.

نکته: در زمان ALTER کردن یک SEQUENCE عملیات صحت سنجی آن دستور انجام می شود. برای مثال نمی توان مقدار MAXVALUE را به مقداری که SEQUENCE آن را تولید کرده و از آن رد شده است تغییر داد و برای اجرای این دستور خطای اوراکل دریافت می کنیم.

مثال: در دستور ALTER مقدار MAXVALUE از مقدار فعلی SEQUENCE کمتر است(SEQUENCE نیز از نوع صعودی است) بنابراین خطای اوراکل دریافت می کنیم.

 

مشاهده اطلاعات SEQUENCE در دیتا دیکشنری

اطلاعات هر SEQUENCE که ایجاد شده است در دو VIEW از دیتا دیکشنری قرار می گیرد.

–DBA_OBJECTS

–DBA_SEQUENCES

اطلاعات کامل هر SEQUENCE در DBA_SEQUENCES قرار دارد که ستون های این VIEW را در شکل زیر مشاهده می کنید.

نکته: ستون LAST_NUMBER در DBA_SEQUENCES یکی بیشتر از آخرین شماره تولید شده توسط هر SEQUENCE است. یعنی عددی که با یک NEXTVAL تولید خواهد شد. البته اگر یک SEQUENCE از CACHING استفاده کند عدد مربوط به CACHING به این عدد اضافه می شود.

مثال: اطلاعات SEQUENCE ها را نمایش دهید.