Home >> Courses >> Oracle PL/SQL 11g

Oracle PL/SQL 11g Course

Course Topics
  • This course introduces students to PL/SQL and helps them understand the benefits of this powerful programming language. In the course, you shall learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications.
  • You shall also learn to create anonymous PL/SQL blocks and are introduced to stored procedures and functions.
  • Learn about declaring variables, trapping exceptions and they also learn to declare and control cursors.
  • Learn also: Declaring PL/SQL Variables Working with Composite Data Types Creating the Executable Section Using Explicit Cursors Writing Control Structures Including Exception Handling
Course Objectives
  • Write PL/SQL code to interface with the database
  • Design PL/SQL program units that execute efficiently
  • Handle run-time errors
  • Describe stored procedures and functions
Course Topics
Introduction to PL/SQL
  • What is PL/SQL
  • PL/SQL Environment
  • Benefits of 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
  • The SQL Cursor concept
  • Use SQL Cursor Attributes to Obtain Feedback on DML
  • Save and Discard Transactions
Writing Control Structures
  • Control PL/SQL Flow of Execution
  • Conditional processing Using IF Statements
  • Conditional Processing CASE Statements
  • Handle Nulls to Avoid Common Mistakes
  • Build Boolean Conditions with Logical Operators
  • 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
  • Trapping User-Defined Exceptions
  • Propagate Exceptions
  • Use The RAISE_APPLICATION_ERROR Procedure To Report Errors To Applications
Creating Stored Procedures and Functions
  • Differences Between Anonymous Blocks and Subprograms
  • Creating and invoking Procedure
  • Creating and invoking Functions
  • Passing Parameter to the Function
Student's profile
Already working: 47%
Students: 22%
Freshers: 27%
Others: 4%