PL/SQL for Beginners - PL/SQL Tutorial 1

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension
for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7).
PL/SQL (Procedural Language extensions to SQL) is designed specifically for the seamless processing of
SQL commands. PL/SQL stored and compiled in the database, runs within the Oracle executable and
inherits the robustness, security, and portability of the Oracle Database.
PL/SQL is a combination of SQL along with the procedural features of programming languages.

1. P L / S Q L T u t o r i a l - B y E r G u r p r e e t S i n g h

Visit: http://gsbprogramming.blogspot.in

2. P L / S Q L T u t o r i a l - B y E r G u r p r e e t S i n g h

Visit: http://gsbprogramming.blogspot.in




What is PL/SQL?
PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension
for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7).
PL/SQL (Procedural Language extensions to SQL) is designed specifically for the seamless processing of
SQL commands. PL/SQL stored and compiled in the database, runs within the Oracle executable and
inherits the robustness, security, and portability of the Oracle Database.
PL/SQL is a combination of SQL along with the procedural features of programming languages.

Features of PL/SQL
 block (modular) structure
 flow-control statements and loops
 variables, constants, and types
 structured data
 customized error handling
 it allows you to store compiled code directly in the database.
 once a given block of code is loaded into memory, any number of users can use the same copy of
it simultaneously (although behavior is as though each user had her own copy), which is useful for
the Oracle WebServer.
 enables you to define triggers, which are subprograms that the database executes automatically
in response to specified events.
 is tightly integrated with SQL.
 offers extensive error checking.
 supports object-oriented programming.
 It supports developing web applications and server pages.

3. P L / S Q L T u t o r i a l - B y E r G u r p r e e t S i n g h

Visit: http://gsbprogramming.blogspot.in
PL/SQL Syntax
PL/SQL block consists of three sub-parts:
 DECLARATIONS
 EXECUTABLE COMMANDS
 EXCEPTION HANDLING

DECLARATIONS
This section includes the declarations for variables, cursors, sub-programs, and other elements to be used
in the program.
This section starts with DECLARE keyword. This section is optional.

EXECUTABLE COMMANDS
This section includes executable PL/SQL statements of the program. It should have at least one executable
line of code.
This section starts with BEGIN keyword and ends with END; keyword. This section is mandatory.

EXCEPTION HANDLING
This section includes exception(s) that handle errors in the program.
This section starts with EXCEPTION keyword and is placed between BEGIN and END;


Basic structure:

DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;


Using Comments:
 Single line comments: Single line comments can be written as:

-- Single line Comment

 Multi-line comments: Multi-line comments can be written as:
/* Multi-line
Comment */

4. P L / S Q L T u t o r i a l - B y E r G u r p r e e t S i n g h

Visit: http://gsbprogramming.blogspot.in
Declaring Variables:

Syntax:

Variable_name data_type;
Or
Variable_name data_type:=value;
Or
Variable_name data_type DEFAULT value;



For example:
vSalary number;
or
vSalary number:=1000;
or
vSalary number DEFAULT 1000;

You can also use CONSTANT and NOT NULL as:
Variable_name CONSTANT data_type NOT NULL :=value;
For example:
vMax CONSTANT number NOT NULL:=100;
When you use CONSTANT or NOT NULL then you must initialize the variable.

PL/SQL identifiers are constants, variables, exceptions, procedures, cursors, and reserved words. The
identifiers consist of a letter optionally followed by more letters, numerals, dollar signs, underscores, and
number signs and should not exceed 30 characters. By default, identifiers are not case-sensitive. So you
can use integer or INTEGER to represent a numeric value. You cannot use a reserved keyword as an
identifier.



Printing a message
To print a message use:
dbms_output.put_line(‘message’);

To concatenate use ||
dbms_output.put_line(‘message1’||’message2’);
dbms_output.put_line(‘Employee Id: ’||65489);

To print value of a variable use:
dbms_output.put_line(‘Value: ’||variable_name);

5. P L / S Q L T u t o r i a l - B y E r G u r p r e e t S i n g h

Visit: http://gsbprogramming.blogspot.in
Sample PL/SQL Program:

DECLARE
vMessage varchar2(50):=’Welcome to PL/SQL’;
BEGIN
dbms_output.put_line(vMessage);
END;
/

The END; line signals the end of the PL/SQL block. To run the code from SQL command line, you may need
to type / at the beginning of the first blank line after the last line of the code. When the above code is
executed at SQL prompt, it produces the following result:

Welcome to PL/SQL
PL/SQL procedure successfully completed.