7.1. Manipulating Data through SQL

The Structured Query Language, SQL for short, includes Data Definition Language (DDL) statements, Data Control Language (DCL) statements, and Data Manipulation Language (DML) statements. You learned how to create, alter, and delete objects using DDL statements in Chapter 3, "Database Storage and Schema Objects." Chapter 6, "User Administration and Security," showed you how to use the DCL statements GRANT and REVOKE to give and take privileges on database objects. In this section, you will learn how to use the DML statements INSERT, UPDATE, and DELETE to add, modify, and remove data from your tables.

After using DML statements to add rows to a table, update rows in a table, or delete rows from a table, you must make these changes permanent by executing a COMMIT command. Alternatively, you can undo the DML changes with a ROLLBACK command. Until you commit the changes, other database sessions will not be able to see your changes.

7.1.1. Using INSERT Statements

You use the INSERT statement to add rows to one or more tables. You can create these rows with specific data values or copy them from existing tables using a subquery.

7.1.1.1. Inserting into a Single Table

When using SQL, the only way to add rows to an Oracle10g table is with an INSERT statement and the most common variety of INSERT statement is the single table insert. Figure 7.1 shows a diagram of the syntax for the single-table INSERT statement.

Figure 7.1. The syntax for a single-table INSERT statement

The column list is optional, with the default being a list of all columns in the table in COLUMN_ ID order. See the data dictionary views USER_TAB_COLUMNS, ALL_TAB_COLUMNS, or DBA_TAB_ COLUMNS for the COLUMN_ID. While inserting into a table is more common, you can also insert into a view, as long as the view does not contain one of the following:

• A DISTINCT operator

• A set operator (UNION, MINUS, and so on)

• An aggregate function (SUM, COUNT, AVG, and so on)

• A GROUP BY, ORDER BY, or CONNECT BY clause

• A subquery in the SELECT list

Here are some examples of using the INSERT statement to insert rows into a single table. The following inserts one row, channel 3, in the channels table:

INSERT INTO channels (channel_id ,channel_desc
   ,channel_class ,channel_class_id
   ,channel_total ,channel_total_id) VALUES
   (3 ,'Direct Sales' ,'Direct'
   ,12 ,'Channel total' ,1);

The following inserts one row, channel 5, in the channels table:

INSERT INTO channels VALUES
  (5 ,'Catalog' ,'Indirect' ,13 ,'Channel total' ,1);

The following copies zero or more rows from the territories table in the home_office database into the regions table:

INSERT INTO regions (region_id ,region_name)
  SELECT region_seq.NEXTVAL , terr_name

FROM territories@home_office
WHERE class = 'R';

The number and datatypes of values in the VALUES list must match the number and datatypes in the column list. The database will perform implicit datatype conversion if necessary to convert the values into the datatype of the target.

7.1.1.2. Inserting into Multiple Tables

Most INSERT statements are the single-table variety, but Oracle also supports a multiple-table INSERT statement. You most frequently use multitable inserts in data warehouse Extract, Transform, and Load (ETL) routines.

With a multitable insert, you can make a single pass through the source data and load the data into more than one table. By reducing the number of passes through the source data, you can reduce the overall work and thus achieve faster throughput. Figure 7.2 shows a diagram of the multitable INSERT statement syntax.

If a WHEN condition evaluates to TRUE, the corresponding INTO clause is executed. If no WHEN condition evaluates to TRUE, the ELSE clause is executed. The keyword ALL tells the database to check each WHEN condition. On the other hand, the keyword FIRST tells the database to stop checking WHEN conditions after finding the first TRUE condition.

In the following example, an insurance company has policies for both property and casualty in the policy table, but in their data mart, they break out these policy types into separate fact tables. During the monthly load, new policies are added to both the property_premium_fact and casualty_premium_fact tables. You can use a multitable INSERT to add these rows more efficiently than two separate INSERT statements. The multitable INSERT would look like this:

INSERT FIRST
WHEN policy_type = 'P' THEN
  INTO property_premium_fact(policy_id
       ,policy_nbr ,premium_amt)
  VALUES (property_premium_seq.nextval
         ,policy_number ,gross_premium)
WHEN p.policy_type = 'C' THEN
  INTO casualty_premium_fact(policy_id
       ,policy_nbr ,premium_amt)
  VALUES (property_premium_seq.nextval
         ,policy_number ,gross_premium)
SELECT policy_nbr ,gross_premium ,policy_type
FROM policies
WHERE policy_date >=
      TRUNC(SYSDATE,'MM') - TO_YMINTERVAL('00-01'),

By using this multitable INSERT statement instead of two separate statements, the code makes a single pass through the policy table instead of two and thus loads the data more efficiently.

Figure 7.2. The syntax for the multitable INSERT statement

7.1.2. Using UPDATE Statements

You use an UPDATE statement to change existing rows in a table. Figure 7.3 shows a diagram of the syntax for the UPDATE statement.

The column list can be either a single column or a comma-delimited list of columns. A single list of columns lets you assign single values—either literals or from a subquery. The following updates customer XYZ's phone and fax numbers, and sets their quantity based on their orders:

UPDATE order_rollup r
SET phone = '3125551212'
   ,fax   = '7735551212'
   ,qty   = (SELECT SUM(d.qty)
             FROM order_details d
             WHERE d.customer_id = r.customer_id)
WHERE r.customer_id = 'XYZ';

Like the CREATE TABLE and ALTER TABLE statements you saw in Chapter 6, when you use a comma-delimited list of columns, you must enclose them in parentheses. The comma-delimited list lets you assign multiple values from a subquery. The following updates both the quantity and price for customer XYZ for the order they placed on October 1, 2004:

UPDATE order_rollup
SET (qty, price) = (SELECT SUM(qty), SUM(price)
                    FROM order_details
                    WHERE customer_id = 'XYZ')
WHERE customer_id = 'XYZ'
 AND  order_period = TO_DATE('01-Oct-2004'),

Figure 7.3. The syntax for the UPDATE statement

Assigning multiple values from a single subquery can save you from having to perform multiple subqueries, thus improving the efficiency of your SQL.

7.1.3. Using DELETE Statements

You use the DELETE statement to remove rows from a table. A diagram of the syntax for the DELETE statement is shown in Figure 7.4.

Here are some examples of a DELETE statement. The following removes orders from certain states:

DELETE FROM orders
WHERE state IN ('TX','NY','IL')
 AND  order_date < TRUNC(SYSDATE) - 90

The following removes customer GOMEZ:

DELETE FROM customers
WHERE customer_id = 'GOMEZ';

The following removes duplicate line_detail_ids. Note that the keyword FROM is not needed.

DELETE line_details
WHERE rowid NOT IN (SELECT MAX(rowid)
                    FROM line_detail
                    GROUP BY line_detail_id)

--Remove all rows from the table order_staging
DELETE FROM order_staging;

The WHERE clause is optional, and when it is not present, all rows from the table are removed. If you need to remove all rows from a table, consider using the TRUNCATE statement. TRUNCATE is a DDL statement and, unlike the DELETE statement, does not support a ROLLBACK. Using TRUNCATE, unlike using DELETE, does not generate undo and executes much faster for a large table. If you want to empty a table of all rows, use a TRUNCATE statement instead of a DELETE. The TRUNCATE executes faster and may generate significantly less undo.

Figure 7.4. The syntax for the DELETE statement