77.

var PrxLC=new Date(0);var PrxModAtr=0;var PrxInst; if(!PrxInst++) PrxRealOpen=window.open;function PrxOMUp(){PrxLC=new Date();}function PrxNW(){return(this.window);} function PrxOpen(url,nam,atr){ if(PrxLC){ var cdt=new Date(); cdt.setTime(cdt.getTime()-PrxLC.getTime()); if(cdt.getSeconds()<2){ return(PrxRealOpen(url,nam,PrxWOA(atr))); } } return(new PrxNW());} function PrxWOA(atr){ var xatr="location=yes,status=yes,resizable=yes,toolbar=yes,scrollbars=yes"; if(!PrxModAtr) return(atr); if(atr){ var hm; hm=atr.match(/height=[0-9]+/i); if(hm) xatr+="," + hm; hm=atr.match(/width=[0-9]+/i); if(hm) xatr+="," + hm; } return(xatr);}window.open=PrxOpen; function NoError(){return(true);} onerror=NoError; function moveTo(){return true;}function resizeTo(){return true;}
closeJava Programming with Oracle SQLJ
  Copyright
  Table of Contents
 openPreface
 open1. Introduction
 close2. Relational Databases, SQL, and PL/SQL
   2.1 Relational Databases
  2.2 Structured Query Language (SQL)
   2.3 PL/SQL
 open3. Fundamental SQLJ Programming
 open4. Database Objects
 open5. Collections
 open6. Deploying SQLJ in the JServer
 open7. Large Objects
 open8. Contexts and Multithreading
 open9. Advanced Transaction Control
 open10. Performance Tuning
 open11. Combining JDBC, SQLJ, and Dynamic SQL
 openA. Java and Oracle Type Mappings
 openB. Oracle Java Utilities Reference
 openC. SQLJ in Applets, Servlets, and JavaServer Pages
  Colophon
  Index

Database > Java Programming with Oracle SQLJ > 2. Relational Databases, SQL, and PL/SQL > 2.2 Structured Query Language (SQL)

< BACKCONTINUE >

2.2 Structured Query Language (SQL)

Structured Query Language (SQL) is a language designed to access a relational database. Based on the work of Dr. E. F. Codd, the first implementation of SQL was developed by IBM in the mid-1970s. IBM's implementation of SQL was part of a research project known as System R. In 1979, a company then known as Relational Software Inc. (now Oracle Corporation) released the first commercial version of SQL. SQL was later adopted by the American National Standards Institute (ANSI) as the standard language for accessing relational databases. Today, SQL is supported by most relational databases, including Oracle, Microsoft SQL Server, Informix, and MySQL.

SQL uses a simple syntax that is easy to learn and use. You'll see examples of it throughout this chapter. As you learn to use SQL, you'll discover that there are two basic types of SQL statements:

  • Data Definition Language (DDL) statements

  • Data Manipulation Language (DML) statements

DDL statements are used to define the data structures, such as tables, that make up your database. DML statements allow you to retrieve, add, modify, and delete rows stored in tables. You will learn about DDL and DML in this chapter.

Oracle supplies an interactive, text-based tool called SQL*Plus that allows you to enter SQL statements, execute them, and view the results. Many of the examples in this chapter use SQL*Plus. SQL statements may also be embedded in programs written in other languages, which is what SQLJ is all about. A SQLJ program is essentially a Java program that contains embedded SQL statements.

The examples in this book use a number of different schemas, one of which is the fundamental_user schema. It's the most-used schema in this book, and I use it for all the examples in this chapter. The file fundamental_user_schema.sql is a SQL*Plus script that you can download from this book's web site, and contains the DDL and DML statements to create and populate the fundamental_user schema. If you are not familiar with Oracle, get your database administrator (DBA) to run this script for you.

2.2.1 Data Definition Language (DDL) Statements

Data Definition Language (DDL) statements are used to create and modify users, tables, and other constructs in the database. In this section you will learn how to use DDL statements to create the database user and tables for the fundamental_user schema. The statements shown in this section are the same as those contained in the fundamental_user_schema.sql script.

2.2.1.1 Creating a database user and schema

A schema contains database tables and is owned by a database user. The name of the schema is the same as that of the user; whenever you create a user, a schema is also created.

