Oracle PL/SQL Course Overview The Oracle PL/SQL course is designed to introduce Oracle DBAs,software development personnel and database support staff to the primary features and constructs of the PL/SQL Programming Language. Similarly,the course is targeted to closely follow the official Oracle Database curriculum for certification. Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered. Versions supported 12cR2,18c and 19c.
TARGET AUDIENCE:
Who will the Course Benefit? Oracle database administrators,software development personnel and database support staff who need to write PL/SQL scripts to enhance their productivity in manipulating data.
COURSE PREREQUISITES:
Requirements A working knowledge of Oracle SQL or SQL Developer is required along with practical experience in writing SQL statements. This knowledge can be obtained by attendance on the pre-requisite Oracle SQL course. Some previous programming experience will also prove advantageous.
COURSE CONTENT:
Oracle PL/SQL Training Course Course Contents – DAY 1 Course Introduction • Administration and Course Materials • Course Structure and Agenda • Delegate and Trainer Introductions Session 1: PL/SQL FUNDAMENTALS • What is PL/SQL? • Basic Elements • Variables and Constants • Data Types • Initialising Variables and Assigning Values • Using SQL Statements in Code • Generating Output to SQL or SQL Developer Session 2: PROGRAM LOGIC • IF THEN ELSIF ELSE Statements • CASE Statements • The Basic Loop Construct • WHILE and FOR Loops • Nested and Labelled Loops • The GOTO Statement • The CONTINUE Statement Session 3: USING CURSORS • What is a Cursor? • Implicit and Explicit Cursors • Cursor Operations • Declaring,Opening and Closing Cursors • Fetching Rows • Status Checking • Using Cursors FOR UPDATE • The Cursor FOR Loop • Parameterised Cursors Oracle PL/SQL Training Course Course Contents – DAY 2 Session 4: EXCEPTIONS AND NESTED BLOCKS • The EXCEPTION Section • Types of Exception • Handling Named System-Raised Exceptions • Handling Un-named System-Raised Exceptions • User-Declared Exceptions and Application Errors • WHEN OTHERS THEN NULL • Nested and Labelled Blocks • Propagation of Exceptions • Scope of Variables and Cursors • Scope of Goto Statements Session 5: PL/SQL RECORDS AND INDEX-BY TABLES • Declaring Record Types • Handling PL/SQL Records • Nested Records • Declaring PL/SQL Index-By Tables or Associative Arrays • PL/SQL Table Built-in Functions • Manipulating PL/SQL Tables or Associative Arrays
COURSE OBJECTIVE:
Course Objectives To provide the skills needed to write stand-alone programs using the main features of Oracle's PL/SQL Programming Language.
FOLLOW ON COURSES:
Further Learning Oracle PL/SQL Stored Program Units Oracle Database 19c Administration