SoFunction
Updated on 2025-03-02

A complete summary of common statements for Oracle database

Create/delete database

# Createcreate database databasename
# deletedrop database databasename

Create/delete tables

# General creationcreate table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
# Create a new table using old tablesselect * into table_new from table_old 
# Create a new table using old tablescreate table tab_new as select col1,col2… from tab_old 
# Delete tabledrop table tabname

Other table operations

# Rename tablealter table Original table name rename to New table name

Add fields

  • grammar:alter table table name add (field name field type default value is empty);

  • For example:

    alter table tablename add (ID varchar2(30) default 'null' not null);

Modify fields

  • grammar:alter table table name modify (field name field type default value whether empty);

  • For example:

    alter table tablename modify (ID number(4));

Rename the field

  • grammar:alter table table name rename column column name to new column name -- where: column is the keyword

  • For example:

    alter table tablename rename column ID to newID;

Delete fields

  • grammar:alter table table name drop column field name;

  • For example:

    alter table tablename drop column ID;

Primary key/index/view related

# Add primary keyalter table tabname add primary key(col)
# Delete the primary keyalter table tabname drop primary key(col)
# Create an indexcreate [unique] index idxname on tabname(col….)
# Delete the index (The index is not changeable, and you must delete and rebuild if you want to change it.)drop index idxname
# Create a viewcreate view View name as select statement
# Delete the viewdrop view viewname

Data Insert

# Add all fields - Syntax:insert into Table name values(Values ​​for all columns); 
#For example:insert into test values(1,'zhangsan',20);
 
# Custom field addition - Syntax:insert into Table name(List) values(The corresponding value);
# For example:insert into test(id,name) values(2,'lisi');

# Insert Table 1 data into Table 2-Syntax:insert into surface2 select * from surface1;
# For example:insert into test(name,role,createId,createdate) select name,role,id,now() from test_user;

Data update

# grammar:update surface set List=New value [where condition] --更新满足condition的记录
# For example:update test set name='zhangsan2' where name='zhangsan'

Data deletion

# delete syntax:delete from Table name where condition --删除满足condition的记录
# For exampledelete from test where id = 1;

delete from test -->Delete all deleteWhen deleting，Logs will be recorded Deletion will be slow

# truncate syntax:truncate table Table name --Delete all数据，It will not affect the table structure，不Logs will be recorded，Data cannot be restored，Delete quickly

Field stitching

  • CONCAT (char1, char2), in oracle, the concat function can only splice two values, this mysql is different
  • Returns the result after two strings are concatenated, two parameters char1 and char2 are the two strings to be concatenated.
  • Equivalent operation: Connection operator "||"
  • If any of char1 and char2 are NULL, it is equivalent to connecting a space
  • Note: It is recommended to use "||" to be more intuitive when connecting multiple strings.
 #String function CONCAT() function, used to connect stringsSELECT CONCAT(ename,sal) FROM emp
 
SELECT CONCAT( CONCAT(ename,','),sal) FROM emp
 
SELECT ename||','||sal FROM emp

merge into

  • In Oracle, regular DML statements can only complete a single function. For example, insert/delete/update can only choose one of three, while merge into statements can update/insert/delete a table at the same time.
  • merge into is commonly used in data synchronization scenarios. It will select a base table as the data source and then match the records of the target table. According to the matching results, the target table can be updated/inserted/deleted at the same time.

grammar

MERGE INTO target_table
USING source_table ON (join_condition)
WHEN MATCHED THEN UPDATE SET … [WHERE …] [DELETE … WHERE …]
WHEN NOT MATCHED THEN INSERT (column_list) VALUES (value_list) [WHERE …]
LOG ERRORS INTO … REJECT LIMIT [integer|UNLIMITED]
  • target_table is the table to be updated
  • source_table is the reference table
  • USING … ON () is the joining condition of two tables, used to determine whether the records match
  • WHEN MATCHED Operations performed by records that meet matching conditions can be updated or deleted
  • WHEN NOT MATCHED For records that do not meet the matching conditions, you can insert them.
  • LOG ERRORS INTO can record records matching errors into log tables

Case

material

create table src_table(
id number(6),
name varchar2(32),
salary number(6),
bonus number(6));
 
insert into src_table values(1,'Vincent',1000,100);
insert into src_table values(2,'Victor',2000,200);
insert into src_table values(3,'Grace',3000,300);
 
 
create table tgt_table(
id number(6),
name varchar2(32),
age number(6),
salary number(6),
bonus number(6));
 
insert into tgt_table values(1,'someone',1,0,0);
insert into tgt_table values(3,'someone',3,0,0);
insert into tgt_table values(4,'someone',4,0,0);
commit;

Update and insert simultaneously

merge into tgt_table t
using src_table s on (=)
when matched then update set =, =, =10
when not matched then insert values(,,10,,+50);
 
commit;
 
