دستورات DDL یا زبان تعریف داده ها
DDL سر نام Data Definition Language می باشد که امکان تعریف یا توصیف اشیای پایگاه داده را به شما می دهد. ساختار record ها، تعریف field ها، محل فایل ها و شیوه ذخیره سازی داده ها در پایگاه اطلاعات به واسطه ی دستورات DDL صورت می پذیرد. دستورات این زبان به شما اجازه ی اجرای عملیات زیر را می دهد.
- ایجاد، اصلاح و حذف اشیا پایگاه داده (schema objects)
- دادن یا لغو و ابطال مجوزها (privilege) و نقش های کاربری (role)
- تحلیل اطلاعات موجود در جدول، اندیس یا گروه (cluster)
- ایجاد گزینه هایی برای حسابرسی
- افزودن توضیحات (comment) به دیکشنری داده ها (data dictionary)
دستورهای CREATE، ALTER و DROP نیازمند دسترسی انحصاری و exclusive به شی مشخص شده می باشند. به عنوان مثال چنانچه کاربر دیگری در حال اجرای تراکنش در جدول مورد نظر باشد، دستور ALTER TABLE عملیات لازم را انجام نخواهد داد.
دستورات GRANT،REVOKE ،ANALYZE ، AUDIT و COMMENT نیازی به دسترسی انحصاری به شی مشخص شده ندارند. برای مثال، شما می توانید هنگامی که کاربر دیگری در حال اجرای عملیات بروز رسانی بر روی جدول مورد نظر است، آن جدول را تجزیه و تحلیل کنید.
Oracle تراکنش جاری را پیش و پس از اجرای هر دستور DDL به طور صریح اجرا می کند.
غالب دستورهای DDL باعث می شوند، Oracle اشیا (schema object) خود را دوباره کامپایل یا اعتبار دهی کند.
دستورات DDL عبارتند از
- CREATE : به منظور ساخت اشیایی همچون CREATE TABLE، CREATE FUNCTION، CREATE SYNONYM ، CREATE VIEWبکار می رود.
- ALTER : به منظور اصلاح اشیایی همچون ALTER TABLE، ALTER USER ، ALTER TABLESPACE، ALTER DATABASE بکار می رود.
- DROP : این دستور جهت حذف اشیایی همچون DROP TABLE، DROP USER،DROP TABLESPACE، DROP FUNCTION استفاده می شود.
- از این دستور برای نام گذاری مجدد جداول (اصلاح اسم جداول) استفاده می کنیم.
- TRUNCATE : به منظور حذف تمامی سطرهای جدول مورد استفاده قرار می گیرد.
دستور CREATE
به منظور ایجاد یک پایگاه داده، جدول، اندیس و رویه ی ذخیره شده (stored procedure) جدید بکار می ورد.
یک دستور Create در زبان SQL، در واقع یک شی جدید پایگاه داده ی رابطه ای ایجاد می کند. انواع اشیایی که می تواند توسط این دستور ایجاد شود، عبارتند از: جدول ها، نمایه ها (views)، دامنه ها (domain)، مجموعه کاراکترها (character set) و غیره ... .
مثال:
به منظور ایجاد یک جدول جدید، دستور زیر را می نویسیم.
1 2 3 4 5 6 7 | create table emp (empno number(5) primary key, name varchar2(20), sal number(10,2), job varchar2(20), mgr number(5), Hiredate date, comm number(10,2));<button></button> |
حال با داشتن جدول emp می خواهیم یک جدول جدید به نام TAX ایجاد کنیم که داری ساختار زیر بوده و نیز سطر دربردارنده ی آن دسته از کارکنانی که درآمد آن ها بالغ بر 5000 را داخل آن درج کنیم.
برای این منظور ابتدا جدول TAX را با تعریف اسم ستون ها و نوع داده های آن ایجاد کرده، سپس برای وارد کردن سطر جدید از جدول emp، دستور INSERT into EMP SELECT …. را بکار می بریم.
1 2 3 4 5 | create table tax (empno number(5), tax number(10,2)); insert into tax select empno,(sal-5000)*0.40 from emp where sal > 5000;<button></button> |
بجای اجرای دستورات بالا، می توان همین نتیجه را با نوشتن دستور CREATE TABLE AS بدست آورد.
1 2 3 | create table tax as select empno,(sal-5000)*0.4 as tax from emp where sal>5000<button></button> |
همچنین می توان با استفاده از دستور CREATE TABLE AS، رونوشت یا کپی هایی از جداول مورد نظر گرفت. برای ایجاد یک کپی (به نام EMP2 ) از جدول EMP، کافی است دستور زیر را بکار ببرید.
1 | create table emp2 as select * from emp;<button></button> |
حال برای کپی کردن جدول های مورد نظر بدون لحاظ کردن سطرهای آن ها (فقط ساختار جدول و نه محتویات آن)، دستور زیر را بکار می بریم.
1 | create table emp2 as select * from emp where 1=2;<button></button> |
امکان ایجاد جداول موقتی
در ویرایش 8i، Oracle قابلیت نوینی تحت عنوان ایجاد جداول موقتی را ارائه نمود. تعریف (definition) جدول موقتی برای تمامی session ها قابل مشاهده می باشد، اما داده های موجود در آن تنها برای session ای که داده ها را داخل جدول درج می کند، قابل رویت می باشد. برای ایجاد جدول موقتی دستور CREATE GLOBAL TEMPORARY TABLE را بکار می بریم. کلیدواژه ی ON COMMIT نشانگر این است که آیا داده های موجود در جدول منحصر به تراکنش (حالت پیش فرض) هستند یا مختص به session.
- ON COMMIT DELETE ROWS نشانگر این است که جدول موقتی مختص به تراکنش (transaction-specefic) می باشد. در این حالت oracle تمامی سطرهای جدول را پس از هر بار اجرای کامل (commit) حذف می کند(truncate).
- ON COMMIT PRESERVE ROWS بیانگر این است که جدول موقتی منحصر به session (session specific) است. در این حالت oracle تنها زمانی سطرهای جدول را حذف می کند که session کاملا خاتمه داده شده باشد.
این مثال یک جدول موقتی ایجاد می کند که منحصر به تراکنش/transaction-specific می باشد.
1 2 3 4 5 6 7 8 9 10 11 | CREATE GLOBAL TEMPORARY TABLE taxable_emp (empno number(5), ename varchar2(20), sal number(10,2), tax number(10,2)) ON COMMIT DELETE ROWS;<button></button> |
همچنین می توان اندیس در جداول موقتی ایجاد کرد. اندیس های ایجاد شده در این جداول نیز همانند خود جدول موقتی بوده و دامنه ی session یا transaction داده های موجود در آن با دامنه ی session/transaction داده های درون جدول زیرین (underlying table) یکسان می باشد.
دستور ALTER
از دستور ALTER TABLE برای اصلاح یا اعمال تغییرات به ساختار کلی جدول استفاده می کنیم.
مثال:
به منظور افزودن ستون های جدید به نام های addr، city، pin، ph و fax به جدول employee، کافی است دستور زیر را بکار ببرید:
1 2 3 4 5 | alter table emp add (addr varchar2(20), city varchar2(20), pin varchar2(10),ph varchar2(20)); <button></button> |
اصلاح نوع داده، طول و عرض یک ستون
می خواهیم طول ستون ename را از varchar2(20) به varchar2(30) تغییر دهیم. برای این منظور دستور زیر را می نویسیم.
1 | alter table emp modify (ename varchar2(30))<button></button> |
عرض یک ستون را می توان به اندازه ی بزرگترین رشته یا مقداری که جدول در خود نگه می دارد، کاهش داد.
1 | alter table emp modify (ename varchar2(15));<button></button> |
انجام عملیات بالا تنها در نسخه ی 8i یا بالاتر پایگاه داده ی oracle امکان پذیر می باشد. در ویرایش های 8.0 و 7.3 امکان کاهش عرض ستون وجود ندارد و تنها به یک شرط می توان عرض ستون را کاهش داد و آن هم زمانی است که ستون مورد نظر تهی باشد.
برای اینکه امکان تغییر نوع داده فراهم آید، ستون مورد نظر باید (در تمامی نسخه های oracle) تهی باشد.
حذف ستون ها به وسیله ی دستور drop
از ویرایش 8i به بعد oracle یک قابلیت نوین فراهم شد و آن حذف ستون های یک جدول به طور مستقیم بود. این امکان در نسخه های پیشین این پایگاه داده در دسترس نبود.
برای حذف ستون های PIN و CITY از جدول emp، دستور زیر را می نویسیم.
1 | alter table emp drop column (pin, city); <button></button> |
توجه داشته که اگر جدول دارای تنها یک ستون بود دیگر حذف ستون امکان پذیر نمی باشد.
چنانچه ستونی که شما مایل به حذف آن هستید دارای primary key constraintمی باشد، باید از عبارت cascade استفاده کنید.
1 | alter table emp2 drop column (empno) cascade constraints;<button></button> |
به منظور حذف ستون های دلخواه در نسخه های 8.0 و 7.0 پایگاه داده ی oracle و اصلاح اسم ستون ها در کلیه ی ویرایش های آن، باید به ترتیب زیر عمل کنید.
می خواهیم ستون ها pin و city را از حذف کرده و اسم ستون SAL را به SALARY تغییر دهیم.
مرحله ی اول: با استفاده از subquery یک جدول موقتی به همراه ستون های دلخواه خود ایجاد کنید.
1 2 3 | create table temp as select empno, ename, sal AS salary, addr, ph from emp;<button></button> |
مرحله دوم: جدول اصلی را حذف کنید.
1 | drop table emp;<button></button> |
مرحله ی سوم: پس از حذف جدول اصلی، اسم جدول موقتی را به اسم جدول حذف شده تغییر دهید.
1 | rename temp to emp;<button></button> |
دستور RENAME
دستور RENAME برای اصلاح اسم جدول بکار می رود.
چند نکته که باید درنظر بگیرید.
- Oracle محدودیت های جامعیتی (integrity constraint)، اندیس ها را به صورت خودکار از شی قبلی به شی جدید انتقال می دهد.
- Oracle تمامی اشیایی که وابسته به شی rename شده (شی ای که مجددا نام گذاری شده) هستند، از جمله نمایه ها (view)، synonym ها، رویه های ذخیره شده (stored procedure) و نیز توابعی که به جدول rename شده اشاره می کنند را فاقد اعتبار (invalidate) می کند.
برای تغییر اسم جدولی از emp2 به employee2، می توان دستور زیر را بکار برد.
1 | rename emp2 to employee2<button></button> |
دستور DROP
از این دستور برای حذف جداول، توابع، رویه ها (procedure)، پکیج ها، نمایه ها (view) و غیره .. استفاده می شود.
به مثال زیر توجه کنید.
دستور زیر جدول emp2 را حذف می کند.
1 | drop table emp2;<button></button> |
چنانچه جدول emp2 دارای محدودیت primary key می باشد که جداول دیگر به آن اشاره می کنند (ارجاع می دهند)، لازم است اول محدودیت جامعیتی ارجاعی (referential integrity constraint) را حذف کرده و بعد جدول مد نظر را حذف کنید و یا جدول مورد نظر را با حذف محدودیت های ارجاعی (referential constraint) آن پاک کنید که برای این منظور دستور زیر را می نویسیم.
1 | drop table emp2 cascade constraints;<button></button> |
دستور Truncate
دستور نام برده تمامی سطرهای جدول مورد نظر را به طور دائمی حذف می کند. Truncate بسیار شبیه یا به عبارتی معادل دستور “DELETE FROM ” می باشد، اما تفاوتی هایی هم دارد که به شرح زیر می باشد.
- به این خاطر که truncate هیچ داده ی rollback ای تولید نمی کند، تغییرات اعمال شده به هیچ وجه قابل بازگشت نمی باشند (رکوردهای حذف شده قابلیت بازگشت نخواهند داشت).
- اگر delete trigger ای هم در جدول تعریف شده باشد، (trigger) اجرا نخواهد شد. Trigger مربوط به دستور Delete به هیچ عنوان هنگام اجرای دستور Truncate Table فعال نمی‌شود. در صورتیکه با اجرای دستور Delete تریگر آن نیز فعال خواهد شد.
- دستور Truncate Table باعث می‌شود که Page های متعلق به جدول deallocate شوند.deallocate شدن Page ها بدین معنا است که رکوردهای موجود در جدول واقعاً حذف نشوند بلکه Extent های مربوط به آن Page ها علامت Empty خورده تا دفعات بعد مورد استفاده قرار گیرند اما دستور Delete به طور فیزیکی محتوای Page ها مربوط به جدول را خالی می‌کند.
1 | truncate table emp;<button></button> |
اگر هم به فضای خالی درجدول خود نیاز ندارید، می تواند از عبارت REUSE storage در دستور خود بدین ترتیب استفاده کنید.
1 | truncate table emp reuse storage;<button></button> |