Previous | Table of Contents | Next

Page 125

Week 1

Day 6

Using SQL to Modify
Data

Day 5, "Introduction to Structured Query Language (SQL)," briefly talked about the Data Manipulation Language (DML) facet of SQL and delved into the use of the SELECT statement. The SELECT statement can look at only the contents of tables; it does not have the capability to create or modify data. This lesson explores the use of the three remaining DML statements—INSERT, UPDATE, and DELETE.

In the course of developing an Oracle application, you probably will use a "front-end" tool, such as Oracle Forms, Visual Basic, or PowerBuilder, in which the application development environment internally generates many INSERT, UPDATE, and DELETE statements. However, almost every application requires the development of scripts containing SQL statements. Also, you may need to write PL/SQL procedures and functions that contain these statements.

Another reason for learning how to use SQL to modify data is to transform or migrate data from another database into an Oracle database. To migrate legacy data, you have to develop a set of scripts that contain SQL statements to "scrub" the legacy data—for example, correct invalid codes or values—before the data is added to your Oracle database.

Page 126

SQL Data-Manipulation Language

On Day 3, "Logical Database Design," you learned about the three perspectives of a database: the user perspective, the conceptual perspective, and the physical perspective. The best way to fully comprehend how SQL modifies data is to focus on the conceptual perspective. Think of only tables, columns, and rows, and you'll master SQL and Oracle more quickly. Initially, don't worry about how Oracle executes SQL statements; instead, concentrate on the purpose of the SQL statement.

Here's another helpful hint for successful use of DML. When you think about the effect of an SQL statement (INSERT, UPDATE, or DELETE), visualize a set of rows, rather than individual rows, being affected.

The first things that many programmers and developers want to learn about Oracle are the internal operating system file formats and special codes. However, this approach is wrong! Keep in mind the following:

Adding Rows with INSERT

The INSERT statement adds rows to a table. You supply literal values or expressions to be stored as rows in the table.

NOTE
The term INSERT leads some new SQL users to think they can control where a row is inserted in a table. Remember that a large reason for the use of relational databases is the logical data independence they offer—in other words, a table has no implied ordering. A newly inserted row simply goes into a table at an arbitrary location.

INSERT Syntax

The INSERT statement takes two forms. The first form is 

INSERT INTO table_name
[(column_name[,column_name]...[,column_name])]
VALUES
(column_value[,column_value]...[,column_value])

Page 127

The variables are defined as follows:

table_name is the table in which to insert the row.

column_name is a column belonging to table_name.

column_value is a literal value or an expression whose type matches the corresponding column_name.

For instance, suppose you want to add a new course to the Course table. Listing 6.1 contains the INSERT statement used to accomplish this.

Listing 6.1. Example of an INSERT statement. 

SQL> insert into Course
      2  (Course_ID, Department_ID, Title, Description, Units, Additional_Fees)
  3  values
  4  (`501', `PSYCH', `PSYCH IN FILM',
   5  `Seminar on the portrayal of psychologists and psychiatrists in film',
  6  3, 25);
1 row created.

Notice that the number of columns in the list of column names must match the number of literal values or expressions that appear in parentheses after the keyword VALUES. Listing 6.2 contains an example of what occurs if six columns are specified but only five literal values are supplied. If you specify more column names than values, Oracle returns an error message. Of course, Oracle has no way of knowing which column value is missing.

Listing 6.2. More columns specified than values supplied in an INSERT statement. 

SQL> insert into Course
      2  (Course_ID, Department_ID, Title, Description, Units, Additional_Fees)
  3  values
  4  (`501', `PSYCH', `PSYCH IN FILM',
  5  3, 25);
values
*
ERROR at line 3:
ORA-00947: not enough values

Conversely, Listing 6.3 illustrates what occurs if you specify fewer column names than values.

Page 128

Listing 6.3. Fewer columns specified than values supplied in an INSERT statement. 

SQL> insert into Course
  2  (Course_ID, Department_ID, Title, Description, Units)
  3  values
  4  (`501', `PSYCH', `PSYCH IN FILM',
   5  `Seminar on the portrayal of psychologists and psychiatrists in film',
  6  3, 25);
values
*
ERROR at line 3:
ORA-00913: too many values

If a column name referenced in an INSERT statement is misspelled, Oracle returns an error message. In Listing 6.4, the column Units is misspelled as Unit.

Listing 6.4. Column name misspelled in an INSERT statement. 

SQL> insert into Course
   2  (Course_ID, Department_ID, Title, Description, Unit)
  3  values
  4  (`501', `PSYCH', `PSYCH IN FILM',
   5  `Seminar on the portrayal of psychologists and psychiatrists in film',
  6  3);
(Course_ID, Department_ID, Title, Description, Unit)
                                               *
ERROR at line 2:
ORA-00904: invalid column name
TIP
If you execute an INSERT with a long list of column names and Oracle returns ORA-00947 or ORA-00913, it's your responsibility to do the dirty work of matching the list of column names with the list of values or expressions. If you check and still can't find the problem, try reducing the number of columns and values to isolate the problem.

Specifying Values in the INSERT Statement

Each column value supplied in an INSERT statement must be one of the following:

Previous | Table of Contents | Next

Используются технологии uCoz