PL/SQL Full Course By BishoyiFy

Contents

About=1
Chapter 1 Getting Started with Oracle Database

Section 1.1:HelloWorld

Section 1.2:SQLQuery

Section 1.3:Helloworld! From Table
Section 1.4: Hello World from PL/SQL
Chapter 2:Getting Started with PL/SQL
Section21:Hello World

Section22:Definition of PL/SQL
Section23:Difference between %TYPE and %ROWTYPE.
Section24:Createorreplaceaview
Section25:CreateaTable
Section26:AboutPL/SQL
Chapter3:AnonymousPL/SQLBlock
Section31:Anexampleofananonymousblock
Chapter4:PL/SQLprocedure
Section41:Syntax
Section42:HelloWorld
Section43:In/OutParameters
Chapter5:DataDictionary
Section51:Describesallobjectsinthedatabase
Section52:Toseeallthedatadictionaryviewstowhichyouhaveaccess
Section53:Textsourceofthestoredobjects
Section54:GetthelistofalltablesinOracle
Section55:Privilegeinformation
Section56:Oracleversion
Chapter6:Dates
Section61:DateArithmetic-DifferencebetweenDatesinDays,Hours,Minutesand/orSeconds
Section62:SettingtheDefaultDateFormatModel
Section63:DateArithmetic-DifferencebetweenDatesinMonthsorYears
Section64:ExtracttheYear,Month,Day,Hour,MinuteorSecondComponentsofaDate
Section65:GeneratingDateswithNoTimeComponent
Section66:GeneratingDateswithaTimeComponent
Section67:TheFormatofaDate
Section68:ConvertingDatestoaString
Section69:ChangingHowSQL/PlusorSQLDeveloperDisplayDates
Section610:TimeZonesandDaylightSavingsTime
Section611:LeapSeconds
Section612:GettingtheDayoftheWeek
Chapter7:WorkingwithDates
Section71:DateArithmetic
Section72:Add_monthsfunction
Chapter8:DUALTable
Section81:Thefollowingexamplereturnsthecurrentoperatingsystemdateandtime
Section82:Thefollowingexamplegeneratesnumbersbetweenstart_valueandend_value
Chapter9:JOINS
Section91:CROSSJOIN
Section92:LEFTOUTERJOIN
Section93:RIGHTOUTERJOIN
Section94:FULLOUTERJOIN
Section95:ANTIJOIN28
Section96:INNERJOIN
Section97:JOIN
Section98:SEMIJOIN
Section99:NATURALJOIN
Chapter10:HandlingNULLvalues
Section101:OperationscontainingNULLareNULL,exceptconcatenation
Section102:NVL2togetadifferentresultifavalueisnullornot
Section103:COALESCEtoreturnthefirstnon-NULLvalue
Section104:Columnsofanydatatypecancontainnulls
Section105:EmptystringsareNULL
Section106:NVLtoreplacenullvalue
Chapter11:StringManipulation
Section111:INITCAP
Section112:Regularexpression
Section113:SUBSTR
Section114:Concatenation:Operator||orconcat()function

Section115:UPPER
Section116:LOWER
Section117:LTRIM/RTRIM
Chapter12:IF-THEN-ELSEStatement38
Section121:IFTHEN
Section122:IF-THEN-ELSE
Section123:IF-THEN-ELSIF-ELSE

Chapter13:Limitingtherowsreturnedbyaquery(Pagination)39
Section131:GetthefirstNrowswithrowlimitingclause
Section132:GetrowNthroughMfrommanyrows(beforeOracle12c)
Section133:GetNnumbersofRecordsfromTable
Section134:Skippingsomerowsthentakingsome
Section135:Skippingsomerowsfromresult
Section136:PaginationinSQL
Chapter14:RecursiveSub-QueryFactoringusingtheWITHClause(AKACommon TableExpressions)

Section141:SplittingaDelimitedString
Section142:ASimpleIntegerGenerator
Chapter15:Differentwaystoupdaterecords44
Section151:UpdateusingMerge
Section152:UpdateSyntaxwithexample
Section153:UpdateUsingInlineView
Section154:Mergewithsampledata
Chapter16:UpdatewithJoins

