Implementation Statement of oracle update data update

Implementation Statement of oracle update data update

SQL> -- create demo table
SQL> create table Employee (
2 ID VARCHAR2 (4 BYTE) not null,
3 First_Name VARCHAR2 (10 BYTE ),
4 Last_Name VARCHAR2 (10 BYTE ),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number (8, 2 ),
8 City VARCHAR2 (10 BYTE ),
9 Description VARCHAR2 (15 bytes)
10)
11/

Table created.

SQL>
SQL> -- prepare data
SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('01 ', 'jason', 'martin ', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19960725, 'toronto ', 'programmer ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('02 ', 'alison', 'mathews ', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19760321, 'vancouver ', 'tester ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('03', 'James ', 'Smith', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19781212, 'vancouver ', 'tester ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('04 ', 'cela', 'Rice', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19821024, 'vancouver ', 'manager ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('05 ', 'Robert', 'black', to_date ('000000', 'yyyymmdd'), to_date ('20140901', 'yyyymmdd'), 19840115, 'vancouver ', 'tester ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('06', 'linda ', 'green', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19870730, 'New York ', 'tester ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('07 ', 'David', 'Larry', to_date ('123', 'yyyymmdd'), to_date ('123456', 'yyyymmdd'), 19901231, 'New York ', 'manager ')
3/

1 row created.

SQL> insert into Employee (ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('08', 'James ', 'cat', to_date ('20170101', 'yyyymmdd'), to_date ('20170101', 'yyyymmdd'), 19960917, 'vancouver ', 'tester ')
3/

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2/

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-----------------------------------------------------------------------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-apr-01232.78 Vancouver Tester

8 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- Modify multiple rows with a single UPDATE statement;
SQL>
SQL>
SQL> UPDATE Employee
2 SET City = 'L. .'
3 WHERE City = 'New York ';

2 rows updated.

SQL>
SQL> select * from employee;

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-----------------------------------------------------------------------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 L.A. Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 L.A. Manager
08 James Cat 17-SEP-96 15-apr-01232.78 Vancouver Tester

The SQL statements listed below are based on the following table:

Create table test (name varchar2 (30), code varchar2 (10), I _d varchar2 (10 ));

Insert data

Insert into test (name, code, I _d) values ('zhu1', '001', '1 ');
Insert into test (name, code, I _d) values ('zhu2', '002', '2 ');
Insert into test (name, code, I _d) values ('zhu3', '003 ', '3 ');
Commit;
Select * from test s;

1. update the data whose I _d is 1

-- Method 1
Update test set name = 'zhurhyme1 ',
Code = '007 'where I _d = '1 ';
Commit;

This can be successful

-- Method 2

Update test set (name, code) = (
'Zhurhyme2', '007 ')
Where I _d = '1 ';

Note: The update set must be a subquery, so you need to change it:

-- Method 3

Update test set (name, code) = (
Select 'zhurhyme3', '007 'from dual)
Where I _d = '1 ';

Commit;

2. After update is finished, let's write about for update, for update

The following information is found on the Internet, but the specific website cannot be found now. Please forgive me for your carelessness and reference others without writing any source.

For update is often used, but for updade of is not often used. Now we make a distinction between the two.

A. select * from test for update locks all rows in the table and can only be read but not written.

B. select * from test where I _d = 1 for update: only the row I _d = 1 is locked, but other rows in other tables are not locked.

Create another table

Create table t (dept_id varchar (10), dept_name varchar2 (50 ));

C. select * from test a join t on a. I _d = t. dept_id for update; in this way, all data in the two tables will be locked.

D. select * from test a join t on a. I _d = t. dept_id where a. I _d = 1 for update; this will lock the data that meets the conditions

E. select * from test a join t on. I _d = t. dept_id where. I _d = 1 for update of. I _d; pay attention to the distinction between d and e. e only locks the data rows that meet the conditions in the test table, but does not lock the data in the t table, because the previous update in procedure, the data to be updated needs to be associated for query, so the use of for update causes blocking for updates by other users.

For update of is a row-Level Lock. This row-Level Lock starts when a cursor is opened, ends at the transaction's commit or rollback, rather than the close of the cursor.

If two cursor columns update the same row record of the table at the same time, in fact, only one cursor is executing, and the other one is waiting until the other is complete, and it will execute it on its own. if the first cursor cannot be properly processed and the second cursor does not release resources, a deadlock is generated.

Executing the following code will lead to deadlocks (executed in two command Windows)

Declare
Cursor cur_test
Is
Select name, code from test where I _d = 1 for update of name;
Begin

For rec in cur_test loop
Update test set name = 'tttt1' where current of cur_test;
End loop;
End;
/

Declare
Cursor cur_test
Is
Select name, code from test where I _d = 1 for update of name;
Begin

For rec in cur_test loop
Update test set name = 'tttt2' where current of cur_test;
End loop;
End;
/

Note that no commit exists in two pl/SQL blocks;