Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 | 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.
1 2 3 4 | 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.
1 2 3 4 5 6 | 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.
You will find below topics in this article.
- What is PL/SQL
- Oracle PL/SQL Data Types and Variables and Literals
- Oracle PL/SQL Operators
- Oracle PL/SQL Conditional Statements
- Oracle PL/SQL Loops
- Oracle PL/SQL Procedures and Procedure Parameters
- Oracle PL/SQL Functions
- Oracle PL/SQL Cursor
- Oracle PL/SQL Records
- Oracle PL/SQL Exception
- Oracle PL/SQL Trigger
- Oracle PL/SQL Packages
- Oracle PL/SQL Collections
You can find more information about pl/sql at docs.oracle.com
