In focus

Introduction To PL/SQL

In this article we will learn about concepts of PL/SQL. PL/SQL which stands for Procedural Language/Structured Query Language.

Akhil Kumar Mar 22, 2016

PL/SQL stands for Procedural Language/Structured Query Language.

PL/SQL is Extension of SQL Language. It's a language developed by the Oracle corporation and the full name  is procedural language/structured query language. PL/SQL is a high performance transaction processing language and also totally portable. It  provides a built-in interpreted and operating system independent programming language.

PL/SQL Syntax

  1. {DECLARE}  
  2. declaration ststements;  
  3. begin  
  4. executable ststements;  
  5. {EXCEPTION}  
  6. exception handling statements;  
  7. end;  
Advantages of PL/SQL

PL/SQL is a Procedural language/Structured query language in which these are following advantages 
  • Error exceptional handling - It handles the error or exceptions and giving user  friendly error message. 

  • Reduces Network Traffic - PL/SQL nature is Entire block of SQL statement execute to the oracle database engine at once so it's benefit to reduce the Network Traffic.

  • Allowance SQL - It is flexible, robust and simply to learn by the support of PL/SQL. it is very easy and conceptual .

  • Best performance - It engine procedure multiple sql statement simultaneously.

  • Higher productivity - PL/SQL great productivity through enabling using of better tools. 

  • Completely portability - PL/SQL are portable to all operating system and platform on which Oracle database runs.

  • Tight integration with PL/SQL -  work properly within oracle database.

  • Security - PL/SQL can obtain data abstraction, access control. 

  • Intermediate Calculation - PL/SQL Enumeration done fast and oracle engines increase the transaction program.
Procedures in PL/SQL
  • PL/SQL performs one or more specific task and it is same as in other procedure language. Procedure is also called PL/SQL.

  • A procedure language includes a header and a body. The header structure of the name of the procedure and the variables or parameters passed through the procedure. 

  • The body structure or declaration statements, execution statement and exception statement same as a general PL/SQL Block. 

  • A procedure is same as an anonymous Procedure Block but it is named for repeated usage.
  1. create or replace procedure add_new  
  2. is  
  3. x1 num:=$x1;  
  4. x2 num:=$x2;  
  5. x3=num;  
  6. begin  
  7. x3=x1+x2;  
  8. dbms_output.put_line(x3);  
  9. end add_new;  
Types of Loops in PL/SQL

There are three types of loop in the Pl/Sql.

Basic Loop

A Basic Loop is utilized when an arrangement of segments is to be executed at any rate once before the loop ends. An EXIT condition must be determined in the loop, generally the loop will get into an unbounded number of emphasis.

  1. DECLARE  
  2. x num := 8;  
  3. BEGIN  
  4. LOOP  
  5. dbms_output.put_line(x);  
  6. x := x + 8;  
  7. IF x > 39 THEN  
  8. exit;  
  9. END IF;  
  10. END LOOP;  
  11. -- after exit, control resumes here  
  12. dbms_output.put_line('After Exit x is: ' || x);  
  13. END;  
  14. /  
While Loop

The while loop is enclosed by the keywords LOOP and END LOOP. Before each iteration of the loop, the expression is evaluated. If the expression returns true loop runs otherwise exit from loop.

The while loop is enclosed by the keywords LOOP and END LOOP, prior to every iteration of the loop, the expression is evaluated. On the off chance that the expression returns true LOOP runs generally exit from LOOP.

  1. DECLARE  
  2. a num(3) := 10;  
  3. BEGIN  
  4. WHILE a < 30 LOOP  
  5. dbms_output.put_line('value of a: ' || a);  
  6. a := a + 1;  
  7. END LOOP;  
  8. END;  
  9. /  
For Loop

Numeric for loop loops repeat over a predetermined scope of whole numbers. The extent is a piece of a loop plan, which is enclosed by the keywords for and loop.

  1. DECLARE  
  2. a number(3);  
  3. BEGIN  
  4. FOR a in 11 .. 21 LOOP  
  5. dbms_output.put_line('value of a: ' || a);  
  6. END LOOP;  
  7. END;  
  8. /  

oracle pl/sql programming query