مدیریت views
view در sql در واقع همان جداول مجازی هستند که توسط آن می توانید عملیات خاصی که شامل دستورات sql می شوند را انجام دهید و به صورت خروجی داشته باشید.
از مزیت های view این است که بعد از یک بار اجرا به صورت موقت در سرور ذخیره شده و برای مراجعات بعدی از همان استفاده می شود و سرعت خیلی بالایی دارد.
از view در پایگاه داده به عنوان، جدول های منطقی نیز یاد می شود.
View ها نمایشگر یک یا چند جدول دیگر است.
یک view داده های خود را از جداولی که مبتنی بر آن هاست برمی گیرد. این نوع جداول تحت عنوان جداول پایه نیز شناخته می شوند.
هر عملیات DML ای که روی یک جدول انجام می دهید، بی شک روی جدول پایه تاثیر می گذارد. با view همان طوری برخورد می کنیم که با هر جدول دیگری، بدین معنا که می توان روی view همان عملیات بروز رسانی، درج و حذف رکورد از view همانندهر جدول دیگری را داشت.
View ها از انجایی که همان کارایی جداول معمولی را دارند ولی فضایی که این جدول ها به خود تخصیص می دهند را اشغال نمی کند، بسیار کارامد و پرطرفدار هستند.
در زیر نحوه ی ایجاد، جایگزینی و حذف view ها با استفاده از دستورات SQL را به شما خواهیم آموخت.
نحوه ی ایجاد views
فرض بگیرید دو جدول به نام های EMP و DEPT داریم. به منظور مشاهده ی empno، ename، sal، depno، اسم دپارتمان و مکان قرار گیری آن باید یک join query مانند نمونه ی زیر ایجاد کنید.
select e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc
From emp e, dept d where e.deptno=d.deptno;
بنابراین، هر زمان که بخواهیم جزئیات emp و اسم دپارتمانی که کارکنان در آن فعالیت دارند را مشاهده کنیم، لازم است از یک join query طولانی استفاده کنیم. بجای اینکه join query مزبور را بارها و بارها نوشته و اجرا کرد، می توان با بکار گیری دستور مربوطه ی SQL " CREATE VIEW " یک view (جدول مجازی) بر روی این جدول ها ایجاد کرد و از این طریق به روند کار سرعت بخشید:
create view emp_det as select e.empno,
e.ename,e.sal,e.deptno,d.dname,d.loc
from emp e, dept d where e.deptno=d.deptno;
حال، دیگر لازم نیست برای بازیابی و مشاهده ی جزئیات کارکنان و اسم دپارتمان های مورد نظر، یک join query طولانی نوشت، بلکه کافی است دستور ساده ی زیر بکار ببرید.
select * from emp_det;
نتیجه ی دستور ساده ی فوق با join query ذکر شده، یکسان می باشد. اکنون می توانید با view (جدول مجازی) ایجاد شده " EMP_DET "، به همان شیوه که با هر جدول دیگری کار می کنید، برخورد داشته باشید. فرض کنید تمامی کارکنانی که در Deptno = 10 مشغول به کار هستند، خود متعلق به دپارتمان Accounts بوده، و اغلب شما با این دسته از کارکنان سروکار دارید. از این رو هر زمان که باید یک دستور DML یا SELECT بکار ببرید، لازم است همراه آن یک شرط WHERE نیز تعیین کنید، مانند این نمونه: .....WHERE DEPTNO=10 . به منظور اجتناب از انجام این کار، می توان یک (view) همانند مثال زیر ایجاد کرد.
CREATE VIEW accounts_staff AS
SELECT Empno, Ename, Deptno
FROM Emp
WHERE Deptno = 10
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;
حال جهت مشاهده ی کارکنان دپارتمان accounts، دیگر لازم نیست یک query بنویسید که شرط WHERE در آن قید شده باشد. در عوض می توان query ساده ی زیر را اجرا کرد.
Select * from accounts_staff;
Select sum(sal) from accounst_staff;
Select max(sal) from accounts_staff;
همان طور که مشاهده می کنید، view ها کار شما را به طور قابل چشم گیری سهولت می بخشند. Query ای که جدول مجازی (view) ACCOUNTS_STAFF را تعریف می کند، تنها به آن سطرهایی ارجاع (reference) می دهد که در دپارتمان شماره ی 10 موجود می باشد. بعلاوه با استفاده از WITH CHECK OPTION می توان یک constraint ساخت که با استفاده از آن جلوی insert کردن در view از کاربر گرفته می شود. با توجه به مثال فوق، دستور INSERT بکار گرفته شده در مثال زیر، از طریق جدول مجازی (view) " ACCOUNTS_STAFF " به طور موفقیت آمیز یک سطر داخل جدول EMP درج می کند.
INSERT INTO Accounts_staff VALUES (110, 'ASHI', 10);
اما دستور INSERT نمونه ی زیر لغو شده (rollback) و در نتیجه یک پیغام خطا برمی گرداند زیرا که دستور مذکور سعی دارد یک سطر جدید در دپارتمان شماره ی 30 درج کند که امکان انتخاب آن با استفاده از جدول مجازی ACCOUNTS_STAFF وجود ندارد.
INSERT INTO Accounts_staff VALUES (111, 'SAMI', 30);
FORCE VIEWS
یک view در زمانی که ساخته می شود حتی اگر قابلیت اجرا نداشته باشد، چنانچه مشکل نگارشی (syntax error) نداشته باشد، قابل ساخته شدن می باشد.
View ای که این گونه ایجاد می شوند، view های نادرست نیز نامیده می شوند.
برای مثال، چنانچه view ای به یک جدول که وجود خارجی نداشته باشد یا یک ستون نامعتبر از جدول موجود اشاره کند و یا مالک view نام برده دارای مجوز لازم نباشد، باز امکان ایجاد view جدید و افزودن آن به کاتالوگ سیستم (data dictionary) وجود دارد.
Force view اجازه ی ایجاد view را به شما می دهد حتی زمانی که view نامعتبر بوده و یا قابلیت اجرای آن وجود نداشته باشد. برای این منظور از ساختار نگارشی زیر استفاده می کنیم.
CREATE FORCE VIEW AS ...;
پس از ایجاد این نوع view، oracle یک پیغام برگردانده و (وضعیت) view ایجاد شده را INVALID باقی می گذارد. اگر بعده ها شرایط به گونه ای تغییر یابد که امکان اجرای پرس و جوی (query) view نامعتبر فراهم آید، view مجددا کامپایل شده و به دنبال آن معتبر می شود.
در صورت سعی بر استفاده از view نامعتبر، oracle به صورت پویا (dynamic) این view را کامپایل می کند.
نحوه ی جایگزینی و اصلاح جداول
به منظور اصلاح تعریف view، باید view مد نظر را به یکی از روش های زیر جایگزین کرد:
- یک view را می توان حذف کرده و آن را مجدد ایجاد کرد. هنگامی که یک view حذف می شود، تمامی مجوزهای اعطا شده به کاربران و نقش های کاربری revoke می شوند. پس از اینکه view مجدد ایجاد می شود، مجوزهای لازم باید دوباره اعطا شوند.
- یک view را می توان با تعریف مجدد آن به وسیله ی دستور CREATE VIEW که دربردانده ی OR REPLACE option می باشد، جایگزین کرد. این گزینه به شما امکان می دهد که تعریف جاری view را جایگزین کرده، در عین حال تمامی مجوزهای امنیتی جاری را حفظ کنید.
فرض کنید view ای به نام ACCOUNTS_STAFF همانند مثال پیشین ایجاد کرده اید. همچنین object privilege های متعددی به دیگر کاربران و نقش های کاربری اعطا کرده اید. اما متوجه می شوید که برای بوجود آمدن امکان اصلاح شماره ی دپارتمان تعریف شده در عبارت WHERE که باید بجای 20 شماره ی 30 باشد، مجبور به تعریف مجدد ACCOUNTS_STAFF هستید. حال به منظور حفظ مجوزهایی که قبلا اعطا کرده بودید، می توان نسخه ی جاری ACCOUNTS_STAFF را با دستور زیر جایگزین کنید.
CREATE OR REPLACE VIEW Accounts_staff AS
SELECT Empno, Ename, Deptno
FROM Emp
WHERE Deptno = 30
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;
جاگزینی یک view، نتایج زیر را به دنبال دارد.
- جایگزینی یک view، باعث می شود تعریف آن view (view definition) در کاتالوگ سیستم تغییر یابد. البته گفتنی است که اشیا زیرین (underlying objects) که view مورد نظر به آن ها اشاره (reference) می کند، تحت تاثیر قرار نمی گیرد.
- چنانچه قبلا تعریف شده باشد ولی در تعریف view جدید includeنشده باشد، در آن صورت constraintای که مرتبط با WITH CHECK OPTION می باشد حذف خواهد شد.
- کلیه ی view ها و program unit های PL/SQL متصل و وابسته به view جایگزین شده، نامعتبر می گردند.
با کمی محدودیت، می توان از طریق view عملیات درج، بروز رسانی و حذف سطر را از جدول پایه (base table) داشت. دستور زیر با بهره گیری از view ACCOUNTS_STAFF، یک سطر جدید در جدول EMP درج می کند.
INSERT INTO Accounts_staff
VALUES (199, 'ABID', 30);
INSERT INTO Accounts_staff
VALUES (199, 'ABID', 30);
محدودیت هایی بر روی عملیات DML
نکاتی در زمینه ی کار با view ها وجود دارد که زیر به ترتیب شرح داده شده است. توصیه می کنیم آن ها را به همین ترتیب ذکر شده در کار با view ها در نظر داشته و رعایت کنید:
- چنانچه view مد نظر توسط query ای تعریف شده باشد که دربردارنده ی عملگرهای SET یا DISTINCT، عبارت GROUP BY یا توابع گروهی (group function) باشد، در این صورت امکان درج، بروز رسانی یا حذف سطر از جدول پایه (از طریق view) وجود ندارد.
- در صورتی که view توسط WITH CHECK OPTION تعریف شده، از آنجایی که view دیگر قادر به انتخاب سطر از جدول پایه نیست، امکان درج، بروز رسانی سطر در جدول پایه (به واسطه ی view) وجود ندارد.
- اگر یک ستون NOT NULL (ستونی که مقدار NULL ندارد) که دارای عبارت DEFAULT (مقدار پیش فرض( نیست از view حذف گردد، در آن صورت نمی توان از طریق view سطر جدید وارد جدول پایه کرد.
- اگر view مربوطه با استفاده از یک عبارت ایجاد شده مانند DECODE(deptno, 10, "SALES", ....)، در چنین موردی نیز امکان درج یا بروز رسانی سطر در جدول پایه (از طریق view) وجود ندارد.
Constraint ای که توسط WITH CHECK OPTION جدول مجازی (view) ACCOUNTS_STAFF ایجاد شده، تنها اجازه ی درج و بروز رسانی سطرهایی را در جدول EMP می دهد که شماره ی دپارتمان آن ها 10 باشد. حال نمونه ای را در نظر بگیرید که جدول مجازی (view) ACCOUNTS_STAFF با دستوری مشابه دستور زیر تعریف شده (یعنی، ستون DEPTNO را لحاظ نکنیم).
CREATE VIEW Accounts_staff AS
SELECT Empno, Ename
FROM Emp
WHERE Deptno = 10
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;
نظر به این تعریف view، شما می توانید فیلدهای EMPNO یا ENAME رکورد موجود را بروز رسانی کنید، اما دیگر قادر به درج سطر از طریق ACCOUNTS_STAFF view در جدول EMP نخواهید بود زیرا که view مذکور اجازه ی اعمال تغییر به فیلد DEPTNO را به شما نمی دهد. اگر از پیش مقدار DEFAULT (10) را برای فیلد DEPTNO تعریف کرده بودید، در آن صورت به شما اجازه ی اجرا دستور insert داده می شد.
چنانچه مایل نیستید هیچ گونه عملیات DML ای بر روی view ها صورت گیرد، در آن صورت کافی است WITH READ ONLY option را ایجاد کنید. با انجام این کار، دیگر اجازه ی اجرای هیچ عملیات DML ای بر روی view داده نمی شود.
ارجاع به view های نامعتبر
هنگامی که یک کاربر سعی بر ارجاع یا اشاره به یک view نامعتبر می کند، oracle در جواب یک پیغام خطا برمی گرداند.
ORA-04063: view 'view_name' has errors
پیغام مزبور زمانی صادر می گردد که view مورد نظر موجود می باشد اما به دلیل وجود خطاهایی در query آن، غیر قابل استفاده می باشد (یا query از زمان ایجاد دارای مشکلات یا خطاهایی بوده و یا با موفقیت ایجاد شده اما بعدا بخاطر حذف شدن اشیا زیرین یا اصلاح آن ها، غیر قابل استفاده شده است).
حذف view ها-دستور DROP VIEW
شما می توانید با استفاده از دستور DROP VIEW، view را مورد نظر خود را حذف کنید.
مثال:
DROP VIEW Accounts_staff;
تعریف view هایی که عملیات join در آن بکار رفته
Oracle این اجازه را به شما می دهد که با کمی محدودیت، view هایی تعریف کنید که از عملگر join استفاده می کنند. View ساده زیر را در نظر بگیرید:
CREATE VIEW Emp_view AS
SELECT Ename, Empno, deptno FROM Emp;
این view یک view ساده است که عملیات join در آن صورت نمی گیرد. اگر یک دستور ساده ی SQL را در آن اجرا کنید:
UPDATE Emp_view SET Ename = 'SHAHRYAR' WHERE Empno = 109;
به دنبال آن EMP که جدول زیرین (underlying table) view می باشد، تغییر یافته و اسم کارکن شماره ی 109 در جدول EMP از ASHI به SHAHRYAR تبدیل می شود.
اما چنانچه view ای تعریف کنید که شامل عملیات join می باشد، مانند نمونه ی زیر
CREATE VIEW Emp_dept_view AS
SELECT e.Empno, e.Ename, e.Deptno, e.Sal, d.Dname, d.Loc
FROM Emp e, Dept d /* JOIN operation */
WHERE e.Deptno = d.Deptno
AND d.Loc IN ('HYD', 'BOM', 'DEL');
در این صورت محدودیت هایی برای تعریف جداول پایه ی EMP یا DEPT از طریق این view بوجود می آید.
منظور از modifiable join view، نمایه یا view ای است که از پیوند دو یا چند جدول بوجود آمده که اجازه ی اجرای عملیات درج/بروز رسانی/حذف را بر روی تمامی جداول تشکیل دهنده فراهم می کند.
به عبارتی دیگر modifiable join view، view ای است که دارای چندین (بیش از یک) جدول در عبارت سطح بالا (top-level) FROM دستور SELECT باشد و نیز هیچ یک از موارد زیر را دربرنداشته باشد.
- · عملگر DISTINCT
- · توابع جمعی Aggregate functions: AVG, COUNT, GLB, MAX, MIN, STDDEV, SUM, VARIANCE
- · Set operation ها از جمله عملیات ترکیبی UNION، UNION ALL، INTERSECT و MINUS
- · عبارت های Group BY و HAVING
- · عبارت های START WITH یا CONNECT BY
- · شبه ستون ROWNUM
اجرا هر دستور UPDATE، INSERT یا DELETE بر روی modifiable join view، تنها می تواند یک جدول پایه ی زیرین (underlying base table) را modify (اصلاح) کند.
مثال زیر یک دستور UPDATE را نشان می دهد که با موفقیت EMP_DEPT_VIEW را اصلاح می کند:
UPDATE Emp_dept_view
SET Sal = Sal * 1.10
WHERE Deptno = 10;
دستور UPDATE بر روی EMP_DEPT_VIEW پذیرفته نبوده و اجرا نخواهد شد:
UPDATE Emp_dept_view
SET Loc = 'BOM'
WHERE Ename = 'SAMI';
اجرای این دستور با شکست مواجه شده و پیغام خطای ORA-01779 را برمی گرداند ("نمی تواند ستونی را که به یک جدول key-preserved نگاشت می شود، اصلاح کند")، دلیلش این است که دستور نام برده سعی می کند جدول زیرین DEPT را اصلاح کند و این جدول هم در نمایه ی EMP_DEPT، key preserved نمی باشد. یک جدول زمانی key preserved تلقی می گردد که table key (کلید جدول) در view نقش یک کلید را ایفا کند.
در کل، تمامی ستون های تغییر پذیر (modifiable columns) یک modifiable join view باید به ستون های یک جدول key-preserved نگاشت (map) شود. چنانچه view مورد نظر با استفاده از عبارت WITH CHECK OPTION تعریف شده باشد، در آن صورت تمامی join column ها و همچنین کلیه ی ستون های جداول تکراری تغییر ناپذیر (non-modifiable) خواهند بود.
برای مثال، اگر EMP_DEPT با عبارت WITH CHECK OPTION تعریف شده بود، دستور UPDATE زیر اجرا نمی شد.
UPDATE Emp_dept_view
SET Deptno = 10
WHERE Ename = 'SAMI';
علت عدم اجرا شدن دستور بالا این است که این دستور سعی دارد عملیات بروز رسانی را روی یک join column انجام دهد.
اجرای دستور Delete بر روی یک join view
عملیات حذف را تنها به این شرط می توان روی join view اجرا کرد که فقط یک key-preserved table در join وجود داشته باشد.
دستور DELETE زیر با موفقیت بر روی EMP_DEPT view اجرا می شود.
DELETE FROM Emp_dept_view
WHERE Ename = 'SMITH';
اجرای دستور DELETE بر روی view نام برده مجاز می باشد زیرا که این دستور می تواند در جدول EMP که جدول پایه می باشد، به عنوان عملیات حذف عمل کند و دلیل دیگر آن این است که جدول EMP تنها جدول key preserved در join می باشد.
در مثال زیر، از آنجایی که جداول E1 و E2 هر دو key-preserved هستند، اجرای عملیات حذف (DELETION) بر روی view امکان پذیر نمی باشد.
CREATE VIEW emp_emp AS
SELECT e1.Ename, e2.Empno, e1.Deptno
FROM Emp e1, Emp e2
WHERE e1.Empno = e2.Empno;
چنانچه view به وسیله ی عبارت WITH CHECK OPTION ایجاد شده باشد و key-preserved table مورد نظر تکرار شده باشد، در آن صورت نمی توان از view حذف سطر را داشت. به مثال زیر توجه کنید.
CREATE VIEW Emp_mgr AS
SELECT e1.Ename, e2.Ename Mname
FROM Emp e1, Emp e2
WHERE e1.mgr = e2.Empno
WITH CHECK OPTION;
هیچ عملیات حذفی نمی تواند بر روی این view صورت گیرد، زیرا که view نام برده شامل پیوند یک جدول preserved keyبا خودش هست.
اجرای دستور insert بر روی یک join view
دستور INSERT که در مثال زیر مشاهده می کنید با موفقیت بر روی EMP_DEPT view اجرا می شود، زیرا که تنها یک جدول پایه preserved-key طی این فرایند اصلاح می شود و آن جدول EMP است. مقدار 40 نیز یک DEPTNO (شماره ی دپارتمان( مجاز در جدول DEPTتلقی می گردد (بنابراین جامعیت محدودیتی FOREIGN KEY اعمال شده بر روی جدول EMP کاملا برآورده می شود).
INSERT INTO Emp_dept (Ename, Empno, Deptno)
VALUES ('ASHU', 119, 40);
دستور INSERT زیر به همین دلیل ذکر شده اجرا نمی شود: عملیات UPDATE بر روی جدول پایه EMP با موفقیت اجرا نخواهد شد: محدودیت جامعیتی FOREIGN KEY اعمال شده بر روی جدول EMP نقض می شود.
INSERT INTO Emp_dept (Ename, Empno, Deptno)
VALUES ('ASHU', 110, 77);
دستور INSERT زیر با شکست مواجه شده و منجر به صدور پیغام خطای ORA-01776 می شود ("نمی توان بیش از یک جدول پایه را از طریق view اصلاح کرد").
INSERT INTO Emp_dept (Ename, Empno, Deptno)
VALUES (110, 'TANNU’, 'BOMBAY');
یک دستور INSERT نمی تواند به صورت ضمنی یا صریح به ستون های یک جدول non-key-preserved اشاره کند. چنانچه join view به واسطه ی عبارت WITH CHECK OPTION تعریف شده باشد، شما دیگر نمی توانید دستور INSERT را بر روی آن اجرا کنید.
بازیابی اطلاعات درباره ی VIEW ها
به منظور بازیابی و مشاهده ی اطلاعاتی درباره ی تعداد view های موجود در schema خود، کافی است دستور زیر را نوشته و اجرا کنید.
Select * from user_views;
بازایابی اطلاعات درباره ی ستون هایی که در join view قابلیت بروز رسانی را دارند
در زیرکاتالوگ سیستمی را مشاهده می کنید که نشان می دهد کدام ستون ها قابلیت بروز رسانی (شدن) را دارند.
اگر در رابطه با امکان تغییر یا modify شدن یک view شک دارید، کافی است از دستور:
USER_UPDATABLE_COLUMNS مورد نظر view SELECT from
استفاده کنید، همانند مثال زیر
SELECT * FROM USER_UPDATABLE_COLUMNS WHERE TABLE_NAME = 'EMP_DEPT_VIEW';
ممکن است نتیجه ی زیر را برگرداند.
OWNER TABLE_NAME COLUMN_NAM UPD
---------- ---------- ---------- ---
SCOTT EMP_DEPT EMPNO NO
SCOTT EMP_DEPT ENAME NO
SCOTT EMP_DEPT DEPTNO NO
SCOTT EMP_DEPT DNAME NO
SCOTT EMP_DEPT LOC NO
5 rows selected.