EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Software Development Software Development Tutorials PL/SQL Tutorial PL/SQL Table
 

PL/SQL Table

Updated April 5, 2023

PL_SQL Table

 

 

Introduction to PL/SQL Table

PL/SQL table is a relational object which can store the data by using rows and columns. The columns of the table consist of different attributes or values that you want to store in the table. Each and every individual record consisting of the values of all the attributes or columns is referred to as a row of the table. Multiple records having the same number and type of attributes can be stored inside the table. There can be any number of tables created and used inside a particular database in Pl/ SQL. You can connect multiple tables by making use of foreign key constraints if they are related.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

In this article, we will study how we can create the tables and create the primary keys and foreign key constraints on them. Also, we will try to insert the values inside the table. All this will be done by using the demonstrations with the help of examples.

Syntax:

The syntax of creating a new table in Pl/ SQL is as shown below.

CREATE TABLE name of table
(
Name of first column datatype [NOT NULL | NULL],
Name of second column datatype [NOT NULL | NULL],
Name of third column datatype [NOT NULL | NULL],
Name of forth column datatype [NOT NULL | NULL],
…..
Name of nth column datatype [NOT NULL | NULL],
)

The terminologies used in the above syntax are as described below –

  • Name of the column – This is the column name that will define which attribute value you want to store in that column. The column names should be short and meaningful so that you can easily understand from the name itself the purpose and type of column created for storing values.
  • Datatype – The datatype can be NUMBER, VARCHAR, BOOLEAN, DATE, TIMESTAMP, DATETIME, etc., which will represent what type of value will be stored in this column of the table.
  • NOT NULL | NULL – This constraint can be applied to the individual columns and is optional in nature. By specifying this constraint, we can have control over whether the NULL values be stored inside the column or not. This is an optional parameter. When not specified, the default value of this constraint is considered as NULL.
  • Name of the table – This is the table name that you want to create in which you will store the data in row-column format.

Note: Along with all the terms used in the above syntax, you can even make the use of primary keys, which can be auto-incremented and will work as an identifier to recognize an individual row from the multiple rows of the table. This primary key should be unique for each row or record of the table and can be made up of single or multiple columns grouped together. Another kind of key used is the foreign key which is created when a particular column from one table is actually referencing the column of another table.

Examples of PL/SQL Table

Below are some different examples.

Example #1

Let us try to create a simple table named Articles which will store the information of all the articles and the metadata about articles such as the name of an article, published date, number of words, rate, etc. For this, we can make use of the following Pl/ SQL query statement –

CREATE TABLE Articles(
article_id NUMBER(6) NOT NULL
topic VARCHAR2(50) NULL,
number_of_words VARCHAR2(50) NULL,
posted_date DATE NULL
);

The execution of the above query statement gives the following output –

pl sql table 1

Now, let us try to insert some records in the above table which is created. For inserting the records or rows in the table, we will need to use the INSERT statement in PL/ SQL, and our query statement will look somewhat kind of the below statement.

INSERT INTO Articles VALUES (101,"PL/ SQL Importance", 2000, "2021-01-26");

The execution of the above query statement gives the following output –

pl sql table 2

Note that the values we are trying to insert may change depending on our requirement, but the number of column values specified and the type of the values should be the same. It is allowed that you don’t specify the value of the columns whose default value is specified while creating those columns in CREATE TABLE statement. But in that case, the list and order of columns being specified should be mentioned in the INSERT statement.
Let us try to retrieve the data of the articles table to see if the row we specified is properly inserted or not by using the SELECT query statement.

SELECT * FROM Articles;

The execution of the above query statement gives the following output that confirms that our record is inserted properly –

pl sql table 3

Example #2

Let us try to create the table in which we will have a primary key. Each table can contain only one primary key, and it is required that for every row of the table, the primary key should be filled and should not contain a NULL value in it. The primary key can be declared by using a single column or multiple columns. We will try to create a table named Sessions in which the session-id column will be a primary key for us. Our query statement for creating the table will be as shown below –

CREATE TABLE Sessions(
session_id NUMBER(6) NOT NULL ,
source VARCHAR2(50) NULL,
number_of_participants VARCHAR2(50) NULL,
scheduled_date DATE NULL,
CONSTRAINT session_PK PRIMARY KEY (session_id)
);

The execution of the above query statement gives the following output –

example 2

Let us try to insert a row in the Sessions table by using the below query statement –

INSERT INTO Sessions VALUES (111,"International Database Organization", 3500, "2021-01-26");

The execution of the above query statement gives the following output –

Output 4

For confirming whether the session records have been inserted or not, we can fire the following SELECT query statement –

SELECT * FROM sessions;

The execution of the above query statement gives the following output that confirms that our record is inserted properly –

Output 5

If we try to insert a value with the same primary key value using the below statement, then it gives out the error –

INSERT INTO Sessions VALUES (111,"Hadoop System", 3980, "2021-02-26");

The execution of the above query statement gives the following output –

output

Conclusion

The tables are used to store the relational data in terms of rows and columns. We can set primary key and foreign key constraints on the columns of the tables in PL/ SQL.

Recommended Articles

We hope that this EDUCBA information on “PL/SQL Table” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PL/SQL GROUP BY
  2. PL/SQL to_DATE
  3. PL/SQL Cursor Loop
  4. PL/SQL TRIM

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - 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
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

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

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*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?

🚀 Limited Time Offer! - 🎁 ENROLL NOW