Introduction to PL/SQL

1. Introduction to PL/SQL

1
Introduction to PL/SQL
Copyright © 2009, Oracle. All rights reserved.

2. Objectives

After completing this lesson, you should be able to do the
following:
• Explain the need for PL/SQL
• Explain the benefits of PL/SQL
• Identify the different types of PL/SQL blocks
• Output messages in PL/SQL
1-2
Copyright © 2009, Oracle. All rights reserved.

3. About PL/SQL

PL/SQL:
• Stands for “Procedural Language extension to SQL”
• Is Oracle Corporation’s standard data access language for
relational databases
• Seamlessly integrates procedural constructs with SQL
1-3
Copyright © 2009, Oracle. All rights reserved.

4. About PL/SQL

PL/SQL:
• Provides a block structure for executable units of code.
Maintenance of code is made easier with such a welldefined structure.
• Provides procedural constructs such as:
– Variables, constants, and data types
– Control structures such as conditional statements and loops
– Reusable program units that are written once and executed
many times
1-4
Copyright © 2009, Oracle. All rights reserved.

5. PL/SQL Environment

PL/SQL engine
PL/SQL
block
procedural
SQL
Procedural
statement
executor
SQL statement
executor
Oracle database server
1-5
Copyright © 2009, Oracle. All rights reserved.

6. Benefits of PL/SQL

Integration of procedural constructs with SQL
Improved performance
SQL 1
SQL 2
…
SQL
IF...THEN
SQL
ELSE
SQL
END IF;
SQL
1-6
Copyright © 2009, Oracle. All rights reserved.

7. Benefits of PL/SQL

1-7
Modularized program development
Integration with Oracle tools
Portability
Exception handling
Copyright © 2009, Oracle. All rights reserved.

8.

PL/SQL Block Structure
DECLARE (optional)
– Variables, cursors, user-defined exceptions
BEGIN (mandatory)
– SQL statements
– PL/SQL statements
EXCEPTION (optional)
– Actions to perform
when errors occur
1-9
END; (mandatory)
Copyright © 2009, Oracle. All rights reserved.

9. PL/SQL Block Structure

Block Types
Anonymous Procedure Function
1 - 11
[DECLARE]
PROCEDURE name
IS
BEGIN
--statements
BEGIN
--statements
[EXCEPTION]
[EXCEPTION]
FUNCTION name
RETURN datatype
IS
BEGIN
--statements
RETURN value;
[EXCEPTION]
END;
END;
END;
Copyright © 2009, Oracle. All rights reserved.

10.

Program Constructs
1 - 13
Tools Constructs
Database Server
Constructs
Anonymous blocks
Anonymous blocks
Application procedures
or functions
Stored procedures or
functions
Application packages
Stored packages
Application triggers
Database triggers
Object types
Object types
Copyright © 2009, Oracle. All rights reserved.

11. Block Types

Create an Anonymous Block
Enter the anonymous block in the SQL Developer workspace:
1 - 15
Copyright © 2009, Oracle. All rights reserved.

12.

Execute an Anonymous Block
Click the Run Script button to execute the anonymous block:
Run Script
1 - 16
Copyright © 2009, Oracle. All rights reserved.

13. Program Constructs

Test the Output of a PL/SQL Block
Enable output in SQL Developer by clicking the Enable
DBMS Output button on the DBMS Output tab:
Enable DBMS
Output
1
2
DBMS Output
Tab
Use a predefined Oracle package and its procedure:
– DBMS_OUTPUT.PUT_LINE
DBMS_OUTPUT.PUT_LINE(' The First Name of the
Employee is ' || v_fname);
…
1 - 17
Copyright © 2009, Oracle. All rights reserved.

14.

Test the Output of a PL/SQL Block
1 - 18
Copyright © 2009, Oracle. All rights reserved.

15. Create an Anonymous Block

Quiz
A PL/SQL block must consist of the following three sections:
• A Declarative section which begins with the keyword
DECLARE and ends when the executable section starts.
• An Executable section which begins with the keyword
BEGIN and ends with END.
• An Exception handling section which begins with the
keyword EXCEPTION and is nested within the executable
section.
1. True
2. False
1 - 19
Copyright © 2009, Oracle. All rights reserved.

16. Execute an Anonymous Block

Summary
In this lesson, you should have learned how to:
• Integrate SQL statements with PL/SQL program constructs
• Describe the benefits of PL/SQL
• Differentiate between PL/SQL block types
• Output messages in PL/SQL
1 - 20
Copyright © 2009, Oracle. All rights reserved.

17. Test the Output of a PL/SQL Block

Practice 1: Overview
This practice covers the following topics:
• Identifying the PL/SQL blocks that execute successfully
• Creating and executing a simple PL/SQL block
1 - 21
Copyright © 2009, Oracle. All rights reserved.