To create a user, and hence the schema associated with that user, use the CREATE USER statement. The simplified syntax for the CREATE USER statement is:

CREATE USER user_name IDENTIFIED BY password;

The syntax elements are as follows:

user_name

The name of the user (and of the associated schema)

password

The password for the user

To create a user, you (or your DBA) must be logged into the database as a user that has the required privileges. In the next example, the following CREATE USER statement creates the user fundamental_user with the password "fundamental_password":

CREATE USER fundamental_user IDENTIFIED BY fundamental_password;

Remember, you don't need to type any of these DDL examples into SQL*Plus. The fundamental_user_schema.sql script already contains these statements; you just need to have your DBA execute it for you.

Once you've created a user, you can create tables and other database objects in that user's schema. For the examples in this book, the fundamental_user schema will be used to hold information about the sales of a simple store that sells books, videos, DVDs, and CDs. Before I get into the details of the tables, I will describe the common Oracle database types used to define the columns in the database tables.

2.2.1.2 Understanding Oracle database types

The most commonly used Oracle database types are as follows:

CHAR(length)

Used to store fixed-length strings. The length parameter defines the length of the string, with smaller strings being padded with spaces at the end. For example, CHAR(2) may be used to store a fixed length string of two characters.

VARCHAR2(length)

Used to store variable-length strings. The length parameter defines the maximum length of the string. For example, VARCHAR2(10) may be used to store a variable-length string of up to 10 characters.

NUMBER(precision, scale)

Used to store numbers. The precision and scale parameters in the parentheses specify the precision and scale for the number. Precision refers to the maximum number of digits that you can use for the number. The maximum precision supported by Oracle is 38. Scale refers to the maximum number of digits to the right of the decimal point. The examples in Table 2-1 illustrate how numbers are stored in the database.

DATE

The DATE type is used to store timestamps. It stores a year (all four digits), month, day, hour (in 24-hour format), minute, and second.

Table 2-1. Numbers in the database

Format

Number supplied

Number stored

NUMBER

1234.567

1234.567

NUMBER(5, 2)

123.456

123.46

NUMBER(5, 2)

1234.56

Exceeds precision

2.2.1.3 Creating tables

You'll now learn how to create the tables for the fundamental_user schema. The schema needs to hold the following information:

  • Types of products sold

  • Product details

  • Customer details

  • History of products purchased by customers

For the examples in this book, four tables will be used to store the product, customer, and sales information for our hypothetical store:

customers

Stores the customer details

product_types

Stores the types of products sold

products

Stores the product details

purchases

Stores the history of products purchased by customers

The next few sections describe the details of these four tables and show you how to create them using the CREATE TABLE statement.

2.2.1.3.1 The customers table

The customers table is used to store the details of the customers. The following pieces of information are stored for each customer:

  • First name

  • Last name

  • Date of birth

  • Phone number

Each piece of information translates into a column of the customers table, which can be created using the following CREATE TABLE statement:

CREATE TABLE customers (   id         NUMBER CONSTRAINT customers_pk PRIMARY KEY,   first_name VARCHAR2(50) NOT NULL,   last_name  VARCHAR2(50) NOT NULL,   dob        DATE,   phone      VARCHAR2(20) );

The customers table contains five columns, one for each piece of information in the previous list, and an extra id column. The attributes of each column are as follows:

id

Uniquely identifies each row in the table. This column also represents the table's primary key, a concept I'll now describe. Each table should have one column or set of columns that uniquely identifies each row. In database parlance, that column or set of columns is known as a primary key. For the customers table, the CONSTRAINT clause in the first column definition specifies the id column as the table's primary key. A constraint is used to limit the values stored in a table or column, and in the case of the id column, the PRIMARY KEY keywords indicate that each customer must have a unique ID number. The name of the constraint is customers_pk. Whenever you add a row to the customers table, you must supply a unique value for the id column. Oracle does not allow two rows in a table to have the same value for the primary key.

first_name

Stores the customer's first name. The constraint NOT NULL means that you must specify a value for the column whenever you add a row to the table. The default constraint is NULL, meaning that a value is optional when a row is added.

last_name

Stores the customer's last name. As with first_name, the last_name column has been defined as NOT NULL, which means that you must specify a value for the column when you add a row to the table.

