PL/SQL Tutorial ~ Hub Of Geekz

Introduction to PL/SQL

During 1980s we were using the SQL which is not a procedural language, so because of this we were facing a lot of issues. So a new programming language was developed by Oracle Corporation in the late 1980s as procedural extension language for SQL and the Oracle relational database which was named as PL/SQL.

PL/SQL stands for Procedural Language extension of SQL.PL/SQL is a combination of SQL along with the procedural features of programming languages.

Features of PL/SQL

PL/SQL has the following features:

· PL/SQL is tightly integrated with SQL

· It offers extensive error checking

· It is completely portable and high performance language

· It offers a variety of programming structures

· It supports structured programming through functions and procedures

· It supports developing web applications and server pages

· PL/SQL provides a built-in interpreted and OS independent programming environment.

· PL/SQL saves time on design and debugging by strong features, such as exception handling, encapsulation, data hiding, and object-oriented data types.

· Applications written in PL/SQL are fully portable.

· PL/SQL provides high security level.

· PL/SQL provides access to predefined SQL packages.

A Simple PL/SQL Block

PL/SQL block consists of 3 sections:

The Declaration section (optional).
The Execution section (mandatory).
The Exception Handling section (optional).

Declaration Section:

This section is optional and is used to declare any placeholders. These placeholders can be variables, constants cursors etc. The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE.

Execution Section:

Unlike declaration section, it is a mandatory section where the logic of the program will be written. The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. We can use different types of constructs as for, while loops and conditional statements in this section. All the SQL queries will be also part of this section.

Exception Section:

This section is optional. The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. So if we think that there can be any error or exception then we can use this section and handle that exception.

DECLARE
     Variable declaration
BEGIN
     Program Execution
EXCEPTION
     Exception handling
END;

Simple Program:

DECLARE

msg_to_user varchar2(20):= 'Hello, World! This is my first program';

BEGIN

dbms_output.put_line(msg_to_user );

END;

Hello, World! This is my first program

PL/SQL procedure successfully completed.