||Oracle Tips by Burleson
DML vs. DDL
DML changes data in an object. If you
insert a row into a table, that is DML. You have manipulated the
data. When you create, change or remove a database object, it is
referred to as data definition language (DDL). As we will discuss
at the end of this chapter, all DDL statements issue an implicit
commit, so they are a permanent change. All DML statements change
data and must be committed before the change becomes permanent.
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
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
"EDITOR_ACTIVE" CHAR(1) DEFAULT 'Y',
CONSTRAINT "EDITOR_PK" PRIMARY
) TABLESPACE “USERS”
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.
The above book excerpt is from:
Fast writing SQL Reports with SQL*Plus
Col. John Garmany