Oracle DML Commands

INSERT, UPDATE and DELETE statements have to comply with constraints such as NOT NULL, UNIQUE or CHECK. Also primary or foreign constraints can not be violated.

INSERT

INSERT is used to add data to the database. An INSERT statement using a VALUES clause inserts a single row as opposed to an INSERT statement with a subquery which can insert potentially multiple rows selected by the subquery. The INSERT command can also insert data into an updatable view's base table.

schema - owner.
table_name or view_name.
@dbl - database link for a remote table or view.
subquery1 - a subquery that is identical to a view query.
subquery2 - a subquery that returns rows or values to be inserted.
column - column name(s) to be inserted into. If omitted, all the table or view columns must have a matching value or subquery2 item.
expr - an expression or list of values corresponding to the inserted values.

UPDATE

schema - owner.
table_name, view_name or snapshot.
@dbl - database link for a remote table or view.
t_alias - object name alias.
subquery1 - a subquery that is in the same format as a view definition.
subquery2 - a subquery that returns values to be used to update the object.
subquery3 - a subquery that returns a single value.
column - column name(s) to be updated. Lists must be in parentheses.
expr - new value(s) assigned to the subquery or column list of columns.
WHERE - clause limiting rows to be updated. If no WHERE clause present then all rows are updated.

DELETE

The DELETE statement removes rows from tables allowing ROLLBACK or COMMIT. In contrast, TRUNCATE deletes rows without the possibility of ROLLBACK.

schema - owner.
table_name or view_name.
@dbl - database link for a remote table or view.
alias - table, view or subquery shorthand used in a WHERE clause, for instance,
```
SELECT a.FIELD, b.FIELD FROM TABLE1 a, TABLE2 b WHERE a.FIELD = b.FIELD;
```
subquery - a subquery that selects data to be deleted. The subquery can not query a table which appears in the same FROM clause as the subquery.
WHERE condition - deletion based on a condition.

SELECT

The SELECT statement is used to retrieve database values from a table/view or set of tables/views based on given selection criteria. An unrestricted SELECT retrieves all rows. Additionally, the SELECT statement is the basis for all subqueries in all other commands. Results are sorted in ascending order unless specified by DESC. The minimum form of the SELECT statement is as follows.

SELECT * FROM <table_name>;

Note that the DUAL table is required when doing non-directed SELECT statements such as from sequences or non-table related, single-row functions. If two or more tables have common column names in a SELECT statement then the table name or table alias must be used to differentiate between the columns. Column aliases can only be used in the ORDER BY clause. When the DISTINCT operator is used then the total number of bytes selected in all select list expressions is limited to the size of a data block less a small overhead.

DISTINCT - returns only one copy of any duplicate rows or of specific columns determined by the position of the DISTINCT and returned by the SELECT. If the DISTINCT is placed immediately following the SELECT, all combinations of the columns that follow will be distinct (unique with respect to all other rows selected).
ALL - the default. Returns all rows including duplicates.
* - all columns.
expr - selects an expression.
c_alias - column alias used in subsequent statements in the SELECT statement to refer to the aliased column.
schema - owner.
table_name, table, view_name, view or snapshot.
@dblink - database link for a remote table or view.
subquery - subquery which is specified identically to a view query definition.
WITH READ ONLY - subquery can not be updated. Applies to views only since this is the only way in which a subquery can be updated.
t_alias - table alias used in subsequent subqueries or WHERE clause conditions.
WHERE - the WHERE clause is used to restrict the rows returned. Only WHERE clauses use indexes. Thus the WHERE clause must match an index or parts of an index in the order of the columns in an index beginning with the first column otherwise a full table-scan will result. A SELECT command between two tables without a WHERE clause will return the Cartesian Product of both tables. The Cartesian Product would contain each row in the first table matched with each row of the second table.
ORDER BY - the ORDER BY clause forces ordering of returned rows with either ASC (ascending order - the default) or DESC (descending order). Note that the ORDER BY clause is only applied to the results of the SELECT statement after the WHERE clause has been applied. Thus it is not neccessary to include the contents of the WHERE clause into the ORDER BY clause since the WHERE clause has already ordered that set. The only case where the ORDER BY clause would contain columns contained in the WHERE clause would be when the WHERE clause must be over-ridden by the ORDER BY clause; this is extremely inefficient. It is best to use the WHERE clause, indexes and the ORDER BY clause in concert in order to achieve the most efficient return time of the result set of rows.
START WITH CONNECT BY - returns rows in a hierarchical order.
GROUP BY - the GROUP BY clause groups rows based on specified column values. The expression for the SELECT statement must include atleast one grouping function such as COUNT() and all columns not participating in the grouping functions must be included in the GROUP BY column list. The GROUP BY clause does not restrict values but simply groups them into coagulated sets of values. The GROUP BY clause can use a column name but not the column positional specification or column alias. Note the two examples below. It should be stated that application of the WHERE clause would be faster than that of the HAVING clause since the HAVING clause is applied to the resulting grouped by set rather than the WHERE clause applying to the database on retrieval of the data. In general the WHERE clause will filter rows out of the result before those rows are grouped and functions are performed on the groups. Therefore the WHERE clause is faster than the HAVING clause.
```
SELECT DEPT, EMPLOYEE, AVG(SALARY) FROM EMPLOYEES WHERE DEPT IN (20, 30) GROUP BY DEPT, EMPLOYEE;
```
```
SELECT DEPT, EMPLOYEE, AVG(SALARY) FROM EMPLOYEES GROUP BY DEPT, EMPLOYEE HAVING DEPT IN (20, 30);
```
HAVING - the HAVING clause must restrict the GROUP BY for those groups for which the HAVING condition is TRUE. There can not be a HAVING clause without a GROUP BY clause.
UNION, UNION ALL, INTERESECT and MINUS - SELECT statement set operation.
ASC or DESC - ascending or descending order.
FOR UPDATE - locks selected rows.
OF - only locks rows of given table.
NOWAIT - returns the statement initiator (user or program) the control if the SELECT statement attempts to lock a row which is locked by another user. Otherwise the SELECT statement will wait for any locked rows to be free and return the results when they are free.

