Oracle PL/SQL Introduction

Introduction to PL SQL | PL/SQL is a procedural language that is an extension of SQL. It was developed by Oracle Corporation in the early ’90s to enhance the capabilities of SQL. PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java.

Similar to the SQL language, every database has its own PL/SQL language. The Oracle 6.0 version introduced the PL/SQL language for the Oracle database software.

Basically, PL/SQL is a block-structured programming language. When PL/SQL blocks are submitted into the Oracle server then all SQL statements are executed within the SQL engine and all procedural statements are executed separately within the procedural statement executor under PL/SQL engine. It increases the processing speed and decreases traffic.

Oracle PL/SQL Block Structure

PL SQL is a block-structured language, which is divided and written in logical blocks of the code. Each block consists of three sub-parts,

DECLARE
   -- declaration section
   <declaration statements>
BEGIN
   -- executable section
   <executable statements>
EXCEPTIONS
   -- exception handling section
   <exception handling statements>
END;

Declaration Section

It is an optional section, which starts with the DECLARE keyword. It contains the definition of PL SQL identifiers, and it can have,

• Variable declarations
• Constant declarations
• Cursors declarations
• User-defined exceptions

Execution Section

It is a mandatory section, which starts with the BEGIN keyword and ends with END; keyword. This section should have at least one executable line of code, which may be just a NULL command to indicate that nothing should be executed. It can have,

• SELECT INTO clause
• DML, DCL
• Conditional control statement

Exception handling section

It is also an optional section, which starts with the EXCEPTION keyword. It can have,

• Code to handle runtime error

Different Ways to Type Oracle PL/SQL Code

We can directly write the PL SQL code in the SQL *Plus tool, but it is not recommended because for errors or mistakes, we have to type the code again. SQL statements were only a few lines of code, so we can write it over and over again but PL/SQL codes are longer than that. There are multiple ways,

1) Write code in any text editor (like notepad) then copy and paste to SQL *Plus
2) Using text file i.e. save code in a file
3) Using SQL*Plus EDIT command

If you are a beginner to Oracle PL/SQL then among these three approach the 3^rd approach is the best approach to type PL/SQL code. We will discuss it in detail.

1) Copy and paste from the notepad to SQL *Plus.
Write PL/SQL code in notepad, then copy and paste it into SQL prompt. For errors and mistakes, correct the code and again copy and paste.

2) Using text file
You can write PL/SQL code in a text file and save the file with .sql extension. Now, in the SQL *Plus tool execute the file. Example:- we have HelloWorld.sql file on my desktop. To execute this file,

SQL> @C:\Users\user\Desktop\HelloWorld

Want to become a Certified Oracle PL SQL Professional? Go through the MindMajix’s Oracle PL SQL Training.

Using SQL*Plus EDIT command

We can use the EDIT or ED command to invoke our system’s text editor. Under Windows, this will be Notepad unless we have specifically defined a different editor. When we issue the EDIT or ED command, then SQL *Plus will launch Notepad and automatically place in it the text of the most recently executed PL/SQL block or SQL statement. But before executing EDIT or ED command SQL *Plus tool,

• We must open the SQL *Plus tool of Oracle database in administrator mode otherwise, we will get an error: SP2-0110: Cannot create save file afiedt.buf
• We must execute a dummy select query before executing the ED or EDIT command. Without executing a dummy select query, the afiedt.buf file will not be opened, and give an error: SP2-0107: Nothing to save.

To open the SQL *Plus tool in administrator mode use Windows Key + S and search for SQL *Plus, and now open the SQL *Plus in administrator mode. Now, connect to the user, execute the dummy select query, then execute the ED command. Example:-

SQL> conn scott/tiger;
SQL> select * from emp;
SQL> ed

Now, afiedt.buf file will be opened in the notepad. We can write/edit code, then save it (CTRL+S), and exit (CTRL+W).

When we save and exit from afiedt.buf file, SQL*Plus will not immediately execute it. It is placed in an internal buffer. We must use the / command in order to execute the code.

SQL> /

To edit the recent code, just type ED command to re-open the afiedt.buf file which contain most recent PL/SQL program or select query.

The environment setup for PL/SQL

SQL> set serveroutput on;

