In week 6 of our Oracle 9i training course we will be learning about Data types with the differences between different Oracle versions, creating tables, altering tables, dropping tables, Integrity constraints supported by Oracle, Inserting records, Modifying the records and deleteting the records. If you can’t see the screen shots, please save them on to your hard disk.
Table: Tables are basic units of data storage. A table is defined as intersection of rows and columns. Data is stored in rows and columns.
Oracle Data Types:
CHAR: Char data type stores fixed-length character strings.
Max Size: Oracle 7 255 bytes Default and minimum size is 1 byte.
Max Size: Oracle 8 2000 bytes Default and minimum size is 1 byte.
Max Size: Oracle 9 2000 bytes Default and minimum size is 1 byte.
VARCHAR or VARCHAR2: This data type stores variable-length character data. Varchar is a deprecated data type and is a synonym for varchar2.
Max Size: Oracle 7 2000 bytes minimum is 1
Max Size: Oracle 8 4000 bytes minimum is 1
Max Size: Oracle 9 4000 bytes minimum is 1
NUMBER: The NUMBER data type stores fixed and floating point numbers. We can specify Precision
(Total number of digits) and scale (number of digits to the right of decimal point).
Max Size: Oracle 7 The precision p can range from 1 to 38.The scales can range from -84 to 127.
Max Size: Oracle 8 The precision p can range from 1 to 38.The scales can range from -84 to 127.
Max Size: Oracle 9 The precision p can range from 1 to 38.The scales can range from -84 to 127.
DATE: The DATE data type stores dates and time in the table.
Max Size:Oracle 7 from January 1, 4712 BC to December 31, 4712 AD.
Max Size:Oracle 8 from January 1, 4712 BC to December 31, 9999 AD.
Max Size:Oracle 9 from January 1, 4712 BC to December 31, 9999 AD.
LONG: The LONG data type stores variable-length character data (bigger than VARCHAR2) up to two gigabytes length. You can use LONG columns to store long text strings.
Max Size:Oracle 7 2 Gigabytes
Max Size:Oracle 8 2 Gigabytes
Max Size:Oracle 9 2 Gigabytes
RAW: This data type stores binary data. We must specify the size for RAW value.
Max Size:Oracle 7 Maximum size is 255 bytes.
Max Size:Oracle 8 Maximum size is 2000 bytes
Max Size:Oracle 9 Maximum size is 2000 bytes
LONG RAW: Raw binary data of variable length.
Max Size:Oracle 7 2 Gigabytes.
Max Size:Oracle 8 2 Gigabytes.
Max Size:Oracle 9 2 Gigabytes.
LARGE OBJECTS (LOBs): The above data types are called traditional oracle data types. LOBs are added from Oracle 8.0 version. From there Oracle 8.0 is considered as ORDBMS (Object relational database management system.). The traditional oracle database is extended to include object-oriented concepts and structures such as abstract data types, nested tables, varying arrays, object views and references.
LOB datatypes are capable of storing large volumes of data. The LOB datatypes available are BLOB, CLOB, NCLOB, and BFILE.
CLOB ( Character Large Object): This data type is used to store character data.
Max Size:Oracle 7
Max Size:Oracle 8 4Gigabytes
Max Size:Oracle 9 4Gigabytes
BLOB ( Binary Large Object): This data type is used for binary data.
Max Size:Oracle 7
Max Size:Oracle 8 4Gigabytes
Max Size:Oracle 9 4Gigabytes
NLOB (National Character Large Object): This datatype is used to store character data containing Unicode characters.( ASCII character is of 1 byte and UNICODE character is of 2 bytes )
Max Size:Oracle 7
Max Size:Oracle 8 4Gigabytes
Max Size:Oracle 9 4Gigabytes
BFILE (Binary File): It is a pointer to external file. The files referenced by BFILE exist in the file system. The database only maintains a pointer to the file. The size of the external file is limited only by the operating system. ORACLE does not maintain concurrency and integrity of the data.
Note: A table can use more than one LOB. Earlier to LOBs it was possible to have only one LONG datatype column in a table.
CREATING TABLES: A table name can not exceed 30 characters and necessarily should start with a character. Blank spaces and other special characters except an “under score” ( _ ) are not allowed in table names.
Each column in the table will be given a unique name. Every column is assigned a data type corresponding to the data to be entered into the column. Column names are also restricted to 30 characters and follow the same rules as table names.
Syntax for creating a Table:
CREATE TABLE
(
_ _ _
Example:
DROPPING A TABLE: To drop a table it must already exist in the database. The syntax for DROP TABLE statement is
DROP TABLE
CONSTRAINTS: A constraint may be defined as a business rule that data need to satisfy to enter the table. An integrity constraint defines a business rule for a table column. When enabled, the rule will be enforced by oracle. The two basic types of constraints are column constraints and table constraints. The difference between the two constraints is former applies to individual columns and the later one apply to group of columns.
Integrity Constraints Supported by Oracle
1) NOT NULL: A column with this constraint will not allow NULL values.
2) PRIMARY KEY: There can be only one primary key column in a table. This will only UNIQUE values. Does not allow NULL values.
3) UNIQUE KEY: We can have any number of primary keys in a table. UNIQUE Constraint also would accept only UNIQUE values.
4) CHECK: This constraint defines a condition which need to be satisfied by the value entering into the table.
5) FOREIGN KEY: A foreign key is a combination of columns with values based primary. It is also known as referential integrity constraint. Values that a foreign key can take are the values that are present in primary key.
Creating Table Using Constraints:
Note: In the above screen shot lines after “—-“ are comments. Different ways of defining constraints are given above.
ALTER TABLE command is used for modifying structure of the table.
1) 1) Adding and deleting or renaming columns.
2) 2) Increasing and decreasing column data size.
3) 3) Changing column data type.
4) 4) Enable or Disable constraints.
Syntax:
ALTER TABLE
ADD | MODIFY | DROP | ENABLE | DISABLE
The following screen shots illustrate how to add a column and how to remove a column.
ALTER TABLE can be used to add and remove constraints as shown below
Entering records into a table
- To enter records into a table we use INSERT command
- Using & we can read the values from key board in SQL .
SQL > INSERT INTO STUD (SNO, SNAME)
VALUES (3 , ‘MANOJ’);
1 row created.
SQL > SELECT * FROM STUD;
SNO SNAME COURSE FEE
—- ————— ————— ———-
1 KISHAN ORACLE 9i 123.45
2 SHARADA ORA APPS 1234.5
3 MANOJ
Modifying existing values using UPDATE
UPDATE command is used to update the values of the fields.
Syntax :
UPDATE
SET
SQL > UPDATE STUD
2 SET COURSE = ‘J2EE’ WHERE SNO=3;
1 row updated.
SQL > SELECT * FROM STUD;
SNO SNAME COURSE FEE
—– ———- ————— ————— ——– —
1 KISHAN ORACLE 9i 123.45
2 SHARADA ORA APPS 1234.5
3 MANOJ J2EE
SQL > UPDATE STUD
2 SET COURSE=’J2EE’ ;
3 rows updated.
SQL > SELECT * FROM STUD;
SNO SNAME COURSE FEE
———- ————— ————— ———-
1 KISHAN J2EE 123.45
2 SHARADA J2EE 1234.5
3 MANOJ J2EE
Note: Without proper condition UPDATE will take effect on more than one record.
Using DELETE command
This command is used to delete rows from a table.
SQL > SELECT * FROM STUD;
SNO SNAME COURSE FEE
———- ————— ————— ———-
1 KISHAN J2EE 123.45
2 SHARADA J2EE 1234.5
3 MANOJ J2EE
SQL > DELETE FROM STUD
2 WHERE SNO=3;
1 row deleted.
SQL > SELECT * FROM STUD;
SNO SNAME COURSE FEE
———- ————— ————— ———-
1 KISHAN J2EE 123.45
2 SHARADA J2EE 1234.5
SQL > DELETE FROM STUD;
2 rows deleted.
ROLLBACK, COMMIT AND SAVEPOINT
ROLLBACK, COMMIT and SAVEPONT are called as Transaction Control Commands. A transaction is a sequence of SQL statements that Oracle treats as a single unit. Various changes we make to the table with INSERT, UPDATE, DELETE commands are temporary. To make them permanent we use COMMIT command. To undo the changes we use ROLLBACK.
