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 Table
Secondary Sidebar
Java NIO Scatter/Gather

Java 11

Java NIO File

Bootstrap 4 Datepicker

Java Project Maven

Java NIO Path

PL/SQL Table

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.

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.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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 –

All in One Software Development Bundle(600+ Courses, 50+ projects)
Python TutorialC SharpJavaJavaScript
C Plus PlusSoftware TestingSQLKali Linux
Price
View Courses
600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,130 ratings)
  • 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

Let us discuss examples of PL/SQL Table.

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 – PL/SQL Table

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

This is a guide to PL/SQL Table. Here we discuss the Introduction, syntax, Examples, and code implementation. You may also have a look at the following articles to learn more –

  1. PL/SQL GROUP BY
  2. PL/SQL to_DATE
  3. PL/SQL Cursor Loop
  4. PL/SQL TRIM
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
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

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

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

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

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

EDUCBA Login

Forgot Password?

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

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

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

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

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

Let’s Get Started

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