0252-0254

Previous Table of Contents Next

Page 252

subquery is used when the VALUES option is not used. The columns in the subquery must match the sequence and datatypes of the columns in the insert list.

The following example inserts one row into the dept table:

 INSERT INTO dept  (dept_no,                    name,                    adrs_id)    VALUES (dept_seq.NEXTVAL,            `CUSTOMER SERVICE',            adrs_seq.NEXTVAL); 

Sequences dept_seq and adrs_seq are used to retrieve the next numeric values for dept_no and adrs_id.

If you need to insert multiple rows, the INSERT EXAMPLE 1 statement must be executed for each individual row. If a subquery can be used, multiple rows are inserted for each row returned by the subquery as in the following example:

 INSERT INTO emp (emp_id,                  first_name,                  last_name,                  dept_no,                  hire_date,                  job_title,                  salary,                  manager_id)       SELECT emp_seq.NEXTVAL,              new.first_name,              new.last_name,              30,              SYSDATE,              `CUSTOMER REPRESENTATIVE',              new.salary,              220       FROM candidates new       WHERE new.accept      = `YES'             AND new.dept_no = 30; 

This example inserts all rows from the candidates table that are assigned to department number 30 and are accepted. Because the department number and manager ID are known, they are used as constants in the subquery. The UPDATE statement is used to change existing rows in the database. The syntax for the UPDATE statement is

 UPDATE  schema.table SET column(s) = expr sub  query  WHERE condition 

schema is an optional parameter to identify which database schema to use for the update. The default is your own.

table is mandatory and is the name of the table.

SET is a mandatory reserved word.

column is a list of columns that receive the updated values.

expr is the new value to be assigned.

Page 253

sub query is a select statement that retrieves the new data values.

WHERE is optional and is used to restrict which rows are to be updated.

The next example transfers an employee named John Doe to department 30:

 UPDATE emp    SET dept_no = 30 WHERE last_name = `DOE'       AND first_name = `JOHN'; 

If there is more than one John Doe, further restrictions must be made in the WHERE clause:

 UPDATE emp    SET salary = salary + (salary * .05); 

This update example gives everyone in table emp a 5 percent increase in salary. The following example gives raises to employees located in Rochester:

 UPDATE emp a    SET a.salary = (SELECT a.salary                        + (a.salary * DECODE(d.name, `SALES', .1,                                                     `ADMIN', .07,                                                              .06)) FROM dept d                    WHERE d.dept_no = a.dept_no) WHERE a.dept_no = (SELECT dept_no                    FROM dept y, addresses z                    WHERE y.adrs_id = z.adrs_id                      AND z.city    = `ROCHESTER'); 

The amount of the raise is handled by the DECODE statement evaluating the department name. Employees in the sales department receive a 10 percent raise, the admin department receives a 7 percent raise, and everyone else receives a 6 percent raise.

The DELETE statement is used to remove database rows. The syntax for DELETE is

 DELETE FROM schema.table WHERE condition 

schema is an optional parameter to identify which database schema to use for the delete. The default is your own.

table is mandatory and is the name of the table.

WHERE restricts the delete operation.

An example of a SQL DELETE statement is provided here:

 DELETE FROM addresses WHERE adrs_id = (SELECT e.adrs_id                  FROM emp e                  WHERE e.last_name   = `DOE'                    AND e.first_name  = `JOHN'); DELETE FROM emp  e WHERE e.last_name   = `DOE'   AND e.first_name  = `JOHN'; 

Page 254

If employee John Doe leaves the company, you probably want to delete his records from the database. One way to accomplish this is to delete the row containing his name from the addresses table and the emp table. To find John Doe in the addresses table, you must perform a subquery using the emp table. Therefore, the entry in the emp table must be the last row to be deleted or else there is an orphan row in the addresses table.

In the next example, all rows in the dept table are deleted if the corresponding adrs_id is null:

 DELETE FROM dept WHERE adrs_id is null; 

Deletes are permanent! Once the commit takes place, it is impossible to get the rows apart by issuing an INSERT statement. There is no undo command.

TIP
DELETEs, UPDATEs, and INSERTs are all permanent once a commit takes place. The only possible rescue is issuing a ROLLBACK before issuing a commit. You can test changes with the help of ROLLBACK to reverse the change if the correct result is not obtained.

Master/Detail or Parent/Child SQL

If a SQL statement is constructed with multiple tables, a parent/child relationship might be in effect. This relationship can be enforced using foreign keys and cascade deletes within the schema. If so, the order of events must be considered when issuing an INSERT, UPDATE, or DELETE statement.

The user must be familiar with the database schema in use and the corresponding constraints to properly join tables. Writing poorly constructed SELECT statements does not generally harm the database but might decrease the system performance and possibly give a false relationship representation to the users. Poorly constructed INSERT, UPDATE, or DELETE statements can produce disastrous results.

You must make certain assumptions before considering the following examples:

  • An employee cannot be entered without a department number. This indicates that the emp table is a child of the dept table.
  • Addresses do not have to be entered when creating a new employee or department. Therefore, the addresses table is optional and is a child of the emp table and a child of the dept table.

If these constraints are enforced in the database, protection is provided when a parent row is deleted, but the corresponding children are not deleted.

In the following example, all the department names and numbers are displayed (the parent) with all the corresponding employees (the children) in the departments:

Previous Table of Contents Next


Oracle Unleashed
Oracle Development Unleashed (3rd Edition)
ISBN: 0672315750
EAN: 2147483647
Year: 1997
Pages: 391

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