Oracle PL/SQL Course

Module-1 Oracle Database Program with PL/SQL

Introduction to PL/SQL
This section provides an overview of PL/SQL, highlighting the benefits of using PL/SQL subprograms. Students will learn about different types of PL/SQL blocks and how to create a simple anonymous block. It also explains how to generate output from a PL/SQL block.

Declare PL/SQL Identifiers
Students will explore different types of identifiers used in PL/SQL subprograms, the usage of the declarative section to define identifiers, and the use of variables to store data. The module covers scalar data types, the %TYPE attribute, bind variables, and sequences in PL/SQL expressions.

Write Executable Statements
This section teaches basic PL/SQL block syntax guidelines, commenting code, deploying SQL functions in PL/SQL, and converting data types. Students will understand nested blocks, PL/SQL operators, SQL cursor concepts, and SQL cursor attributes for obtaining feedback on DML operations. The section also covers saving and discarding transactions.

Control Structures
Students will learn conditional processing using IF and CASE statements, and looping structures including simple loops, WHILE loops, FOR loops, and the CONTINUE statement.

Composite Data Types
This part covers the use of PL/SQL records, the %ROWTYPE attribute, and performing insert and update operations using PL/SQL records.

Overview of Collections
Students will learn about different collection types, including index-by tables (associative arrays), clustered tables, and VARRAYs.

Explicit Cursors
This section explains explicit cursors, including declaration, opening, fetching data, closing, cursor FOR loops, and attributes such as %NOTFOUND and %ROWCOUNT. Students will also learn about the FOR UPDATE clause and WHERE CURRENT clause.

Exception Handling
Students will understand exceptions, how to handle exceptions in PL/SQL, and trap predefined, non-predefined, and user-defined exceptions. This section also covers propagating exceptions and using the RAISE_APPLICATION_ERROR procedure.

Stored Procedures
This part covers creating modularized and layered subprogram designs, modularizing development with PL/SQL blocks, understanding the PL/SQL execution environment, and the benefits of subprograms. Students will learn the differences between anonymous blocks and subprograms, create, call, and remove stored procedures, implement procedure parameters and modes, and view procedure information.

Triggers
This section introduces triggers, including their event types and body, business application scenarios, creating DML triggers using SQL Developer, and understanding firing (timing). Students will learn the differences between statement-level and row-level triggers, create INSTEAD OF and DISABLED triggers, and manage, test, and remove triggers effectively.