If we don’t set up the environment then PL/SQL procedure will be completed successfully but it will not display the output. To display the output to the screen we need to set the serveroutput on. Note that we need to do an environment set up only once for one session, not for every PL SQL program.

Oracle PL/SQL Hello World Program

Now, let us develop our first PL SQL program to display “Hello, world’ to the screen.

To display a message to the output screen we should follow the following syntax:-
dbms_output.put_line('Message to display');

Here dbms_output is a package name, and put_line is a procedure name. The package can be used either in the executable section or in the exception section of the PL/SQL block.

Open SQL *Plus tool in administrator mode, connect to the user, execute some any select statement, and now execute ED command

SQL> conn scott/tiger;
SQL> set serveroutput on;
SQL> select * from emp;
SQL> ed

PL SQL program to display Hello, World!

BEGIN
  dbms_output.put_line('Hello, World!');
END;

Now, execute the PL SQL program.

SQL> /
Hello, World!

PL/SQL procedure successfully completed.

If you have done any syntax mistake while writing the program then you can edit it by executing ED command in the SQL *Plus.

SQL> ed

Character Sets and Lexical Units

The PL/SQL program is written as lines of text using a specific set of characters. Those characters are,
1) Upper-case and lowercase letters A…Z and a…z
2) Numerals 0…9
3) Symbols + – * / < > = ! ~ ^ ; : . ‘ @ % , ” # $ & _ | ? ( ) { } [ ]
4) Tabs, spaces, and carriage returns

The PL/SQL keywords are not case-sensitive. The lower-case letters are equivalent to corresponding upper-case letters except within string and character literals.

A line of PL/SQL text contains groups of characters known as lexical units. The lexical units are,
1) delimiters (simple and compound symbols)
2) identifiers
3) literals
4) comments

Delimiters in Oracle PL/SQL

A delimiter is a simple or compound symbol that has a special meaning to PL/SQL. The list of PL/SQL delimiters is given below.

Delimilter	Meaning
+, -, *, /	Addition, subtraction/negation, multiplication, division
%	Attribute indicator
‘	Character string delimiter
.	Component selector
(,)	Expression or list delimiter
:	Host variable indicator
,	Item separator
“	Quoted identifier delimiter
@	Remote access indicator
=	Relational operator
;	Statement terminator
:=	Assignment operator
=>	Association operator
||	Concatenation operator
**	Exponentiation operator
<< ... >>	Label delimiter (begin and end)
/* ... */	Multi-line comment delimiter (begin and end)
--	Single-line comment indicator
..	Range operator
<, >, <=, >=	Relational operators
<>, '=, ~=, ^=	Different versions of NOT EQUAL

Comment in Oracle PL/SQL

Comments are used to describe the code, which helps anyone reading the source code. The comments are completely ignored by the PL/SQL compiler. PL SQL supports two types of comments.

1) Single line comments (--):- The PL/SQL single-line comments start with the delimiter -- (double hyphen)
2) Multi line comments (/* … */):- The PL/SQL multi-line comments are enclosed by /* and */.

Example:-

-- This is a single line comment
BEGIN
  /* This is a 
   * Multi line 
   * Comment.
   */
  dbms_output.put_line('Hello, World!');
END;

Note:- We cannot nest comments.

We cannot use single-line comments in a PL/SQL block that will be processed by an Oracle Pre-compiler program because end-of-line characters are ignored. As a result, single-line comments extend to the end of the block, not just to the end of a line. In this case, use the /* */ notation instead.

BEGIN
  /* This is an /* example
   * of */ wrong comment.
   */
  dbms_output.put_line('Hello, World!');
END;

BEGIN
  /* This is a -- valid
   * comment.
   */
  dbms_output.put_line('Hello, World!');
END;

Oracle 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. The size of identifier should not exceed 30 characters. By default, identifiers are not case-sensitive.

Reserved words:- The identifiers having a special syntactic meaning to PL/SQL are called reserved words. For example, BEGIN and END are reserved words. Trying to redefine a reserved word causes a compilation error. We cannot use a reserved keyword as an identifier. Often, the reserved words are written in upper-case for readability. For the list of reserved words see:- PL/SQL reserver words.

Pre-defined Identifiers:- Identifiers globally declared in package STANDARD, such as the exception INVALID_NUMBER, can be re-declared. However, re-declaring pre-defined identifiers are error-prone because our local declaration overrides the global declaration.