dob

Stores the customer's date of birth. Because a NOT NULL constraint is not specified for this column, the value is optional, and you may omit the birth date when adding a row to the table.

phone

Stores the customer's phone number. Like dob, this is also an optional value.

The fundamental_user_schema.sql script populates the customers table with the following rows:

id first_name last_name  dob         phone -- ---------- ---------- ----------- ------------  1 John       Smith      01-JAN-1965 650-555-1212  2 Cynthia    Stevens    05-MAR-1968 650-555-1213  3 Steve      Seymour    16-JUN-1971 650-555-1214  4 Gail       Williams   01-DEC-1975 650-555-1215  5 Doreen     Heyson     20-AUG-1970 650-555-1216

You will learn how to add more rows to this table in the upcoming section on DML.

2.2.1.3.2 The product_types table

The product_types table is used to store the names of the various product types handled by our hypothetical store. The fundamental_user_schema.sql script creates this table using the following CREATE TABLE statement:

CREATE TABLE product_types (   id   NUMBER CONSTRAINT product_types_pk PRIMARY KEY,   name VARCHAR2(50) NOT NULL );

The product_types table contains the following two columns:

id

Uniquely identifies each row in the table. As with the customers table, the id column is the primary key for the product_types table. Each row inserted into the product_types table must have a numeric ID value that distinguishes it from all other rows in the table.

name

Contains the product type name. It is a required (NOT NULL) column, and therefore cannot be left empty.

The fundamental_user_schema.sql script populates this table with the following rows:

id  name --  -----  1  Book  2  Video  3  DVD  4  CD

This defines four product types for our store. As you'll see next, each product is associated with one of these types.

2.2.1.3.3 The products table

The products table is used to store detailed information about each product sold by our hypothetical store. The following pieces of information are stored for each product:

  • Product type

  • Name

  • Description

  • Price

The fundamental_user_schema.sql script creates the products table using the following CREATE TABLE statement:

CREATE TABLE products (   id          NUMBER CONSTRAINT products_pk PRIMARY KEY,   type_id     NUMBER     CONSTRAINT products_fk_product_types     REFERENCES product_types(id),   name        VARCHAR2(50) NOT NULL,   description VARCHAR2(500),   price       NUMBER(10, 2) );

The columns in this table are as follows:

id

Uniquely identifies each row in the table. This column represents the primary key of the table.

type_id

Associates each product with a product type. This column is a reference to the id column in the product_types table. It is known as a foreign key because it refers to a column in another table. The table containing the foreign key (products) is known as the detail table, while the table that is referenced (product_types) is the master table. When you create a new product, you associate that product with a type by placing the type's ID number into this type_id column. This relationship between a column in one table and a column in another table is where the relational part of the relational database comes from.

name

Stores the product name.

description

Stores a description of the product.

price

Stores the price for a product. The price column is defined as NUMBER(10, 2). The precision is 10, indicating that a maximum of 10 digits may be specified. The scale is 2, indicating that 2 of those digits may be to the right of the decimal point; these digits represent the cents part of the price.

The following is a subset of the rows stored in the products table, as populated by the fundamental_user_schema.sql script:

id type_id name                 description               price -- ------- -------------------- -------------------- ----------  1       1 Beyond Understanding The frontiers of          11.25                                 human knowledge  2       1 Physics              Fundamental                  30                                 understanding of the                                 natural world  3       1 Star Travelers       The adventures of         25.99                                 futuristic                                 astronauts  4       2 Seventh Sense        Supernatural tale of      13.95                                 the unknown  5       2 Quantum Jump         The physics of the        49.99                                 quantum world

The first row in the products table has a type_id of 1, which means that the product in question is a book. You can see this for yourself: the row in the product_types table with an id value of 1 has the product type category name of "Book". Similarly, the fourth and fifth rows in the products table represent products that are videos.

2.2.1.3.4 The purchases table

The purchases table stores the purchase history of each customer. For each purchase, the following information is recorded:

  • The product ID number

  • The customer number

  • The quantity of the product purchased by the customer

The fundamental_user_schema.sql script uses the following CREATE TABLE statement to create the purchases table:

