PL/SQL - SPLessons

PL/SQL Overview

Chapter 2

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PL/SQL Overview

PL/SQL Overview

shape Description

In the late 1980’s, the Oracle corporation has built the PL/SQL Programming Language. The PL/SQL is a procedural expansion language for oracle relational database and structure query language. PLSQL Overview chapter gives the clear description of PL/SQL Syntax, Delimiters and Identifiers.

The following are some key facts of the PL/SQL language.

PL/SQL Syntax

shape Description

Oracle database allows users to create tables, objects, and execute all the SQL statements like INSERT, UPDATE and DELETE. In addition to these, it also provides PL/SQL-a very powerful and flexible procedural language, which gives us the ability to combine these SQL statements together. It allows us to write procedural language to create loops, perform conditional executions and provides a built-in setup that gives fine control over 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, in PLSQL Overview chapter, 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 the named blocks are called as sub-programs. And, a subprogram can be termed as a procedure or function. The key differences among the procedure and functions is- procedure is summoned as a stand alone statement and passes qualities to the calling program only by the means of parameter and is used to perform an action that does not returns a value, whereas a function can be utilized as a part of an expression and gives back a value to that expressions. And, sub-programs can be nested inside one another and termed as bundles. These block structures are called as the language of PL/SQL blocks 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

An executable block contains the actual code that gets executed and must be defined in the program. This 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 exception handling begins with the key word EXCEPTION. This block will be optional and comprises of error handling methods inside the program.

shape Syntax

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

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

Executable command => Contains the executable code.

Exception handling => Handles the errors inside the program.

shape Examples

The below example explains a sample PL/SQL 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 describes the execution process of PL/SQL program. i.e., the Hello world program.

PL/SQL Delimiters

shape Description

Delimiters are symbols that contains a specific definition. Successive is the series in PL/SQL containing the delimiters.

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
, Relational operators
–= Single line comment indicator
,’=,~=,^= Distinct NOT EQUAL versions

PL/SQL identifiers

shape Description

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

The following are the valid identifier names, such as:

The following are a series of identifiers that are not accepted in PL/SQL:

Summary

shape Key Points

  • PLSQL Overview – It is a procedural expansion language developed by the oracle corporation.
  • PL/SQL Syntax – Syntax is a structure of the 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 – It is a symbol with specific definition.
  • PL/SQL Identifiers – Procedures, variables and constants.