SQL

PL/SQL is a blend of SQL alongside the procedural elements of programming dialects. It was created by Prophet Company in the mid 90’s to improve the capacities of SQL.

PL/SQL is one of three key programming dialects implanted in the Prophet Database, alongside SQL itself and Java.This instructional exercise will give you awesome comprehension on PL/SQL to continue with Prophet database and other progressed RDBMS ideas.

Audience

This instructional exercise is intended for Programming Experts, why should willing learn PL/SQL Programming Dialect in straightforward and simple steps. This instructional exercise will give you awesome comprehension on PL/SQL Programming ideas, and in the wake of finishing this instructional exercise, you will be at middle level of aptitude from where you can take yourself to more elevated amount of mastery.

Prerequisites

Before continuing with this instructional exercise, you ought to have an essential comprehension of programming fundamental ideas like what is database, source code, word processor and execution of projects, and so on. In the event that you as of now have understanding on SQL and other PC programming dialect then it will be an additional point of preference to continue.

Suggested Audience
PL/SQL engineers, DW ETL designers, DBAs and creation DBAs.

 

1. PL/SQL Overview

  • Definition
  • Object-orientation implementation
  • Advantages and disadvantages
  • Assumed knowledge
  • Performance

2. PL/SQL Language Semantics & Constructs

  • Block structures
  • Variables and constants
  • Query processing
  • Control structures
  • Modules and sub-programs
  • Abstract objects (e.g., collections, object types)
  • Error and exception handlers
  • Use of SQL*Plus

3. PL/SQL Language Properties & Conventions

  • Character set
  • Lexical elements (e.g., reserve words, comments)
  • Declarations
  • Defaults
  • NULL/NOT NULL
  • %TYPE and %ROWTYPE attributes
  • Naming conventions
  • Scoping
  • Name resolution
  • Assignments (e.g., to variables)
  • Expressions and comparisons
  • Built-in functions
  • Data types

4. PL/SQL – The Big Picture

  • Client-server architecture PL/SQL execution flow SQL*Plus DBMS_OUTPUT.LIN

5. How Sql Interoperates With PL/SQL

  • SELECT INTO
  • DML within PL/SQL block
  • PL/SQL block sequencing
  • Savepoints
  • Commit/rollback

6. Conditional Constructs

  • IF-THEN
  • IF-THEN-ELSE
  • ELSEIF
  • Nested IF
  • CASE statements
  • NULLIF/COALESCE
  • Loops
  • EXIT WHEN
  • GOTO

7. Exception & Error Handling

  • Reason for
  • Runtime vs. compile-time errors
  • Built-in exceptions
  • How exceptions are ‘raised’/’propagated’

8. SQL With PL/SQL Programming

  • Definition and concepts
  • INSERT, UPDATE and DELETE of PL/SQL records
  • FOR loops and cursors
  • Subqueries
  • REF CURSOR
  • Transaction controls

9. PL/SQL Subprograms

  • Definition
  • Advantages
  • Subprogram types
  • Procedures
  • Functions
  • Parameter passing
  • Invoking
  • Overloading

10. PL/SQL Objects – An Introduction

  • Definition
  • Advantages
  • Structure
  • Initialisation
  • Attributes
  • Constructors
  • Methods
  • Manipulating

11. Introduction To Package Concepts

  • Definition
  • Benefits
  • Specifications
  • Body
  • How to call
  • Private objects
  • Variables and cursors

12. TRIGGERS

  • Definition
  • When to consider
  • BEFORE/AFTER
  • ROW/STATEMENT
  • INSTEAD OF

13. PL/SQL Debugging

  • DBMS_DEBUG
  • ADP_DEBUG
  • How to debug a PL/SQL block
  • How to start the ‘debugger’
  • Breakpoints
  • Executing the debugger
  • ‘Stepping’ the debugger
  • ADP package definition
  • ADP debugger body
  • XXX.SQL strings