Quoted Identifiers:- For flexibility, PL/SQL lets us enclose identifiers within double-quotes. Quoted identifiers are seldom needed, but occasionally they can be useful. They can contain any sequence of printable characters including spaces but excluding double-quotes. Thus, the following identifiers are valid:

“X+Y”
“last name”
“on/off switch”
“employee(s)”
“*** header info ***”

The maximum size of a quoted identifier is 30 characters not counting the double-quotes. Though allowed, using PL/SQL reserved words as quoted identifiers is a poor programming practice.

Literals in Oracle PL/SQL

PL/SQL literals can be categories in following groups:-
1) Numeric literals
2) Character literals
3) String literals
4) Boolean literals
5) Datetime literals

Character Literals

A character literal is an individual character enclosed by single quotes (apostrophes). It can be letters, numerals, spaces, and special symbols. Examples:- ‘a’, ‘A’, ‘%’, ‘0’, ‘ ‘, ‘(‘

PL/SQL is case sensitive within character literals. For example, PL/SQL considers the literals ‘A’ and ‘a’ to be different. Also, the character literals ‘0’..’9′ are not equivalent to integer literals but can be used in arithmetic expressions because they are implicitly convertible to integers.

String literals

String literals are a sequence of zero or more characters enclosed by single quotes. Examples:-

‘Hello, World!’
’30-Dec-2021′

All string literals except the null string (‘ ‘) have datatype CHAR. The PL/SQL String literal is case-sensitive. For example:- ‘Hello’, and ‘hello’ are two different String literals. To represent an apostrophe within a string, we can write two single quotes, which is not the same as writing a double quote. Example:-

‘I”m a string, you”re a string.’

Boolean literals

Boolean literals are the predefined values TRUE, FALSE, and NULL (which stands for a missing, unknown, or inapplicable value). Remember, Boolean literals are values, not strings.

Numeric literals

In arithmetic expressions, the PL/SQL uses two types of numeric literals.
a) Integer. Example:- 850, 69, -5848, 0, +84
b) Reals. Example:- 85.54, -5.8, +9.9, .15, 9.

The numbers 9.0 and 15. are reals even though they have integral values.

Numeric literals cannot contain dollar signs or commas but can be written using scientific notation. Simply suffix the number with an E (or e) followed by an optionally signed integer. Example:- 3E7, 2.0E-2 3.14159e0, -1E38, -9.5e-3. E stands for “times ten to the power of.”

2E4 = 2 * (10 ** 4) = 2 * 10000 = 20000
2E–4 = 2 * (10 ** -4) = 2 * 0.0001 = 0.0002

The range of numric literals is 1E-130 .. 10E125. For the value of a numeric literals falls outside of this range give a compilation error.

Real literals can also use the trailing letters f and d to specify the types BINARY_FLOAT and BINARY_DOUBLE respectively:

-- Single-precision floating-point number
x BINARY_FLOAT := sqrt(2.0f); 

-- Double-precision floating-point number
y BINARY_DOUBLE := sqrt(2.0d); 

Datetime literals

Datetime literals have various formats depending on the datatype. For example:

d1 DATE := DATE '1998-12-25';
t1 TIMESTAMP := TIMESTAMP '1997-10-22 13:01:01';
t2 TIMESTAMP WITH TIME ZONE := TIMESTAMP '1997-01-31 09:26:56.66 +02:00';

-- Three years and two months
-- (For greater precision, we would use the day-to-second interval)
i1 INTERVAL YEAR TO MONTH := INTERVAL '3-2' YEAR TO MONTH;

-- Five days, four hours, three minutes, two and 1/100 seconds
i2 INTERVAL DAY TO SECOND := INTERVAL '5 04:03:02.01' DAY TO SECOND;

We can also specify whether a given interval value is YEAR TO MONTH or DAY TO SECOND. For example, current_timestamp – current_timestamp produces a value of type INTERVAL DAY TO SECOND by default. We can specify the type of interval using the formats:

(interval_expression) DAY TO SECOND
(interval_expression) YEAR TO MONTH

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Learn More,

• GROUP BY Clause in Oracle
• HAVING Clause in Oracle

Search