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. |
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.sqlDropping the ex_employee Table
SQL> drop table ex_employee; Table dropped.
Page 124
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.SQLModifying 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.sqlUsing 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 . .