تعریف ستون مجازی در جدول های اوراکل

ژانویه

پس از انتشار اوراکل 11g امکان ایجاد ستون های مجازی در جدول های اوراکل فراهم شده است. این ستون ها مانند هر ستون نرمال دیگری هستند با این تفاوت که بصورت Read Only بوده و توسط DDL ها تغییر نمی کنند. مقدار این ستون ها بر روی دیسک ذخیره نمی شوند بلکه در زمان اجرا با انجام پرس و جو محاسبه می شوند. در اصل این ستون ها بر اساس یک عبارت تعیین مقدار می شوند. توجه کنید که این ستون ها در زمان اجرای پرس و جو محاسبه و مقدار آنها از انجام محاسبه یک عبارت خواهد بود.

شکل کلی تعریف یک Virtual Column به صورت زیر است. یعنی تنها تعیین نام ستون و کلمه AS کافی است و هر آنچه که در براکت های باز و بسته اختیاری است.

[column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL

با استفاده از UPDATE نمی توان مقدار آنها را تغییر داد اما می توانند در بخش WHERE یک دستور UPDATE مورد استفاده قرار گیرند. امکان تعریف شاخص یا Index بر روی آنها وجود دارد و می توان بعنوان منابع آماری از آنها استفاده نمود. فرض کنید جدولی داریم از دو ستون معمولی به نام های inv_id و inc_count که نوع number هستند و مقداری مانند زیر را دارند.

کد زیر یک Select بر روی داده های جدول می زند و یک ارزیابی را بر روی ستون inv_count انجام می دهد و طبق نتیجه حاصله ستون مجازی را ایجاد و مقدار های سطری آنرا ایجاد می کند. که به این صورت است: اگر مقداز سطری از ستون inv_count کوچکتر مساوی از 100 بود عبارت GETTING LOW و اگر بزرگتر از 100 بود عبارت OKAY را در سطر ستون مجازی قرار بده.

,select inv_id, inv_count
‘case when inv_count <= 100 then ‘GETTING LOW
‘when inv_count > 100 then ‘OKAY
end
;from inv

پرس و جوی بالا باعث شد تا یک Virtual Columns در زمان اجرای پرس و جو و بر حسب یک عبارت محاسباتی ایجاد شود و مقدار دهی آن بر حسب نتیجه همان عبارت باشد. اما می توانید در زمان تعریف یک جدول ستونی را بعنوان ستون مجازی تعریف کنید. در شکل زیر جدولی به نام inv ایجاد کرده ایم و ستونی به نام inv_status را با عبارت generated always as بعنوان ستون مجازی تعریف کرده ایم که دقیفا همان محاسبه گفته شده در بالا در انجام می دهد. با این تفاوت که در خروجی select * from inv دیگر بجای نام CASEWHENINV نام INV_STATUS می اید.

در کد شکل بالا عبارت generated always کاملا اختیاری است و حتی اگر آنرا پیش از AS نیاورید مشکلی نخواهد داشت و تنها کمک می کند که بدانیم این یک ستون مجازی است. فرض کنید در شرکتی هستید و باید از جدول ها و هر شی دیگری که ایجاد کرده اید یک مستند ایجاد کنید تا دیگران هم پس از شما بدانند که چه اتفاقی افتاده. عبارت generated always به خوانند گان آن مستند کمک خواهد نمود.

اما چگونه در جدول inv ایجاد شده از کد بالا مقداری را Insert کنیم. جدول سه ستون دارد پس کد زیر را اجرا کنید.

;(insert into inv values(1,55,100

قطعا خطای زیر را خواهید دید :

ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

حالا insert زیر را انجام دهید :

;(‘insert into inv values (1,100,’amirnami

بازهم همان خطای بالا را خواهید دید. بطور کلی اگر datatype ستون مجازی را تعیین نکتید نوع آن بر حسب نتیجه محاسبه عبارت در نظر گرفته خواهد شد. همانطور که در آغاز مطلب دیدید تعیین نوع اختیاری بود. حال کد insert زیر را انجام دهید بطوری که هیچ داده ای را وارد نمی کنیم.

;(insert into inv values (1,100

و با insert بالا خطای زیر را خواهید دید.

ERROR at line 1:
ORA-00947: not enough values

راه حل درج بصورت insert مثال زیر خواهد بود.

;(nsert into inv (inv_id, inv_count) values (1,100

یک select روی جدول بزنید تا نتیجه مشاهده شود.

;select * from inv

با قطعه کد زیر یک ستون به نام inv_comm که مقدار سطری آن نتیجه ضرب 0.1 در ستون inv_count است به جدول inv اضافه یا ADD می کنیم.

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