در این فصل انواع دستورات DML(Data Manipulation Language) توضیح داده می شوند. دستورهای INSERT، DELETE، UPDATE از نوع DML هستند که باعث می شوند اطلاعات جدول های دیتابیس تغییر یابند. همچنین در ادامه، روش کنترل تراکنش ها بر اساس مفهوم READ CONSISTENCY و عملیات COMMIT، ROLLBACK و SAVEPOINT توضیح داده می شوند.

زمانی که یک دستور از نوع DML اجرا می شود یکی از حالت های زیر رخ می دهد:

1.اطلاعات جدید به یک جدول اضافه می شوند(توسط دستور INSERT).

2.اطلاعات قبلی تغییر می کند(توسط دستور UPDATE).

3.اطلاعات قبلی حذف می شوند(توسط دستور DELETE).

در دیتابیس اوراکل به مجموعه ای از دستورات DML که یک عمل خاصی را انجام می دهند یک تراکنش(TRANSACTION) می گویند.

 

دستور INSERT

سینتکس دستور INSERT را در شکل زیر می بینید. در این دستور کلمه TABLE نام یک جدول از دیتابیس می باشد و کلمات COLUMN نام ستون یا ستون هایی از آن جدول هستند که در لیست VALUES برای هر کدام یک مقدار خاص درنظر گرفته می شود تا در جدول مورد نظر درج شود.

نکته: در دستور INSERT برای مقادیری از نوع تاریخ یا رشته، از علامت ” استفاده می شود.

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

نکته: اگر سطری که توسط دستور INSERT به یک جدول اضافه می کنیم، شامل اطلاعات برای تمام ستون های آن جدول است (و ترتیب ستون های جدول رعایت شده باشد) دیگر نیاز نیست در آن دستور از نام ستون ها استفاده کنیم.

مثال:  جدول DEPARTMENTS دارای چهار ستون است که در آنها توسط دستور زیر اطلاعات با نوع داده مناسب درج می شوند ولی نام ستون ها استفاده نشده است.

نکته: اگر در دستور INSERT نام و مقدار یکی از ستون های جدول تعریف نشود، برای آن ستون مقدار NULL درج می شود. همچنین می توان در ستون های جدول به روش صریح و با استفاده از لیست VALUES یک مقدار NULL درج کرد.

مثال: جدول DEPARTMENTS چهار ستون دارد که توسط هر دو دستور در ستون سوم و چهارم مقدار NULL  درج می شود.

نکته: می توان در دستور INSERT و در لیست VALUES از توابع مختلف استفاده نمود.

مثال: با استفاده از تابع SYSDATE تاریخ و زمان فعلی سیستم در ستون HIRE_DATE درج می شود.

مثال: اطلاعات کارمند جدید در جدول EMPLOYEES درج شود. از تابع تبدیل نوع داده TO_DATE استفاده می شود و تاریخ با فرمت مناسب ذخیره می شود.

 

کُپی اطلاعات با استفاده از دستور INSERT

اگر  سه شرط زیر رعایت شوند می توان با استفاده از دستور INSERT اطلاعات یک جدول را در جدول دیگر کُپی کرد:

1.در داخل دستور INSERT از یک SUBQUERY استفاده شود.

2.نوع داده و تعداد ستون های دستور INSERT با دستور SUBQUERY برابر باشد.

3.در دستور INSERT از لیست VALUES استفاده نشود.

مثال: تمام سطرهایی که توسط دستور SUBQUERY انتخاب می شوند در جدول SALES_REPS درج شود.

مثال: یک کُپی از جدول EMPLOYEES در جدول COPY_EMP ذخیره شود (در دیتابیس جدول COPY_EMP با ساختار یکسان با جدول EMPLOYEES وجود دارد).

استفاده از متغیرهای تعویضی در دستور INSERT

در زمان درج اطلاعات توسط دستور INSERT می توان از متغیرهای تعویضی استفاده نمود. ابتدا متغیرهای تعویضی را با ذکر چند مثال توضیح می دهیم.

متغیرهای تعویضی در قسمت های مختلف یک دستور SQL از جمله نام جداول، ستون ها و یا عبارات ORDER BY و شروط WHERE استفاده می شوند. با استفاده از این متغیرها می توانیم مقادیر یا نام ها را از کاربر بپرسیم و یا در بیرون از دستور تعریف کنیم. تا زمانی که این متغیرها تعیین نشود دستور اجرا نمی شود. برای تعریف متغیر تعویضی باید از علامت &  استفاده کنیم.

مثال: در این مثال ابتدا مقدار متغیر EMPLOYEE_NUM از کاربر پرسیده می شود و سپس بر اساس مقدار دستور SELECT اجرا می شود.

نکته: مقادیر رشته و تاریخ باید مابین علامت ‘ ‘ قرار بگیرند.

مثال: استفاده از چندین متغیر در یک دستور SELECT:

نکته: برای انکه فقط یکبار مقدار یک متغیر از کاربر پرسیده شود و در جاهای دیگر که آن متغیر قرار دارد از همان مقدار استفاده شود باید در زمان نوشتن متغیر برای مرتبه اول از علامت && استفاده گردد.

