【oracle】事务更新及处理

INSERT INTOEMP(EMPNO,ENAME,JOB,HIREDATE,SAL)   --增加操作

VALUES(6612，'张三','CLERK',TO_DATE('1989-09-19','YYYY-MM-DD'),600);

 

UPDATE EMP SET SAL=3000，COMM=500

WHERE EMPNO=7369;

 

UPDATE EMP SET SAL=SAL*1.2

WHERE SAL<(SELECT AVG(SAL) FROM EMP);

 

UPDATE EMP SET (JOB,SAL,HIREDATE)=(

 SELECT JOB,SAL,HIREDATE

 FROM EMP      --将返回的结果作为更新的内容

 WHERE EMPNO=7839)

WHERE EMPNO=7369;

 

DELETE FROM EMP    --删除操作

WHERE EMPNO=7566;

 

DELETE FROM EMP

WHERE EMPNO IN(7369，7566，7788);

 

DELETE FROM EMP

WHERE SAL=(

 SELECT MAX(SAL)

 FROM EMP

);

 

DELETE FROM EMP

WHERE TO_CHAR(HIREDATE,'YYYY')=1987;

 

SET AUTOCOMMIT=OFF        --取消自动提交处理

 

SET AUTOCOMMIT=ON        --打开自动提交处理

 

SAVEPOINT SP_A;    --设置保存点

 

DELETE FROM EMP

WHERE SAL=(

 SELECT MAX(SAL)

 FROM EMP);

 

ROLLBACK TO SP_A;   --回到保存点SP_A

 

SELECT * FROM EMP

WHERE DEPTNO=10 FOR UPDATE;    --给这条记录上锁，别的session无法访问

 

SELECT USER FROM DUAL;   --显示登录用户