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.

UPDATE

DELETE

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

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.

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.

  1. SET AUTOCOMMIT IMMEDIATE - activates autocommit.
  2. SET AUTOCOMMIT ON - activates autocommit.
  3. SET AUTOCOMMIT OFF - deactivates autocommit.
  4. 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 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.