SQL & PLSQL for DBAs

Course Objectives
  • Write SQL/PLSQL code to interface with the database
  • Design SQL/PLSQL program units that execute efficiently
  • Use SQL/PLSQL programming constructs and conditional control statements
  • Handle run-time errors
  • Describe stored procedures and functions
Introduction to SQL:

  • Controlling User Access
  • Manage Schema Objects
  • Manipulating Large Data Sets
  • Generating Reports by Grouping Related Data
  • Managing Data in Different Time Zones
  • Retrieving Data Using Subqueries
  • Hierarchical Retrieval
  • Regular Expression Support
Introduction to PL/SQL:

  • What is PL/SQL
  • Overview of the Types of PL/SQL blocks
  • Create and Execute a Simple Anonymous Block
  • Generate Output from a PL/SQL Block
Declaring PL/SQL Identifiers:

  • Identify the Different Types of Identifiers in a PL/SQL subprogram
  • Use the Declarative Section to Define Identifiers
  • List the Uses for Variables
  • Store Data in Variables
  • Declare PL/SQL Variables
Writing Executable Statements:

  • Describe Basic Block Syntax Guidelines
  • Use Literals in PL/SQL
  • Customize Identifier Assignments with SQL Functions
  • Use Nested Blocks as Statements
  • Reference an Identifier Value in a Nested Block
  • Qualify an Identifier with a Label
  • Use Operators in PL/SQL
  • Use Proper PL/SQL Block Syntax and Guidelines
Interacting with the Oracle Server:

  • Identify the SQL Statements You Can Use in PL/SQL
  • Include SELECT Statements in PL/SQL
  • Retrieve Data in PL/SQL with the SELECT Statement
  • Avoid Errors by Using Naming Conventions When Using Retrieval and DML Statements
  • Manipulate Data in the Server Using PL/SQL
Writing Control Structures:

  • Control PL/SQL Flow of Execution
  • Conditional processing Using IF Statements
  • Conditional Processing CASE Statements
  • Use Iterative Control with Looping Statements
Working with Composite Data Types:

  • Learn the Composite Data Types of PL/SQL Records and Tables
  • Use PL/SQL Records to Hold Multiple Values of Different Types
  • Inserting and Updating with PL/SQL Records
  • Use INDEX BY Tables to Hold Multiple Values of the Same Data Type
Using Explicit Cursors:

  • Cursor FOR Loops Using Sub-queries
  • Increase the Flexibility of Cursors By Using Parameters
  • Use the FOR UPDATE Clause to Lock Rows
  • Use the WHERE CURRENT Clause to Reference the Current Row
  • Use Explicit Cursors to Process Rows
  • Explicit Cursor Attributes
  • Cursors and Records
Handling Exceptions:

  • Handling Exceptions with PL/SQL
  • Predefined Exceptions
  • Trapping Non-predefined Oracle Server Errors
  • Functions that Return Information on Encountered Exceptions
Creating Stored Procedures and Functions:

  • Differences Between Anonymous Blocks and Subprograms
  • Creating and invoking Procedure
  • Creating and invoking Functions
  • Passing Parameter to the Function

Comments are closed.