PL/SQL Free tutorial:The Ultimate Guide To Introduction Of Pl/sql

Introduction Of plsql

PL/SQL stands for Procedural Language extension for SQL.It was developed to extend the feature and security of SQL.This language was developed by Oracle Corporation in the late 1980s.
In this  PL SQL tutorial, we will learn basic structure and features of  PL SQL.

 It has many Notable facts:

plsql Client Server Architecture

 1)PL/SQL is a completely portable, high-performance transaction- processing language.
 2)PL/SQL provides a built-in interpreted and OS independent programming environment.
 3)PL/SQL can also directly be called from the command-line SQL*Plus  interface.
 4)PL/SQL can be used for both Server Side and Client Side Development.
 5)It supports developing Web Applications and Server Pages
 6)It supports Structured Programming through Functions and Procedures. It supports Object Oriented Programming.
 7)PL/SQL is tightly integrated with SQL. It offers extensive Error Checking.It offers a variety of Programming Structures.
 8)Support for Object-Oriented Programming, Better Performance Higher Productivity, High Security and Many more...

Now come to the Programming of PL/SQL:

PL/SQL programs are divided and written in logical blocks of code.

 Declarations 

 This section starts with the keyword DECLARE. It is an optional section and defines all variables,  cursors, and other elements to be used in the program.

 Executable Commands 

 This section is enclosed between the keywords BEGIN and END and it is a mandatory section. It should have at least one executable line of code.

 Exception Handling 

 This section starts with the keyword EXCEPTION. This section is again optional and contains exception(s) that handle errors in the program

 Example:

  DECLARE
<Declarations section>
Variables, Cursor, User Defined Exceptions etc.
 BEGIN
<Executable Command(s)>
SQL Statements
PL/SQL Statements
EXCEPTION
<Exception Handling>
Action to perform when Error Occur.
END;
/

Note: Only BEGIN and END Keywords are Mandatory

Some Programming examples:
  Example Without Declaration:

BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;
/

Example With Declaration:

DECLARE
Name VARCHAR2(30) := 'Manish Jain' ;
BEGIN
DBMS_OUTPUT.PUT_LINE('Your Name is : ' || Name);
END;
/

Explanation of above code:
This pl SQL tutorial totally based on Oracle 10g Enterprise Edition.Before starting pl SQL programming
write "set serveroutput on" in command editor of Oracle 10g.Then write above code and execute on Oracle 10g.

In the above code, Name is a variable of Character Type and it is declared as Varchar2 with size 30 byte.
There is  another way to declare Character we will discuss later. Varchar2() takes as much space as required.Therefore it use has maximum.

For Integer declare number with its size i.e age number(10);

:= is assignment operator and = is a compere operator.
The character is written in a single quote as above.Then all programming language for termination semi-colon i.e  ;

DBMS_OUTPUT.PUT_LINE(); it is used for print on command editor like printf in C.

|| is used to append Variable with Character or string like in Java we use  + operator.

If we want user input then we write as:
 name varchar2(20):='&name';

If you have a query then feel free to ask and some suggestion comment below.

Next Topic: Operators in pl sql