Lesion 1 oracle

Introduction to PL/SQL

Copyright © 2004, Oracle. All rights reserved.

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 • Use iSQL*Plus as a development environment for PL/SQL • Output messages in PL/SQL

1-2

Copyright © 2004, Oracle. Allrights reserved.

What Is PL/SQL?

PL/SQL: • Stands for Procedural Language extension to SQL • Is Oracle Corporation’s standard data access language for relational databases • Seamlessly integratesprocedural constructs with SQL

1-3

Copyright © 2004, Oracle. All rights reserved.

About PL/SQL

PL/SQL: • Provides a block structure for executable units of code. Maintenance of code ismade easier with such a well-defined structure. • Provides procedural constructs such as:
– Variables, constants, and types – Control structures such as conditional statements and loops – Reusableprogram units that are written once and executed many times

1-4

Copyright © 2004, Oracle. All rights reserved.

PL/SQL Environment

PL/SQL Engine procedural PL/SQL Block SQL ProceduralStatement Executor

SQL Statement Executor Oracle Database Server

1-5

Copyright © 2004, Oracle. All rights reserved.

Benefits of PL/SQL

• •

Integration of procedural constructs with SQLImproved performance
SQL 1 SQL 2 …

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

1-6

Copyright © 2004, Oracle. All rights reserved.

Benefits of PL/SQL

• • • •

Modularized program developmentIntegration with Oracle tools Portability Exception handling

1-7

Copyright © 2004, Oracle. All rights reserved.

Benefits of PL/SQL

1-8

Copyright © 2004, Oracle. All rights reserved.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...