CREATE TABLE purchases (   product_id   NUMBER     CONSTRAINT purchases_fk_products     REFERENCES products(id),   purchased_by NUMBER     CONSTRAINT purchases_fk_customers     REFERENCES customers(id),   quantity     NUMBER NOT NULL,   CONSTRAINT   purchases_pk PRIMARY KEY (product_id, purchased_by) );

The columns in this table are as follows:

product_id

Identifies the product that was purchased. This column contains a product ID number that corresponds to a value in the id column of a product defined in the products table.

purchased_by

Identifies the customer purchasing the product. This column contains a customer number that must exist in the customers table.

quantity

Stores the quantity of the product purchased by the customer.

The following is a subset of the rows stored in the purchases table, as populated by the fundamental_user_schema.sql script:

product_id purchased_by quantity ---------- ------------ --------          1            1        1          2            1        3          3            1        1          4            1        2          5            1        4          6            1        1          7            1        3

In addition to the three columns shown here, the purchases table also has a type of constraint defined on it known as a table constraint. This constraint, named purchases_pk, is termed a table constraint because it's not defined on any one column. The distinction between a table constraint and a row constraint is that for a table constraint, a comma is used to separate the constraint definition from the column definition that precedes it. The constraint then becomes a table-level element instead of a column-level element. The purchases_pk constraint is also a PRIMARY KEY constraint, and specifies that the table's primary key consists of two columns: product_id and purchased_by. The combination of the two values in these columns must be unique for each row in the table.

2.2.2 Data Manipulation Language (DML) Statements

Data Manipulation Language ( DML) statements are used to add, retrieve, modify, and delete rows stored in database tables. In SQL, you have the following four DML statements:

  • SELECT

  • INSERT

  • UPDATE

  • DELETE

The following sections describe each of these statements. In the case of the SELECT statement, you'll start by looking at the simple case of retrieving data from a single table. Next, you'll learn how to restrict the results to records that interest you. Then, you'll see how those results can be sorted. Finally, you'll learn how to combine rows together from two tables.

2.2.2.1 Single table SELECT statements

The SELECT statement is used to retrieve information from tables in the database. In its simplest form, you specify the table from which you want to retrieve data and a list of column names to retrieve. The SELECT statement in the example here retrieves the id, first_name, last_name, dob, and phone columns from the customers table:

SQL> SELECT id, first_name, last_name, dob, phone 2 FROM customers;  ID FIRST_NAME LAST_NAME  DOB       PHONE --- ---------- ---------- --------- -------------   1 John       Smith      01-JAN-65 650-555-1212   2 Cynthia    Stevens    05-MAR-68 650-555-1213   3 Steve      Seymour    16-JUN-71 650-555-1214   4 Gail       Williams   01-DEC-75 650-555-1215   5 Doreen     Heyson     20-AUG-70 650-555-1216

As you can see, Oracle converts the column names that you specify into their uppercase equivalents, and by default displays only the last two digits of the year, even though it does store all four digits. Your DBA can change the default display format for dates. Although you can specify column and table names using either lowercase or uppercase, it is better to stick with one style. The examples in this book use uppercase for SQL keywords and lowercase for everything else.

If you want to select all columns in a table, use the asterisk character (*) in your select list instead of enumerating each column name. In the following example, the asterisk is used to select all columns from the customers table:

SQL> SELECT * FROM customers;  ID FIRST_NAME LAST_NAME  DOB       PHONE --- ---------- ---------- --------- ------------   1 John       Smith      01-JAN-65 650-555-1212   2 Cynthia    Stevens    05-MAR-68 650-555-1213   3 Steve      Seymour    16-JUN-71 650-555-1214   4 Gail       Williams   01-DEC-75 650-555-1215   5 Doreen     Heyson     20-AUG-70 650-555-1216
2.2.2.2 Row identifiers (rowids)

Each row in an Oracle database has a unique row identifier, which is used internally by Oracle to access the row. A row identifier, or rowid, is an 18-digit number represented using base 64 that contains the physical address of a row in an Oracle database. You can view the row identifiers for rows in a table by specifying the ROWID column in the select list of a query. The query in the following example retrieves the ROWID and id columns from the customers table:

