Oracle10g SQL, Chapter 8

Objectives:  

• DML Commands: INSERT, UPDATE, DELETE.

• Database Integrity Example.

• Setting a default value.

• New MERGE command.

• Transaction Processing.

• Query Flashbacks.
   

Nested Query in the INSERT STATEMENT:

Syntax:

      INSERT INTO TableName1(ColumnName1, ColumnName2, ColumnName3,�)  
                        SELECT ColumnName1, ColumnName2, ColumnName3.  
                        FROM  TableName2  
                        WHERE <conditional expression>;

Notes:   Map your columns correctly!!  Also,  you could insert into a table based on a select statement involving more than one table.

INSERT INTO customers (customer_id, first_name, last_name)
        SELECT 10, first_name, last_name FROM customers WHERE customer_id=1;

Nested Query in the UPDATE STATEMENT: 
      Updates a column value based on one instance of value returned by a sub-query.

Syntax:

UPDATE table_name     SET  columname  =  (  SELECT column_name/expression )  
         WHERE ...

Notes:  The select clause is being used to assign a value to a column:

Your sub-select must be enclosed in parentheses. 

Make sure that your nested query returns only one value!!

Example:  

SQL> select employee_id, last_name, salary from employees;

 

EMPLOYEE_ID LAST_NAME      SALARY
----------- ---------- ----------
          1 Smith          800000
          2 Johnson        600000
          3 Hobbs          150000
          4 Jones          500000

SQL> UPDATE employees SET salary = (SELECT MAX(salary) FROM employees)
WHERE employee_id = 2;

1 row updated.

 

SQL> select employee_id, last_name, salary from employees;

 

EMPLOYEE_ID LAST_NAME      SALARY
----------- ---------- ----------
          1 Smith          800000
          2 Johnson        800000
          3 Hobbs          150000
          4 Jones          500000

UPDATE customers
     SET (city, state) = (SELECT city, state FROM zipcode z WHERE  z.zip = customers.zip_code)

THE RETURNING CLAUSE:

The returning clause can be used to return any data from the table just updated, but just for one row of data.  In 10g this can include Aggregate functions. 

SQL> VAR v_dob VARCHAR2(20);

SQL> UPDATE customers SET phone = '555-1212' WHERE customer_id=2 RETURNING dob INTO :v_dob;

1 row updated.

SQL> PRINT :v_dob;

V_DOB
--------------------------------
05-FEB-68 

TRUNCATE TABLE <table-name>:  This is commond to remove all rows from a table.  It is much more effecient than DELETE FROM table.  It is actually considered a DDL command instead of a DML command.

TRUNCATE TABLE customers2; 

DEFAULT VALUES:

ALTER TABLE employees ADD (start_date DATE DEFAULT SYSDATE NOT NULL);

SQL> INSERT INTO employees (employee_id, first_name, last_name) VALUES (5, 'Howard', 'Stern');

1 row created.

SQL> select employee_id, first_name, last_name, start_date from employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME  START_DAT
----------- ---------- ---------- ---------
          1 James      Smith      19-FEB-07
          2 Ron        Johnson    19-FEB-07
          3 Fred       Hobbs      19-FEB-07
          4 Susan      Jones      19-FEB-07
          5 Howard     Stern      19-FEB-07

Merging Rows Using MERGE:

MERGE INTO products p USING product_changes pc ON (p.product_id = pc.product_id)
WHEN MATCHED THEN
    UPDATE
        SET p.product_type_id = pc.product_type_id,
                p.name = pc.name,
                p.description = pc.description,
                p.price = pc.price
WHEN NOT MATCHED THEN
    INSERT (p.product_id, p.product_type_id, p.name, p.description, p.price)
    VALUES (pc.product_id, pc.product_type_id, pc.name, pc.description, pc.price);

Transaction Processing: 

