Introduction to PL/SQL

Objectives

After completing this lesson, you should be able to do the

About PL/SQL

PL/SQL:
Stands for “Procedural Language extension to SQL”
Is Oracle Corporation’s standard

About PL/SQL

PL/SQL:
Provides a block structure for executable units of code. Maintenance

PL/SQL Environment

PL/SQL engine

Oracle database server

SQL statement executor

Procedural statement executor

procedural

SQL

PL/SQL block

Benefits of PL/SQL

Integration of procedural constructs with SQL
Improved performance

SQL
IF...THEN
SQL
ELSE
SQL
END IF;
SQL

SQL 1

SQL

Benefits of PL/SQL

Modularized program development
Integration with Oracle tools
Portability
Exception handling

PL/SQL Block Structure

DECLARE (optional)
Variables, cursors, user-defined exceptions
BEGIN (mandatory)
SQL statements
PL/SQL statements
EXCEPTION (optional)
Actions

Block Types

Anonymous Procedure Function

[DECLARE]
BEGIN
--statements
[EXCEPTION]
END;

PROCEDURE name
IS
BEGIN
--statements
[EXCEPTION]
END;

FUNCTION name
RETURN datatype
IS
BEGIN
--statements
RETURN

Program Constructs

Application triggers

Application packages

Application procedures
or functions

Anonymous blocks

Tools Constructs

Object types

Database triggers

Stored packages

Stored

Create an Anonymous Block

Enter the anonymous block in the SQL Developer

Execute an Anonymous Block

Click the Run Script button to execute the

Test the Output of a PL/SQL Block

Enable output in SQL Developer

Test the Output of a PL/SQL Block

Quiz

A PL/SQL block must consist of the following three sections:
A Declarative

Summary

In this lesson, you should have learned how to:
Integrate SQL statements

Practice 1: Overview

This practice covers the following topics:
Identifying the PL/SQL blocks