Before getting started with creating a new database you should know some basic SQL and SQL*Plus. Following are some commands that will help you find your way around Oracle.
Important Note:
SQL is not case sensitive. Some programmers prefer to write SQL commands in upper case while others use lower or mixed case.
Important Note:
SQL statements must be terminated by a ";" character.
SQL commands are issued at the "SQL>" prompt from within the Oracle interface program, sqlplus. There are built in editing commands to allow you to fix typing errors and so forth, but it is difficult and not very powerful.
Alternatively, you can use scriptfiles which contain commands, queries, and so forth. This is a better way to use SQL since you can use your favourite editor.
As an example let us assume that we have 2 files named "insert3.sql", which contains SQL commands to insert data into various tables, and "query5.sql", which contains a simple SQL query. To have the contents of these files interpreted by sqlplus just as if you were to type them at the "SQL>" prompt manually, all you need to do is the following:
zeus=> sqlplus smith @query5
SQL*Plus: Release 3.2.3.0.0 - Production on Fri Apr 26 09:24:56 1996
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
Enter password:
Using Oracle with SQL script files.
You can see that the name of the file is specified on the command line as "@sc_query5". The ".sql" is dropped and an "@" character is prepended to the filename. This causes sqlplus to load the file and interpret the contents as regular SQL and SQL*Plus commands.
Important Note:
Your SQL files should always have the ".sql" extension. When you type the filename on the command line you may choose to include the extension or not. If you do not include it, it is assumed to be there.
As another example, to use the other file mentioned above, you would do this:
zeus=> sqlplus smith/my-password @insert3
Using Oracle with SQL script files -- specifying username and password on the command line.
You will notice that it is possible to include your password on the command line.
Before you can start issuing SQL statements to retrieve and process information you will need some data to work with. Before you create a database of information you should pause and put some serious thought into the creation of the tables and the relationships between all the items of data. If done properly any changes to the structure of the database can be effected with only simple SQL commands to add and/or delete tables or to modify portions of tables.
To create a table using SQL you simply use the CREATE statement. For example, assume that you have a very simple application that requires two tables; one will store model information of new cars while the other will contain information on their engines. These tables might be created in the following manner:
create table car_vinfo
(
car_name varchar2(30),
car_type varchar2(15),
car varchar2(15),
primary key (car_name)
);
create table car_nvinfo
(
car_name varchar2(30),
car_cyl number(2,0),
car_lp100 number,
primary key (car_name)
);
Example of SQL commands to create tables.
In the first table there are 3 columns:
* car_name - the model name of the car such as Supra or Pathfinder
* car_type - the type of vehicle such as sport or sedan
* car - the car maker, such as Toyota or Chrysler
The second table also has 3 columns:
* car_name - same as other table
* car_cyl - the number of cylinders in the engine
* car_lp100 - fuel economy in litres per 100 km
When a table is created, types should be associated with each of the columns. These types are similar to those in other programming languages. Basically they define the type of information that a column can hold. In the above example the columns were given a type of either VARCHAR2 or NUMBER.
Following are some of the more commonly used types:
Type Description
varchar2(size) stores variable sized data with a maximum size of 2000
bytes
number(l,d) stores numeric data where l stands for the total length
of the number and d for the number of decimal digits --
so number(5,3) can have up to 2 integer digits and 3
decimal digits
date Oracle stores dates in the format DD-MON-YY -- so July 1,
1996 would be stored as 01-JUL-96
integer stores an integer value
long stores variable sized information up to 2 Gb in size
Common
SQL variable types.
You will notice the following line in both CREATE statements:
primary key (car_name)
This specifies that the primary key in the tables is CAR_NAME. A primary key should be unique in the database. Other examples might include social security number, serial number, or student-ID.
The primary key can also be declared in a slightly different manner -- when the column is actually defined:
create table car_vinfo
(
car_name varchar2(30) primary key,
car_type varchar2(15),
car varchar2(15),
);
Alternate declaration of the primary key.
You can also declare a column so that it is mandatory, which is a good idea especially with your primary keys. To declare a column this way, use the NOT NULL qualifier:
create table car_vinfo
(
car_name varchar2(30) not null primary key,
car_type varchar2(15),
car varchar2(15),
);
Creating a table with a mandatory column.
By specifying a column as NOT NULL, Oracle will not accept a row of data for which that column entry is blank.
This command can be used to provide a quick summary of a table and its columns. For example:
SQL> describe car_vinfo
Name Null? Type
------------------------------- -------- ----
CAR_NAME NOT NULL VARCHAR2(30)
CAR_TYPE VARCHAR2(15)
CAR VARCHAR2(15)
Using the DESCRIBE command to determine a tables structure.
Important Note:
This command is a common enhancement to SQL, although it is not part of the SQL standard. Oracle has included this command as part of its SQL*Plus.
Before you can retrieve or otherwise manipulate your data, you need to put data into your tables. To do this you can use the INSERT command.
The basic format for the INSERT command places values into all the columns of a specified table. Columns that are left blank will automatically have a NULL value inserted into them. The basic format is:
insert into <table-name> values (val-1, val-2, ..., val-N);
Format for the INSERT command.
Alternatively, you can explicitly specify the columns into which you wish to insert values. For example:
insert into <table-name> (column-name-1, column-name-2) values
(val-1, val-2);
Alternate format for the INSERT command -- columns are specified.
To insert information into the tables defined above in the 7.16.3.2 Creating Tables section, for example, you might issue the following commands:
insert into car_vinfo values ('Supra Turbo', 'sport', 'Toyota'); (1)
insert into car_vinfo values ('Eagle Talon', 'sport', 'Chrysler'); (2)
insert into car_vinfo values ('Sentra', '', 'Nissan'); (3)
insert into car_nvinfo (car_lp100, car_name) values (3, 'XR5'); (4)
Examples of inserting information into the CAR_VINFO and CAR_NVINFO tables.
The first 2 INSERT commands are straight forward; the specified information is inserted into the table CAR_VINFO. The 3rd INSERT command inserts a new row into the CAR_VINFO table, but omits the 2nd column, CAR_TYPE, which is given a NULL value. This is fine -- columns can be omitted as long as they are not flagged as NOT NULL. The 4th INSERT command only inserts 2 values into the CAR_NVINFO table. The columns specified are CAR_LP100 and CAR_NAME and the values are provided in the same order.
Important Note:
Columns which are flagged as PRIMARY KEY must always be inserted into columns. You can not insert information into a table for which there is a primary key defined unless you also provide a primary key.
Important Note:
You can use the '' (single quote) characters to denote a NULL, or you can use the NULL keyword. However, be careful with numeric fields. Inserting a NULL into a numeric field can sometimes cause arithmetic operations to fail. For example, 1+NULL = NULL!!!!
What happens when you have to change information in a table or add more information? Say that you omitted a column in one table because the information was not yet available but all the other information was. You could have inserted all the information less that one column. Days or weeks later, when the information becomes available, you wish to insert it into your table. There are several ways to do this:
1. You can delete all the information from your table and re-insert all the correct information. This is a bad idea.
2. You can create a new table for your new column of information and change the previous table to remove that empty column. This is possible and not very hard, but not a good idea. The original database design called for this particular table to be constructed a particular way, so we should do everything to maintain that design.
Important Note:
Sometimes you will want to delete a column from a table or add another. This is discussed later in section 7.16.3.7 Altering A Table.
3. You can update the data stored in the table with the UPDATE command.
There are 3 parts to the UPDATE command:
* update - this keyword is followed by the name of the table you want to change
* set - this keyword is followed by the names of one or more columns that you wish to update
* where - this keyword is followed by selection criteria
The UPDATE command.
The UPDATE and SET keywords are mandatory but the WHERE clause is not.
For example, if you wished to update the CAR_NVINFO table so that the XR5 information we entered is complete (recall that we only inserted the car name and fuel consumption information but omitted the number of cylinders), you could use the following command:
update car_nvinfo set car_cyl = 3 where car_name = 'XR5';
Example of updating information according to unique criteria.
Since CAR_NAME is a primary key it is guaranteed to be unique and we can be certain than only one record will change. However, if you had a different selection criteria many different records might be changed. For example:
update car_nvinfo set car_cyl = 6 where car_lp100 > 5; (1)
update car_nvinfo set car_cyl = 4; (2)
Examples of updating information according to some non-unique criteria.
In the 1st example, the number of cylinders for cars that consume more than 5 litres of gas for every 100 km is set to 6. In the 2nd example, the number of cylinders for all cars is set to 4.
Important Note:
Unless you use a WHERE clause in your UPDATE command, all your rows will change. Be careful what criteria you specify with the WHERE clause and then double-check it to make sure it is correct.
At times it may be okay to update your table, but at other times you may have to be more drastic. Sometimes, under various circumstances, information will have to be deleted from your tables. To remove a row of data from a table, you would use the DELETE command.
There are 2 parts to the DELETE command:
* delete from - this keyword is followed by the name of the table from which you wish to delete data
* where - this keyword is followed by the selection criteria
The DELETE command.
The DELETE FROM portion is mandatory but the WHERE clause is not.
For example, say that you wished to remove the Sentra row from the CAR_VINFO table because it has no information in the CAR_TYPE column. You could use the following command:
delete from car_vinfo where car_name = 'Sentra';
Example of removing a unique item from a table with the DELETE command.
Of course you can make your WHERE clause more or less specific, as you require.
Important Note:
Be VERY CAREFUL with the DELETE command -- it is dangerous. You can easily delete all your rows from a table with this very simple command:
delete from <table-name>;
It is possible to completely delete a table, its contents, and its definition. While this may not necessarily be a good idea, it is sometimes the only viable option. To do this you would use the DROP TABLE command:
drop table <table-name>;
Format for the DROP TABLE command.
The table and all its contents are deleted from the tablespace. You should be very careful with this command because, obviously, it can be very dangerous.
At times you can get by if you just UPDATE the information in a table or just DELETE it. Sometimes, however, it becomes necessary to modify the tables to store more columns of information. This can be done with the ALTER command:
alter table <table-name> add (col-name new-type);
Format for the ALTER command.
Say that you need to add an extra column for spark plug gap values to the table CAR_NVINFO. You could do this the hard way -- create a brand new table, merge the new information with the older information, and then delete the old table. Alternatively, we could just ALTER the existing table. For example:
SQL> describe car_nvinfo;
Name Null? Type
------------------------------- -------- ----
CAR_NAME NOT NULL VARCHAR2(30)
CAR_CYL NUMBER(2)
CAR_LP100 NUMBER
SQL> alter table car_nvinfo add (car_spark_gap integer);
Table altered.
SQL> describe car_nvinfo
Name Null? Type
------------------------------- -------- ----
CAR_NAME NOT NULL VARCHAR2(30)
CAR_CYL NUMBER(2)
CAR_LP100 NUMBER
CAR_SPARK_GAP NUMBER(38)
The first DESCRIBE command displays the current structure of the CAR_NVINFO table. After the ALTER command is issued, the DESCRIBE command shows that a new column, CAR_SPARK_GAP, has been added and has the type NUMBER(38), which is the same as INTEGER.
The ALTER command itself was fairly straight-forward. The ADD modifier is followed by the new column name and its type. You can also MODIFY the type of a column in a table with a command similar to the following:
alter table <table-name> modify (col-name new-type);
Alternate format for the ALTER command which uses the MODIFY clause.
You must be careful with the MODIFY option. Under certain circumstances you will be unable to modify a column. For instance, the column to be modified must be empty to change the datatype. In this case, Oracle will inform you of an error and it will not change the table.
The ROLLBACK command is very powerful, and can be quite dangerous so it should only be used after careful consideration. Simply put, it is the SQL version of an UNDO command -- it undoes all the changes since the last COMMIT statement.
The COMMIT statement is also very powerful and important as it actually makes any changes to the database tables permanent. Once a COMMIT statement is issued, there is very little you can do to go back to a previous state in the database.
Important Note:
Actually, you can use the SAVEPOINT command to identify a point in the transaction to which you can later roll back. Do not rely on this, however, since there are severe limitations to the command.
The COMMIT command has 3 options:
* work - the COMMIT WORK command is identical to COMMIT
* force - this option forces an in-doubt transaction and stores the ID of any such transaction in the table DBA_2PC_PENDING
* comment - associates a comment with the transaction (50 character literal) and stores it in the table DBA_2PC_PENDING
The COMMIT command.
The ROLLBACK command also has 3 options:
* work - the ROLLBACK WORK command is identical to ROLLBACK
* to point - this option forces an the roll back to the specified save-point
* force id - forces the roll back of an in-doubt transaction by the id specified in the table DBA_2PC_PENDING
The ROLLBACK command.
When to COMMIT is a big question that has many different answers. The most common method is to issue a COMMIT after every transaction. In some organizations this is a policy and is intended to maintain the integrity of the data in the tables. In some situations, however, depending on hardware and/or network setups, issuing a COMMIT may be very time consuming so it may not be a viable option after every transaction. In other circumstances it is entirely left up to the programmer.
Data is saved in the database regardless of the COMMIT statement being issued or not. That means that you can quit out of Oracle without having to COMMIT your data. Data is simply not made permanent until a COMMIT statement is issued.
Important Note:
Unless data is saved to the database with a COMMIT command, it will not be permanent and could be accidentally deleted with a ROLLBACK command. It is very important to issue COMMIT statements in order to provide data integrity. Remember that rolling back is still possible if SAVEPOINTs are specified.
Following is an example of the COMMIT command being issued after some information is added to a table:
insert into car_vinfo values ('Supra Turbo', 'sport', 'Toyota');
insert into car_vinfo values ('Eagle Talon', 'sport', 'Chrysler');
commit;
The COMMIT command is used to make changes permanent.
The following example of the ROLLBACK command undoes the insertions:
insert into car_vinfo values ('Sentra', '', 'Nissan');
insert into car_nvinfo (car_lp100, car_name) values (3, 'XR5');
rollback;
The ROLLBACK command undoes the previous 2 INSERT commands.
In the above example, the work is actually un-done all the way back to the previous COMMIT statement. Obviously it is important to be careful with the ROLLBACK and COMMIT commands.