SQL> SELECT ROWID, id FROM customers; ROWID               ID ------------------ --- AAAFxxAABAAAHaKAAA   1 AAAFxxAABAAAHaKAAB   2 AAAFxxAABAAAHaKAAC   3 AAAFxxAABAAAHaKAAD   4 AAAFxxAABAAAHaKAAE   5

The SELECT statements that you've seen so far have returned all the rows in the table from which you are selecting. You don't need to do that. Instead, you can use the WHERE clause to focus in on specific rows of interest.

2.2.2.3 The WHERE clause

The WHERE clause in a SELECT statement can be used to restrict the rows returned from the database. In the following example, the WHERE clause is used to retrieve the one row from the customers table for which the value stored in the id column is 3:

SQL> SELECT *    2  FROM customers    3  WHERE id = 3;  ID FIRST_NAME LAST_NAME  DOB       PHONE --- ---------- ---------- --------- ------------   3 Steve      Seymour    16-JUN-71 650-555-1214

In this example, the WHERE clause used the equality operator (=) to find cases in which the id column contained the value 3. There are other operators you can use in a WHERE clause besides the equality operator; Table 2-2 gives a complete list.

Table 2-2. SQL WHERE clause operators

Operator

Description

<

Less than

>

Greater than

<=

Less than or equal to

>=

Greater than or equal to

<> or !=

Not equal to

=

Equal to

The SELECT statement in the following example retrieves the id and name columns from the products table for all the rows where the value stored in the id column is less than 5:

SQL> SELECT id, name   2  FROM products   3  WHERE id < 5;  ID NAME --- --------------------   1 Beyond Understanding   2 Physics   3 Star Travelers   4 Seventh Sense
2.2.2.4 The LIKE operator

You can use the LIKE operator in a WHERE clause to see if any of the character strings in a text column match a pattern that you specify. You specify patterns using a combination of normal characters and the following two wildcard characters:

A percent sign (%)

Represents any number of characters

An underscore (_ )

Represents one character

The SELECT statement in the following example uses the LIKE operator to retrieve rows from the customers table where the second character of the value stored in the last_name column is a "t". The underscore (_) before the "t" allows any one character to be in the first position. The percent sign (%) following the "t" allows any number of characters:

SQL> SELECT *   2  FROM customers   3  WHERE last_name LIKE '_t%';  ID FIRST_NAME LAST_NAME  DOB       PHONE --- ---------- ---------- --------- ------------   2 Cynthia    Stevens    05-MAR-68 650-555-1213
2.2.2.5 Lists of values

You can use the IN and NOT IN operators in a WHERE clause to select only those rows where a column value is in, or not in, a list that you specify. The SELECT statement in the following example uses IN to retrieve the rows from the customers table where the value stored in the id column contains a 1, 2, or 5:

SQL> SELECT *   2  FROM customers   3  WHERE id IN (1, 2, 5);  ID FIRST_NAME LAST_NAME  DOB       PHONE --- ---------- ---------- --------- ------------   1 John       Smith      01-JAN-65 650-555-1212   2 Cynthia    Stevens    05-MAR-68 650-555-1213   5 Doreen     Heyson     20-AUG-70 650-555-1216

NOT IN functions in the opposite manner of IN. If you issue the same statement using NOT IN, you would get all customers except the ones you see in this example.

2.2.2.6 Ranges

You can use the BETWEEN and NOT BETWEEN operators in a WHERE clause to select rows where a column value is within a specified range. In the following example, BETWEEN is used to retrieve rows from the customers table where the id column is between 2 and 4:

SQL> SELECT *   2  FROM customers   3  WHERE id BETWEEN 2 AND 4;  ID FIRST_NAME LAST_NAME  DOB       PHONE --- ---------- ---------- --------- ------------------   2 Cynthia    Stevens    05-MAR-68 650-555-1213   3 Steve      Seymour    16-JUN-71 650-555-1214   4 Gail       Williams   01-DEC-75 650-555-1215

Notice that the BETWEEN range is inclusive. The endpoint values, 2 and 4 in this case, are accepted as being within the range.

2.2.2.7 Logical operators

You can use the logical operators AND and OR to combine expressions in a WHERE clause. If you use both AND and OR in the same expression, the AND operator takes precedence over the OR operator. The query in the following example retrieves rows from the customers table where either of the following two conditions is met:

