 |
|
Oracle Tips by Burleson |
Managing Tables
The table is the basic building block of any
database system. We discussed tables in Chapter 1 and talked about
normalizing data to remove redundancy. In this section, we are going
to discuss the different types of tables inside an Oracle database and
how they are created and used. We need this information as we
progress into manipulating the data in tables with the INSERT, UPDATE and DELETE
statements. In computer parlance, updates are DML.
You create a table by defining the column names
and their data types. Columns can be any of the data types discussed
in Chapter 2, to include user defined data types. When we loaded the
PUBS schema, we ran the pubs_db.sql
script that contained the commands to create the tables. Let’s look
at the AUTHOR table.
CREATE TABLE
AUTHOR
(
AUTHOR_KEY VARCHAR2(11),
AUTHOR_LAST_NAME VARCHAR2(40),
AUTHOR_FIRST_NAME VARCHAR2(20),
AUTHOR_PHONE VARCHAR2(12),
AUTHOR_STREET VARCHAR2(40),
AUTHOR_CITY VARCHAR2(20),
AUTHOR_STATE VARCHAR2(2),
AUTHOR_ZIP VARCHAR2(5),
AUTHOR_CONTRACT_NBR NUMBER(5)
);
This statement creates a table named AUTHOR that
contains nine columns defined within the parentheses. Each column
definition is separated by a coma and contains the data type and size
of the column. The CREATE TABLE command can be quite involved,
defining the table storage location and constraints.
CREATE TABLE
"PUBS"."EDITOR"
(
"EDITOR_KEY" VARCHAR2(9) NOT NULL,
"EDITOR_LAST_NAME" VARCHAR2(30) NOT NULL,
"EDITOR_FIRST_NAME" VARCHAR2(30) NOT NULL,
"HIRE_DATE" DATE DEFAULT SYSDATE NOT NULL,
"EDITOR_ACTIVE" CHAR(1) DEFAULT 'Y',
CONSTRAINT "EDITOR_PK" PRIMARY KEY("EDITOR_KEY")
USING INDEX
TABLESPACE "INDX"
) TABLESPACE “USERS”
In the example above, I created a table called
EDITOR in the PUBS schema or user. It has five columns, all of which
will not allow NULL values except for
editor_active. The
hire_date column will default
to the SYSDATE, if a date is not provided when a row is inserted.
Likewise, the editor_active
column will default to Y. I defined a primary key
constraint on the editor_key
called editor_pk. The
editor_pk constraint uses an
index, which will be built in the INDX tablespace. The table itself
will be built in the USERS tablespace.
We are not going to get that involved with our table creation at this
time, since most of those items belong in the realm of the DBA or will
be covered in detail in Chapter 5. By default, the table will be
created in the user’s default tablespace defined when the user was
created. Also, columns not defined as NOT NULL will accept NULL
values. If I wanted to see my user information, I could query the
user_users view with this command:
SQL> desc
user_users;
Name Null? Type
----------------------------------------- -------- -------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
SQL> select default_tablespace from user_users;
DEFAULT_TABLESPACE
------------------------------
USERS
Since the user views will only show me information
that belongs to me, the user_users view will only display my information, since I am the
only user defined as me.
Once we have created the table, we can begin
inserting data. Sometimes, I want to create a table to hold some data
temporarily. This would allow me to temporarily create a table with
some intermediate data in it, that I then could repeatedly query
from. This is very easy with something called CTAS (pronounced
“sea-taz”).
The above text is
an excerpt from:
Easy Oracle SQL
Get Started Fast Writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
by John Garmany
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
