What is the usage of merge in oracle
In Oracle, the merge command can perform inserts and updates operations on a table at the same time in one SQL statement. The merge statement is often used to perform merge operations on two specified tables. The syntax is "MEGER INTO merge." Target table USING merge source table ON condition".
The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.
What is the usage of merge in oracle
merge command
Through this merge you can perform inserts and updates on a table in one SQL statement at the same time
Using the meger statement, you can perform a merge operation on the two specified tables. The syntax is as follows:
MEGER INTO table1_name USING table2_name ON join_condition WHEN MATCHEO THEN UPDATE SET... WHEN NOT MATCHED THEN INSERT ...VALUES...
The syntax description is as follows:
table1_name indicates the target table that needs to be merged.
table2_name indicates the source table that needs to be merged.
join_condition represents the merge condition.
when matcheo then update means that if the conditions for merging are met, the update operation will be performed.
when not matched then insert means that if the conditions are not met, the insertion operation will be performed.
update and insert
If you only want to merge the data that meets the conditions in the source table into the target table, you can only use the update clause. If you want to merge the data in the source table that does not meet the merge conditions. To merge data into the target table, you can only use the insert clause.
In both the update clause and the insert clause, you can use the where clause to specify the conditions for updated inserts. At this time, for the merge operation, two levels of filtering conditions are provided. The first level is the merge condition, specified by the on clause in the meger clause, and the second level is the where condition specified in the update or insert clause. This makes the merge operation more flexible and sophisticated.
Here we create two tables, one is the person table and the other is the newpersono table. The structures of the two tables are the same
SQL> create table person( 2 pid number(4), 3 page number(3) 4 );
The table has been created.
--Insert three rows of data
SQL> insert into person values(1,20);
1 row has been created.
SQL> insert into person values(2,21);
1 row has been created.
SQL> insert into person values(3,22);
1 row has been created.
SQL> create table newperson( 2 pid number(4), 3 page number(3) 4 );
Table has been created.
--插入三行数据 SQL> insert into newperson values(1,100);
1 row has been created.
SQL> insert into newperson values(4,100);
1 row has been created.
SQL> insert into newperson values(5,100);
1 row has been created.
SQL> select * from person; PID PAGE ---------- ---------- 1 20 2 21 3 22 SQL> select * from newperson; PID PAGE ---------- ---------- 1 100 4 100 5 100 SQL> merge into person p1 2 using newperson p2 3 on (p1.pid=p2.pid) 4 when matched then 5 update set p1.page=p2.page 6 when not matched then 7 insert (pid,page) values(p2.pid,p2.page);
3 rows have been merged.
--The above SQL statement is when the pid in person is equal to the pid in newperson, set the corresponding page in person to the age in newperson. When it does not match, insert the unqualified condition into person. The data. The execution results are as follows:
SQL> select * from person; PID PAGE ---------- ---------- 1 100 2 21 3 22 5 100 4 100 --newperson表中的数据不会改变: SQL> select * from newperson; PID PAGE ---------- ---------- 1 100 4 100 5 100
Recommended tutorial: "Oracle Video Tutorial"
The above is the detailed content of What is the usage of merge in oracle. For more information, please follow other related articles on the PHP Chinese website!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



Oracle View Encryption allows you to encrypt data in the view, thereby enhancing the security of sensitive information. The steps include: 1) creating the master encryption key (MEk); 2) creating an encrypted view, specifying the view and MEk to be encrypted; 3) authorizing users to access the encrypted view. How encrypted views work: When a user querys for an encrypted view, Oracle uses MEk to decrypt data, ensuring that only authorized users can access readable data.

Uninstall method for Oracle installation failure: Close Oracle service, delete Oracle program files and registry keys, uninstall Oracle environment variables, and restart the computer. If the uninstall fails, you can uninstall manually using the Oracle Universal Uninstall Tool.

Deleting all data in Oracle requires the following steps: 1. Establish a connection; 2. Disable foreign key constraints; 3. Delete table data; 4. Submit transactions; 5. Enable foreign key constraints (optional). Be sure to back up the database before execution to prevent data loss.

Oracle Invalid numeric errors may be caused by data type mismatch, numeric overflow, data conversion errors, or data corruption. Troubleshooting steps include checking data types, detecting digital overflows, checking data conversions, checking data corruption, and exploring other possible solutions such as configuring the NLS_NUMERIC_CHARACTERS parameter and enabling data verification logging.

Solutions to Oracle cannot be opened include: 1. Start the database service; 2. Start the listener; 3. Check port conflicts; 4. Set environment variables correctly; 5. Make sure the firewall or antivirus software does not block the connection; 6. Check whether the server is closed; 7. Use RMAN to recover corrupt files; 8. Check whether the TNS service name is correct; 9. Check network connection; 10. Reinstall Oracle software.

Oracle database paging uses ROWNUM pseudo-columns or FETCH statements to implement: ROWNUM pseudo-columns are used to filter results by row numbers and are suitable for complex queries. The FETCH statement is used to get the specified number of first rows and is suitable for simple queries.

The method to solve the Oracle cursor closure problem includes: explicitly closing the cursor using the CLOSE statement. Declare the cursor in the FOR UPDATE clause so that it automatically closes after the scope is ended. Declare the cursor in the USING clause so that it automatically closes when the associated PL/SQL variable is closed. Use exception handling to ensure that the cursor is closed in any exception situation. Use the connection pool to automatically close the cursor. Disable automatic submission and delay cursor closing.

In Oracle, the FOR LOOP loop can create cursors dynamically. The steps are: 1. Define the cursor type; 2. Create the loop; 3. Create the cursor dynamically; 4. Execute the cursor; 5. Close the cursor. Example: A cursor can be created cycle-by-circuit to display the names and salaries of the top 10 employees.
