INSERT INTO (subquery)
 [(colunm_list)]
 { VALUES (value_list) | subquery};

INSERT INTO
 (SELECT * FROM departments WHERE name LIKE 'dp%')
 VALUES ('test', 'Test department.', 'Nowhere', NULL, NULL);

We also cannot insert rows into a view that contains any of the following:

• An aggregate function
• A DISTINCT operator
• A set operator (UNION, INTERSECT, MINUS, and UNION ALL)
• A GROUP BY, ORDER BY, or CONNECT BY clause
• A subquery in the SELECT clause

Using the INSERT statement we can insert into several tables. There are two types of multi-table insert: simple multitable insert and conditional multitable insert.

INSERT ALL
  INTO [schema.]{table_1 | view_1}[@DBlink]
    [(colunm_list)] VALUES ( expr_list )
  INTO [schema.]{table_2 | view_2}[@DBlink]
    [(colunm_list)] VALUES ( expr_list )
  ...
  INTO [schema.]{table_n | view_n}[@DBlink]
    [(colunm_list)] VALUES ( expr_list )
  subquery;

INSERT ALL
  INTO sales (prod_id, cust_id, time_id, amount)
       VALUES (product_id, customer_id, weekly_start_date, sales_sun)
  INTO sales (prod_id, cust_id, time_id, amount)
       VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
  INTO sales (prod_id, cust_id, time_id, amount)
       VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
  INTO sales (prod_id, cust_id, time_id, amount)
       VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
  INTO sales (prod_id, cust_id, time_id, amount)
       VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
  INTO sales (prod_id, cust_id, time_id, amount)
       VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
  INTO sales (prod_id, cust_id, time_id, amount)
       VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)
 SELECT product_id, customer_id, weekly_start_date, sales_sun,
        sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
   FROM sales_input_table;

The conditional insert (unlike the simple multitable insert) can insert new rows only in some of the tables, not all of them. The general syntax of the conditional insert statement is

INSERT { ALL | FIRST }
  WHEN condition_1
    THEN INTO [schema.]{table_1 | view_1}[@DBlink]
  WHEN condition_2
    THEN INTO [schema.]{table_2 | view_2}[@DBlink]
  ...
  WHEN condition_n
    THEN INTO [schema.]{table_n | view_n}[@DBlink]
  [ELSE]
    INTO [schema.]{table | view}[@DBlink]
  subquery;

INSERT FIRST
  WHEN category = 'V' THEN
    INTO video_sales (product_id, customer_id, quantity, price) VALUES (pid, cid, qty, price)
  WHEN category = 'A' THEN
    INTO audio_sales (product_id, customer_id, quantity, price) VALUES (pid, cid, qty, price)
  WHEN category = 'B' THEN
    INTO book_sales (product_id, customer_id, quantity, price) VALUES (pid, cid, qty, price)
  ELSE INTO errors VALUES (sysdate, 'Unknown product code: ' || categody)
 SELECT pid, cid, qty, price, category
   FROM sales;

Updating rows in a table

UPDATE [schema.]{table | view}[@DBlink]
   SET column = {expression | (subquery)}
 [WHERE conditions];

UPDATE [schema.]{table | view}[@DBlink]
   SET (column_list) = (subquery)
 [WHERE conditions];

UPDATE employees
   SET dob = trunc(dob);

UPDATE employees e
   SET bonus = (SELECT salary*0.10
                  FROM employees s, departments
                 WHERE s.ssn = head AND e.dep = id)
 WHERE dep = 'sd03';

Merging rows in a table

MERGE INTO [schema.]{table | view}[@DBlink]
   USING {[schema.]{table | view} | (subquery)}
      ON ( condition )
   WHEN MATCHED THEN UPDATE SET column = expression
   WHEN NOT MATCHED THEN INSERT [(column_list)] VALUES (expression_list);

MERGE INTO products p
  USING new_products np
     ON (p.id = np.id)
   WHEN MATCHED THEN UPDATE SET
        p.price=np.price, p.min_price=np.min_price, p.qty=p.qty+np.qty
   WHEN NOT MATCHED THEN INSERT
        VALUES (np.id, 33, np.price, np.min_price, np.qty);

Deleting rows from a table

DELETE [FROM] [schema.]{table | view}[@DBlink]
  [WHERE conditions];

-- remove all employees from the department 'sd03'
DELETE FROM employees
 WHERE dep = 'sd03';

-- remove all subdepartments
DELETE FROM departments
 WHERE id LIKE 's%';

-- remove all rows from the table TestTable
DEKETE FROM TestTable;

Another way to remove all rows from a table is to use the TRUNCATE command. The syntax for the TRUNCATE statement is

TRUNCATE TABLE [schema.]table [{ DROP | REUSE} STORAGE];

• TRUNCATE is DDL, and like all DDL, performs an implicit commit. That is, we cannot roll back a TRUNCATE. Any uncommitted DML changes also will be committed by the TRUNCATE.
• TRUNCATE is very fast on both large and small tables. DELETE will generate undo information, in case a roll back issued, but TRUNCATE will not generate undo.
• TRUNCATE resets the high-water mark in the table and all indexes. Since full-table scans and index fast scans read all data blocks up to the high-water mark, full-scan performance after a DELETE will not improve; after a TRUNCATE, it will be very fast.
• TRUNCATE does not fire any DELETE triggers.
• There is no object privileges that can be granted to allow a user to truncate another user's table. The DROP ANY TABLE system privilege is required to truncate a table in another schema.
• When a table is truncated, the storage for the table and all indexes can be reset back to the initial size. A DELETE will never shrink the size of a table or its indexes.
• We cannot truncate a table from an enabled referential integrity constraint. We must first disable the foreign key constraints that reference the parent table, and then truncate the parent table.

One more way to remove all rows from a table is to drop the table and recreate it back. This method also has its own characteristics:

• It invalidates dependent objects (like PL/SQL blocks using this table).
• It drops indexes, triggers, or referential integrity constraints.
• It requires privileges for other users to be re-granted.

Locking a table

The LOCK TABLE statement is used to lock am entire table, preventing other sessions from performing most or all DML on it. The LOCK TABLE statement syntax is

LOCK TABLE [schema.]table IN locking_mode MODE [NOWAIT];

• Row Share (RS)
• Row Exclusive (RX)
• Share (S)
• Share Row Exclusive (SRX)
• Exclusive (X)

Transaction control

• If a transaction is not yet started, then the commands INSERT, UPDATE, DELETE, and SELECT FOR UPDATE implicitly start a new transaction.
• The SET TRANSACTION statement explicitly starts a new transaction. This happens only if the transaction is not yet started. If it has been started already, then Oracle produces an error message "SET TRANSACTION must be first statement of transaction".

• We explicitly end it using COMMIT or ROLLBACK. Note that ROLLBACK TO SAVEPOINT does not end a transaction.
• When we execute a DDL statement (like TRUNCATE, ALTER, DROP, CREATE, etc).

Oracle implements consistency (one of the key concepts underlying the use of the transaction control statements) to guarantee that the data seen by a statement or transaction does not change until that statement or transaction completes. This support is only important to multiuser databases, where one session can change and commit data that is being read by another session.

There are two levels of consistency:

• statement level (default one). Use command

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

• transaction level, Use command

  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET TRANSACTION READ ONLY;

• SELECT (not SELECT FOR UPDATE)
• LOCK TABLE
• SET ROLE
• ALTER SYSTEM
• ALTER SESSION

Using the SET TRANSACTION statement we can also select a particular roll back segment to use for it. The syntax of the corresponding statement is

SET TRANSACTION USE ROLLBACK SEGMENT segment_name;