In this article, we learn about the basics of PL/SQL.
- PL/SQL was developed by Oracle Corporation in the 90s to enhance the SQL.
- PL/SQL is a completely portable, high-performance transaction processing language.
- It provides a completely built-in interpreted and OS independent programming environment.
FEATURES OF PL/SQL
- PL/SQL is tightly integrated with SQL.
- It offers extensive error checking.
- It offers numerous data types.
- It offers a variety of programming structures.
- It supports structured programming through functions and procedures.
- It supports object-oriented programming.
- It supports the development of web applications and server pages.
PL/SQL SYNTAX
- PL/SQL syntax is based on that of ADA and pascal programming language.
- The basic syntax of PL/SQL is a block-structured language.
- PL/SQL programs are divided and written in the logical block of code. Each block consists of three subparts.
- Every PL/SQL statement ends with a semicolon (;). PL/SQL blocks can be nested within other PL/SQL blocks using BEGIN and END.
SYNTAX OF PL/SQL:
DECLARE
<declaration syntax>
BEGIN
<executable commands>
EXCEPTION
<exception handling>
END;
EXAMPLE OF PL/SQL:
DECLARE
message varchar2(20):= ‘Hello!’;
BEGIN
dbms_output.put_line(message);
END;
SECTIONS AND DESCRIPTION
DECLARATIONS:
- This section starts with the keyword DECLARE.
- It is an optional section and defines all variables, cursors, subprograms, and other elements to be used in the program.
EXECUTABLE COMMANDS:
- This section is enclosed between the keyword BEGIN and END. It is a mandatory section.
- It consists of the executable/SQL statement of the program.
- It should have at least one executable line of code, which may be just a NULL command to indicate that nothing should be executed.
EXCEPTION HANDLING:
- This section starts with the keyword EXCEPTION.
- This optional section contains exceptions that handle errors in the program.
If you are a newbie to database learning — SQL Server recommended is the following must-watch video: -
