Updated March 17, 2023
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
- Functions that are mainly used for data manipulation purposes like performing basic logical and arithmetic calculations.
- A typical structure of function has 3 parts i.e. a declaration part, the execution part, and the exception error handling.
- 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 –
