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.

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

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 –

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 –

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 –

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 –

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 –

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 –

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 –

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