select * from tgt_table;
  • When matched updates the name, salary, age fields to match the existing record (id is 1, 3).
  • When not match is the record that does not exist in the target table (id is 2), and the record is inserted, and the bouns field is added to 50.
  • The clauses of Matched and not matched are independent, and one can be selected or appear at the same time.
  • Records with id 4 in the target table do not exist in the source table (the join condition is not met), so they will not be involved

For matching records, you can use the where clause to further limit the scope

merge into tgt_table t
using src_table s on (=)
when matched then update set = where >=2;
 
Select * from tgt_table order by id;
  • Here the matching record bonus field is updated, and the records with id>=2 are further restricted to be updated.
  • The conditions of the Where clause can be specified through the source table or the target table, here it is specified through the source table.

After the update clause, you can also follow the delete ... where ... clause to delete the records on the match

merge into tgt_table t
using src_table s on (=)
when matched then update set =+50 where >=3 delete where id>=2;
 
select * from tgt_table order by id;
  • delete where id>=2 specifies deletion of records with ID greater than or equal to 2, but note that records with id 2 have not been deleted, only 3 have been deleted
  • Because delete will only be deleted within the record range that the update matches, the update clause has a where >=3, delete will also be restricted by this condition.
  • The actual execution effect is delete where >=3 and id>=2. Only records with id 3 meet this condition
  • Records with id 4 are not within the matching range, are not affected by merge into statements, and will not be deleted.

Record synchronization errors

When data synchronization is performed, the structure/data type/constraints of the source table and the target table may not be consistent, which leads to partial failure of data synchronization. Now we modify the tgt table to limit the value of the salary field to exceed 3000, that is, inserting numbers more than 3000 will fail:

Alter table tgt_table modify salary number(6) check(salary<=3000);

First, call dbms_errlog.create_error_log to create an error log table for tgt_table, the table name is errlog:

exec dbms_errlog.create_error_log('tgt_table', 'errlog');
  • The first parameter specifies the base table to create the error log, and the second parameter is the error log table name

Then when executing the merge into statement, follow the log errors into clause at the end. If an error is encountered during the execution of the statement, the error will be recorded in the error log for later troubleshooting and repair. Here, tgt_table is cleared, try to synchronize the 3 pieces of data of src_table, and at the same time, the salary is increased by 1000

truncate table tgt_table;
 
merge into tgt_table t
using src_table s on (=)
when not matched then insert values(,,10,+1000,)
log errors into errlog('something is wrong.');
 
select * from tgt_table;
  • Here we clear the tgt_table table, and then try to synchronize the 3 records in src_table. During the synchronization process, we increased the salary by 1000.
  • Because the tgt_table table salary has a constraint that cannot exceed 3000, the statement is rolled back and no record is synchronized.
  • 'something is wrong.' in errlog is a user-defined error tag that can help identify which statement causes the error

Querying the errlog table, you can see the cause of the failure. The record with id is 3. The salary after adding 1000 is 4000, which violates the constraints of the target table (check salary <=3000)

If we don't want the statement to roll back when an error occurs, we can follow it with a reject limit N clause, and limit the statement to roll back only if more than N errors occur:

merge into tgt_table t
using src_table s on (=)
when not matched then insert values(,,10,+1000,)
log errors into errlog('Allow one error.') reject limit 1;
 
select * from tgt_table;
  • We have added 1 reject limit 1 clause after the above statement. When 1 or less error occurs, it will not roll back.
  • Therefore, records with ids of 1 and 2 were successfully inserted, and the statement did not report an error
  • The user tag of errlog is modified to 'Allow one error.'
  • Through the Allow one error tag, we found that records with ID 3 were not inserted due to violation of the constraint
  • However, there is a reject limit 1 clause, and the statement allows 1 or less errors, so the record that meets the conditions is successfully inserted
  • If you select reject limit unlimited, the number of errors is not limited

Pagination query

In Oracle, you can use the ROWNUM keyword to implement pagination query.

Example 1: Query the first 10 records in the table

SELECT *
FROM your_table
WHERE ROWNUM <= 10;

Example 2: Records 11 to 20 in the query table

SELECT *
FROM (
    SELECT t.*, ROWNUM AS rnum
    FROM (
        SELECT *
        FROM your_table
        ORDER BY your_column
    ) t
    WHERE ROWNUM <= 20
)
WHERE rnum >= 11;

Example 3: Query the records from 21 to 30 in the table and sort them by specified columns

SELECT *
FROM (
    SELECT t.*, ROWNUM AS rnum
    FROM (
        SELECT *
        FROM your_table
        ORDER BY your_column
    ) t
    WHERE ROWNUM <= 30
)
WHERE rnum >= 21;

Note that using ROWNUM before querying the results, otherwise an incorrect result may be produced. In addition, the use of subqueries can use rnum to filter in outer queries, thereby achieving pagination effect.

Group query/filter

In Oracle, grouping and filtering syntax can be used to group and filter data.

The basic syntax is as follows:

SELECT column1, column2, ..., aggregate_function(column)
FROM your_table
WHERE conditions
GROUP BY column1, column2, ...
HAVING conditions;

Among them, column1, column2, … are the columns to be queryed, aggregate_function is an aggregate function (such as SUM, COUNT, AVG, etc.), your_table is the table to be queryed, conditions are the query conditions, and column1, column2, … are the columns to be grouped.

Example 1: Statistics the number of employees in each department and filters out departments with more than 5 employees

SELECT department, COUNT(*) AS employee_count
FROM your_table
GROUP BY department
HAVING COUNT(*) > 5;

Example 2: Calculate the average salary for each department and filter out departments whose average salary is greater than 1,000

SELECT department, AVG(salary) AS average_salary
FROM your_table
GROUP BY department
HAVING AVG(salary) > 1000;

Example 3: Statistics the maximum salary for each department and filters out departments with maximum salary of more than 5,000

SELECT department, MAX(salary) AS highest_salary
FROM your_table
GROUP BY department
HAVING MAX(salary) > 5000;

In the HAVING clause, you can use the aggregate function to filter the grouped results again. Unlike the WHERE clause, the HAVING clause is filtered after grouping, and an aggregate function can be used, while the WHERE clause is filtered before grouping, and an aggregate function cannot be used.

Note that in the SELECT clause, in addition to grouped columns and aggregate functions, you can also select other columns that need to be displayed, such as:

SELECT department, job, COUNT(*) AS employee_count, AVG(salary) AS average_salary
FROM your_table
GROUP BY department, job
HAVING COUNT(*) > 5;

Sort

In Oracle, the query results can be sorted using the ORDER BY clause. The syntax of the ORDER BY clause is as follows:

SELECT column1, column2, ...
FROM your_table
WHERE conditions
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

where column1, column2, … is the column to be sorted, ASC represents ascending order (default), and DESC represents descending order.

Example 1: Sorting the results in ascending order of salary

SELECT *
FROM your_table
ORDER BY salary ASC;

Example 2: Sorting the query results in ascending order of department and descending order of salary

SELECT *
FROM your_table
ORDER BY department ASC, salary DESC;

Example 3: Sorting the query results by ascending name and descending age

SELECT *
FROM your_table
ORDER BY name ASC, age DESC;

You can specify multiple columns in the ORDER BY clause as needed and set the sorting method for each column. If the sorting method is not specified, the default is ascending order.

Note that the ORDER BY clause should be used after the WHERE clause to sort the filtered results. If there is only one column that needs to be sorted, you can write the above column name directly. If there are multiple columns that need to be sorted, use comma-separated.

Connection query

In Oracle, different join query syntax can be used to join multiple tables. The following are some common connection query syntax and examples in Oracle:

1. Inner connection (INNER JOIN):

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON  = ;

Example:

SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

2.Left connection (LEFT JOIN):

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON  = ;

Example:

SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

3. Right connection (RIGHT JOIN):

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON  = ;

Example:

SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

4. Full external connection (FULL OUTER JOIN):

SELECT column1, column2, ...
FROM table1
FULL OUTER JOIN table2 ON  = ;

Example:

SELECT employees.employee_id, employees.first_name, departments.department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;

5. Cross-connection (CROSS JOIN):

SELECT column1, column2, ...
FROM table1
CROSS JOIN table2;

Example:

SELECT employees.first_name, departments.department_name
FROM employees
CROSS JOIN departments;

Note: The above syntax and examples are for reference only, and should be adjusted according to the specific table and column names when used. Join query can associate data in multiple tables by specifying JOIN conditions to obtain richer query results.

Subquery

In Oracle, subqueries can be used as part of a query statement to use the results of the subquery in the query. Here are the syntax and examples of subqueries in Oracle:

grammar:

SELECT column1, column2, ...
FROM table1
WHERE columnN IN (SELECT columnM FROM table2 WHERE condition);

Example:

  • Use a subquery to get the data that meets the criteria in a table:
SELECT * 
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);
  • Use a subquery to get the maximum or minimum value in a table:
SELECT employee_id
FROM employees 
WHERE salary = (SELECT MAX(salary) FROM employees);
  • Use subqueries as existence checks:
SELECT employee_id, first_name, last_name
FROM employees 
WHERE EXISTS (SELECT * FROM job_history WHERE job_history.employee_id = employees.employee_id);
  • Use a subquery as the computed column:
SELECT employee_id, first_name, last_name, (SELECT MAX(salary) FROM employees) - salary AS salary_diff
FROM employees;

Note: The above syntax and examples are for reference only, and should be adjusted according to the specific table and column names when used. Subqueries can be nested in the main query to filter, calculate, or inspect data based on the results of the subquery.

Summarize

This is the end of this article about the summary of common statements for Oracle database. For more information about the comprehensive statements for Oracle, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!