PL/SQL - SPLessons

PL/SQL Syntax

Chapter 5

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PL/SQL Syntax

PL/SQL Syntax

shape Description

Oracle database allows to create tables, objects, and execute all the familiar SQL statements like INSERT,UPDATE and DELETE. In addition, it also offers PL/SQL a very powerful and flexible procedural language, which gives us the ability to tie these SQL statements together. It gives us the ability to write procedural language, to create loops, perform conditional executions and provides a huge built-in setup, which gives fine control on execution of statements. And PL/SQL helps in defining business logic more efficiently and allows the clients to create reusable units of work.

The programs of PL/SQL are divided and composed into a code of logical blocks. These blocks can either be anonymous or named. The anonymous blocks are called as mysterious blocks and named blocks are called as sub-programs. And a subprogram can be termed as procedure or function. The key contrast among the procedure and functions are procedure is summoned as a stand alone proclamation and passes qualities to the calling program only by the means of parameter and a function can be utilized as a part of an expressions and it gives back a value to that expressions. And a sub-program can be nested inside one another and termed as bundles. These block structure is called as the language of PL/SQL block and these blocks are characterized into 3-sections.

Declarations

DECLARE is the keyword with which the PL/SQL block begins.It is an discretionary block and describes cursors, sub-programs, constants, nested programs, exceptions and variables.

Executable commands

And these executable block must compulsory be defined in the program. An executable block contains the actual code that get executed.These block is enclosed between BETWEEN and END keywords. It contains at least one executable line of code in the PL/SQL executable statements.

Exception Handling

The exceptional handling begins with the key word EXCEPTION. These block will be optional and comprising of error handling inside the program.

shape PLSQL Syntax

DECLARE
<declaration section>
BEGIN
<executable commands>
EXCEPTION
<exception handling>
END;

Declaration section => It is optional block and describe some attributes.

Executable command => Contains the executable code.

Exception handling => Handle the errors inside the program.

shape Examples

The below example describes a sample PLSQL Syntax program.

SQL> DECLARE
  2  message varchar2(20):='Hello,world!';
  3  BEGIN
  4  dbms_output.put_line(message);
  5  END;
  6  /

Hello world
PL/SQL procedure successfully completed.

The above example describe the PL/SQL program execution. i.e.,Hello world program.

PL/SQL Delimiters

shape Description

The delimiter is a symbol with a specific definition. Successive is the series of delimiters in PL/SQL.

Delimiter Description
% Attribute indicator
. Component selector
Character string delimiter
+,-,*,/ Addition,subtraction,multiplication,division
Quoted identifier delimiter
, Item separator
: Host variable indicator
(,) List or expression delimiter
@ Remote access indicator
(,) List or expression delimiter
; Statement terminator
(:=) Assignment operator
|| Concatenation operator
=> Association operator
Single line comment indicator
,= Relational operators
,’=,~=,^= Different versions of NOT EQUAL

PL/SQL identifiers

shape Description

The PL/SQL identifiers are variables, procedures, constants, reserved words and exceptions. An identifier contains discretionary letter took after by numerals, dollar signs, letters, number signs and underscores and that doesn’t go more than 30 characters. Also, these identifiers are not case sensitive by default, so one can utilize lower case or upper case to represent a numeric values and shouldn’t utilize catch phrase that are held for an identifier.

The following are the valid identifier names, such as,

The following are the  series of identifiers that are illegal in PL/SQL:

Summary

shape Key Points

PLSQL Syntax draws out following main points.

  • PLSQL Syntax – Syntax is a structure of a program code.
  • Declaration – It is an optional block in PL/SQL syntax that begins with the keyword DECLARE.
  • Executable commands – It is a mandatory block in PL/SQL syntax.
  • Exception handling – It is a mandatory block in PL/SQL syntax that begins with the keyword EXCEPTION.
  • PL/SQL Delimiters – Is a symbol with specific definition.
  • PL/SQL Identifiers – Identifiers are variables,procedures and the constants.