PL/SQL Data Types

Introduction to PL/SQL Data Types

There is often a need to execute the same SQL queries multiple times, and to cater to this need; Oracle comes up with the PL/SQL in the early ’90s. It is developed to enhance the capabilities of SQL. It is embedded in the Oracle database. PL/SQL is an extension of the Structured Query Language in the Oracle Database System. It is a procedural language consisting of three parts: a declarative part that can accept any number of parameters, an executable part consisting of SQL queries, and an exception handling.

PL/SQL is stored in the database in a compiled form and can be referred to in the code with its name. PL/SQL allows the developer to manipulate the data using any decision logic, iteration, and other features supported by the procedural programming language.

Key Features of PL/SQL

Key Features of PL/SQL are given below:

• PL/SQL allows the developer to execute any number of queries in one block using a single command.
• PL/SQL can act as a procedure, functions, triggers, and types, and are stored in the database and can be used any number of times by the applications.
• Unlike SQL, error handling is possible in PL/SQL.
• PL/SQL returns a single scalar value.

PL/SQL Data Types

Like any other language, PL/SQL also has data types. let’s have a look at some of the data types defined in the PL/SQL

PL/SQL Datatype	Description
BINARY_INTEGER	Accepts Integer numeric data, signed integer in the range from -2,147,483,648 to 2,147,483,647 and represented in 32 bits
BLOB	Accepts binary data
BLOB (n)	Binary large object data where the range of n is in between 1 to 2 147 483 647
BOOLEAN	Accepts either true or false (logical values)
CHAR	Accepts string data of fixed length 1
CHAR (n)	String data of length n
CHAR VARYING (n)	Character string data of variable-length where n is the maximum limit
CHARACTER	It is a fixed-length character string and the maximum size can be of 32,767 bytes.
CHARACTER (n)	A variable-length character string with a maximum size of n
DATE	It is used to store date-time data which includes fields like a year, month, day, hour, minute, second and many more.
DECIMAL	Accepts decimal data with a maximum precision of 38 decimal digits.
DOUBLE	Floating point number of double-precision
FLOAT	Floating point type with a maximum precision of 126 bits
INT	it’s a singed four-byte integer numeric data
INTEGER	it’s a singed four-byte integer numeric data

Examples of PL/SQL Data Types

So far we have seen the different data types available in the PL/SQL, let’s have a look at some of the example and see how can we create and execute stored procedures in the PL/SQL

Procedures in PL/SQL is a subprogram that consists of SQL statements, the procedures in Oracle are stored as a database object and has a unique name by which it can be referred. The procedure can have a nested block that allows the developer to perform any logical and arithmetic operations on the data.

Let’s have a look at the syntax of the stored procedure

Syntax:

CREATE OR REPLACE PROCEDURE

<procedure_name>
(
<parameterl IN/OUT <datatype>
..
.
)
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

Syntax Explanation:

• The create or replace keywords instructs the compiler to create a database object with the name given in the procedure, the procedure name has to be unique otherwise the compiler will throw an error.
• Now that you have an idea of the procedure syntax, let’s write a very simple stored procedure and understand the different components of it.
• Like any other programming language, we will start with a very simple Hello World program. This stored procedure will take the name as an input and display the result as Hello World from <Your Name>

CREATE OR REPLACE PROCEDURE Print_Message (p_name IN VARCHAR2)

Code:

IS
BEGIN
dbms_output.put_line (‘Hello World from '|| p_name);
END;
/
EXEC Print_Message (‘Mohit’);

Code Explanation:

• Here the first line has a keyword Create or replace which will instruct the compiler to either create a stored procedure with a name Print_Message that takes a Varchar2 as an input or replace the existing stored procedure with the same name.
• The line dbms_output.put_line will concatenate the Hello World from the message with the input which you pass to the procedure and display it on the console.
• To call a stored procedure EXEC keyword is used followed with the parameter values in the same data types as defined in the stored procedure.

Functions in the PL/SQL

Function in PL/SQL is very similar to the stored procedure, the only big difference is that a function should have a return value. It should either return a value or should raise an exception. Let’s see some of the characteristics of the functions in the PL/SQL

1. Functions that are mainly used for data manipulation purposes like performing basic logical and arithmetic calculations.
2. A typical structure of function has 3 parts i.e. a declaration part, the execution part, and the exception error handling.
3. If the function does not have any DML statement in it, it can be called by the select statement.

let’s see the syntax of the Functions

Syntax:

CREATE OR REPLACE FUNCTION

<procedure_name>
(
<parameter IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

Syntax Explanation:

• Create a function keyword that will instruct the compiler to create a function if not already present. The return data type is mandatory, again the function name should be unique.
• Exception handling block is optional but it is always advisable to have it.

Conclusion

Now let’s wrap up by summarizing the key points that we have discussed in this article, PL/SQL is the compiled block of SQL statements that are stored in the database as an object. These objects can be of type stored procedure, functions, and types. We have seen the different data types that are being offered in PL/SQL. We learn the basic structure of stored procedures and functions in PL/SQL and their characteristics.

Recommended Articles

This is a guide to PL/SQL Data Types. Here we discuss the examples with code explanation and key features of PL/SQL along with functions in the PL/SQL. You may also look at the following articles to learn more –

1. CASE statement in PL/SQL
2. Cursor in PL/SQL
3. Cursor in MySQL
4. SQL Server Data Types