Altering Columns in SELECT Queries

Changing Column Headings

Column headers can be altered in queries by substituting strings with those column headers. Thus the header is altered from the column name to some other value. This new column name is known as a column alias.

Below is an example. Note that the column alias does not need to be enclosed in single quotes ('') unless that column alias contains spaces.

SELECT table_name "Table Name" FROM ALL_TABLES;

Table Name
------------------------------
DUAL
SYSTEM_PRIVILEGE_MAP
TABLE_PRIVILEGE_MAP
STMT_AUDIT_OPTION_MAP
AUDIT_ACTIONS
PSTUBTBL
DEF$_TEMP$LOB
DEPT
EMP
BONUS
SALGRADE
LOCATION
DEPARTMENT
JOB
EMPLOYEE
SALARY_GRADE
PRODUCT
PRICE
CUSTOMER
SALES_ORDER
ITEM

Applying Formatting to Rows by Column

COLUMN <column_name> FORMAT <format_model>;

Thus the following commands would produce the following result.

column table_name format A10;
select table_name from all_tables;

TABLE_NAME
----------
DUAL
SYSTEM_PRI
VILEGE_MAP

TABLE_PRIV
ILEGE_MAP

STMT_AUDIT
_OPTION_MA
P
...

Setting Numeric Formats

The SET NUMFORMAT command controls the display of all number values and calculation results in a SELECT statement. The SET FORMAT command can be overwritten by individual column formatting commands.

SET NUMFORMAT <numeric_format_model>

Apostrophes in Character Strings

Apostrophes or single quotes are used to delineate the start and end of strings. Simply use two single quotes to display a single quote.

select 'This table''s name is ', table_name from all_tables;

'THISTABLE''SNAMEIS'  TABLE_NAME
--------------------- --------------------------------
This table's name is  DUAL
This table's name is  SYSTEM_PRIVILEGE_MAP
This table's name is  TABLE_PRIVILEGE_MAP
This table's name is  STMT_AUDIT_OPTION_MAP
This table's name is  AUDIT_ACTIONS
This table's name is  PSTUBTBL
This table's name is  DEF$_TEMP$LOB
This table's name is  DEPT
This table's name is  EMP
This table's name is  BONUS
This table's name is  SALGRADE
This table's name is  LOCATION
This table's name is  DEPARTMENT
This table's name is  JOB
This table's name is  EMPLOYEE
This table's name is  SALARY_GRADE
This table's name is  PRODUCT
This table's name is  PRICE
This table's name is  CUSTOMER
This table's name is  SALES_ORDER
This table's name is  ITEM

Concatenation Operators

The concatenation operator || can be used to concatenate strings and columns in SELECT query statements.

select owner||' owns '||table_name from all_tables;

OWNER||'OWNS'||TABLE_NAME
-------------------------------------------------------------
SYS owns DUAL
SYS owns SYSTEM_PRIVILEGE_MAP
SYS owns TABLE_PRIVILEGE_MAP
SYS owns STMT_AUDIT_OPTION_MAP
SYS owns AUDIT_ACTIONS
SYS owns PSTUBTBL
SYSTEM owns DEF$_TEMP$LOB
SCOTT owns DEPT
SCOTT owns EMP
SCOTT owns BONUS
SCOTT owns SALGRADE
DEMO owns LOCATION
DEMO owns DEPARTMENT
DEMO owns JOB
DEMO owns EMPLOYEE
DEMO owns SALARY_GRADE
DEMO owns PRODUCT
DEMO owns PRICE
DEMO owns CUSTOMER
DEMO owns SALES_ORDER
DEMO owns ITEM

COMMIT

Commit stores database changes.

ROLLBACK

ROLLBACK removes database changes.

SET AUTOCOMMIT

AUTOCOMMIT automatically commits changes to the database on INSERT, UPDATE and DELETE statements. SET AUTOCOMMIT has three options.

SET AUTOCOMMIT IMMEDIATE - activates autocommit.
SET AUTOCOMMIT ON - activates autocommit.
SET AUTOCOMMIT OFF - deactivates autocommit.
SHOW AUTOCOMMIT - shows the status of AUTOCOMMIT.

Firing Triggers with INSERT, UPDATE and DELETE Commands

INSERT, UPDATE and DELETE commands can fire a trigger when specified as an insert, update or delete trigger respectively. A view change will also fire any underlying base table triggers. Note that Oracle triggers are not rules. Rules can be used to construct a knowledge base. This means that for instance, an INSERT statement which selects multiple rows from a SELECT statement query can only fire a trigger once and not a trigger for each insertion. A trigger fired from a multiple row returning select statement will cause an error.

Updating Views

If a view has a WITH CHECK OPTION clause with its definition then the altered rows must meet the criteria in the defining SELECT statement of the view. The base table of a view can be altered with the INSERT, UPDATE and DELETE statements under the following conditions.

The view does not have a join.
The view does not use a set operator.
The view does not contain a GROUP BY clause.
The view does not use any grouping function.
The view does not have a DISTINCT operator.

The DUAL Table

The DUAL table is contained within the data dictionary and is used as a dummy object to pass SQL DML statement results into. For example, SELECT 'test string' FROM DUAL; gives the string test string.