book

Beginning Oracle PL/SQL, Second Edition

by Donald J. Bales

May 2015

Beginner

492 pages

11h 48m

English

Apress

Read now

Unlock full access

TablesAn Entity-Relationship DiagramData Definition Language (DDL)It’s Your Turn to Create a TableIndexesDDL AgainIt’s Your Turn to Create an IndexConstraintsColumn ConstraintsTable ConstraintsIt’s Your Turn to Create a ConstraintTriggersViewsInsertInsert . . .ValuesIt’s Your Turn to Insert with ValuesInsert . . . SelectIt’s Your Turn to Insert with SelectUpdateFix a Mistake with UpdateIt’s Your Turn to UpdateUpdate and SubqueriesDeleteA Change in OrderIt’s Your Turn to DeleteSelectJoinsIt’s Your Turn to SelectYour Working ExampleYour Example NarrativeYour Example ERDCreate a Code TableIt’s Your Turn to Create Code TablesCreate a Content TableIt’s Your Turn to Create Content TablesCreate an Intersection TableIt’s Your Turn to Create Intersection TablesSummary
BlocksAnonymous BlocksExceptionsCommon ExceptionsCatching an ExceptionFunctionsCreate a FunctionIt’s Your Turn to Create a FunctionProceduresCreate a ProcedureIt’s Your Turn to Create a ProcedureNested BlocksAn Example of Nesting BlocksRules for NestingPackagesCreate a Package SpecificationIt’s Your Turn to Create a Package SpecificationCreate a Package BodyIt’s Your Turn to Create a Package BodyBenefits of Using PackagesSummary
PL/SQL Data TypesVariablesVariable NamingVariable DeclarationsVariable AnchorsVariable AssignmentsNULL ValueIt’s Your Turn to Declare VariablesScopeScope RulesIt’s Your Turn to Scope Things OutTypesTable TypesRecord TypesMultidimensional ArraysParametersParameter NamingParameter DeclarationsParameter ScopeIt’s Your Turn to Declare ParametersSummary
InsertsCatching a DUP_VAL_ON_INDEX ExceptionUsing PL/SQL to Predetect a DuplicateIF I Don’t Tell You Now, When ELSE Can I?Using SQL to Predetect a DuplicateIt’s Your Turn to InsertUpdatesUpdating a RowUsing SQL to Perform Complex UpdatesDeletesSelectsNo Data FoundToo Many RowsIt’s Your Turn to SelectSummary
CursorsCursor DeclarationsFetching Rows from a Cursor ManuallyCursor RecordsA Singleton CursorIt’s Your Turn to Fetch ManuallyFetching Rows from a Cursor AutomaticallyIt’s Your Turn to Fetch AutomaticallyBulk CollectBulk Collect with a CursorBulk Collect with a Select StatementIt’s Your Turn to Bulk CollectFORALLSummary
Information ModelingObject OrientationKey TenetsReuseService OrientationA Roadmap to RealityStep 1: Table PackagesStep 2: User-Defined TypesStep 3: Object ViewsStep 4: Object TablesA Change in OrderObject TypesCreate a User-Defined Type SpecificationCreate a User-Defined Type ImplementationIt’s Your Turn to Create a User-Defined TypeObject ViewsCreate an Object ViewIt’s Your Turn to Create an Object ViewObject TablesCreate an Object TableIt’s Your Turn to Create an Object TableImpedance Mismatch?Nested Types and CollectionsIt’s Your Turn to Prove There’s No Impedance MismatchSummary
PreventionAnchorsData Type PrefixesExplicit ConversionsPreparationBlockingBread CrumbsAfter the FactSuccess MessagesFailure MessagesIt’s Your Turn to Use put_line( )As It HappensA DEBUG TableA DEBUGGER PackageIt’s Your Turn to Use Debug LoggingOne Step at a TimeDebugging with Oracle SQL DeveloperDebugging Anonymous PL/SQLDebugging with TOAD for OracleDebugging with Visual StudioIt’s Your Turn to Use a DebuggerProfiling PL/SQLProfiler’s TablesProfiler’s MethodsProfiling ReportsIt’s Your Turn to ProfileProfiling SQLExplain PlanPhysics vs. Explain PlanIt’s Your Turn to Use Explain PlanTKPROFSummary
SQL Test PatternsPL/SQL Test PatternsA Testing ToolA TEST TableA TEST Package SpecificationA TEST Package BodyIt’s Your Turn to Create a Testing ToolTestingTesting a Code Table PackageIt’s Your Turn to Test a Code Table PackageTesting a Content Table Package
Testing an Intersection Table PackageIt’s Your Turn to Test an Intersection Table PackageTesting a TypeIt’s Your Turn to Test a TypeAutomating TestingAutomate Test ProcessingIt’s Your Turn to Automate Test ProcessingSummary
Indestructible DocumentationSQL*Plus Documentation ToolsGUI Development Environment ToolsRules for Documentation CommentsDocumentation on DemandA Text-Based Documentation Formatting ToolAccessing Documentation on DemandIt’s Your Turn to Access Documentation on DemandDistributable DocumentationAn HTML-Based Documentation Formatting ToolGenerating Distributable DocumentationIt’s Your Turn to Generate Distributable DocumentationDocumentation DistributionSummary
Polymorphic CommandsParametric SQLCode Table MethodsContent Table MethodsIntersection Table MethodsHierarchical Table MethodsThe Black BoxTable MethodsUniversal MethodsDivide and ConquerData MigrationOn-Demand Data ProcessingPolling Data ProcessingInterfacingReportingSummary
How to Download Oracle Database SoftwareHow to Install Oracle Database SoftwareHow to Use SQL*PlusHow to Download This Book’s Source CodeHow to Create a New UsernameHow to Write a SQL*Plus ScriptHow to Execute a SQL*Plus ScriptHow to Describe Your Tables and Stored Procedures

Overview

Beginning Oracle PL/SQL gets you started in using the built-in language that every Oracle developer and database administrator must know. Oracle Database is chock-full of built-in application features that are free for the using, and PL/SQL is your ticket to learning about and using those features from your own code. With it, you can centralize business logic in the database, you can offload application logic, and you can automate database- and application-administration tasks.

Author Don Bales provides in Beginning Oracle PL/SQL a fast-paced and example-filled tutorial. Learn from Don’s extensive experience to discover the most commonly used aspects of PL/SQL, without wasting time on obscure and obsolete features.

The author takes his 20+ years of experience and a wealth of statistics he's gathered on PL/SQL usage over those years and applies the 80/20 rule: cover what's most needed and used by PL/SQL professionals and avoid what's not necessary! The result is a book that covers all the key features of PL/SQL without wasting your time discussing esoteric and obsolete parts of the language. Learn what really matters, so that you can get to work feeling confident with what you know about PL/SQL.

Covers the key topics that matter, including variables and datatypes, executing statements, working with cursors, bulk operations, real-world objects, debugging, testing, and more.

Teaches you to write production-level, object-oriented PL/SQL. You'll explore relational PL/SQL, but unlike most other books on the subject, this one emphasizes the use of PL/SQLs object-oriented features as well.

Guides you in working through real examples of using of PL/SQL. You'll learn PL/SQL by applying it to real-world business problems, not by heavy theory.