Oracle PL/SQL Tutorial - PL/SQL Table

• « Previous
• Next »

A relational database allows you to store multiple tables in a database management system (DBMS).

You can manipulate the data in the tables using a query language on a computer.

The current query language of choice is Structured Query Language (SQL).

SQL is a set of nonprocedural commands to manipulate the data in tables in a relational database management system (RDBMS).

A table in a relational database is a logical definition for how data is to be organized when it is stored.

Example

Table 1-1. Relational Database Geniuses (Authors)

Table 1-2. Relational Genius's Publications (Author Publications)

You can find which publications were written by each genius simply by using the common datum in both of these tables: the ID.

If you look at Codd's data in Table 1-1, you see he has ID 100. Next, if you look at ID 100's (Codd's) data in Table 1-2, you see he has written two titles:

• Learn Oracle
• Learn CSS

These two tables have a relationship, because they share an ID column with the same values.

Note

The following table is the Table Definition for Table 1-1

Data Definition Language (DDL)

To create the authors table, as defined in Table 1-3, in Oracle, you'll need to create a SQL script.

In SQL it is called Data Definition Language(DDL).

It's SQL for defining the relational database.

The following code shows the authors table's DDL.

CREATE TABLE authors ( 
 id number(38), 
 name varchar2(100), 
 birth_date date, 
 gender varchar2(30) 
); 

The syntax for the CREATE TABLE statement is as follows:

CREATE TABLE <table_name> ( 
    <column_name_1> <data_type_1>, 
    <column_name_2> <data_type_2>, 
    <column_name_N> <data_type_N> 
); 

<table_name> is the name of the table, <column_name> is the name of a column, <data_type> is one of the Oracle data types.

The following are the Oracle data types you'll use most often:

VARCHAR2: store up to 32,767 bytes text like data in a column. You must define the maximum number of characters by specifying the desired number in parentheses after the keyword VARCHAR2.

NUMBER: store a decimal number with 38 digits of precision. You can control the size of a number. You can specify the maximum number of digits to the left of a decimal point, followed by a comma (,), and optionally the maximum number of decimal digits to the right of the decimal point, by specifying the desired constraint in parentheses after the keyword NUMBER.

DATE: store a date and time value.

To create the author_books table, follow these steps.

CREATE TABLE author_books ( id number(38), title varchar2(100), publish_date date );

Index

You should create a unique index on each table's primary key column(s).

Primary key column(s) uniquely identifies entries in the table.

In the authors table, that primary key column is id.

Let's create a unique index on the name, birth_date, and gender columns of the authors table.

CREATE UNIQUE INDEX authors_uk1 on authors (name, birth_date, gender ); 

The syntax for the CREATE INDEX statement is as follows:

CREATE [UNIQUE] INDEX <index_name> on <table_name> ( 
    <column_name_1>, 
    <column_name_2>, 
    <column_name_N> 
); 

where <index_name> is the name of the index, <table_name> is the name of the table, and <column_name> is the name of a column.

The keyword UNIQUE is optional, as denoted by the brackets [ ] around it.

It means that the database must check to make sure that the column's combination of the values is unique within the table.

The following DDL shows how to create an Index on the Title Column in the Publication Table.

CREATE INDEX author_books_k1 on author_books (title); 

• Next »
• « Previous