Implementation statement for Oracle Update Data Update

Implementation statement for Oracle Update Data Update

Sql>--Create demo table
Sql> CREATE TABLE Employee (
2 ID VARCHAR2 (4 BYTE) not NULL,
3 first_name VARCHAR2 (BYTE),
4 last_name VARCHAR2 (BYTE),
5 start_date Date,
6 end_date Date,
7 Salary Number (8,2),
8 City VARCHAR2 (BYTE),
9 Description VARCHAR2 (MB)
10)
11/

Table created.

Sql>
Sql>--Prepare data
sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Jason ', ' Martin ', to_date (' 19960725 ', ' YYYYMMDD '), to_date (' 20060725 ', ' YYYYMMDD '), 1234.5 6, ' Toronto ', ' Programmer ')
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Alison ', ' Mathews ', To_date (' 19760321 ', ' YYYYMMDD '), to_date (' 19860221 ', ' YYYYMMDD '), 6661.7 8, ' Vancouver ', ' Tester ')
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (', ' James ', ' Smith ', to_date (' 19781212 ', ' YYYYMMDD '), to_date (' 19900315 ', ' YYYYMMDD '), 6544.7 8, ' Vancouver ', ' Tester ')
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Celia ', ' Rice ', to_date (' 19821024 ', ' YYYYMMDD '), to_date (' 19990421 ', ' YYYYMMDD '), 2344.7 8, ' Vancouver ', ' Manager '
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Robert ', ' Black ', to_date (' 19840115 ', ' YYYYMMDD '), to_date (' 19980808 ', ' YYYYMMDD '), 2334.7 8, ' Vancouver ', ' Tester ')
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' ', ' Linda ', ' Green ', to_date (' 19870730 ', ' YYYYMMDD '), to_date (' 19960104 ', ' YYYYMMDD '), 4322.7 8, ' New York ', ' Tester '
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (' Modified ', ' David ', ' Larry ', to_date (' 19901231 ', ' YYYYMMDD '), to_date (' 19980212 ', ' YYYYMMDD '), 7897.7 8, ' New York ', ' Manager '
3/

1 row created.

sql> INSERT INTO Employee (ID, first_name, last_name, start_date, end_date, S Alary, city, Description)
2 values (', ' James ', ' Cat ', to_date (' 19960917 ', ' YYYYMMDD '), to_date (' 20020415 ', ' YYYYMMDD '), 1232.7 8, ' 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
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
Jason Martin 25-jul-96 25-jul-06 1234.56 Toronto Programmer
Alison Mathews 21-mar-76 21-feb-86 6661.78 Vancouver Tester
James Smith 12-dec-78 15-mar-90 6544.78 Vancouver Tester
Celia Rice 24-oct-82 21-apr-99 2344.78 Vancouver Manager
Robert Black 15-jan-84 08-aug-98 2334.78 Vancouver Tester
Linda Green 30-jul-87 04-jan-96 4322.78 New York Tester
David Larry 31-dec-90 12-feb-98 7897.78 New York Manager
James Cat 17-sep-96 15-apr-02 1232.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.A. '
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
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
Jason Martin 25-jul-96 25-jul-06 1234.56 Toronto Programmer
Alison Mathews 21-mar-76 21-feb-86 6661.78 Vancouver Tester
James Smith 12-dec-78 15-mar-90 6544.78 Vancouver Tester
Celia Rice 24-oct-82 21-apr-99 2344.78 Vancouver Manager
Robert Black 15-jan-84 08-aug-98 2334.78 Vancouver Tester
Linda Green 30-jul-87 04-jan-96 4322.78 L.A. Tester
David Larry 31-dec-90 12-feb-98 7897.78 L.A. Manager
James Cat 17-sep-96 15-apr-02 1232.78 Vancouver Tester

The SQL listed below is based on the following table

CREATE TABLE test (name VARCHAR2, code varchar2 (), I_d varchar2 (10));

Inserting 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 updates i_d to 1 data

--Way 1
Update test set name= ' zhurhyme1 ',
Code= ' 007 ' where i_d= ' 1 ';
Commit

This will work.

--Way 2

Update test Set (Name,code) = (
' Zhurhyme2 ', ' 007 ')
where i_d= ' 1 ';

Note that this is not possible, and update set must be a subquery, so it needs to be changed to:

--Way 3

Update test Set (Name,code) = (
Select ' Zhurhyme3 ', ' 007 ' from dual)
where i_d= ' 1 ';

Commit

2.update said, write about for update,for update of

The following information is found from the Internet, but the specific Web site can not be found now, please forgive the little brother's carelessness, quoting other people's Dongdong and do not write the place.

For update is often used, and for updade of is not used, now make a distinction between the two

A. Select * from test for update locks all rows of a table, can only read and cannot write

B. Select * FROM Test where i_d = 1 for update locks only i_d=1 rows, but not other rows in other tables

Create a table below

CREATE TABLE T (dept_id varchar (a), Dept_name varchar2 (50));

C. SELECT * FROM Test a join T in a.i_d=t.dept_id for update; This will lock all data for both tables

D. Select * FROM Test a join T in a.i_d=t.dept_id where a.i_d=1 for update; This locks the data that satisfies the condition

E. Select * FROM Test a join T in a.i_d=t.dept_id where a.i_d=1 for update of A.i_d; Note that the D and e,e only lock the data rows that meet the criteria in the table test, without locking the data in the table T, because the data in the procedure is preceded by an update, and the update needs to be associated with the query, so using a for update causes blocking for other user updates. That's why we found this information.

For update of is a row-level lock, which begins when a cursor is opened, and ends with a commit or rollback of the transaction, rather than a cursor close.

If there are two cursor for the same row record of the table, there is actually only one cursor executing, and the other is waiting until the other completes, It itself again to execute. If the first cursor cannot be handled well, the second cursor does not voluntarily release the resource, then the deadlock is generated.

Executing the following code will deadlock (performed 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 there are no commits in two pl/sql blocks;