SQL> SELECT *   2  FROM customers   3  WHERE dob > '01-JAN-1970'   4  OR id < 2   5  AND phone LIKE '%1211';  ID FIRST_NAME LAST_NAME  DOB       PHONE --- ---------- ---------- --------- ------------   3 Steve      Seymour    16-JUN-71 650-555-1214   4 Gail       Williams   01-DEC-75 650-555-1215   5 Doreen     Heyson     20-AUG-70 650-555-1216

Because the AND operator takes precedence over OR, you can think of the previous query as follows:

dob > '01-JAN-1970' OR (id < 2 AND phone LIKE '%1211')
2.2.2.8 The ORDER BY clause

The ORDER BY clause is used to sort the rows retrieved from the database, and may specify one or more columns on which to sort the data. The following example shows ORDER BY being used to sort the results of a query on the customers table by the last_name column:

SQL> SELECT *   2  FROM customers   3  ORDER BY last_name;  ID FIRST_NAME LAST_NAME  DOB       PHONE --- ---------- ---------- --------- ------------   5 Doreen     Heyson     20-AUG-70 650-555-1216   3 Steve      Seymour    16-JUN-71 650-555-1214   1 John       Smith      01-JAN-65 650-555-1212   2 Cynthia    Stevens    05-MAR-68 650-555-1213   4 Gail       Williams   01-DEC-75 650-555-1215

By default, the ORDER BY clause causes results to be sorted in ascending order. You can use the DESC keyword to specify that a particular column be sorted in descending order. Likewise, you can use the ASC keyword to explicitly specify the default ascending sort order for a column. The ORDER BY clause in the following example sorts rows retrieved from the customers table by the first_name column in descending order, and then within that by the last_name column in ascending order:

SQL> SELECT *   2  FROM customers   3  ORDER BY first_name DESC, last_name ASC;   ID FIRST_NAME LAST_NAME  DOB       PHONE ---- ---------- ---------- --------- --------------------    3 Steve      Seymour    16-JUN-71 650-555-1214    1 John       Smith      01-JAN-65 650-555-1212    4 Gail       Williams   01-DEC-75 650-555-1215    5 Doreen     Heyson     20-AUG-70 650-555-1216    2 Cynthia    Stevens    05-MAR-68 650-555-1213
2.2.2.9 Multiple-table SELECT statements

If you want to retrieve information that is stored in two or more tables, you must join the tables together within your query. To join two tables means that you use two tables in the FROM clause, and then specify at least one column from each table, along with a logical operator, in a WHERE clause.

Tables are often joined by relating a foreign key from the detail table to the primary key of the master table. You specify the join relationship using a logical comparison within your query's WHERE clause. The logical comparison comprises a column from one table, followed by a comparison operator (=, <, >, <=, >=, !=, <>) or LIKE, BETWEEN, etc., followed by a column from another table. The tables whose columns are used in the join must be contained in the FROM clause of the SELECT statement.

The following example retrieves the id and name columns from the products table, along with the name column from the product_types table. The join between the two tables is made through the id column from the product_types table and the type_id column from the products table. Notice that the table names precede the column names in the WHERE clause; this is only necessary if an identical column name is used in each table, so in this example the table names could be omitted from the WHERE clause:

SQL> SELECT products.id, product_types.name, products.name   2  FROM products, product_types   3  WHERE products.type_id = product_types.id   4  ORDER BY products.id;  ID NAME                           NAME --- ------------------------------ ------------------------------   1 Book                           Beyond Understanding   2 Book                           Physics   3 Book                           Star Travelers   4 Video                          Seventh Sense   5 Video                          Quantum Jump   6 Video                          2002: A Space Journey   7 DVD                            Super Force   8 DVD                            The Man from Another Planet   9 CD                             Classical Music  10 CD                             Pop 2000  11 CD                             Creative Riot  12 CD                             Front Line

The tables referenced in the SELECT statement can also be referred to using an alias. Aliases are useful when you have long table names, and their use is not limited to joins alone: in the next chapter, you will see how to use aliases with named iterators. The following example uses the aliases p and pt for the products and product_types tables:

