Difference Between SQL and PLSQL

Difference Between SQL and PLSQL

Key Differences

There are many differences between SQL and PL/SQL. SQL is a non-procedural language that executes a single query at a time whereas, PL/SQL is a procedural language and executes blocks of code at once which helps reduce traffic and increases processing speed. PL/SQL provides support features such as variables, conditional (if-else statements), and iterative (loops such as for, while) constructs. SQL does not provide support for these features. PL/SQL also offers error and exception-handling features that do not exist in SQL. PL/SQL is used to write a programming block that has procedures, functions, triggers, packages, and variables within its syntax. On the other hand, SQL queries and commands are written using DDL and DML.

Sr. No.	Comparison	SQL	PL/SQL
1	Definition	It is a database Structured Query Language.	It is a database programming language using SQL.
2	Variables	Variables are not available in SQL.	Variables, constraints, and data types features are available in PL/SQL.
3	Control structures	No Supported Control Structures like for loop, if, and other.	Control Structures are available like, for loop, while loop, if, and other.
4	Nature of Orientation	It is a Data-oriented language.	It is an application-oriented language.
5	Operations	Query performs the single operation in SQL.	PL/SQL block performs Group of Operation as a single block resulting in reduced network traffic.
6	Declarative/ Procedural Language	SQL is a declarative language.	PL/SQL is a procedural language.
7	Embed	SQL can be embedded in PL/SQL.	PL/SQL can’t be embedded in SQL.
8	Interaction with Server	It directly interacts with the database server.	It does not interact directly with the database server.
9	Exception Handling	SQL does not provide error and exception handling.	PL/SQL provides error and exception handling.
10	Writes	It is used to write queries using DDL (Data Definition Language) and DML (Data Manipulation Language) statements.	The code blocks, functions, procedures triggers, and packages can be written using PL/SQL.
11	Processing Speed	SQL does not offer a high processing speed for voluminous data.	PL/SQL offers a high processing speed for voluminous data.
12	Application	You can fetch, alter, add, delete, or manipulate data in a database using SQL.	You can use PL/SQL to develop applications that show information from SQL in a logical manner.

 

Oracle / PLSQL: PIVOT

 

Oracle / PLSQL: PIVOT Clause

This Oracle tutorial explains how to use the Oracle PIVOT clause with syntax and examples.

Description

The Oracle PIVOT clause allows you to write a cross-tabulation query starting in Oracle 11g. This means that you can aggregate your results and rotate rows into columns.

Syntax

The syntax for the PIVOT clause in Oracle/PLSQL is:

SELECT * FROM
(
  SELECT column1, column2
  FROM tables
  WHERE conditions
)
PIVOT 
(
  aggregate_function(column2)
  FOR column2
  IN ( expr1, expr2, ... expr_n) | subquery
)
ORDER BY expression [ ASC | DESC ];

Parameters or Arguments

aggregate_function

It can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.

Example

CREATE TABLE orders
( order_id integer NOT NULL,
  customer_ref varchar2(50) NOT NULL,
  order_date date,
  product_id integer,
  quantity integer,
  CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

To show you the data for this example, we will select the records from the orders table with the following SELECT statement:

SELECT order_id, customer_ref, product_id
FROM orders
ORDER BY order_id;

These are the records in the orders table. We will be using these records to demonstrate how the PIVOT clause works:

order_id	customer_ref	product_id
50001	SMITH	10
50002	SMITH	20
50003	ANDERSON	30
50004	ANDERSON	40
50005	JONES	10
50006	JONES	20
50007	SMITH	20
50008	SMITH	10
50009	SMITH	20

Now, let's create our cross-tabulation query using the following PIVOT clause:

SELECT * FROM
(
  SELECT customer_ref, product_id
  FROM orders
)
PIVOT
(
  COUNT(product_id)
  FOR product_id IN (10, 20, 30)
)
ORDER BY customer_ref;

In this example, the PIVOT clause would return the following results:

customer_ref	10	20	30
ANDERSON	0	0	1
JONES	1	1	0
SMITH	2	3	0

Now, let's break apart the PIVOT clause and explain how it worked.

Specify Fields to Include

First, we want to specify what fields to include in our cross tabulation. In this example, we want to include the customer_ref and product_id fields. This is done by the following portion of the statement:

(
  SELECT customer_ref, product_id
  FROM orders
)