Oracle PL SQL Tutorial

Welcome to Oracle PL SQL Tutorials. The objective of these tutorials is to provide an in-depth understanding of Oracle PL SQL.

In addition to free Oracle Oracle PL SQL  Tutorials, we will cover common interview questions, issues, and how to’s of Oracle PL SQL .

Introduction of Oracle PL SQL Tutorials

PL/SQL stands for Procedural Language extensions to the Structured Query Language (SQL). SQL is a powerful language for both querying and updating data in relational databases.

Oracle created PL/SQL that extends some limitations of SQL to provide a more comprehensive solution for building mission-critical applications running on the Oracle database.

PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL.

PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java.

This Topic will give you a great understanding on PL/SQL to proceed with Oracle database and other advanced RDBMS concepts.

The PL/SQL programming language was developed by Oracle Corporation in the late 1980s as a procedural extension language for SQL and the Oracle relational database. The following are notable facts about PL/SQL:

-PL/SQL is a completely portable, high-performance transaction-processing language.

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

-PL/SQL can also directly be called from the command-line SQL*Plus interface.

-Direct call can also be made from external programming language calls to database.

-PL/SQL's general syntax is based on that of ADA and Pascal programming language.

-Apart from Oracle, PL/SQL is available in TimesTen in-memory database and IBM DB2.

Learn Oracle PL SQL by Tekslate - Fastest growing sector in the industry.
Explore Online Oracle PL SQL Training and course is aligned with industry needs & developed by industry veterans.
Tekslate will turn you into Oracle PL SQL Expert.

Features of PL/SQL

PL/SQL has the following features:

-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 developing web applications and server pages.

PL/SQL Block consists of three sections:

-The Declaration section (optional).

-The Execution section (mandatory).

-The Exception Handling (or Error) section (optional).

Declaration Section:

The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.

Execution Section:

The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement,s and SQL statements form the part of the execution section.

Exception Section:

The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.

Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.

SQL Command Categories

SQL commands are grouped into four major categories depending on their functionality. They are as follows:

Data Definition Language (DDL)

These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.

Data Manipulation Language (DML)

These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE.

Transaction Control Language (TCL)

These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.

Data Control Language (DCL)

These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

Advantages of PL/SQL

PL/SQL has the following advantages:

-SQL is the standard database language and PL/SQL is strongly integrated with SQL. PL/SQL supports both static and dynamic SQL. Static SQL supports DML operations and transaction control from the PL/SQL block. Dynamic SQL is SQL that allows embedding DDL statements in PL/SQL blocks.

-PL/SQL allows sending an entire block of statements to the database at one time. This reduces network traffic and provides high performance for the applications.

-PL/SQL gives high productivity to programmers as it can query, transform, and update data in a database.

-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 a high-security level.

-PL/SQL provides access to predefined SQL packages.

-PL/SQL provides support for Object-Oriented Programming.

-PL/SQL provides support for Developing Web Applications and Server Pages.