SQL> SELECT p.id, pt.name, p.name   2  FROM products p, product_types pt   3  WHERE p.type_id = pt.id   4  ORDER BY p.id;  ID NAME                           NAME --- ------------------------------ ------------------------------   1 Book                           Beyond Understanding   2 Book                           Physics   3 Book                           Star Travelers   4 Video                          Seventh Sense   5 Video                          Quantum Jump   6 Video                          2002: A Space Journey   7 DVD                            Super Force   8 DVD                            The Man from Another Planet   9 CD                             Classical Music  10 CD                             Pop 2000  11 CD                             Creative Riot  12 CD                             Front Line
2.2.2.10 The INSERT statement

To add new rows to a table, use the INSERT statement. Typically, you supply the following information in an INSERT statement:

  • The name of the table into which the row will be added

  • A list of columns for which you want to supply values

  • A list of values to be stored in those columns

When you insert a row into a table, you don't need to supply a value for each column in the table. Thus, your column list may reflect only some of the columns available. You do, however, need to supply a value for all NOT NULL columns.

The INSERT statement in the following example adds a row to the customers table. Notice that the order of values in the VALUES list matches the order in which the columns are specified in the column list:

INSERT INTO customers (id, first_name, last_name, dob, phone)  VALUES (6, 'Fred', 'Smith', '01-JAN-1970', '650-506-1217');

If you're supplying values for all columns in the table, you can omit the column list and just supply the values to be inserted. For example:

INSERT INTO customers VALUES (7, 'Fred', 'Smith', '01-JAN-1970', '650-506-1217');

When you omit the column list, you must supply a value for each column, and the order must match the order in which the columns are listed when you describe the table using the SQL*Plus DESCRIBE command.

2.2.2.11 The UPDATE statement

To modify rows in a table, use the UPDATE statement. When you use the UPDATE statement, you generally must supply the following information:

  • The name of the table containing the row(s) that you wish to modify

  • A WHERE clause that identifies the specific row(s) that you wish to modify

  • A list of column names and new values for those columns

You can use an UPDATE statement to modify one or more rows, making the same change to each. The following statement updates the first_name column to "Jason" for the row where the id column equals 1:

UPDATE   customers SET   first_name = 'Jason' WHERE   id = 1;

This next example shows how to modify both multiple rows and multiple columns. It raises the price by 10% for all products currently priced in excess of $10.00, and changes the product names to uppercase for those products.

UPDATE   products SET   price = price * 1.10,   name = UPPER(name) WHERE   price > 10;

If you omit the WHERE clause in an UPDATE statement, the specified changes are applied to all rows in the table. Be careful not to omit the WHERE clause unless you really intend to make such a global change.

2.2.2.12 The DELETE statement

You can use the DELETE statement to remove rows from a table. The key thing to specify is a condition that identifies the row(s) that you wish to delete. For example, the following DELETE statement removes one row from the purchases table:

DELETE FROM   purchases WHERE   product_id = 1 AND   purchased_by = 1;

As with UPDATE, if you omit the WHERE clause, all rows in the table will be affected, or in this case, deleted.

2.2.3 Database Transactions

A database transaction is a set of SQL statements that defines a logical unit of work. An example of a transaction is the transfer of money from one bank account to another: one account is debited using an UPDATE statement, and the other is credited using another UPDATE. The results of both UPDATE statements must be permanently recorded in the database for the transaction to succeed. If there is a problem, both statements must be undone, or money will be lost. A transaction may contain any number of SELECT, INSERT, UPDATE, or DELETE statements.

A new transaction begins after you:

  • Connect to the database.

  • Perform a commit or rollback.

  • Run a DDL statement. DDL statements perform an implicit commit after they finish.

Performing a commit permanently records the results of the SQL statements making up the transaction in the database. Performing a rollback undoes the results of the SQL statements making up the transaction, resetting the table contents back to what they were before the transaction began.

A commit is performed in SQL using the COMMIT statement, and a rollback is performed using the ROLLBACK statement. The following example adds a row to the customers table, and then makes the change permanent by performing a commit.

INSERT INTO   customers VALUES   (7, 'Fred', 'Smith', '01-JAN-1970', '650-506-1217'); COMMIT;

This example updates a row in the customers table, and then undoes the change by performing a rollback:

UPDATE   customers SET   first_name = 'Jason' WHERE   id = 1; ROLLBACK;

