, , ,

DDL statements

Data definition language (DDL) refers to the subgroup of SQL statements that create, alter, or drop database objects.
This sub-category of SQL statements is of particular interest to database architects or database administrators who must define an original database design and who must respond to requirements and extend a database design. It is also of interest to database application developers, because there are times when the easiest way to meet an application requirement is to extend an existing database object definition.
In general DDL statements begin with one of the following keywords: CREATE, ALTER, or DROP. Examples of DDL statements for creating database objects include: CREATE TABLE, CREATE TRIGGER, CREATE PROCEDURE, and CREATE SEQUENCE. These statements generally contain multiple clauses used to define the characteristics and behavior of the database object. Examples of DDL statements for altering database objects include: ALTER TABLE, and ALTER PROCEDURE. These statements generally are used to alter a characteristic of a database object.
DDL statements can be executed from a variety of interactive and application interfaces although they are most commonly executed in scripts or from integrated development environments that support database and database object design.
Describing Tables
The best way to think of a table for most Oracle beginners is to envision a spreadsheet containing several records of data. Across the top, try to see a horizontal list of column names that label the values in these columns. Each record listed across the table is called a row. In SQL*Plus, the command describe enables you to obtain a basic listing of characteristics about the table.
SQL> DESCRIBE po_headers_all
Name                       Type           Nullable Default                                                                                                Comments
————————– ————– ——– —————————
PO_HEADER_ID               NUMBER                                                                                                                                 
AGENT_ID                   NUMBER(9)                                                                                                                              
TYPE_LOOKUP_CODE           VARCHAR2(25)                                                                                                                           
LAST_UPDATE_DATE           DATE                                                                                                                                   
LAST_UPDATED_BY            NUMBER                                                                                                                                 
SEGMENT1                   VARCHAR2(20)   R2(1)
Commenting Objects
You can also add comments to a table or column using the comment command. This is useful especially for large databases where you want others to understand some specific bits of information about a table, such as the type of information stored in the table. An example of using this command to add comments to a table appears in the following block:
SQL> comment on table employee is
  2  ‘This is a table containing employees’;
Comment created.

You can see how to use the comment command for adding comments on table columns in the following code block:
SQL> comment on column employee.empid is
  2  ‘unique text identifier for employees’;
Comment created.

Comment information on tables is stored in an object called USER_TAB_COMMENTS, whereas comment information for columns is stored in a different database object, called USER_COL_COMMENTS. These objects are part of the Oracle data dictionary. You’ll find out more about the Oracle data dictionary later in the book.
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply