Table of Contents
This chapter describes data manipulation language (DML).
It introduces DML commands in alphabetical order, and provides command descriptions, syntax diagrams, privileges, and examples. The syntax diagrams follow the format of “Chapter 3. SQL Operations”, and key words and syntax components will be explained in separate tables.
INSERT inserts zero or more rows into a table or a view.
A detailed description of INSERT follows:
-
Syntax
-
Privileges
Users who have the INSERT ANY TABLE system privilege can insert rows into all tables and views.
To insert rows into a table, the user should own the table, or have the INSERT schema object privilege for the table.
To insert rows into the base table of a view, both of the following two conditions should be satisfied:
-
The user owns the view, or has the INSERT schema object privilege for the view.
-
The owner of the schema to which the view belongs owns the base table, or has the INSERT schema object privilege for the base table.
-
-
Components
-
insert
Component Description single_table_insert Inserts a single row into a single table or view, by specifying the value explicitly or by using subqueries. multi_table_insert Inserts a row, evaluated in a subquery, into one or more tables. An alias cannot be given to the subquery, but can use a values_clause or WHEN conditional clause by referring to columns in the SELECT list of the subquery.
multi_table_insert can only be used in local tables, not in views or remote tables.
-
single_table_insert
Component Description into_clause Inserts specific column values into a row. values_clause Specifies the column values for the row to be inserted.
The column values should be specified in the same order as the columns into which they are to be inserted. If the column order is not specified in the statement, the column values should be specified in the column order defined in the table.
returning_clause Returns a value of the result row where the insert occurred. Evaluates an expression in the row, and stores its result in a host variable or tbPSM variables.
returning_clause has the following restrictions:
-
expr should only be a simple expression or a single-set aggregate function without a GROUP BY clause.
-
Simple expressions and single-set aggregate functions cannot be used together in the same returning_clause.
-
DISTINCT cannot be used in single-set aggregate functions.
-
LONG type values cannot be received from returning_clause.
subquery Inserts every row that is returned as the results of the subquery. For detailed syntax, refer to “5.1. SELECT”.
When inserting column values through a subquery, the number and order of columns returned as the result of the subquery should be the same as those of columns into which the values are to be inserted. Subqueries can refer to any table or view. To refer to the table or view to be inserted, use an alias.
error_logging_clause If an error occurs, details about the error and the data to be inserted are stored in the error logging table without stopping execution, and the next row will be processed. -
-
Component Description dml_table_expression Specifies objects into which rows will be inserted. column To insert values into particular columns, specify the column names.
If a default value is defined in a column which is not in the list, the default value is inserted, and if not, NULL is inserted. If a NOT NULL restriction is defined in a column which is not in the list, an error will occur and the row will not be inserted.
If the columns to which values will be inserted are not specified in the list, values should be assigned to all columns. If an error occurs while inserting a row using the INSERT statement, all rows which were inserted before the error are deleted together.
-
dml_table_expression
Component Description schema Specifies the schema that contains the object to be inserted. By default, the schema of the current user will be used. table
view
subquery
Specifies the name of the table, view, or subquery into which rows will be inserted. If INSERT is used with a table, INSERT triggers connected to the table will fire.
If a view or subquery is specified, rows will only be inserted to a key-preserved table. To insert rows into a view, the view must be updatable. If any of the following is defined in the view, the view is not updatable:
If the view is defined with WITH CHECK OPTION, the view should be able to select updated results. Otherwise, the view is not updatable.
PARTITION (partition) Specifies the name of the partition if the table is partitioned. Partitioned tables can improve performance. dblink Specifies a complete or partial name of a database link. An at symbol (@) must prepend the name. WITH CHECK OPTION Specifies that inserts should not be allowed if the insert result cannot be selected from views or subqueries. alias Specifies an alias for a table, view, or subquery so that it can be referenced elsewhere in the INSERT statement. -
values_clause
Component Description expr Specifies column values. Column names cannot be specified here. For details of the syntax, see “3.3. Expressions”. DEFAULT If DEFAULT is specified as a value for a column, if a default value is declared for the column, the default value is inserted, otherwise NULL is inserted. DEFAULT cannot be specified for views. -
returning_clause
Component Description expr Evaluates an expression in the result row, and its result is returned using returning_clause. For more information, refer to “3.3. Expressions”. data_item Specifies a host variable or tbPSM variable to store the expr value returned from the row. -
error_logging_clause
Component Description table Specifies the name of the error logging table.
By default, ERR$_ is added before the name of the table to insert. An error logging table name can be automatically created using the DBMS_ERRLOG package. The user can choose the name, but it is not recommended.
expr An expression that returns a string. It is used to tag an execution statement. REJECT LIMIT Specifies the maximum allowable number of errors. If more than this many errors occur, the DML statement will fail and be rolled back. The default value is zero. The following errors can be processed:
-
NOT NULL restriction
-
CHECK restriction
-
UNIQUE restriction
-
PRIMARY KEY and FOREIGN KEY restrictions
-
Data type error
The clause cannot be used for the following:
-
DEFERRED restriction
-
Direct-Path INSERT, multi-table INSERT, and MERGE statements
-
PRIMARY KEY, FOREIGN KEY, and UNIQUE restrictions in a UPDATE statement
-
Tables containing LONG or LOB type columns
-
-
multi_table_insert
Component Description ALL into_clause If several into_clauses are used after the reserved word ALL, this executes every into_clause once for the result rows of each subquery. values_clause Specifies column values for the row to be inserted. Columns of subqueries can be used.
The column values should be specified in the same order as the columns into which they are to be inserted. If the column order is not specified in the statement, the column values should be specified in the column order defined in the table.
subquery Inserts all rows returned by the subquery. For detailed syntax, see “5.1. SELECT”.
When inserting column values using a subquery, the number and order of columns returned as the result of the subquery should be the same as those of the columns into which the values are to be inserted. Subqueries can refer to any table or view. To refer to the table or view to be inserted, use an alias.
conditional_insert_clause Use a WHEN conditional clause to determine whether into_clause is executed. WHEN conditional clauses should refer to the select list of the subquery, and a single multi_table_insert clause can contain up to 127 WHEN clauses. -
conditional_insert_clause
Component Description ALL If ALL is specified, all into_clauses satisfying the WHEN conditional clause are executed. The default value is ALL. FIRST If FIRST is specified, only the first into_clause satisfying the WHEN conditional clause is executed, and the remaining into_clauses are skipped. WHEN condition THEN Specifies the condition for the WHEN conditional clause. If the condition is satisfied, the into_clause is executed. into_clause Used to insert rows only to specific columns. values_clause Specifies column values for the row to be inserted.
The column values should be specified in the same order as the columns into which they are to be inserted. If the column order is not specified in the statement, the column values should be specified in the column order defined in the table.
ELSE If any WHEN conditional clause is not satisfied, the into_clause that appears after the ELSE clause is executed. If there is no ELSE clause, no action is taken for that row.
-
-
Examples
The following example illustrates the use of INSERT:
INSERT INTO EMP VALUES (35, 'John', 'Houston', 30000, 5); INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES (35, John, 5); INSERT INTO EMP VALUES (35, 'John', DEFAULT, 30000, NULL);
The following example illustrates the use of error_logging_clause:
SQL> create table p (a number primary key); Table 'P' created. SQL> insert into p values (1); 1 row inserted. SQL> insert into p values (2); 1 row inserted. SQL> insert into p values (3); 1 row inserted. SQL> insert into p values (4); 1 row inserted. SQL> create table f (a number references p(a)); Table 'F' created. SQL> insert into f values (1); 1 row inserted. SQL> insert into f values (3); 1 row inserted. SQL> exec dbms_errlog.create_error_log('f'); PSM completed. SQL> commit; Commit completed. SQL> insert into f (select 1 from dual union all select 5 from dual) log errors reject limit 1; 1 row inserted. SQL> select * from f; A ---------- 1 3 1 3 rows selected. SQL> select * from err$_f; TIB_ERR_NUMBER$ --------------- TIB_ERR_MESG$ -------------------------------------------------------------------------------- TIB_ERR_ROWID$ TIB_ERR_OPTYP$ ------------------ -------------------- TIB_ERR_TAG$ -------------------------------------------------------------------------------- A -------------------------------------------------------------------------------- -10008 INTEGRITY constraint ('SYS'.'SYS_CON25700497') violated: primary key not found. I 5 1 row selected.
UPDATE updates column values in specified rows in a table or view.
A detailed description of UPDATE follows:
-
Syntax
-
Privileges
Users who have the UPDATE ANY TABLE system privilege can update any table or view. To update a table, the user must own the table, or have the UPDATE schema object privilege for the table.
To update the base table of a view, both conditions below should be satisfied:
-
The user owns the view, or has the UPDATE schema object privilege for the view.
-
The owner of the schema that contains the view owns the base table or has the UPDATE schema object privilege for the base table.
-
-
Components
-
update
Component Description dml_table_expression Specifies the objects to be updated. set_clause Specifies the column values to be updated. where_clause Specifies that only rows that meet a specified condition should be updated. By default, all rows in the table or view are updated. returning_clause Receives values of the result rows updated by returning_clause. Computes column expressions from the updated rows, and stores the results to host variables or tbPSM variables. error_logging_clause If an error occurs, details about the error and the data to be updated are stored in the error logging table without stopping execution, and the next row will be processed. -
dml_table_expression
Component Description Schema Specifies the schema that contains the object to be updated. By default, the schema of the current user will be used. table
view
subquery
Specifies the name of the table, view, or subquery where rows will be updated. If UPDATE is used with a table, UPDATE triggers connected to the table will fire.
If a view is specified, the base table of the view is updated. To update the view, the view should be updatable. If any of the following is defined in the view, the view is not updatable:
-
Set operator
-
DISTINCT operator
-
Aggregate function or analytic function
-
GROUP BY or CONNECT BY clause
-
WITH READ ONLY
dblink Specifies a complete or partial name of a database link. An at symbol (@) must prepend the name.
Database links have the following restrictions:
-
For a remote table, it is impossible to query a user-defined type or a REF object that is not supported in Tibero.
-
For a remote table, it is impossible to query an ANYTYPE, ANYDATA, or ANYDATASET column that is not supported in Tibero.
PARTITION (partition) Specifies the name of a partition, if the table is partitioned. Partitioning can improve performance. WITH CHECK OPTION Prohibits updating if the subquery cannot select the result of the update. alias Specifies an alias for the table, view or subquery so that it can referenced elsewhere in the UPDATE statement. -
-
Component Description column Specifies the column names to update. Columns that are not specified are not changed.
If a column is an LOB that is part of the partitioning key of a partitioned table, UPDATE will fail if the row would no longer be able participate in partitioning due to the UPDATE result.
subquery Specifies a scalar subquery that returns exactly one row. If multiple rows are returned, UPDATE will fail.
If only one column is specified in update_set_clause, the subquery should return only one value from the SELECT list.
If multiple columns are specified in update_set_clause, the number of values returned from the SELECT list from the subquery and the number of columns should match.
If the subquery does not return a row, the column will be updated to NULL.
expr Specifies a new value for the column to be updated to. For more information, refer to “3.3. Expressions”. DEFAULT If a default value is set for the column, the column will be updated to the default value. If a default value is not set, the column will be updated to NULL. DEFAULT cannot be specified when updating a view. -
where_clause
Component Description condition Conditional expression followed by the reserved word WHERE. Only rows are updated when the conditional expression is true.
Calculates a condition value based on the rows to be updated and can include a subquery. If where_clause is omitted, all rows of the table or view are updated.
-
Component Description expr An expression which evaluates the value to be returned through returning_clause from the result row. For more information, refer to “3.3. Expressions”. data_item Specifies a host variable or tbPSM variable to store the expr value returned from the row. returning_clause has the following restrictions:
-
expr should only be a simple expression or a single-set aggregate function without a GROUP BY clause.
-
Simple expressions and single-set aggregate functions cannot be used together in the same returning_clause.
-
DISTINCT cannot be used in single-set aggregate functions.
-
LONG type values cannot be received from returning_clause.
-
-
-
Examples
The following example illustrates the use of UPDATE:
UPDATE EMP SET SALARY = 35000; UPDATE EMP SET SALARY = 35000 WHERE DEPTNO = 5; UPDATE EMP SET DEPTNO = DEFAULT WHERE DEPTNO IS NULL; UPDATE EMP SET SALARY = SALARY * 1.05, ADDR = (SELECT LOC FROM DEPT WHERE DEPTNO = 5) WHERE DEPTNO = 5;
DELETE deletes rows from a table or a view. When deleting rows from a view that allows deleting, a base table to delete from will be chosen. The table may be partitioned.
A detailed description of DELETE follows:
-
Syntax
-
Privileges
Users who have the DELETE ANY TABLE system privilege can delete rows from any table or view.
To delete rows from a table, the user should own the table or have the DELETE schema object privilege for the table.
To delete rows from the base table of a view, both of the following conditions should be satisfied:
-
The user owns the view or has the DELETE schema object privilege for the view.
-
The owner of the schema that contains the view also owns the base table, or has the DELETE schema object privilege for the base table.
-
-
Components
-
delete
Component Description dml_table_expression Specifies the objects to be deleted. where_clause Only deletes rows that meet certain conditions. Evaluates the condition for each row to be deleted. Subqueries can be included. By default, all rows from a table or the base table of a view are deleted. For more information, refer to “3.4. Conditional Expressions”. returning_clause If returning_clause is used, the value of a deleted row can be received. Evaluates a value of the expression from the deleted row, and stores the result to host variables or tbPSM variables.
error_logging_clause If an error occurs, details about the error and the data to be deleted are stored in the error logging table without stopping execution, and the next row will be processed. -
dml_table_expression
Component Description schema Specifies the schema that contains the table or view. By default, the schema of the current user will be used. table
view
subquery
Specifies the name of the table, view, or subquery into which rows will be updated. If DELETE is used with a table, DELETE triggers connected to the table will fire.
If a view is specified, rows from the base table of the view are deleted. To delete rows from a view, the view should be updatable. If any of the following is defined in the view, the view is not updatable:
The table or index space occupied by the deleted row is retained.
PARTITION (partition) Specifies the name of the partition if the table is partitioned. Partitioned tables can improve performance. dblink Specifies a complete or partial name of a database link. An at symbol (@) must prepend the name.
Database links have the following restrictions:
-
For a remote table, it is impossible to query a user-defined type or a REF object that is not supported in Tibero.
-
For a remote table, it is impossible to query an ANYTYPE, ANYDATA, or ANYDATASET column that is not supported in Tibero.
subquery_restriction_clause If WITH READ ONLY is specified, the table or view cannot be deleted.
WITH CHECK OPTION is ignored.
alias Specifies an alias for the table, view or subquery so that it can referenced elsewhere in the DELETE statement. -
-
where_clause
Component Description condition Specifies conditional expressions after the reserved word WHERE. Use this clause to delete only those rows whose conditional expression is TRUE.
The condition is evaluated based on the row to be deleted, and can contain subqueries. By default, all rows are deleted.
-
returning_clause
Component Description expr Used to calculate values which are to be returned from the result row through returning_clause. For more information, refer to “3.3. Expressions”. data_item Stores the expr value evaluated from the row to host variables or tbPSM variables. returning_clause has the following restrictions:
-
expr should only be a simple expression or a single-set aggregate function without a GROUP BY clause.
-
Simple expressions and single-set aggregate functions cannot be used together in the same returning_clause.
-
DISTINCT cannot be used in single-set aggregate functions.
-
LONG type values cannot be received from returning_clause.
-
-
-
Examples
The following example illustrates the use of DELETE:
DELETE FROM EMP; DELETE FROM John.EMP WHERE SALARY < 20000
CALL executes a procedure or a function defined as standalone or within a package.
A detailed description of CALL follows:
-
Syntax
-
Privileges
To execute a standalone procedure or function, the EXECUTE schema object privilege for the procedure or function is required.
To execute a procedure or function defined within a package, the EXECUTE privilege for the package is required.
The EXECUTE ANY PROCEDURE system privilege enables users to execute any procedure or function.
-
Components
-
call
-
routine_clause
Component Description schema Specifies the schema that contains the standalone procedure or function, or specifies the schema in which the package where the procedure or function resides.
package Specifies the package that contains the procedure or function. function Specifies the name of the function to execute. procedure Specifies the name of the procedure to execute. argument Specifies arguments for the procedure or function, if applicable.
Arguments have the following restrictions:
-
Pseudo columns cannot be used for arguments.
-
Each argument specified with IN OUT or OUT should have a corresponding host variable.
-
The number of arguments, including return values, is limited to 1000.
-
Strings greater than 4KB, RAW types, and LONG types cannot be used as an argument.
-
-
-
Examples
The following example illustrates the use of CALL:
CALL get_board_name (30);
MERGE selects rows from one or more data sources and updates or inserts them into a target table. Users can specify conditions to determine whether to update or insert the rows.
MERGE is a convenient way to combine multiple operations. Using MERGE, users do not need to execute multiple INSERT, UPDATE, and DELETE DML statements. Users cannot update the same row multiple times in the same MERGE statement.
A detailed description of MERGE follows:
-
Syntax
-
Privileges
To use MERGE, the SELECT schema object privilege is required for the source table, and the INSERT and UPDATE schema object privileges are required for the target table.
To use a DELETE clause in merge_update_clause, the DELETE schema object privilege for the target table is required.
-
Components
-
merge
Component Description hint Specifies hints. INTO Specifies the table where rows will be inserted or updated. schema Specifies the schema name. alias Specifies an alias. USING The USING clause is used to specify the source data to be inserted or updated. Tables, views, and subquery results can be source data. table
view
subquery
Specifies the tables, views, and subqueries to be used as source data. ON (condition) The ON clause is used to specify the conditions for the MERGE operation. The conditions are specified in condition.
If condition evaluates to TRUE for a row of the table of the target data, the row corresponding to the source data table will be updated.
If condition is FALSE for all nodes, the row of the source data table will be inserted to the target data table.
merge_update_clause Specifies a new column value for the target data table. If the ON clause evaluates to TRUE, an update is executed.
If update is executed, all update triggers defined in the target data table fire.
merge_update_clause can be specified alone or with or with merge_insert_clause. If both clauses are specified, the order is not important. Either merge_update_clause or merge_insert_clause can be specified first.
merge_update_clause cannot update columns which are referenced by the ON clause.
merge_insert_clause If the ON clause evaluates to false, this clause specifies the values to be inserted to the target data table.
If INSERT is executed, all insert triggers defined in the target data table fire.
In order to insert all rows of the source data to a table, use an ON condition which always evaluates to false, such as 0=1. This is not the same as omitting merge_update_clause. If merge_update_clause is omitted, the database executes a join, but if a condition is used, the database executes an insert instead of a join.
merge_insert_clause can be specified alone or with merge_update_clause. If both clauses are specified, the order is not important.
-
merge_update_clause
Component Description column Specifies the names of the columns to update. expr An expression to be used to update the values of columns. DEFAULT Used to update a column value to the default value. where_clause Specify where_clause to configure update conditions. Conditions for both the source data and target data can be specified. DELETE where_clause To delete data during an update, specify DELETE where_clause.
Only rows which are affected by update are deleted by DELETE where_clause. If a row is deleted, delete triggers will fire..
-
merge_insert_clause
Component Description column Specifies the names of columns to insert. If this is omitted, the number of columns of target data tables should be same with the number of values specified in the VALUES clause. expr An expression to be used to insert column values. DEFAULT Used to insert the default value of the column. where_clause Specified to insert only those rows which satisfy certain conditions. This condition can only refer to table values of the source data. -
where_clause
Component Description condition Use where_clause to specify update conditions or insert conditions.
-
-
Examples
The following example illustrates the use of MERGE:
MERGE INTO BONUS USING PERSONNEL ON (B.PNUM = PERSONNEL.PNUM) WHEN MATCHED THEN UPDATE SET BONUS.BONUS = BONUS.BONUS*1.5 DELETE WHERE (PERSONNEL.SALARY > 3000) WHEN NOT MATCHED THEN INSERT VALUES (PERSONNEL.PNUM, PERSONNEL.SALARY*0.2);
Tibero can execute the following DML statements in parallel:
-
INSERT INTO SELECT
Parallel DML can specify PARALLEL hints in the same way as parallel queries. The INSERT INTO SELECT statement can specify which part of the statement will be executed in parallel based on the position of the PARALLEL hint.
If the hint comes after SELECT, only the SELECT portion is executed in parallel, and if the hint comes after INSERT, only the INSERT portion of the operation is executed in parallel. Therefore, to execute both operations in parallel, provide hints after both INSERT and SELECT.
-
UPDATE
-
DELETE
To execute DML in parallel in the session, specify the following:
SQL> ALTER SESSION ENABLE PARALLEL DML; Session altered.
If the session attribute is not changed as shown above with ALTER SESSION, PARALLEL hints do not work and parallel DML is not executed.
The following shows an error that occurs when enabling parallel DML while a transaction is already executing:
SQL> INSERT INTO TEMP_TBL VALUES (1, 1); 1 row inserted. SQL> ALTER SESSION ENABLE PARALLEL DML; TBR-12064: Unable to alter the session PDML state within a transaction
To execute ALTER SESSION while a transaction is executing, the current transaction should be terminated using the COMMIT or ROLLBACK commands.
When parallel DML is executed, restrictions exist to ensure transactional integrity. The two restrictions are as follows:
-
A transaction that has executed parallel DML cannot access a table that has been modified by parallel DML.
-
A transaction cannot execute parallel DML to access a table that has been modified in the same transaction.
The following example shows an error that occurs when a transaction which is executing parallel DML tries to access a table which has already been changed by parallel DML:
SQL> ALTER SESSION ENABLE PARALLEL DML;
Session altered.
SQL> INSERT /*+ parallel (3) */ INTO TEMP_TBL2 SELECT /*+ parallel (3) */ * FROM
TEMP_TBL;
100000 rows inserted.
SQL> SELECT COUNT(*) FROM TEMP_TBL2;
TBR-12063: Unable to read or modify an object after modifying it in parallelThe following example shows an error that occurs when trying to access a table which has been changed in the same transaction as parallel DML:
SQL> ALTER SESSION ENABLE PARALLEL DML;
Session altered.
SQL> INSERT INTO TEMP_TBL2 VALUES (1, 1);
1 row inserted.
SQL> INSERT /*+ parallel (3) */ INTO TEMP_TBL2 SELECT /*+ parallel (3) */ * FROM
TEMP_TBL;
TBR-12067: Unable to modify an object with PDML after modifying it
Parallel DML is not executed in the following cases:
-
When there are triggers in the target table for inserting, updating, deleting.
-
When returning_clause exists.
-
When a LOB column exists in the target table.
-
When the index of the target table is executing online_rebuild.
-
When a standby replication is used.
-
When constraints such as self-referential integrity, delete cascade, or deferred integrity exist.
-
When distributed transactions are used.
-
When DDL, TCS, or SCS is used