Section161:Examples:whatworksandwhatdoesn't
Chapter17:Functions
Section171:CallingFunctions
Chapter18:StatisticalFunctions
Section181:Calculatingthemedianofasetofvalues
Chapter19:WindowFunctions
Section191:Ratio_To_Report

Chapter20:CreatingaContext
Section201:CreateaContext
Chapter21:SplittingDelimitedStrings
Section211:SplittingStringsusingaHierarchicalQuery
Section212:SplittingStringsusingaPL/SQLFunction
Section213:SplittingStringsusingaRecursiveSub-queryFactoringClause
Section214:SplittingStringsusingaCorrelatedTableExpression
Section215:SplittingStringsusingCROSSAPPLY(Oracle12c)
Section216:SplittingStringsusingxmltableandFLWORexpressions

Section217:SplittingDelimitedStringsusingXMLTable
Chapter22:CollectionsandRecords
Section221:Useacollectionasareturntypeforasplitfunction
Chapter23:ObjectTypes
Section231:AccessingstoredObjects
Section232:BASE_TYPE
Section233:MID_TYPE
Section234:LEAF_TYPE

Chapter24:Loop
Section241:SimpleLoop
Section242:WHILELoop
Section243:FORLoop
Chapter25:Cursors
Section251:Parameterized"FORloop"Cursor
Section252:Implicit"FORloop"cursor
Section253:HandlingaCURSOR
Section254:WorkingwithSYS_REFCURSOR

Chapter26:Sequences
Section261:CreatingaSequence:Example
Chapter27:Indexes
Section271:b-treeindex
Section272:BitmapIndex
Section273:Function-BasedIndex
Chapter28:Hints
Section281:USE_NL

Section282:APPENDHINT
Section283:ParallelHint
Section284:USE_HASH
Section285:FULL
Section286:ResultinCache
Chapter29:Packages
Section291:DefineaPackageheaderandbodywithafunction
Section292:Overloading

Section293:PackageUsage
Chapter30:ExceptionHandling
Section301:Syntax
Section302:User-definedexceptions
Section303:Internallydefinedexceptions
Section304:Predefinedexceptions
Section305:Definecustomexception,raiseitandseewhereitcomesfrom
Section306:Handlingconnexionerrorexceptions
Section307:Exceptionhandling
Chapter31:Errorlogging

Section311:Errorloggingwhenwritingtodatabase
Chapter32:DatabaseLinks
Section321:Creatingadatabaselink
Section322:CreateDatabaseLink
Chapter33:Tablepartitioning
Section331:Selectexistingpartitions
Section332:Droppartition
Section333:Selectdatafromapartition
Section334:SplitPartition
Section335:MergePartitions
Section336:ExchangeapartitionSection337:Hashpartitioning

Section338:Rangepartitioning
Section339:Listpartitioning
Section3310:Truncateapartition
Section3311:Renameapartition
Section3312:Movepartitiontodierenttablespace
Section3313:Addnewpartition
Chapter34:OracleAdvancedQueuing(AQ)90
Section341:SimpleProducer/Consumer
Chapter35:constraints
Section351:UpdateforeignkeyswithnewvalueinOracle
Section352:Disableallrelatedforeignkeysinoracle
Chapter36:AutonomousTransactions
Section361:Usingautonomoustransactionforloggingerrors
Chapter37:OracleMAF

Section371:TogetvaluefromBinding
Section372:Tosetvaluetobinding
Section373:Toinvokeamethodfrombinding
Section374:Tocallajavascriptfunction
Chapter38:levelquery
Section381:GenerateNNumberofrecords
Section382:FewusagesofLevelQuery
Chapter39:HierarchicalRetrievalWithOracleDatabase12C98

Section391:UsingtheCONNECTBYCaluse
Section392:SpecifyingtheDirectionoftheQueryFromtheTopDown
Chapter40:DataPump
Section401:MonitorDatapumpjobs
Section402:Step3/6:Createdirectory99
Section403:Step7:ExportCommands
Section404:Step9:ImportCommands
Section405:Datapumpsteps101