PL/SQL and SQLPLUS query results are different-Never forget commit !, Sqlpluscommit

PL/SQL and SQLPLUS query results are different-Never forget commit !, Sqlpluscommit

For the same SQL statement, the query results in PLSQL and SQLPLUS are different. Have you ever seen them?

Today, we executed a query select * from t_user in the SQL Window of PLSQL; 6 records are queried:

Later, to facilitate the test of other data, sqlplus was opened and the same query statement was executed. An exception occurred:

Only one record can be queried. You are not mistaken. The query results in PL/SQL and SQLPLUS are different for the same user, the same SQL statement, and the same time. As a result, I was unwilling to open n more SQL plus windows, and only one record was found in the execution results. Another SQL Window is opened in PS/SQL. After the same SQL statement is executed, it is also a record like the sqlplus query result.

So I think that most of the problem lies in the SQL window of the PL/SQL statement that queries 6 pieces of data. In this case, we found that five records have been inserted before the query select * from t_user:

It turns out that, unlike SQL Server and MySQL that have been used before, after performing operations on data in oracle, these operations are only submitted to the memory and physical files are not updated, finally, if you want to update the file to a physical file, run the commit command.

In this case, execute the commit command in the SQL window for inserting 5 data records, and the data is truly inserted into the physical database file!

Therefore, in oracle, as long as the database changes the data (add, delete, modify, and so on), you must execute the commit command to update the physical files submitted to the database, otherwise, the previous operations will be futile.

Those who know EntityFramework may have come up with SaveChanges (). That's right! The truth is, just like a child ~~

It should be noted that once a table isAfter the operation, before commitThe table is locked. Once locked, other transactions cannot operate on the table, as shown below:

Open two sqlplus windows and perform the same update operation on the same table, but not commit.

Run update t_user set user_name = 'usera 'where user_id = 'user5' first ':

Then execute update t_user set user_name = 'userb' where user_id = 'user5 ':

The first operation is updated with one line, and the operation executed is dead, and the message "updated with one line" is not displayed ".

The reason is that the first operation did not execute commit and the t_user table has been locked (that is, the legendaryLock table), So before the first commit operation, other transactions are not allowed to operate on this table.