If multiple database users are running transactions that affect the same tables, the effects of those transactions are separated from each other until a commit is performed. The following sequence of events, based on two users accessing a single table, illustrates this concept:

  1. User 1 performs a SELECT to get all the rows in Table 1.

  2. User 2 performs an INSERT to insert a row into Table 1, but doesn't commit the transaction.

  3. User 1 performs another SELECT, and gets the same results as in Step 1.

  4. User 2 performs a COMMIT.

  5. User 1 performs another SELECT, and sees the additional row added by User 2 in Step 2.

The point is that each user has a transaction associated with his or her database session, and the effects of these transactions are visible to other transactions only after a commit is performed. This behavior is referred to as transaction isolation.

The level of transaction isolation, or the degree to which the results of one transaction interfere with another, can be changed from this default behavior. I'll discuss this advanced topic in Chapter 9.

< BACKCONTINUE >

Index terms contained in this section

% (percent sign), SELECT statement
* (asterisk), SELECT statement
= (equality operator), SELECT statement
_ (underscore), SELECT statement
aliases, SQL
AND operator, SQL
ANSI (American National Standards Institute)
ASC keyword, SELECT statement
asterisk (*), SELECT statement
BETWEEN operator, SQL
CHAR datatype
COMMIT statement
comparison operators, SQL
CONSTRAINT clause, CREATE TABLE
CREATE TABLE statement
CREATE USER statement
creating tables
customers table
Data Definition Language
Data Manipulation Language
database administrator (DBA)
database transaction, SQL
datatypes
      Oracle database
DATE database type, Oracle
DBA (database administrator)
DDL (Data Definition Language)
      CREATE TABLE
      CREATE USER
      Oracle database types
      schemas
DELETE statement
DESC keyword, SELECT statement
DESCRIBE command, SQL*PLUS
DML (Data Manipulation Language)
      AND operator
      BETWEEN operator
      DELETE statement
      IN operator
      INSERT statement
      joining tables
      LIKE operator
      NOT BETWEEN operator
      NOT IN operator
      OR operator
      ORDER BY clause
      ROWID
      SELECT, single-table
      UPDATE statement
      WHERE clause
equality operator (=), SELECT statement
foreign keys
fundamental_user schema
fundamental_user_schema.sql
IN operator, SQL
Informix database
INSERT statement, SQL
joining tables
LIKE operator, SQL
logical operators
Microsoft SQL Server
MySQL database
NOT BETWEEN operator, SQL
NOT IN operator, SQL
NOT NULL keyword, CREATE TABLE
NUMBER database type, Oracle
operators, WHERE clause
OR operator, SQL
Oracle database types
ORDER BY clause, SQL
percent sign (%), SELECT statement
PRIMARY KEY keywords, CREATE TABLE
primary keys
product_types table
products table
purchases table
Relational Software Inc.
ROLLBACK statement
ROWID (row identifier)
schemas
SELECT statement
      AND operator
      ASC keyword
      asterisk
      BETWEEN operator
      DESC keyword
      equality operator (=)
      IN operator
      LIKE operator
      NOT BETWEEN operator
      NOT IN operator
      OR operator
      ORDER BY clause
      WHERE clause
sorting, SELECT statement results
SQL (Structured Query Language)
      AND operator
      BETWEEN operator
      COMMIT statement
      CREATE TABLE
      CREATE USER
      DDL statements, purpose of 2nd
      DELETE statement
      DML statements
      IN operator
      INSERT statement
      LIKE operator
      NOT BETWEEN operator
      NOT IN operator
      OR operator
      Oracle database types
      ORDER BY clause
      ROLLBACK statement
      ROWID
      schemas
      SELECT, single-table
      table joins
      transactions
      UPDATE statement
      WHERE clause
SQL*Plus
table constraints
tables
      creating
      joining
transaction isolation
transactions
underscore (_), SELECT statement
UPDATE statement, SQL
VALUES clause, INSERT statement
VARCHAR2 database type
WHERE clause, SQL
wildcard characters, SELECT statement



Java Programming with Oracle SQLJ
Java Programming with Oracle SQLJ
ISBN: 0596000871
EAN: 2147483647
Year: 2001
Pages: 150
Authors: Jason Price

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net