SQL is declarative , PL/SQL is procedural

PL/SQL stands for "Procedural Language extensions to SQL." It is tightly integrated with SQL and fully supports SQL data types. It adds procedural programming constructs(IF, ELSE, ERROR, EXCEPTION. etc) to standard relational database language(SQL).PL/SQL is used in Oracle relational database, in the Oracle Server, and in Oracle Forms,Oracle reports(client-side application development tools).
Why do we need PL/SQL : - "To add/shift business logic at the database level."
PL/SQL relies on SQL(standard relational database declarative language) for data retrieval and uses its own constructs for data processing.In other words, PL/SQL integrates data-manipulating power of SQL with the processing power of procedural languages and makes PL/SQL highly flexible, efficient. Other features of PL/SQL can be summarized below and refer this for detailed explanation.

Fast execution - PL/SQL programs are complied and stored in database in executable format,so subprogram calls are efficient and improves response time.
High performance - With PL/SQL program block of statements can be send to database server and it reduces the network bandwidth utilization between client and database.
Portable - PL/SQL programs are portable,Write once and execute any where(Provided Oracle database is installed). Just requires a compilation where we want to use it.
Improves security - Using PL/SQL language, we migrate business logic from application code to database server and by doing so,access to resources can be restricted in more convenient way using grants.
Rich predefined package support- Oracle provides various API's(Predefined packages) that can be invoked from PL/SQL context to do various task like- reading files from local machine, handling HTTP requests, display PL/SQL results , etc. For more details refer this.
Object-Oriented Programming support- Functions and data can be encapsulated and it gives
Web Applications and dynamic web pages development support- Using PL/SQL Gateway and the PL/SQL Web Toolkit, a web application can be developed in PL/SQL.Similarly, PL/SQL scripts can be embedded in HTML source code and it can generate dynamic HTML content when requested. Refer this for more detail.

Okay!!!, so many advantages of PL/SQL, How do we write PL/SQL program. Like other languages, we will write hello world program in PL/SQL. The only prerequisite to write PL/SQL program is, you should have oracle database installed and SQL*Plus client shipped with Oracle can be used to write PL/SQL program.Before writing our first PL/SQL program one of the most important thing need to understand structure of PL/SQL program.
Structure of PL/SQL program:-PL/SQL program consists of Blocks (a logical grouping of related statements and declarations). A typical PL/SQL program consist of the three blocks :

Declaration block (Optional) - variable declarations are placed in it, variable declaration should be done prior to it use, else error occurs.
Executable block - It holds all SQL specific or PL/SQL wrapped SQL statements.
Exception handling block (Optional) - It is one of the optional block structure of PL/SQL program, but it is good to have, since debugging of PL/SQL program is a tedious task and it plays an important role to capture unexpected condition and report it.

PL/SQL program unit blocks looks like this :- Comments in Pl/SQL is done by putting -- in front of statement.Blocks with comments below are self explanatory.

DECLARE    -- Declarative part (optional)
  -- Declarations of local types, variables, &amp; subprograms

BEGIN      -- Executable part (required)
  -- Statements (which can use items declared in declarative part)

[EXCEPTION -- Exception-handling part (optional)
  -- Exception handlers for exceptions raised in executable part]
END;

Write first PL/SQL program in SQL*PLUS or SQL Developer tool:- Follow the following steps for SQL*Plus and SQL Developer, as per your convenience.
Write and execute PL/SQL program in SQL*Plus:-
Step 1.Open SQL*Plus and connect with any schema (here I am connecting with hr, shipped with oracle).
SQL> connect hr
Enter password
Connected.
Step 2. Execute following command to create a file where we will write Pl/SQL program.

SQL> ed program.sql
it will ask to save this program, click yes and copy the following code lines in this file.

set serveroutput on  
--Above syntax is not part of pL/SQL, it is used to enable DBMS output on console

Declare
var VARCHAR2(30);
Begin 
var := 'PL/SQL';
 
dbms_output.put_line('Hello ' || var);

Exception
WHEN no_data_found THEN
null;
End;
/

Step 3. Now close this file and execute PL/SQL code saved in program.sql using following command.
SQL> @program.sql
It will display , Hello PL/SQL on console with PL/SQL procedure successfully execution message. Please refer the following diagram for reference and execution flow(Step 1 o Step 3)

PL/SQL program execution from SQL PLUS

Write and execute PL/SQL program in SQL Developer:-
Step 1.Open SQL developer, right click on the a connection node(let's say hr node) in connection tree and Open SQL worksheet.
Step 2. Copy the above code lines and paste in opened worksheet.
Step 3. Select all in worksheet and click green button on top / (Ctrl+A and Ctrl+Enter).The PL/SQL program is executed and displays :
anonymous block completed
Hello PL/SQL
Refer following diagram for the complete picture stated in step 1 to step 3.

PL/SQL program execution from SQL developer.

Before moving ahead we will go back to the PL/SQL program and understand structure of progam:
1st line of the program: "set serveroutput on " - as mentioned it is not part of PL/SQL program, it means even if we do not give it, PL/SQL program will execute successfully however we will not be able to see output "Hello PL/SQL" in console. Here I have discussed it in more detail:Why do we need "set serveroutput on" to see output on console?
PL/SQL program starts with Declaration block and we have declared a variable var of type var VARCHAR2(for the time being just ignore the syntax and all, just understand the structure of the program). Execution Block start with Begin statement and here we assign value to variable declared in declaration block and using DBMS_OUTPUT we print the value of variable with "Hello ".
Finally , Exception block is just created with null(not doing any thing). This is how, PL/SQL program is created.
Note: The slash character (/) at the end of the example executes the PL/SQL, it is mandatory for PL/SQL program executed in SQL*PLUS, it may be omitted in SQL Developer.
When PL/SQL program is executed - PL/SQL engine process the request and uses SQL statement processor internally to execute SQL statements and PL/SQL programs results sent back to client. Read in detail How does PL/SQL program executed internally in Oracle Database server? Here you will learn performance hit because of context switching between PL/SQL engine to SQL processing unit and vice-versa. (Remember, PL/SQL internally uses SQL statement processor. )

In next post we will continue the fundamentals of PL/SQL language(identifiers,literals, keywords) and followed by the building blocks of PL/SQL languages - data types, loops and other control structures.

Next:PL/SQL - Language fundamentals (identifiers, literal and keyword )and Data Types

SQL is declarative , PL/SQL is procedural - Introduction

1 Comments

Contact Form