Oracle-OCP-SQL-11-Creating-Views

Copyright©OracleCorporation,2001.Allrightsreserved.CreatingViews11-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•Describeaview•Create,alterthedefinitionof,anddropaview•Retrievedatathroughaview•Insert,update,anddeletedatathroughaview•Createanduseaninlineview•Perform“Top-N”analysis11-3Copyright©OracleCorporation,2001.Allrightsreserved.DatabaseObjectsDescriptionBasicunitofstorage;composedofrowsandcolumnsLogicallyrepresentssubsetsofdatafromoneormoretablesGeneratesprimarykeyvaluesImprovestheperformanceofsomequeriesAlternativenameforanobjectObjectTableViewSequenceIndexSynonym11-4Copyright©OracleCorporation,2001.Allrightsreserved.WhatisaView?EMPLOYEESTable:11-5Copyright©OracleCorporation,2001.Allrightsreserved.WhyUseViews?•Torestrictdataaccess•Tomakecomplexquerieseasy•Toprovidedataindependence•Topresentdifferentviewsofthesamedata11-6Copyright©OracleCorporation,2001.Allrightsreserved.SimpleViewsandComplexViewsFeatureSimpleViewsComplexViewsNumberoftablesOneOneormoreContainfunctionsNoYesContaingroupsofdataNoYesDMLoperationsthroughaviewYesNotalways11-7Copyright©OracleCorporation,2001.Allrightsreserved.CreatingaView•YouembedasubquerywithintheCREATEVIEWstatement.•ThesubquerycancontaincomplexSELECTsyntax.CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview[(alias[,alias]...)]ASsubquery[WITHCHECKOPTION[CONSTRAINTconstraint]][WITHREADONLY[CONSTRAINTconstraint]];11-8Copyright©OracleCorporation,2001.Allrightsreserved.CreatingaView•Createaview,EMPVU80,thatcontainsdetailsofemployeesindepartment80.•DescribethestructureoftheviewbyusingtheiSQL*PlusDESCRIBEcommand.DESCRIBEempvu80CREATEVIEWempvu80ASSELECTemployee_id,last_name,salaryFROMemployeesWHEREdepartment_id=80;Viewcreated.11-9Copyright©OracleCorporation,2001.Allrightsreserved.CreatingaView•Createaviewbyusingcolumnaliasesinthesubquery.•Selectthecolumnsfromthisviewbythegivenaliasnames.CREATEVIEWsalvu50ASSELECTemployee_idID_NUMBER,last_nameNAME,salary*12ANN_SALARYFROMemployeesWHEREdepartment_id=50;Viewcreated.11-10Copyright©OracleCorporation,2001.Allrightsreserved.RetrievingDatafromaViewSELECT*FROMsalvu50;11-11Copyright©OracleCorporation,2001.Allrightsreserved.QueryingaViewUSER_VIEWSEMPVU80SELECTemployee_id,last_name,salaryFROMemployeesWHEREdepartment_id=80;iSQL*PlusSELECT*FROMempvu80;EMPLOYEESOracleServer11-12Copyright©OracleCorporation,2001.Allrightsreserved.ModifyingaView•ModifytheEMPVU80viewbyusingCREATEORREPLACEVIEWclause.Addanaliasforeachcolumnname.•ColumnaliasesintheCREATEVIEWclausearelistedinthesameorderasthecolumnsinthesubquery.CREATEORREPLACEVIEWempvu80(id_number,name,sal,department_id)ASSELECTemployee_id,first_name||''||last_name,salary,department_idFROMemployeesWHEREdepartment_id=80;Viewcreated.11-13Copyright©OracleCorporation,2001.Allrightsreserved.CreatingaComplexViewCreateacomplexviewthatcontainsgroupfunctionstodisplayvaluesfromtwotables.CREATEVIEWdept_sum_vu(name,minsal,maxsal,avgsal)ASSELECTd.department_name,MIN(e.salary),MAX(e.salary),AVG(e.salary)FROMemployeese,departmentsdWHEREe.department_id=d.department_idGROUPBYd.department_name;Viewcreated.11-14Copyright©OracleCorporation,2001.Allrightsreserved.RulesforPerformingDMLOperationsonaView•YoucanperformDMLoperationsonsimpleviews.•Youcannotremovearowiftheviewcontainsthefollowing:–Groupfunctions–AGROUPBYclause–TheDISTINCTkeyword–ThepseudocolumnROWNUMkeyword11-15Copyright©OracleCorporation,2001.Allrightsreserved.RulesforPerformingDMLOperationsonaViewYoucannotmodifydatainaviewifitcontains:•Groupfunctions•AGROUPBYclause•TheDISTINCTkeyword•ThepseudocolumnROWNUMkeyword•Columnsdefinedbyexpressions11-16Copyright©OracleCorporation,2001.Allrightsreserved.RulesforPerformingDMLOperationsonaViewYoucannotadddatathroughaviewiftheviewincludes:•Groupfunctions•AGROUPBYclause•TheDISTINCTkeyword•ThepseudocolumnROWNUMkeyword•Columnsdefinedbyexpressions•NOTNULLcolumnsinthebasetablesthatarenotselectedbytheview11-17Copyright©OracleCorporation,2001.Allrightsreserved.•YoucanensurethatDMLoperationsperformedontheviewstaywithinthedomainoftheviewbyusingtheWITHCHECKOPTIONclause.•AnyattempttochangethedepartmentnumberforanyrowintheviewfailsbecauseitviolatestheWITHCHECKOPTIONconstraint.CREATEORREPLACEVIEWempvu20ASSELECT*FROMemployeesWHEREdepartment_id=20WITHCHECKOPTIONCONSTRAINTempvu20_ck;Viewcreated.UsingtheWITHCHECKOPTIONClause11-18Copyright©OracleCorporation,2001.Allrightsreserved.DenyingDMLOperations•YoucanensurethatnoDMLoperationsoccurbyaddingtheWITHREADONLYoptiontoyourviewdefinition.•AnyattempttoperformaDMLonanyrowintheviewresultsinanOracleservererror.11-19Copyright©OracleCorporation,2001.Allrightsreserved.DenyingDMLOperationsCREATEORREPLACEVIEWempvu10(employee_number,employee_name,job_title)ASSELECTemployee_id,last_name,job_idFROMemployeesWHEREdepartment_id=10WITHREADONLY;Viewcreated.11-20Copyright©OracleCorporation,2001.Allrightsreserved.RemovingaViewYoucanremoveaviewwithoutlosingdatabecauseaviewisbasedonunderlyingtablesinthedatabase.DROPVIEWempvu80;Viewdropped.DROPVIEWview;11