PL/SQL

PL/SQL

About The Course Course Topic Overview and Description: In this Oracle 11g PL/SQL Bundle you will receive introduction training on PL/SQL database programming language covering syntax, structure and features of the language within the context of database applications and programming. In volume II students will dive into topics such as understanding the basic form and structure of program units stored within the database, building and maintaining stored procedures, functions, packaged programs, and database triggers. Additional topics include taking advantage of advanced programming techniques such as cursor variables and cursor expressions. This Oracle 11g course will prepare students for the Oracle certification exams (OCP). Requirements Familiarity with database and programming principles Benefits of taking this course: Prepare your environment Introducing Database Program Units Creating and Maintaining Packages Using System-Supplied Packages Creating and Maintaining Database Triggers Implementing System Event Triggers Work with Oracle database tools Understand and work language features Work with DECLARE, BEGIN & EXCEPTION clause Understand and use nested blocks Use explicit cursors   The course is for 5 days. Various instructional methods will be used to teach the objectives mentioned above. The training method would be completely interactive and participative. Students will receive course materials which will be discussed during class. Who Should Attend: This course is designed for people that are looking to become application designers and developers, database administrators or business users and non-technical senior end users. Related Job Functions: Application designers and developers Database administrators Business users and non-technical senior end users Course Delivery: The course will be delivered using formal lectures combined with questions based on the topics. The class will be conducted both online and in-person. 

    

Day 1

  • Introduction
  • Course Setup
  • Selecting the appropriate database tool
  • Understanding PL/SQL
  • PL/SQL blocks
  • Working with SQL plus
  • DECLARE clause objects
  • PL/SQL available data types
  • Declaring simple data types and assignments
  • Declaring complex row types and assignments
  • Declaring complex tables
  • Declaring complex records
  • Creating and declaring user-defined types
  • What can be included in the BEGIN clause
  • Working with CHAR and VARCHAR2 variables
  • Handling string literals
  • Working with numeric variables
  • Working with date variables
  • Assigning and using boolean variables 

Day 2

  • Using comparison operators
  • Using SQL functions
  • SQL DML within PL/SQL
  • Using SELECT
  • Exceptions and Embedded SQL
  • Using Sequences
  • Logic Control and Branching
  • Using GOTO
  • Working with LOOPS
  • Creating and using the indefinite loop
  • Creating and using the conditional loop
  • Using nested loops
  • Using the if-then-else
  • Using the case statement
  • What are the types of exceptions
  • Trapping exceptions
  • Identify exception details
  • Creating user-defined exceptions
  • Using SQLCODE and SQLERRM Objects 

Day 3

  • Understanding the implicit cursor
  • Creating user-defined events
  • Understanding the concept and purpose of explicit cursors
  • Learn about cursor attributes
  • Using the continue statement
  • Working with updateable cursors
  • Creating and using cursor parameters
  • FOR…LOOP Cursors
  • Understanding nested blocks
  • Difference between global vs local objects and exceptions
  • Subprograms: how they differ from nested blocks
  • Install Oracle XE
  • SQL developer Installation
  • Workstation Setup
  • Introducing the database program unit
  • Creating stored procedures and functions
  • Understanding the parameter specification
  • Executing procedures and functions
  • Calling functions from within SQL

Day 4

  • Handling compilation errors
  • Recompiling and dropping programs
  • Data dictionary storage
  • Managing dependencies
  • Tracking dependencies
  • Using the dependency tracking utility
  • What is a package?
  • Creating a package
  • Package definition information
  • Advanced programming techniques
  • Using persistent global objects
  • Including initialization logic
  • Object oriented support within packages
  • Package maintenance
  • Learning to use cursor variables
  • Weak cursor definitions
  • REFCURSOR Cursor definition
  • Using Cursor Expression
  • Using the DBMS OUTPUT() Package 

Day 5

  • Using the UTL FILE() Package
  • Understanding the Database Trigger
  • Statement –level triggers
  • Row-level triggers
  • Row level trigger example 1
  • Row level trigger example 2
  • Row level trigger example 3
  • Working with the INSTEAD OF Trigger
  • Using Triggers within an Application
  • Using the CALL statement
  • Trigger maintenance tasks
  • Work with multiple triggers for a single table
  • Handling mutating table issues
  • Using the compound trigger
  • What is a system event trigger
  • Defining scope
  • Identifying available system events
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech
Clients PeopleNTech