Many times a change to the database requires several SQL-Statements, for example transferring money from a savings account to a checking account, requires an UPDATE to the savings account to decrement, and UPDATE to the checking account to increment it, and an INSERT INTO a journal.  This is called a transaction.  If any of the SQL-Statements fail, the database may wind up with incorrect data.  For example if the UPDATE to the savings account succeeds, but the UPDATE to the checking account does not, then your out $500, or whatever the amount is.  To protect against this problem most databases support transaction processing, this works by holding the SQL-Statements in a buffer area, until a COMMIT statement is issued.   A commit statement simply tells the database that it is ok to write the buffer items to disk.  If an error occurs you can undo the entire transaction with the ROLLBACK command. 

A transaction is:

�        Logical Unit of Work.

�        May Consist of Many SQL Statements.

�        Commits and Rollbacks.

�        Savepoints.

�        Can be used to provide data consistency.

ACID Transaction Properties (Atomicity, Consistency, Isolation, Durability):

Transaction Isolation Levels (Phantom Reads, Non-repeatable Reads, Dirty Reads):

• READ UNCOMMITTED (Not supported by Oracle):  Phantom Reads, Non-repeatable Reads, Dirty Reads are all allowed

• READ COMMITTED (Oracle Default): Phantom Reads and Non-repeatable Reads allowed, but Dirty Reads not allowed.

• REPEATABLE READS (Not supported by Oracle): Phantom Reads permitted, but Dirty Reads and Non-repeatable Reads are not allowed.

• SERIALIZABLE (Most Restrictive and usually not necessary): Phantom Reads, Non-repeatable Reads, Dirty Reads are NOT allowed.

SET TRANSACTION:  Indicates the beginning of a transaction and sets key features.

            SET TRANSACTION;

            SET TRANSACTION READONLY;

            -- Transaction Level consistency
            SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

            -- Statement Level consistency
            SET TRANSACTION ISOLATION LEVEL COMMITTED;

COMMIT:  Ends current transaction by saving database changes and starts new transaction.

ROLLBACK:  Ends current transaction by discarding database changes.

            ROLLBACK;

            ROLLBACK TO SAVEPOINT <save-point-name>;

SAVEPOINT:  Defines "midpoint" for the transaction to allow partial rollbacks.

SAVEPOINT <save-point-name>;

Example(s):  

SET TRANSACTION;

FOR iTries IN 1..10 LOOP
        BEGIN
                UPDATE savings  
                SET balance = balance - 500
                WHERE acct_no = 1234;

                 IF (SQL%ROWCOUNT = 0) THEN
                        ROLLBACK;
                        RETURN;
                 END IF;
         EXCEPTION
                WHEN OTHERS THEN
                        ROLLBACK;
                        GOTO end_of_loop;
         END;

         
         BEGIN
                 UPDATE checking  
                  SET balance = balance + 500  
                  WHERE acct_no = 1235;  
                 
                 IF (SQL%ROWCOUNT = 0) THEN
                        ROLLBACK;
                        RETURN;
                 END IF;

        EXCEPTION
              WHEN OTHERS THEN
                        ROLLBACK;
                        GOTO end_of_loop;
        END;

        BEGIN
               INSERT INTO journal (journal_id, tran_tp, credit_account, debit_account, amount)  
                    VALUES (journal_seq.NEXTVAL, 1, 1234, 1235, 500);

                 IF (SQL%ROWCOUNT = 0) THEN
                        ROLLBACK;
                        RETURN;
                 END IF;

        EXCEPTION
              WHEN OTHERS THEN
                        ROLLBACK;
                        GOTO  end_of_loop;
        END;

        EXIT;

        <<end_of_loop>>

            END LOOP;

            COMMIT;  

METHOD TWO - Separate Loops:

CREATE OR REPLACE PROCEDURE transfer_funds (
        p_transfer_from savings.acct_no%TYPE,
        p_transfer_to checking.acct_no%TYPE,
        p_last_update DATE,
        p_amount NUMBER(9,2))
BEGIN

SET TRANSACTION;

FOR iTries IN 1..10 LOOP
        BEGIN      
                UPDATE savings  
                 SET balance = balance - p_amount,
                         last_update = SYSDATE 
                 WHERE acct_no = p_transfer_from
                   AND last_update = p_last_update;

                 IF (SQL%ROWCOUNT = 0) THEN
                        ROLLBACK;
                        RETURN;
                 ELSE
                         SAVEPOINT A;
                         EXIT;  
                 END IF;

        EXCEPTION
              WHEN OTHERS THEN
                        ROLLBACK;
                        IF (Not recoverable(SQLCODE)) THEN
                                RETURN;
                        END IF;
        END;
