Introduction to PL/SQL

Introduction to PL/SQL






What is PL/SQL?
PL/SQL stands for Procedural Language extension of SQL.

PL/SQL is a combination of SQL along with the procedural features of programming languages.

It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.


The PL/SQL Engine:

Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can be stored in the client system (client-side) or in the database (server-side).

About This PL SQL Programming Tutorial
This Oracle PL SQL tutorial teaches you the basics of database programming in PL/SQL with appropriate PL/SQL tutorials with examples. You can use these free online tutorials as your guide to practice, learn, for training, or reference while programming with PL SQL. I will be making more Oracle PL SQL programming tutorials as often as possible to share my knowledge in PL SQL and help you in learning PL SQL better.

Even though the programming concepts discussed in this tutorial are specific to Oracle PL SQL. The concepts like cursors, functions and stored procedures can be used in other database systems like Sybase , Microsoft SQL server etc, with some change in SQL syntax. This PL/SQL tutorial will be growing regularly; let us know if any topic related to PL SQL needs to be added or you can also share your knowledge on PL SQL with us. Lets share our knowledge about PL SQL with others.



A Simple PL/SQL Block:
Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.

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.

How a Sample PL/SQL Block Looks

DECLARE 
     Variable declaration
BEGIN 
     Program Execution 
EXCEPTION 
     Exception handling
END;

Read More

ORACLE-FORU - PL/SQL code for SPACE AFTER EACH LETTER

pl/sql program to insert a space after each  letter in a  given string.

 Procedure:

declare
     str varchar(10):='&str';
     len number(2);
      i number(3):=1;
      newstr varchar(20);
 begin
    len:=length(str);
     while i<=len
     loop
        newstr:=newstr||substr(str,i,1)||' ';
         i:=i+1;
     end loop;
      dbms_output.put_line(newstr);
    end;
     /

Execution:

SQL> @e:\plsql\9b.sql
Enter value for str: svcet
old   2:   str varchar(10):='&str';
new   2:   str varchar(10):='svcet';

s v c e t

PL/SQL procedure successfully completed.


Conclusion:
a pl/sql program is successfully executed for to insert a space after each letter.
INSERTING A ROW USING PL/SQL

Read More

ORACLE-FORU - PL/SQL code for LETTER BY LETTER

pl/sql program to print a string in a letter by letter format.

Procedure:

  declare
      str varchar(10):='&str';
      len number(2);
      i number(3):=1;
  begin
      len:=length(str);
      while i<=len
      loop
          Dbms_output.put_line(substr(str,i,1));
           i :=i+1;
     end loop;
   end;
  /

Output:

SQL> @e:\plsql\9a.sql
Enter value for str: svcet
old   2:   str varchar(10):='&str';
new   2:   str varchar(10):='svcet';
s
v
c
e
t

PL/SQL procedure successfully completed.

Conclusion:
       a pl/sql program is successfully executed for printing a string letter by letter.

Read More

ORACLE-FORU - PL/SQL code to check given number is EVEN OR ODD

pl/sql program to find given number is even or odd.

Procedure:

Declare
     n number(4):=&n;
 Begin
     if mod(n,2)=0
    then
       dbms_output.put_line(n||' even number');
    else
      dbms_output.put_line(n||' odd number');
   end if;
end;
/

Execution:

SQL> @e:\plsql\even.sql
Enter value for n: 5
old   2:  n number(4):=&n;
new   2:  n number(4):=5;

5 odd number

PL/SQL procedure successfully completed.

Conclusion:
       a pl/sql program is successfully executed for finding even or odd.

Read More

ORACLE-FORU - PL/SQL code for FIBINOCCI SERIES

pl/sql program to generate fibinocci series.

Procedure:
   declare
      a number(3):=1;
      b number(3):=1;
      c number(3);
      n number(3):=&n;
begin
  Dbms_output.put_line('the fibinocci series is:');
  while a<=n
    loop
        dbms_output.put_line(a);
        c:=a+b;
        a:=b;
        b:=c;
     end loop;
  end;
 /

Execution:
SQL> @e:\plsql\fibi.sql
Enter value for n: 13
old   5:   n number(3):=&n;
new   5:   n number(3):=13;
the fibinocci series is:
1
1
2
3
5
8
13
PL/SQL procedure successfully completed.

Conclusion:
       a pl/sql program is successfully executed for to generate fibinocci series.

Read More

ORACLE-FORU - PL/SQL code for Reverse Of A Number

pl/sql program to generate reverse for given number.

Procedure:

declare
     n number(4):=&n;
     s number(4):=0;
     r number(4);
 begin
     while n>0
     loop
      r:=mod(n,10);
      s:=(s*10)+r;
      n:=trunc(n/10);
     end loop;
   dbms_output.put_line(‘the reverse number is:’);
   dbms_output.put_line(s);
end;
/

Execution:

SQL> @e:\plsql\rev.sql
Enter value for n: 457
old   2:   n number(4):=&n;
new   2:   n number(4):=457;

the reverse number is:
754

PL/SQL procedure successfully completed.


Conclusion:
       a pl/sql program is successfully executed to generate reverse number for given number.

Read More