11g: Program with PL/SQL
Duration : 5 Days (40 Hours)
Overview
Course Details
Prerequisites
11g: Program with PL/SQL Course Overview:
In the 11g: Program with PL/SQL training course, participants will acquire a comprehensive understanding of the PL/SQL programming language. The course focuses on creating PL/SQL blocks of application code, allowing students to develop practical skills in writing efficient and effective PL/SQL code. Through hands-on exercises and real-world examples, students will gain proficiency in using PL/SQL for database programming, enabling them to design and implement robust database solutions.
Intended Audience:
- Database Administrators
- Forms Developer
- PL/SQL Developer
- Portal Developer
- System Analysts
- Technical Consultant
Learning Objectives of 11g: Program with PL/SQL:
- Introduction
- Introduction to PL/SQL
- Declaring PL/SQL Variables
- Writing Anonymous PL/SQL blocks
- Using SQL Statements within a PL/SQL Block
- Writing Control Structures
- Working with Composite Data Types
- Using Explicit Cursors
- Handling Exceptions
- Creating Stored Procedures and Functions
Module 1: Introduction
- Course Objectives, Course Agenda and Class Account Information
- The Human Resources (HR) Schema
- Appendices Used in this Course
- PL/SQL Development Environments
Module 2: Introduction to PL/SQL
- Understanding the benefits and structure of PL/SQL
- Understanding PL/SQL Blocks
- Generating output messages in PL/SQL
Module 3: Declaring PL/SQL Variables
- Identifying valid and invalid identifiers
- Declaring and initializing variables
- Various data types
- Identifying the benefits of using the %TYPE attribute
- Using bind variables
Module 4: Writing Anonymous PL/SQL blocks
- Lexical Units in a PL/SQL Block
- Using SQL Functions in PL/SQL
- Data Type Conversion
- Using Nested Blocks as Statements
- Referencing an Identifier Value in a Nested Block
- Qualifying an Identifier with a Label
- Operators in PL/SQL
- Using Sequences in PL/SQL Expressions
Module 5: Using SQL Statements within a PL/SQL Block
- Using SQL Statements in PL/SQL
- Retrieving Data in PL/SQL with the SELECT statement
- Using Naming Conventions in DML Statements and data retrieval
- Manipulating Data on the Server Using PL/SQL
- SQL Cursors
- Using SQL Cursor Attributes to Obtain Feedback on DML
- Saving and Discarding Transactions
Module 6: Writing Control Structures
- Controlling PL/SQL Flow of Execution
- Using IF and CASE Statements for conditional processing
- Handling Nulls
- Building Boolean Conditions with Logical Operators
- Using Iterative Control with Loop Statements
Module 7: Working with Composite Data Types
- Composite Data Types of PL/SQL Records and Tables
- Using PL/SQL Records
- Inserting and Updating PL/SQL Records
- Using INDEX BY Tables
- Using Associative arrays
- Declaring and Using VArrays
Module 8: Using Explicit Cursors
- Cursors
- Explicit Cursor Operations
- Controlling Explicit Cursors
- Using Explicit Cursors to Process Rows
- Cursors and Records
- Cursor FOR Loops Using Subqueries
- Explicit Cursor Attributes
- %NOTFOUND and %ROWCOUNT Attributes
Module 9: Handling Exceptions
- Concept of Exception
- Handling Exceptions with PL/SQL
- Predefined Exceptions
- Trapping Predefined and Non-predefined Oracle Server Errors
- Functions that Return Information on Exceptions
- Usage of PRAGMA keyword
- Trapping User-Defined Exceptions
- Propagating Exceptions
Module 10: Creating Stored Procedures and Functions
- Overview of Stored Procedures and Functions
- Differentiating between anonymous blocks and subprograms
- CREATE OR REPLACE PROCEDURE | FUNCTION
- Understanding the Header Area of a Stored Procedure and Function
- Creating Simple Procedures and Functions
- Creating a Simple Procedure with an IN Parameter
- Executing a Procedure and a Function
11g: Program with PL/SQL Course Prerequisites:
- Familiarity with data processing concepts and technique
- Familiarity with programming languages
- Oracle Database 12c: Introduction for Experienced SQL Users
Discover the perfect fit for your learning journey
Choose Learning Modality
Live Online
- Convenience
- Cost-effective
- Self-paced learning
- Scalability
Classroom
- Interaction and collaboration
- Networking opportunities
- Real-time feedback
- Personal attention
Onsite
- Familiar environment
- Confidentiality
- Team building
- Immediate application
Training Exclusives
This course comes with following benefits:
- Practice Labs.
- Get Trained by Certified Trainers.
- Access to the recordings of your class sessions for 90 days.
- Digital courseware
- Experience 24*7 learner support.
Got more questions? We’re all ears and ready to assist!
Request More Details