END LOOP;

IF (SQLCODE <> 0 OR SQL%ROWCOUNT = 0) THEN
        RETURN;
END IF;

FOR iTries IN 1..10 LOOP
        BEGIN
              UPDATE checking  
              SET balance = balance + p_amount  
               WHERE acct_no = p_transfer_to;  

               IF (SQL%ROWCOUNT = 0) THEN
                        ROLLBACK;
                        RETURN;
               ELSE
                        SAVEPOINT B;  
                        EXIT;
                END IF;

        EXCEPTION
                WHEN OTHERS THEN
                       ROLLBACK TO SAVEPOINT A;
                        IF (Not recoverable(SQLCODE)) THEN
                                ROLLBACK;
                                RETURN;
                        END IF;

        END;
END LOOP;

IF (SQLCODE <> 0 OR SQL%ROWCOUNT = 0) THEN
     ROLLBACK;
     RETURN;
END IF;

FOR iTries IN 1..10 LOOP

        BEGIN
               INSERT INTO journal (journal_id, tran_tp, credit_account, debit_account, amount)  
                        VALUES (journal_seq.NEXTVAL, 1, p_transfer_from, p_transfer_to, p_amount);

                 IF (SQL%ROWCOUNT = 0) THEN
                        ROLLBACK;
                        RETURN
                 ELSE
                        COMMIT;  
                        EXIT;
                 END IF;

        EXCEPTION
                WHEN OTHERS THEN
                       ROLLBACK TO SAVEPOINT B;

                        IF (Not recoverable(SQLCODE)) THEN
                                ROLLBACK;
                                RETURN;
                        END IF;

        END;

END LOOP;  

IF (SQLCODE <> 0 OR SQL%ROWCOUNT = 0) THEN
     ROLLBACK; 
     RETURN;
END IF;

In the first example all three SQL-Statements are contained within a loop (Transaction Loop)  they are then committed only if all three finish successfully, otherwise the loop will retry up to ten times to get the transaction to succeeded, if it does not it will finally be rolled back.  The SET TRANSACTION command is Always before the loop begins and the COMMIT is always after the loop completes.

In the second example each statement is given its own loop.  The first SQL-Statement creates a savepoint (A) if it succeeds, otherwise it is rolled back and retries for a maximum of ten tries.  If unsuccessful after ten attempts, the entire transaction is rolled back and processing returns to the calling program. 

The second SQL-Statement Creates a savepoint (B) if it succeeds, otherwise it is Rolled back to the Prior savepoint and retries itself for a maximum of ten tries. If unsuccessful after ten attempts, the entire transaction is rolled back and processing returns to the calling program. 

Finally the last SQL-Statement will COMMIT the entire transaction if successful, otherwise it will rollback to the Prior savepoint and retries itself for a maximum of ten tries. If unsuccessful after ten attempts, the entire transaction is rolled back and processing returns to the calling program. 

QUERY Flashbacks:  Allow you to view state of the database back in a period of time. 

Steps:

1. Grant Permission: GRANT EXECUTE ON SYS.DBMS_FLASHBACK TO store;

2. EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE -10/1440);  -- Ten Minutes (24 * 60 = 1440 minutes in a day).

3. EXECUTE DBMS_FLASHBACK.DISABLE();

Alternatively you can use a System Change Number:

VARIABLE current_scn NUMBER

EXECUTE :current_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();

PRINT current_scn

INSERT INTO products (product_id, product_type_id, name, description, price)
        VALUES (15, 1, 'Back to the Future', 'Complements of the Oracle Time Machine', 39.95);

COMMIT;

SELECT * FROM products WHERE product_id=15;

EXECUTE DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:current_scn); 

SELECT * FROM products WHERE product_id=15;

EXECUTE DBMS_FLASHBACK.DISABLE();