PL/SQL Block

PL/SQL Block:

 Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block. 

 A PL/SQL Block consists of three sections: 
The Declaration section (optional).
The Execution section (mandatory).
The Exception (or Error) Handling section (optional).
Declaration Section:
 The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section. 

Execution Section:
 The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section. 

Exception Section:
 The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors. 

 Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code. 

 This is how a sample PL/SQL Block looks.

DECLARE 
      Variable declaration
 BEGIN 
      Program Execution 
 EXCEPTION 
      Exception handling
 END;

Advantages of PL/SQL

Advantages of PL/SQL

 These are the advantages of PL/SQL. 
 Block Structures: PL SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.

 Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).

 Better Performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic. 

Error Handling: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.

cheack whether given number is palindrome or not.

write a pl/sql program to cheack whether given number is palindrome or not.

set serveroutput on;
Declare
a number(4);
b number(4);
n number(4);
Begin
a:=&a;
b:=0;
n:=a;
while n <>0
loop
b:=10*b+n mod 10;
n:=n/10;
end loop;
if a=b then
dbms_output.put_line('given number is palindromial number ');
else
dbms_output.put_line('given number is not palindromial number ');
end if;
end;
/

Given Table

Given Table
set serveroutput on;
Declare
n number(4);
a number(4);
 Begin
n:=&n;
a:=n;
while a<=10*n
loop
dbms_output.put_line(a);
a:=a+n;
end loop;
end;
/

Sum of 100 odd numbers using While loop

Sum of 100 odd numbers using While loop

  declare
  n number;
  endvalue number;
  sum1 number default 0;
  begin
  endvalue:=&endvalue;
  n:=1;
  while(n<endvalue)
  loop
 sum1:=sum1+n;
 n:=n+2;
 end loop;
 dbms_output.put_line('Sum of Odd numbers between 1 and '|| endvalue   ||'  is '||sum1);
 end;
 /

Enter value for endvalue: 30
old   endvalue:=&endvalue;
new   endvalue:=30;
Sum of Odd numbers between 1 and 30  is 225

Sum of Odd Numbers using for loop

Sum of Odd Numbers using for loop


 declare
 n number;
 sum1 number default 0;
 endvalue number;
 begin
 endvalue:=&endvalue;
 n:=1;
 for n in 1..endvalue
 loop
if mod(n,2)=1
then
sum1:=sum1+n;
end if;
end loop;
dbms_output.put_line(' sum  =  '||sum1);
end;

SQL> /
Enter value for endvalue: 30
old   endvalue:=&endvalue;
new   endvalue:=30;

sum  =  225

cheack whether given number is palindrome or not.

write a pl/sql program to cheack whether given number is palindrome or not.

set serveroutput on;
Declare
a number(4);
b number(4);
n number(4);
Begin
a:=&a;
b:=0;
n:=a;
while n <>0
loop
b:=10*b+n mod 10;
n:=n/10;
end loop;
if a=b then
dbms_output.put_line('given number is palindromial number ');
else
dbms_output.put_line('given number is not palindromial number ');
end if;
end;
/