PL/SQL Blocks || Anonymous blocks Introduction

---

Blocks :
=====
Block is a grouped code or set of statements and these blocks are classified into two types.

1. Anonymous blocks 
2. Sub programs

Anonymous blocks :
==============

• Anonymous blocks are those blocks which we can’t store permanently in the database. Once the user log off from the database these blocks are completely vanished or destroyed.
• These blocks don’t have any proper name that’s why these blocks can be called as unnamed PL/SQL blocks.

Sub programs :
==========

• Sub programs are those blocks which we can store permanently as an object. So that at any particular point of time the end user can access these blocks.
• These blocks saved with a proper name with in the database that’s why these blocks can be called as named PL/SQL blocks.

Anonymous block structure :

PL/SQL block contains four sections

•  Declare
•  Begin
•  Exception
•  end

Note: In these sections declare and exception sections are optional, begin and end sections are mandatory.

Basic PL/SQL block structure

SQL> declare
-------
// declare variables used to write PL/SQL code
-------

begin
Statement-1
|
|
|
Statement-n
end;

Example 1:write a PL/SQL block to display www.TechsNib.com

SQL> begin
dbms_output.put_line(‘www.TechsNib.com’);
end;

SQL>/
PL/SQL Procedure successfully completed

SQL> set serveroutput on
www.TechsNib.com

Note:
Basically the use of SET SERVEROUTPUT is to display the query answer in SQL *PLUS interface.

When you use the DBMS_OUTPUT.PUT_LINE procedure, the procedure will write the passing string into the Oracle buffer. In order to print the content of the Oracle buffer, you should use the SET SERVEROUTPUT command to display the content of the Oracle buffer into your screen. You can also increase the size of the buffer.
Use the “Set serveroutput on” to display the buffer used by dbms_output.

Example 2: Write a PL/SQL block input two numbers and find the sum.

SQL> declare
a number:=5;
b number:=10;
begin
dbms_output.put_line(‘sum is’ || (a+b));
end;

(or)

SQL> declare
a number:=&a;
b number:=&b;
begin
dbms_output.put_line(‘sum is’ || (a+b));
end;

Note: '&' is used to provide runtime values to variables.

Example 3: Write a PL/SQL block input two numbers and interchange the values.

SQL> declare
a number:=&a;
b number:=&b;
c number;
begin
c:=a; -- b:=a-b; -- a:=a+b; //'--' is used to write the comments in PL/SQL Program
a:=b; -- a:=a-b; -- b:=a-b;
b:=c; -- b:=a+b; -- a:=a-b;
dbms_output.put_line(‘a value is’ || a);
dbms_output.put_line(‘b value is’ || b);
end;

---