مثال: مقدار متغیر COLUMN_NAME را یک مرتبه از کاربر بپرسید و دستور زیر را اجرا کنید.

نکته: در محیط SQL*PLUS نیز می توان از متغیرهای تعویضی استفاده نمود.

مثال:

نکته: می توان بجای اینکه مقدار متغیر را از کاربر پرسید، از دستور DEFINE قبل از دستور SELECT استفاده نمود.

مثال: مقدار متغیر EMPLOYEE_NUM را برابر با 200 تنظیم کنید و دستور زیر را اجرا کنید.

مثال: اطلاعات مورد نیاز دپارتمان جدید از کاربر دریافت شود و در جدول DEPARTMENT درج گردد.

 

دستور UPDATE

یکی دیگر از دستورات DML دستور UPDATE است که از آن برای تغییر مقدار اطلاعات جداول استفاده می شود. در شکل زیر سینتکس مربوط به این دستور را می بینید.

نکته: اگر در این دستور از عبارت WHERE استفاده شود عملیات UPDATE فقط برای یک یا چند سطر خاص انجام می شود و در غیر این صورت تمام سطرهای جدول تغییر می کنند.

مثال: مقدار ستون DEPARTMENT_ID برای سطرهایی که مقدار EMPLOYEE_ID آنها برابر با 113 است به مقدار 50 تغییر یابد.

مثال: مقدار ستون DEPARTMENT_ID در تمام سطرها برابر با 110 شود.

مثال: مقدار حق کمیسیون کارمند شماره 114 برابر با مقدار NULL شود.

نکته: در دستور UPDATE می توان از دستور SUBQUERY استفاده نمود.

مثال:  شغل و حقوق کارمند شماره 103 برابر با شغل و حقوق کارمند شماره 205 شود.

مثال: شماره دپارتمان تمام کارمندانی که شغل آنها با شغل کارمند شماره 200 برابر است به شماره دپارتمان کارمند شماره 100 تغییر یابد.

 

دستور DELETE

دستور DELETE یک دستور از نوع DML است که یک یا چند سطر از جدول را حذف می کند. سینتکس این دستور را در شکل زیر می بینید.

در دستور DELETE استفاده از کلمه FROM اختیاری است. همچنین اگر از عبارت WHERE استفاده شود فقط سطرهایی از جدول که مطابق با شرط WHERE هستند حذف می شوند و در غیر این صورت تمام سطرهای جدول حذف خواهند شد.

مثال: سطرهایی از جدول که در آن نام دپارتمان برابر با Finance است را حذف کنید.

مثال: تمام سطرهای جدول COPY_EMP را حذف کنید.

نکته: در دستور DELETE می توان از SUBQUERY استفاده نمود.

مثال: سطرهایی از جدول EMPLOYEES که نام دپارتمان آنها شبیه به Public می باشد را حذف کنید.

نکته: دستور TRUNCATE یک دستور از نوع DDL(DATA DEFINITION LANGUAGE) می باشد که شبیه به دستور DELETE عمل می کند. این دستور تمام سطرهای یک جدول را پاک می کند و فقط ساختار جدول را باقی می گذارد. در شکل زیر سینتکس این دستور را می بینید.

مثال: تمام اطلاعات جدول COPY_EMP حذف شود.

 

کنترل تراکنش های دیتابیس

هر ارتباطی که یک کاربر با دیتابیس برقرار می کند SESSION نامیده می شود. در دیتابیس اوراکل هر تراکنش در یک SESSION خاص و به وسیله مجموعه ای از دستورات از نوع DML یا DDL رخ می دهد.

اگر در یک تراکنش از دستورات DML مانند INSERT، DELETE، UPDATE استفاده شود باید جهت دائمی کردن تغییراتی که این دستورات ایجاد کرده اند از دستور COMMIT استفاده شود.

همچنین اگر در یک SESSION بعد از دستورات DML یک دستور از نوع DDL استفاده شود دیتابیس اوراکل به صورت اتوماتیک یک عمل COMMIT انجام می دهد و دیگر نیازی به اجرای دستور COMMIT نیست.

مثال: یک سطر از جدول EMPLOYEES حذف شود و یک سطر در جدول DEPARTMENTS درج گردد. سپس این تغییرات به صورت دائمی در دیتابیس ذخیره شوند

اگر بعد از دستورات DML بجای دستور COMMIT از دستور ROLLBACK  استفاده شود تمام تغییراتی که توسط آن تراکنش در حافظه انجام گرفته است لغو می شود و اطلاعات به مقادیر اولیه باز می گردند.  همچنین اگر قبل از COMMIT به صورت ناخواسته ارتباط با دیتابیس قطع شود به صورت اتوماتیک یک عمل ROLLBACK انجام می شود.

مثال: به اشتباه تمام سطرهای جدول COPY_EMP حذف می شوند ولی در ادامه با دستور ROLLBACK تمام سطرها برگرداننده می شوند.

نکته: اگر قبل از عمل COMMIT دستورات DML، داده های یک جدول تغییر یابند این تغییرات موقتی در همان SESSION با دستور SELECT نمایش می یابند.

