Overview

A warm welcome to the Oracle PL/SQL course by Uplatz.PL/SQL is a procedural extension of SQL, making it extremely simple to write procedural code that includes/embraces SQL as if it were a single language thus combining the power of both the database Structured Query Language (SQL) as well as the high-level Procedural Language (PL).This comprehensive Oracle PL/SQL training course by Uplatz will teach you all concepts of Oracle PL/SQL programming in great depth. You will build skills on programming, productivity, performance, scalability, portability, and security of your Oracle database. With this training, you will learn how to build and execute PL/SQL programs in Oracle, build stored procedures, triggers, functions, etc., design and execute modular applications, automate the Oracle PL/SQL packages, tuning of Oracle database, and increase the efficiency of data movement - essential skills for any Oracle developer.Key features of PL/SQL are:PL/SQL is a procedural extension of SQL, making it extremely simple to write procedural code that includes SQL as if it were a single language. In comparison, most other programming languages require mapping data types, preparing statements and processing result sets, all of which require knowledge of specific APIs.The data types in PL/SQL are a super-set of those in the database, so you rarely need to perform data type conversions when using PL/SQL. Ask your average Java or.NET programmer how they find handling date values coming from a database. They can only wish for the simplicity of PL/SQL.When coding business logic in middle tier applications, a single business transaction may be made up of multiple interactions between the application server and the database. This adds a significant overhead associated with network traffic. In comparison, building all the business logic as PL/SQL in the database means client code needs only a single database call per transaction, reducing the network overhead significantly.Oracle is a multi-platform database, making PL/SQL and incredibly portable language. If your business logic is located in the database, you are protecting yourself from operating system lock-in.Programming languages go in and out of fashion continually. Over the last 35+ years Oracle databases have remained part of the enterprise landscape. Suggesting that any language is a safer bet than PL/SQL is rather naive. Placing your business logic in the database makes changing your client layer much simpler if you like to follow fashion.Centralizing application logic enables a higher degree of security and productivity. The use of Application Program Interfaces (APIs) can abstract complex data structures and security implementations from client application developers, leaving them free to do what they do best.Oracle PL-SQL course curriculumOverview of PL/SQLWhat is PL/SQL? What are the benefits?PL/SQL Block Structure & Types of BlocksWriting simple anonymous blockUsing SQL*plus to run anonymous blockCreating Script and run itQuizDeclaring PL/SQL VariablesAbout Variables and the syntaxDefining Variables exerciseGuidelines for declaring & initializing variablesDelimiters in string literals (the q' Notation)Types of variables and data types 1Types of variables and data types 2Data Types exerciseDefine Variables Using %TYPEUnderstating the SELECT statement in PL/SQLBind VariablesQuizWriting Executable StatementsInside PL/SQL BlockInside PL/SQL Block (exercise)Nested BlocksSQL & PL/SQL programming GuidelinesQuizInteracting with Oracle DB ServerSELECT statement & DML in PL/SQLSELECT statement & DML in PL/SQL exerciseUnderstanding Implicit SQL cursor/ SQL AttributesImplicit SQL cursor exerciseQuizWriting Control StructureIF Statement Part 1IF Statement Part 2Handling nulls in IF statementCASE expression & CASE StatementBasic LoopWhile LoopFOR LoopNested LoopsNested Loops and LabelsContinue StatementWorking with Composite Data TypesPL/SQL Records: Programmer method part 1PL/SQL Records: Programmer method part 2PL/SQL Records: %ROWTYPE methodNested PL/SQL RecordsINDEX BY tables (Associative arrays)INDEX BY tables MethodsINDEX BY tables of RecordsNested tableVARRAYUsing Explicit CursorsUnderstanding Explicit CursorExplicit Cursor exerciseExplicit Cursor AttributesFOR LOOP CursorCursor with ParametersFOR UPDATE Clause & CURRENT OF ClauseHandling ExceptionsUnderstanding ExceptionsPredefined Oracle Server Error ExceptionCommon mistakes when dealing with exceptionsNon-Predefined Oracle Server Error ExceptionUser-Defined Error ExceptionGroup Functions and exceptionsMany blocks and many exceptionsCreating ProceduresUnderstanding Procedures and subprogramsUnderstanding Procedures SyntaxCreating Procedure (IN parameter)Creating Procedure (OUT parameter)Creating Procedure (IN OUT parameter)Comparing the Parameter ModesAvailable notations for passing parametersUsing the default option for ParametersException handling in multiple blocksUsing Boolean and PL/SQL records as parametersCreating FunctionsUnderstanding user-defined functionsDifference between procedures & functionsCreating function exampleNo_data_found and functionsAdvantages of user-defined functions in SQL StatementsRestrictions when calling functions from SQL part 1Restrictions when calling functions from SQL part 2Creating PackagesUnderstanding packages by nice exampleMore about packagesCreating package practiceCreating package specification without bodyGuidelines for writing packagesThe visibility of a package's componentsWhen & Why to recompile the package?Working with PackagesUnderstanding Overloading Subprograms in PL/SQLOverloading Subprograms practices (procedures)Overloading Subprograms practices (functions)Overloading Standard packageForward DeclarationPersistent State of packagesUsing PRAGMA SERIALLY_REUSABLEPersistent State and cursorusing PL/SQL tables in packagesUsing Oracle-supplied Packages in Application DevelopmentAbout oracle-supplied PackagesDBMS_OUTPUT part 1DBMS_OUTPUT part 2UTL_FILE Package Part 1 / create directoryUTL_FILE Package Part 2 / get_line example 1UTL_FILE Package Part 3 / get_line example 2UTL_FILE Package Part 4 / put_lineUTL_FILE Package Part 5 / exceptionsMailUsing Dynamic SQLExecution flow of SQLWhat is Dynamic SQL?Execute immediate example 1 / delete any tableExecute immediate example 2 / DDL in PLSQLExecute immediate example 3 / USING ClauseExecute immediate example 4 / INTO ClauseExecute immediate example 5 / single row queryWhat is REF Cursor?Dynamic SQL with multi row queryExecute immediate/ more examplesDBMS_SQL Package VS Execute immediateDesign Consideration for PL/SQL CodeStandardizing Part 1Standardizing Part 2Definer's RightsInvoker's RightsAutonomous TransactionsFeatures Autonomous TransactionsThe (IN) parameters always passed by referenceThe (out/in out) parameters can be passed by value or referencePassing by reference example (IN parameters)Passing by value VS passing by reference using nocopyWhen Does NOCOPY hint has no effects?NOCOPY Performance ExerciseUsing the PARALLEL_ENABLE hintUsing the RESULT_CACHE hintUsing the DETERMINISTIC hintUnderstanding Bulk BindingBulk Binding ExampleBulk binding with save exception part 1Bulk binding with save exception part 2Understanding Bulk collectBulk collect another exampleBulk collect and cursorsUsing returning in bulk collectINDICES OFCreating TriggersUnderstanding TriggersTigger Types/ Trigger Event TypesStatement Level trigger VS Row Level (Part 1)Statement Level trigger VS Row Level (Part 2)Triggers common casesStatement Level trigger Example 1Statement Level trigger Example 2Understanding the NEW & OLD qualifiersRow level trigger exampleRow level trigger (create Audit table)Trigger Firing sequenceCompile/enable/ disable/ drop triggerUsing the triggers for populating a default valueInstead of triggers (on Views)Creating Compound, DDL, and Event Database TriggersFollows Statement in triggersUnderstanding Compound triggersUnderstanding Mutating TableSolving Mutating Table issue using compound triggerUsing Compound trigger & array to solve MutatingMutating table & ON DELETE CASCADEDatabase Triggers VS Stored proceduresCreating DDL triggers (Schema or Database)Create system event triggerFinal comments about the triggersUsing the PL/SQL CompilerIntroduction about PL/SQL compilerUnderstanding (plsql_code_type) parameterUnderstanding (plsql_optimize_level) parameterUnderstanding Warnings in PL/SQLUnderstanding (plsql_warnings) parameterMore examples for plsql_warningsUsing the package dbms_warningManaging PL/SQL CodeUnderstanding conditional compilationUnderstanding (dbms_db_version ) packageconditional compilation examplesUnderstanding plsql_ccflags part 1Understanding plsql_ccflags part 2Understanding plsql_ccflags part 3plsql_ccflags Real examplecreate_wrappedThe PL/SQL wrapper utilityManaging Dependencies

Skills

Reviews