Module 1: Introduction to PLSQL
- PL/SQL Engine Architecture
- Structure Of PL/SQL
- The PLSQL Development Environments
- Executing SQL Statements
- Creating PLSQL Blocks
- Save to SQL Scripts
- Executing from SQL Scripts
- Creating Anonymous Blocks
- Generate Output from a PL/SQL Block
Module 2: Declaring PLSQL Variables
- Use of variables
- What are Identifiers
- Declaring PLSQL Variables
- Types of Variables
- Datatypes
- %TYPE Attribute
- Bind Variables
- Printing Bind variables
- Substitution Variables
- Using Define for variables
Module 3: Writing PLSQL Codes
- PLSQL Block Syntax
- SQL Functions in PLSQL
- Nesting Functions
- Datatype Conversion
- Nested Blocks
- Operators
- Commenting Codes
Module 4: Triggers
- Introduction to Triggers
- BEFORE and AFTER Triggers
- ROW-level and STATEMENT-level Triggers
- Trigger Functions
- Practical Examples of Triggers
Module 5: Interacting with database
- SELECT statements in PLSQL
- DMLS using PLSQL Code
- SQL Cursor
- Implicit Cursors Attributes
Module 6: Control Statements
- Controlling flow of execution
- Types of IF Statements
- CASE Statements
- Handling Nulls
- Interactive Control Statements
Module 7: Looping Structures
- FOR Loop
- WHILE Loops
- GOTO Statements
- CONTINUE Statements
- Nested Loops and Labels
Module 8: Composite Datatypes
- PLSQL records
- %ROWTYPE Attribute
- DMLs using %ROWTYPE
- Associative Arrays
- Using INDEX BY table Structure
- INDEX BY table of records
Module 9: Explicit Cursors
- Cursors
- Controlling explicit Cursors
- Fetching data from cursors
- Cursors and records
- Closing cursors
- Cursor FOR loop
- Cursor Attributes
- Parameterized cursors
- For Update
- Cursor with subqueries
- Where CURRENT of
Module 10: Exceptions
- What is Exception?
- Handling Exceptions
- Types of Exception
- Predefined Errors
- Non-predefined Exceptions
- User Defined Exceptions
- RAISE_APPLICATION_ERROR
- Handling Exceptions in block
- SQLCODE and SQLERRM in PLSQL
Module 11: Database Triggers
- Types of triggers
- Creating DML Triggers
- Statement Vs Rows Level triggers
- Using OLD and NEW Qualifiers
- INSTEAD OF Triggers
- AUTONOMOUS Transaction
- Enabling and Disabling triggers
- Compiling Triggers
- Dropping Triggers
- System Level Triggers
- LOGON and LOGOFF triggers
Module 12: Procedures and Functions
- Creating procedures
- IN, OUT, IN OUT Parameters
- Passing parameters to functions
- Calling Functions from PLSQL
- Invoking procedures in PLSQL
- Viewing Codes using Views
- Viewing USER_SOURCE
- Viewing USER_ERRORS
- Viewing SHOW ERRORS
- Viewing USER_OBJECTS
- Compiling invalid Objects
Module 13: Create Packages
- Structures of Package
- What is STANDARD Package
- Public and Private Constructs
- Creating Package Specification
- Creating package Body
- Removing package
- Granting and Revoking permissions
- PLSQL Tables and records in packages
Module 14: Oracle Supplied packages
- Native Dynamic SQL
- EXECUTE IMMEDIATE
- EXECUTE IMMEDIATE using variables
- DBMS_JOBS
Module 15: Using DDLS in PLSQL
- Running / Breaking Jobs
- UTL_FILE
- Creating Directory