What is PL/SQL

Yusuf SEZER

4 years ago

What is PL SQL and what is it used for?

PL/SQL(Procedural Language extensions to SQL) is basically a programming language in Oracle database. PL/SQL allows to use structures such as if, loop, while, function, and procedure in programming languages.

Many databases use the data query language called SQL. However, advanced database management systems such as Oracle, SQL Server meet the features in various programming languages with additional languages to use SQL language flexibly.

SQL Server meets this need with T-SQL and Oracle meets this need with PL/SQL.

The main feature of these languages is that they allow the use of variables, conditional expressions, loops, functions and procedures.

Thanks to these features, operations such as inserting data and processing on data are made easier.

In fact, these operations can be done by programming languages by connecting to the database.

However, since languages such as T-SQL, PL/SQL are included in the database and compiled beforehand, it enables faster processing.

PL/SQL Structure

In many sources you can see that the PL/SQL language is passed as a block-based language.

This is because the commands written are in blocks.

The PL/SQL structure is as follows.

DECLARE
   -- definitions
BEGIN
   -- commands
EXCEPTION
   -- error management
END;

DECLARE

-- definitions

BEGIN

-- commands

EXCEPTION

-- error management

END;

The DECLARE keyword in the PL/SQL structure is the part where the variables to be used in the PL/SQL commands are defined and it is not mandatory.

BEGIN and END is the part where SQL and PL/SQL commands will be run and it is mandatory to use.

The part determined by the EXCEPTION keyword is the area used to find and manage errors in SQL or PL/SQL commands and it is not mandatory.

What is pl/sql used for?

PL/SQL can be used after Oracle installation, there is no need for an additional installation.

PL/SQL commands are used by writing in SQL Plus or SQL Developer tool in Oracle.

You can see the sample code that writes Hello Oracle on the screen.

SET SERVEROUTPUT ON;
BEGIN
   DBMS_OUTPUT.put_line('Hello Oracle!');
END;

SET SERVEROUTPUT ON;

BEGIN

DBMS_OUTPUT.put_line('Hello Oracle!');

END;

You can see the sample code that writes Hello Oracle on the screen using variable.

SET SERVEROUTPUT ON;
DECLARE
    v_message VARCHAR2(20) := 'Hello Oracle!';
BEGIN
   DBMS_OUTPUT.put_line (v_messagge);
END;

SET SERVEROUTPUT ON;

DECLARE

v_message VARCHAR2(20) := 'Hello Oracle!';

BEGIN

DBMS_OUTPUT.put_line (v_messagge);

END;

NOTE: Oracle PL/SQL uses the syntax of Pascal and ADA languages.

You can find more detailed information about below topics in the below link.

PL/SQL Tutorial

You will find below topics in this article.

You can find more information about pl/sql at docs.oracle.com