10/30/2018 PL/SQL Basic Syntax
PL/SQL - BASIC SYNTAX
[Link] Copyright © [Link]
Advertisements
In this chapter, we will discuss the Basic Syntax of PL/SQL which is a block-structured language; this means
that the PL/SQL programs are divided and written in logical blocks of code. Each block consists of three sub-parts
−
[Link] Sections & Description
Declarations
1 This section starts with the keyword DECLARE. It is an optional section and defines all variables,
cursors, subprograms, and other elements to be used in the program.
Executable Commands
2 This section is enclosed between the keywords BEGIN and END and it is a mandatory section. It
consists of the executable PL/SQL statements of the program. It should have at least one executable
line of code, which may be just a NULL command to indicate that nothing should be executed.
Exception Handling
3 This section starts with the keyword EXCEPTION. This optional section contains exceptions that
handle errors in the program.
Every PL/SQL statement ends with a semicolon ;. PL/SQL blocks can be nested within other PL/SQL blocks using
BEGIN and END. Following is the basic structure of a PL/SQL block −
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
The 'Hello World' Example
[Link] 1/4
10/30/2018 PL/SQL Basic Syntax
DECLARE
message varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/
The end; line signals the end of the PL/SQL block. To run the code from the 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 the
SQL prompt, it produces the following result −
Hello World
PL/SQL procedure successfully completed.
The PL/SQL Identifiers
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.
The PL/SQL Delimiters
A delimiter is a symbol with a special meaning. Following is the list of delimiters in PL/SQL −
Delimiter Description
+, -, *, / Addition, subtraction/negation, multiplication, division
% Attribute indicator
' Character string delimiter
. Component selector
, Expression or list delimiter
: Host variable indicator
, Item separator
" Quoted identifier delimiter
= Relational operator
@ Remote access indicator
[Link] 2/4
10/30/2018 PL/SQL Basic Syntax
; Statement terminator
:= Assignment operator
=> Association operator
|| Concatenation operator
** Exponentiation operator
<<, >> Label delimiter beginandend
/*, */ Multi-line comment delimiter beginandend
-- Single-line comment indicator
.. Range operator
<, >, <=, >= Relational operators
<>, '=, ~=, ^= Different versions of NOT EQUAL
The PL/SQL Comments
Program comments are explanatory statements that can be included in the PL/SQL code that you write and helps
anyone reading its source code. All programming languages allow some form of comments.
The PL/SQL supports single-line and multi-line comments. All characters available inside any comment are
ignored by the PL/SQL compiler. The PL/SQL single-line comments start with the delimiter -- doublehyphen and
multi-line comments are enclosed by /* and */.
DECLARE
-- variable declaration
message varchar2(20):= 'Hello, World!';
BEGIN
/*
* PL/SQL executable statement(s)
*/
dbms_output.put_line(message);
END;
/
When the above code is executed at the SQL prompt, it produces the following result −
Hello World
PL/SQL procedure successfully completed.
PL/SQL Program Units
[Link] 3/4
10/30/2018 PL/SQL Basic Syntax
A PL/SQL unit is any one of the following −
PL/SQL block
Function
Package
Package body
Procedure
Trigger
Type
Type body
Each of these units will be discussed in the following chapters.
[Link] 4/4