Guide to Oracle SQL (Buffalo State University Version)

Manipulating Data

The Data Manipulation Language (DML) statements allow you to use SQL to modify (UPDATE), create (INSERT), combine (MERGE), remove (DELETE), and remove all (TRUNCATE) records in your database tables. I introduced these statements in a previous chapter so I will go into more detail in this chapter.

The `INSERT` Statement

The INSERT statement is the most common way to popuate a database table. Oracle also provides a SQL*Loader utility which can be used to populate tables with data that is in various type of formats (fixed, variable, etc.). Table have constraints (rules) that govern what type of data can be stored in a record. I will cover constraints in a future chapter.

INSERT INTO faculty
(facultyid, lastname, firstname, officephone, officeid)
VALUES (123456, 'Gerland', 'James', '716-123-4567', 123456);

1 row inserted.

Figure 10-1: The INSERT Statement

The `DESCRIBE` Statement

The INSERT statement uses the fields it is given to place the values in the record. You need to be aware of the data types for each field. Use the DESCRIBE statement to identify the fields and their data types.

DESCRIBE faculty;

Name        Null?    Type         
----------- -------- ------------ 
FACULTYID   NOT NULL NUMBER(6)    
LASTNAME             VARCHAR2(20) 
FIRSTNAME            VARCHAR2(20) 
OFFICEPHONE          VARCHAR2(20) 
OFFICEID             NUMBER(6)

Figure 10-2: The DESCRIBE Statement

This might be a good time to mention one of my guiding normalization principles. Never store unnecessary data. In the faculty table the officephone field is VARCHAR2(20) and is storing hyphens (-). This is what I mean by "unnecessary data". I woud have made that field NUMBER(10) and not required the hyphens to be there duing data entry and the insert process.

The `UPDATE` Statement

The UPDATE statement uses the criteria on the WHERE clause to modify field values in the record.

SELECT product_name, list_price
FROM product_information
WHERE product_status = 'orderable'
ORDER BY product_name;

PRODUCT_NAME             LIST_PRICE
------------------------ ----------
Desk - W/48                    2500
HD 12GB /I                      543
HD 12GB /I                      479
HD 12GB /S                      633
Laptop 32/10/56                1749
Laptop 128/12/56/v90/110       3219
Monitor 21/SD                  1023
SPNIX4.0 - SL                  1500

8 rows selected.

Figure 10-3: Before the UPDATE Statement

UPDATE product_information
SET list_price = list_price * .5
WHERE product_status = 'orderable';

8 rows updated.

Figure 10-4: The UPDATE Statement

SELECT product_name, list_price
FROM product_information
WHERE product_status = 'orderable'
ORDER BY product_name;

PRODUCT_NAME             LIST_PRICE
------------------------ ----------
Desk - W/48                    1250
HD 12GB /I                    239.5
HD 12GB /I                    271.5
HD 12GB /S                    316.5
Laptop 128/12/56/v90/110     1609.5
Laptop 32/10/56               874.5
Monitor 21/SD                 511.5
SPNIX4.0 - SL                   750

8 rows selected.

Figure 10-5: After the UPDATE Statement

The `DELETE` Statement

The DELETE statement can be used to remove an existing record from a table.

DELETE FROM jimtable 
WHERE jimID = 1;

1 row deleted.

Figure 10-6: The DELETE Statement

SQL Statement Errors

There are many reasons why SQL statements fail and cause result in a SQL error code being displayed.

Syntax Errors

Typos are probably the most common cause of syntax errors. SQL Developer helps by color-coding your SQL which gives you a hint of what might be incorrect syntax. Unfortunately, sql*pus command line does not provide color-coding syntax help.

SELCT jimid FROM jimtable;
SP2-0734: unknown command beginning "SELCT jimi..." - rest of line ignored.

Incorrect field or table names

Trying to retreive a field or use a table that does not exist is another common error.

SELECT home FROM jimtable;
      
SELCT home FROM jimtable
       *
ERROR at line 1:
ORA-00904: "HOME": invalid identifier

Permissions

Typos are probably the most common cause of syntax errors. SQL Developer helps by color-coding your SQL which gives you a hint of what might be incorrect syntax.

SELECT home FROM otherUser.jimtable;
      
Error [42501]: ERROR: permission denied for table.

Constraints

Constraints are rules placed on a table or its fields. For example, a table must have a primary key is a constraint. Also, a field canot be NULL is a constraint. Likewise, you could have a constraint that the field must be greater than 0 and less than 10.

INSERT INTO product_information (product_name)
VALUES 'Test Product';

Error starting at line : 1 in command -
INSERT INTO product_information
(product_name)
VALUES ('test product')
Error report -
ORA-01400: cannot insert NULL into ("TABLEOWNER"."PRODUCT_INFORMATION"."PRODUCT_ID")

Out of Space Errors

The Database Adminstrator allocates disk space for the Oracle databases. Occassionally, there is not enough space to complete a SQL statement.

Figure 10-7: Common SQL Errors

Using an `INSERT` With a `SELECT` Statement

In a previous chapter I mentioned a scenerio where you need to copy an existing employee record from the employees table to the employee_history table. The next figure illustrates how to copy all the records from on table (countries)into another existing table (countries_copy).

INSERT INTO countries_copy 
SELECT * FROM countries;

5 rows inserted.

Figure 10-8: Using INSERT and SELET> To Copy Records

SQL Transactons

When a DML statement is executed the reqult is a transaction. Oracle provides a few additional statements that allow you to work with transactions. The COMMIT statement tells SQL to apply any transactions since the last COMMIT. The ROLLBACK tells SQL to undo any transactions since the last COMMIT. You can also use the SAVEPOINT to create a point in the time ine of your transactions to then be able to ROLLBACK to.

UPDATE jimtable SET firstname = 'James' WHERE jimid = 1;


1 row updated.

SELECT * from jimtable;

     JIMID FIRSTNAME                 LASTNAME
---------- ------------------------- -------------------------
         1 James                     Gerland
         2 Phil                      Phailure
         3 Sally                     Smart

ROLLBACK;

Rollback complete.

SELECT * from jimtable;

     JIMID FIRSTNAME                 LASTNAME
---------- ------------------------- -------------------------
         1 Jim                       Gerland
         2 Phil                      Phailure
         3 Sally                     Smart
         
UPDATE jimtable SET firstname = 'James' WHERE jimid = 1;

1 row updated.

COMMIT;

Commit complete.

SELECT * from jimtable;

     JIMID FIRSTNAME                 LASTNAME
---------- ------------------------- -------------------------
         1 James                     Gerland
         2 Phil                      Phailure
         3 Sally                     Smart

Figure 10-9: Using COMMIT and ROLLBACK Statements

Guide to Oracle SQL (Buffalo State University Version)

Manipulating Data

The INSERT Statement

The DESCRIBE Statement

The UPDATE Statement

The DELETE Statement