EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials PL/SQL Tutorial PL/SQL Data Types
Secondary Sidebar
Python Newspaper

Magic Number in C

Phishing and Pharming

Shell Scripting Interview Questions

Software Testing Interview Questions

What is JavaScript?

PL/SQL Data Types

By Priya PedamkarPriya Pedamkar

plsql 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.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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
Popular Course in this category
PL SQL Training (4 Courses, 2+ Projects)
  4 Online Courses |  2 Hands-on Projects |  17+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more