مثال: به اشتباه تمام سطرهای جدول TEST حذف می شوند ولی دستور ROLLBACK تغییرات انجام شده را لغو می کند. همچنین در ادامه یک سطر با ID برابر با 100 حذف می شود و عمل COMMIT انجام می شود تا این تغییرات در دیتابیس ذخیره شوند. توجه شود که قبل از عمل COMMIT و در همان SESSION دستور SELECT تغییر انجام شده را نمایش می دهد.

زمانی که در یک SESSION توسط دستورات DML سطرهایی از یک جدول تغییر می کنند این سطرها LOCK می شوند بنابراین SESSIONهای دیگر نمی توانند همزمان آنها را تغییر دهند تا زمانی که یک عمل COMMIT یا ROLLBACK در آن SESSION رخ دهد.

 وقتی یک تراکنش در حال اجرا می باشد اگر بعد از یک دستور DML از دستورNAME1  SAVEPOINT استفاده شود و سپس مراحل بعدی تراکنش انجام شود می توان با دستور ROLLBACK TO SAVEPOINT NAME1 به زمان اجرای آن SAVEPOINT بازگشت. البته به شرطی که عمل COMMIT انجام نشده باشد زیرا بعد از هر COMMIT تمامی SAVEPOINT های قبلی حذف می شوند.

مثال: در تراکنش زیر یک SAVEPOINT به نام A بعد از دستور DELETE و یکی دیگر به نام B بعد از دستورات UPDATE و INSERT انجام می شود. بنابراین می توان به هرکدام از آنها ROLLBACK کرد.

 

اصل READ CONSISTENCY

عملیاتی که توسط کاربران یک دیتابیس انجام می شود یا به صورت READ و با دستور SELECT است یا به صورت WRITE و با دستورات INSERT،   UPDATE، DELETE. در تمام این عملیات باید مکانیزمی باشد که هر کاربر مستقل از دیگران به داده های قبل از آخرین COMMIT در دیتابیس دسترسی داشته باشد

دیتابیس اوراکل بر اساس اصل READ CONSISTENCY تضمین می کند یک کاربر در هر SESSION آخرین اطلاعات COMMIT شده را می بیند زیرا تا زمانی که COMMIT انجام نشود SESSION های دیگر متوجه تغییرات نخواهند شد. بنابراین اگر دو SESSION به منظور نوشتن اطلاعات در سطرهای یکسان اقدام کنند باید منتظر هم بمانند ولی در حالت های دیگر هر SESSION به اطلاعات مورد نیاز خود دسترسی دارد.

نکته: یک کاربر می تواند SESSIONهای مختلف داشته باشد و در سطح SESSION مفهوم READ CONSISTENCY پابرجاست.

نکته: زمانی که در یک SESSION با دستورات DML سطرهای یک جدول تغییر می کند قبل از عمل COMMIT اطلاعات قبلی در بخشی از دیسک به نام UNDO SEGMENT قرار دارد و SESSION های دیگر از طریق این بخش به اطلاعات قبلی دسترسی دارند تا زمانی که عمل COMMIT انجام شود.

 

عبارت FOR UPDATE در دستور SELECT

اگر یک کاربر به نام ALI، در یک دستور SELECT از عبارت FOR UPDATE استفاده کند سطرهایی که توسط دستور SELECT انتخاب شده اند LOCK می شوند و SESSIONهای دیگر نمی توانند آن سطرها را تغییر دهند تا زمانی که یک عمل COMMIT یا ROLLBACK توسط کاربر ALI اجرا شود. البته اگر آن سطرها از قبل توسط SESSIONهای دیگر LOCK شده باشند  دیتابیس منتظر UNLOCK شدن آنها می ماند و سپس دستور SELECT FOR UPDATE را برای کاربر ALI اجرا می کند.

نکته: می توان در دستور SELECT FOR UPDATE از عبارت NOWAIT استفاده کرد تا اگر سطرهایی که می خواهیم LOCK کنیم از فبل LOCK بودند دیتابیس کنترل عملیات را به SESSION برگرداند و در پشت صحنه منتظر UNLOCK شدن آن سطرها بماند.

مثال: سطرهایی از جدول EMPLOYEES که JOB_ID برابر با SA_REP دارند راLOCK  کنید.

مثال: برخی از سطرهای دو جدول EMPLOYEES و DEPARTMENTS را LOCK کنید.

نکته: اگر از عبارت  FOR UPDATE OF COLUMN_NAMEاستفاده شود فقط سطرهایی که شامل ستون COLUMN_NAME هستند LOCK می شوند.

مثال: فقط سطرهایی از جدول EMPLOYEES که مطابق با شرط های دستور هستند LOCK شوند (ستون salary در جدول EMPLOYEES می باشد).

نکته: اگر از عبارت FOR UPDATE WAIT X  استفاده شود دیتابیس X ثانیه صبر می کند تا شاید سطرهای مورد نظر UNLOCK  شوند و سپس کنترل را به SESSION بازمی گرداند

مثال: ابتدا 5 ثانیه منتظر UNLOCK شدن سطرها بمانید.