Pl Sql

pl sql training

Course Syllabus

pl sql training

Introduction to PL/SQL :

  1. About PL/SQL
  2. PL/SQL Run-Time Architecture
  3. Benefits of PL/SQL
  4. PL/SQL Block Structure
  5. Block Types
  6. Program Constructs
  7. Examining an Anonymous Block
  8. Executing an Anonymous Block
  9. Enabling Output of a PL/SQL Block
  10. Viewing the Output of a PL/SQL Block

Declaring PL/SQL Variables

1. Use of Variables

2. Requirements for Variable Names

3. Handling Variables in PL/SQL

4. Declaring and Initializing PL/SQL Variables

5. Delimiters in String Literals

6. Types of Variables

7. Guidelines for Declaring and Initializing PL/SQL Variables

8. Guidelines for Declaring PL/SQL Variables

9. Naming Conventions of PL/SQL Structures Used in This Course

10. Scalar Data Types

11. Base Scalar Data Types

12. Declaring Scalar Variables

13. %TYPE Attribute

14. Declaring Variables with the %TYPE Attribute

15. Declaring Boolean Variables

16. LOB Data Type Variables

17. Composite Data Types: Records and Collections

18. Bind Variables

19. Referencing Bind Variables

20. Using AUTOPRINT with Bind Variables

Writing Executable Statements

i. Lexical Units in a PL/SQL Block

ii. PL/SQL Block Syntax and Guidelines

iii. Commenting Code

iv. SQL Functions in PL/SQL

v. SQL Functions in PL/SQL: Examples

vi. Using Sequences in PL/SQL Expressions

vii. Data Type Conversion

viii. Nested Blocks

ix. Nested Blocks: Example

x. Variable Scope and Visibility

xi. Using a Qualifier with Nested Blocks

xii. Determining Variable Scope

xiii. Operators in PL/SQL

xiv. Operators in PL/SQL

xv. Programming Guidelines

xvi. Indenting Code

Interacting with Oracle Database Server: SQL Statements in PL/SQL Programs

1. SQL Statements in PL/SQL

2. SELECT Statements in PL/SQL

3. Retrieving Data in PL/SQL

4. Retrieving Data in PL/SQL

5. Naming Ambiguities

6. Naming Conventions

7. Using PL/SQL to Manipulate Data

8. Inserting Data

9. Updating Data

10. Deleting Data

11. Merging Rows

12. SQL Cursor

13. SQL Cursor Attributes for Implicit Cursors

14. Dynamic SQL

Writing Control Structures

1. Controlling Flow of Execution

2. Agenda

3. IF Statement

4. Simple IF Statement

5. IF THEN ELSE Statement

6. IF ELSIF ELSE Clause

7. NULL Value in IF Statement

8. CASE Expressions

9. CASE Statement

10. Handling Nulls

11. Logic Tables

12. Boolean Expressions or Logical Expression

13. Iterative Control: LOOP Statements

14. Basic Loops

15. WHILE Loops

16. FOR Loops

17. Suggested Use of Loops

18. Nested Loops and Labels

19. PL/SQL CONTINUE Statement

Working with Composite Data Types

a)Composite Data Types

b)PL/SQL Records or Collections

c)PL/SQL Records

d)Creating a PL/SQL Record

e)PL/SQL Record Structure

f)%ROWTYPE Attribute

g)Creating a PL/SQL Record

h)Advantages of Using the %ROWTYPE Attribute

i) Another %ROWTYPE Attribute

j) Inserting a Record by Using %ROWTYPE

k)Updating a Row in a Table by Using a Record

l) Associative Arrays (INDEX BY Tables)

m)Associative Array Structure

n)Steps to Create an Associative Array

o)Creating and Accessing Associative Arrays

p)Using INDEX BY Table Methods

q)INDEX BY Table of Records Option

r)INDEX BY Table of Records Option

s)Nested Tables

t)VARRAY

Using Explicit Cursors

a) Cursors

b) Explicit Cursor Operations

c) Controlling Explicit Cursors

d) Declaring the Cursor

e) Opening the Cursor

f) Fetching Data from the Cursor

g) Closing the Cursor

h) Cursors and Records

i) Cursor FOR Loops

j) Explicit Cursor Attributes

k) %ISOPEN Attribute

l) %ROWCOUNT and %NOTFOUND

m) Cursor FOR Loops Using Subqueries

n) Cursors with Parameters

o) FOR UPDATE Clause

p) WHERE CURRENT OF Clause

Handling Exceptions

1. What Is an Exception

2. Handling the Exception

3. Understanding Exceptions with PL/SQL

4. Exception Types

5. Syntax to Trap Exceptions

6. Guidelines for Trapping Exceptions

7. Trapping Predefined Oracle Server Errors

8. Trapping Non-Predefined Oracle Server Errors

9. Non-Predefined Error Trapping: Example

10. Functions for Trapping Exceptions

11. Trapping User-Defined Exceptions

12. Propagating Exceptions in a Subblock

13. RAISE_APPLICATION_ERROR Procedure

Introducing Stored Procedures and Functions

  1. Procedures and Functions
  2. Differences Between Anonymous Blocks and Subprograms
  3. Procedure
  4. Creating a Procedure
  5. Invoking a Procedure
  6. Function: Syntax
  7. Creating a Function
  8. Invoking a Function
  9. Passing a Parameter to the Function
  10. the Function with a Parameter

pl sql training pl sql basics learn sql online learn pl sql online pl sql learning pl sql tutorial sql training online sql course pl sql course sql course onlineonline sql course