About the course

SQL is a query language that enables you to issue a single query or executes a single query to create, embed, select and update records. PL-SQL is oracle's "Procedural Language" SQL, which enables you to compose a full program (loops, variables, etc.) to achieve numerous selects/inserts/updates/deletes. You should learn SQL first, then move on to PL-SQL. You will probably need both to be an Oracle DBA.

What am I going to learn throughout this course? You will learn

  • Understand SQL, PL, Pl/SQL structure in Oracle
  • Know PL/SQL data types
  • Use operators and functions
  • Use sequences
  • Use logic control, branching, and subprograms
  • Handle errors using exceptions and embedded SQL
  • Understand Pl/SQL cursor
  • Use read and write packages
  • Understand and use database trigger

Audience and Requirements

  • Anyone who is interested to know and apply PL/SQL on Oracle DB
  • Requires database and  database design concept, and SQL

Training Highlights

  • Mentorship will be available through training and after training
  • You will practice on interview questions starting from day one

Curriculum


Module 1: Introduction to Oracle Database and PL/SQL

  • 1.1  Understanding PL/SQL and its Blocks
  • 1.2   Working with SQL Plus
  • 1.3   DECLARE Clause Objects

Module 2: PL/SQL Data Types

  • 2.1  Declaring simple data types and assignments
  • 2.2   Declaring complex data types and assignments
  • 2.3   Declaring complex row types and assignments
  • 2.4   Declaring complex tables
  • 2.5   Declaring complex records
  • 2.6   Creating and declaring user-defined types
  • 2.7   What can be included in the BEGIN clause
  • 2.8   Working with CHAR and VARCHAR2 variables
  • 2.9   Handling string literals
  • 2.10   Working with numeric variables
  • 2.11   Working with date variables
  • 2.12   Assigning and using boolean variables

Module 3: Using Operators and Functions

  • 3.1  Using comparison operators
  • 3.2   Using SQL functions
  • 3.3   SQL DML within PL/SQL
  • 3.4   Using SELECT

Module 4: Using Sequences

Module 5: Using Logic Control, Branching and Sub Programs

  • 5.1  Using GOTO
  • 5.2   Working with LOOPS
  • 5.3   Creating and using the indefinite loop
  • 5.4   Creating and using the conditional loop
  • 5.5   Using nested loops
  • 5.6   Using the If-Then-Else
  • 5.7   Using the CASE statement
  • 5.8   Using the continue statement
  • 5.9   Understanding nested blocks
  • 5.10   Subprograms: how they differ from nested blocks

Module 6: Error Handling Through Exceptions and Embedded SQL

  • 6.1  Trapping exceptions Identify exception details
  • 6.2   SQLCODE & SQLERRM Example
  • 6.3   Creating user-defined exceptions
  • 6.4  Handling compilation errors

Module 7: Understand and Use Pl/SQL Cursor

  • 7.1  Understand the implicit cursor
  • 7.2   Understanding explicit cursors
  • 7.3   FOR…LOOP cursors
  • 7.4   Working with updateable cursors
  • 7.5   Learn about cursor attributes
  • 7.6   Creating and using cursor parameters
  • 7.7   Weak cursor definitions
  • 7.8   REFCURSOR cursor definition
  • 7.9   Using cursor expression

Module 8: Using Read and Write Packages

  • 8.1  Using the DBMS OUTPUT() package
  • 8.2   Using the UTL FILE() package

Module 9: Understand and Apply Database Trigger

  • 9.1  Statement-Level Triggers
  • 9.2   Row-Level Triggers
  • 9.3   Using INSTEAD OF Trigger
  • 9.4   Using the CALL Statement
  • 9.5   Maintaining Trigger
  • 9.6   Setting Multiple Triggers for a Single Table
  • 9.7   Handling Mutating Table Issues
  • 9.8   Using the Compound Trigger
  • 9.9   Using System Event Trigger
Request a detailed syllabus.

Get Answers (Answering their questions)

What kind of learning does ITlearn360.com provide?

ITlearn360.com offers instructor-led online live sessions and classroom-based corporate trainings and bootcamps for various courses and certifications to the learners.

Who are the instructors @ITlearn360.com?

@ITlearn360.com, we have an instructor community of industry professionals who are working in leading organizations and are veterans in their respective fields. These experts belong to various industries and are willing to share their talent with learners like you.

Are classes @ITlearn360.com conducted through online video streaming?

Yes, the classes @ITlearn360.com are conducted through online video streaming where there is two-way communication between users and instructors. The users can speak by using a microphone, chat by sending a message through a chat window and share their screens with an instructor. For better understanding, users also get recorded video of the class.

Sign for next demo class