Previous | Table of Contents | Next

Page 122

Table 6.1 Continued

Data Type Description
raw(size) Defines the column to contain raw binary data with a length specified
in the value in size.
raw mlslabel Defines the column as a binary format for secure operating system label.
varchar2(size) Defines a variable length character string having a maximum length of
the value specified in size.

The varchar2 and char data types are used to define the format of character or alphanumeric columns. They are not the same types of format. The varchar2 format is variable length. This means that the amount of bytes the item actually uses equals the actual characters in the field. The unused spaces in the item contain null spaces. The column defined as char uses all the spaces in its definition. Spaces not occupied by a character contain "white space." White space is an invisible character similar to the space produced by pressing the space bar. A column defined as varchar2(5) that contains the characters `ABC,' for example, is 3 bytes. On the other hand, a column defined as char(5) that contains the same characters has a size of 5 bytes. The values in the two columns are also not equal because the varchar2 column has null values in position 4 and 5, while the char columns contains white space in these positions. It is very difficult to join varchar2 columns to char columns because of this white space / nulls difference.

TIP
The varchar2 definition is generally used for character fields not used for searching purposes or that do not have a long length. I always define columns that contain text or notes as varchar2(2000). This enables the user to enter a maximum of 2,000 characters into the item, but only takes up the actual disk space for the number of characters contained in the field. I use the char definition for fields such as zip code, phone number, or social security number because they are fixed length fields.

Creating, Altering, and Dropping Tables

You can use the create table command to construct relational tables. You execute the command from SQL*PLUS. Listing 6.1 illustrates the create table command. This command defines a table similar to the employee table used throughout this book.

Listing 6.1 Creating a Table

SQL> create table ex_employee
  2    (payroll_number               number(4),
  3     last_name                    varchar2(15),
  4     first_name                   varchar2(15),
  5     absences                     number(2),
  6     wages                        number(8,2),

Page 123

  7     street                       varchar2(20),
  8     city                         varchar2(15),
  9     state                        char(2),
 10     phone                        char(13),
 11     social_security_number       char(11),
 12     employment_date              date,
 13     birth_date                   date,
 14     current_position             varchar2(15),
 15     fk_department                char(4));
Table created.
NOTE
To execute any database commands including logging on to Oracle, the developer must have a user id and must be granted appropriate privileges. A user id with database administrator authority is needed to create the user id and grant the privileges. If you are using a trial copy of Personnel Oracle7, the system/manager id has the database administrator authority. The system/manager user id has the authority to create most of the database items in this chapter. For more discussion on user ids and granting privileges, see those sections within this chapter.n

The command starts with the keywords create table, followed by the name of the table. The name must start with a letter of the alphabet, but it can contain letters, numbers, and underscores in one continuous string. The length of the name may be 1 to 30 characters. The names of the columns appear next. They have the same restrictions as the table names. The column names are enclosed by parentheses and separated by a comma. Each column name must be unique to the table. They do not have to be unique to the database. You cannot use Oracle-reserved words such as resource as a column name. Following the column name, the data type is defined. The column definition may also contain a constraint such as not null. This means that the item must always contain a value. Following the column definitions, an optional tablespace statement may be included. Inclusion of this statement causes the table to be created in a tablespace other than the default. Finally, a semi-colon completes the statement.

To erase or eliminate the table, use the drop table command. Listing 6.2 provides an example of the drop table command. This command eliminates the table created in Listing 6.1. It will no longer be needed. The drop command does not contain any parameters. The command is used when the table is no longer needed or when a column is eliminated from the table. Columns cannot be eliminated from a table after it is created. In order to eliminate a column, the table must be dropped, the create command redefined without the column, and the table recreated. The data can then be reloaded into the table. The following section will further describe how to do this.

Listing 6.2L_06_02.sql—Dropping the ex_employee Table

SQL> drop table ex_employee;
Table  dropped.

Page 124

Altering the Table

After you create the table, you may need to add or modify columns. To accomplish this, use the alter table command and the add and modify options. The former option is used to add columns or constraints to the table. The latter is used to modify the existing columns. Columns that contain data may not be changed from character to numeric or decreased in size. They may only be increased in size. Listing 6.3 contains examples of various alter table commands.

Listing 6.3 L_06_03.SQL—Modifying the Employee Table by Using the Alter Command

SQL> alter table employee add hair_color char(1);

Table altered.

SQL> alter table employee modify hair_color char(2);


Table altered.

You can add and modify multiple columns by using the same alter table command. You cannot, however, delete columns with the alter table command. To delete columns, you must drop the table and redefine it. Of course, dropping the table causes the data it contains to be destroyed. You can copy the data to another table temporarily by using the create table command in conjunction with a Select statement. The data is reloaded into the redefined and recreated table by using an insert command. This command will be discussed in the next chapter. Listing 6.4 illustrates the copy technique.

Listing 6.4 L_06_04.sql—Using the Create Table and Select Statements to Copy a Table

SQL> create table temp_employee
  2    as select * from employee;

Table created.

SQL> select * from temp_employee;

PAYROLL_NUMBER LAST_NAME       FIRST_NAME       ABSENCES     WAGES STREET
-------------- --------------- ---------------- -------- --------- --------------------
CITY            ST PHONE         SOCIAL_SECU EMPLOYMEN BIRTH_DAT CURRENT_POSITIO FK_D GE
--------------- -- ------------- ----------- --------- --------- --------------- ---- --
            23 TAFT            WILLIAM                 2      8500 1234 RIVERFRONT RD
CINCINNATI      OH 234-632-7806  340-90-9856 01-JUN-08 15-SEP-57 VICE PRESIDENT  WEL

.
.

Previous | Table of Contents | Next