Difference Between sql and plsql: Insight with Example Query

Difference Between pl sql and sql | Difference Between sql and plsql | what is Difference Between sql and plsql | sql and plsql | plsql and sql

Overview

In this article, we are going to learn the difference Between SQL and PLSQL. I will explain PLSQLand SQL in brief first. Afterward, we are going to discuss their difference. Then, I will explain difference with real-time scenario code. So, It’s my humble request to read this article ultimately to gain and spread knowledge.

About SQL

SQL is a Structured Query language that operates the database. It includes database creation, deletes, retrieval of specific data from the database.SQL is an ANSI (American National Standards Institute) standard language, but there are many different versions of SQL language.

SQL is a standardized programming language that is used to manage relational databases and perform various operations in their data.SQL is a structured query language, a computer language for storing, manipulating, and retrieving store data in a relational database. It’s necessary to understand SQL before I would discuss the difference Between pl SQL and PLSQL. So, I am taking it as you know SQL basics now.

Also Read: How to get minutes and seconds from datetime in SQL?

About PL/SQL

PL/SQL is programmable SQL, which means we can do the programming by writing SQL queries. PL/SQL is a programming language SQL. It is used to write entire programs with variables, loops, operators, etc. It is mainly used to select/insert/update/delete. It supports the back-end of application languages such as Java, PHP, etc. It allows those reports, web pages, and screens to be created, formatted, and displayed. Now we can discuss the “difference Between SQL and PLSQL” as we know SQL and PL/SQL both.

PL/SQL enables a code block to be executed that improves its performance. It can be procedure, function, cursors, triggers, or loops. PL/SQL is intended for the creation of server pages and web applications. PL/SQL inhibits such characteristics as encapsulation, hiding of data, exceptions, and object-based sort of data.

Difference At a Glance

We can summarize key difference between sql and plsql as follows:

Difference Between sql and plsql

We can differentiate thes on basis of following key points:

Full Form

SQL stands for Structured Query Language whereas PL/SQL stands for Programmable Structured Query Language.

Basic Usage

In SQL, we can execute single command or query at a time. The query can be and DDL (Data Definition Language) or DML (Data manipulation language). DDL is used to add, remove or modify tables(db objects). DML is used to Insert, update or delete data from tables.

On the other hand, PL/SQL is bloc of commands or code which helps us to accomplish multiple task at a time. It can consist of several SQL queries, loops, functions. So many tasks are performed at a time here.

Use of variable

We don’t have variable in sql, on the other hand PL/Sql has variable data type, etc.

Loops

We can not use loops in SQL where as we can use loops in PL/Sql. We can add if/else and other conditional statement in pl sql. PL/Sql supports al types of control statement but SQL does not.

Example code

We can use Control Structures as shown below only in PL/Sql.

    if (db_name() != N'master')
    begin
        raiserror(5001, 16,-1)
        return 1
    end

Type of Language

Sql is declarative language. So, SQL advises the client what to do but not how to  do so. whereas PL/Sql is procedural language. So, PL/SQL says how to do work to the database

Example

Please have a look at below example, the will show this difference

SELECT * FROM tableName; -- SQL 
-- PL/SQL
USE [MVCFinalExam]
GO
/****** Object:  StoredProcedure [sys].[sp_changesubscriber_schedule]    Script Date: 28-11-2020 15:35:59 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER procedure [sys].[sp_changesubscriber_schedule]
(
	@subscriber						sysname,
	@agent_type						smallint,
	@frequency_type					int = NULL,
	@frequency_interval				int = NULL,
	@frequency_relative_interval	int = NULL,
	@frequency_recurrence_factor	int = NULL,
	@frequency_subday				int = NULL,
	@frequency_subday_interval		int = NULL,
	@active_start_time_of_day		int = NULL,
	@active_end_time_of_day			int = NULL,
	@active_start_date				int = NULL,
	@active_end_date				int = NULL,
	@publisher						sysname = NULL
)
AS
BEGIN
	DECLARE @cmd			nvarchar(4000)
	DECLARE @retcode		int
	DECLARE @publisher_type	sysname
	SET @retcode = 0
	
	EXEC @retcode = sys.sp_MSrepl_getpublisherinfo	@publisher		= @publisher,
													@rpcheader		= @cmd            OUTPUT,
													@publisher_type	= @publisher_type OUTPUT
	
	IF @retcode <> 0
		RETURN (@retcode)
	-- Add sp
	SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT
	set @cmd = @cmd + N'sys.sp_MSrepl_changesubscriber_schedule'
	
	EXEC @retcode = @cmd
					@subscriber,
					@agent_type,
					@frequency_type,
					@frequency_interval,
					@frequency_relative_interval,
					@frequency_recurrence_factor,
					@frequency_subday,
					@frequency_subday_interval,
					@active_start_time_of_day,
					@active_end_time_of_day,
					@active_start_date,
					@active_end_date,
					@publisher,
					@publisher_type
	RETURN (@retcode)
END

Purpose

SQL is a language for choosing and editing data sets that is data-oriented. On the other hand PL / SQL is an application programming language.

Communication With Database

SQL communicates directly with the database server. But, PL/SQL does not communicate explicitly with the database server.

Dependency

PL/SQL can have SQL but SQl queries cannot have PL/SQL.

Exception Handling

We can use exception handling in PL/SQL. We can use try/catch block inside PL/SQL. Whereas we can’t do same in plain SQL. SQL queries can’t have this.

An Example of complex query

PL/SQL Example

USE [MVCFinalExam]
GO
/****** Object:  StoredProcedure [sys].[sp_who]    Script Date: 28-11-2020 15:44:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [sys].[sp_who]  --- 1995/11/28 15:48
	   @loginame sysname = NULL --or 'active'
as
declare	 @spidlow	int,
		 @spidhigh	int,
		 @spid		int,
		 @sid		varbinary(85)
select	 @spidlow	=     0
		,@spidhigh	= 32767

if (	@loginame is not NULL
   AND	upper(@loginame collate Latin1_General_CI_AS) = 'ACTIVE'
   )
	begin
	select spid , ecid, status
			  ,loginame=rtrim(loginame)
	      ,hostname ,blk=convert(char(5),blocked)
	      ,dbname = case
						when dbid = 0 then null
						when dbid <> 0 then db_name(dbid)
					end
		  ,cmd
		  ,request_id
	from  sys.sysprocesses_ex
	where spid >= @spidlow and spid <= @spidhigh AND
	      upper(cmd) <> 'AWAITING COMMAND'
	return (0)
	end
if (@loginame is not NULL
   AND	upper(@loginame collate Latin1_General_CI_AS) <> 'ACTIVE'
   )
begin
	if (@loginame like '[0-9]%')	-- is a spid.
	begin
		select @spid = convert(int, @loginame)
		select spid, ecid, status,
			   loginame=rtrim(loginame),
			   hostname,blk = convert(char(5),blocked),
			   dbname = case
							when dbid = 0 then null
							when dbid <> 0 then db_name(dbid)
						end
			  ,cmd
			  ,request_id
		from  sys.sysprocesses_ex
		where spid = @spid
	end
	else
	begin
		select @sid = suser_sid(@loginame)
		if (@sid is null)
		begin
			raiserror(15007,-1,-1,@loginame)
			return (1)
		end
		select spid, ecid, status,
			   loginame=rtrim(loginame),
			   hostname ,blk=convert(char(5),blocked),
			   dbname = case
							when dbid = 0 then null
							when dbid <> 0 then db_name(dbid)
						end
			   ,cmd
			   ,request_id
		from  sys.sysprocesses_ex
		where sid = @sid
	end
	return (0)
end

-- loginame arg is null
select spid,
	   ecid,
	   status,
	   loginame=rtrim(loginame),
	   hostname,
	   blk=convert(char(5),blocked),
	   dbname = case
					when dbid = 0 then null
					when dbid <> 0 then db_name(dbid)
				end
	   ,cmd
	   ,request_id
from  sys.sysprocesses_ex
where spid >= @spidlow and spid <= @spidhigh
return (0) -- sp_who

SQL Example

Select * from table_name

FAQ

Closing Notes

SQL is a declarative language, it only defines which data is required. But PL/SQL is a procedural language that defines both what data is expected and how it can be extracted.