PL/SQL学习四
16.事务控制
事务是用来确保数据库数据的一致性，它由一组相关的DML语句组成。该组DML语句要么全部成功提交,
要么全部取消。
数据库事务主要由INSERT，UPDATE,DELETE和SELECT...FOR UPDATE语句组成。当执行COMMIT或ROLLBACK语句
时，当前事务结束。
16.1 事务和锁
当执行事务操作的时候,oracle会在被作用的表上加表锁，用来防止其他用户改变表的结构;
同时也会在被作用的行上加行锁,以防止其他事务在同样的行上执行DML操作。
在oracle中，为了确保数据库数据的读一致性，不允许其他用户读脏数据(未提交的事务),只有当当前用户提交
事务后,其他用户才能读取提交后的数据。
16.2 提交事务
使用COMMIT来提交事务。当执行了COMMIT后,oracle会进行确认事务变化，结束事务、删除保存点、释放锁等
一系列操作。
注意当使用CREATE、ALTER、DROP、TRUNCATE等DDL语句时，oracle会自动提交事务。
注意当使用GRANT、REVOKE等DCL语句时，oracle会自动提交事务。
注意当使用EXIT正常退出SQL*PLUS时,oracle会自动提交事务,如:
SQL> conn scott/tigger已连接。SQL> select sal from emp where ename='SCOTT'; SAL---------- 1000SQL> UPDATE EMP 2 SET SAL = SAL + 200 3 WHERE LOWER(ENAME) = 'scott';已更新 1 行。SQL> exit从 Oracle Database 11g Enterprise Edition Release 11.2With the Partitioning, OLAP, Data Mining and Real ApplC:\Documents and Settings\Administrator>sqlplus /nologSQL*Plus: Release 11.1.0.6.0 - Production on 星期一 8月Copyright (c) 1982, 2007, Oracle. All rights reservedSQL> conn scott/tigger已连接。SQL> select sal from emp where ename='SCOTT'; SAL---------- 1200
16.3 事务回退
保存点是事务中的一个点，它可以用来取消部分事务。当一个事务结束时，会自动删除该事务内所定义的
所有保存点。当使用ROLLBACK命令时，它可以回退到指定保存点的状态。
1.设置保存点
有两种方法设置,如下:
SQL> savepoint a;保存点已创建。SQL> UPDATE EMP 2 SET SAL = SAL *1.4 3 WHERE LOWER(ENAME) = 'scott';已更新 1 行。--创建保存点bSQL> exec dbms_transaction.savepoint('b');PL/SQL 过程已成功完成。
2.利用保存点回滚
只要定义了保存点且事务还未结束，那么用户可以回滚到定义的保存点的状态。也有两种方式：
(注意利用PL/SQL创建的保存点只能用PL/SQL的方法回滚,用SQL创建的只能用SQL的rollback回滚),如：
--接上面SQL> exec dbms_transaction.savepoint('b');PL/SQL 过程已成功完成。SQL> UPDATE EMP 2 SET SAL = SAL -200 3 WHERE LOWER(ENAME) = 'scott';已更新 1 行。SQL> --b是PL/SQL创建的保存点SQL> rollback to b;rollback to b*第 1 行出现错误:ORA-01086: 从未在此会话中创建保存点 'B' 或者该保存点无效SQL> --a是SQL创建的保存点SQL> exec dbms_transaction.rollback_savepoint('a');BEGIN dbms_transaction.rollback_savepoint('a'); END;*第 1 行出现错误:ORA-01086: 从未在此会话中创建保存点 'a' 或者该保存点无效ORA-06512: 在 line 1 SQL> exec dbms_transaction.savepoint('b');PL/SQL 过程已成功完成。SQL> rollback to a;回退已完成。
16.4 只读事务
只读事务是指只允许执行查询操作，不运行执行任何DML操作的事务。当使用只读事务时，可以确保用户
取得特定时间点的数据(更新前的数据)。设置只读事务后，其他session可能会提交事务，但只读事务不会
获得更新后的数据。
--设置会话只读事务
SET TRANSACTION READ ONLY;
或
EXEC dbms_transaction.read_only
--在只读事务中执行DML操作报错SQL> update emp set sal=3200 where ename='SCOTT';update emp set sal=3200 where ename='SCOTT' *第 1 行出现错误:ORA-01456: 不能在 READ ONLY 事务处理中执行插入/删除/更新操作
16.5 顺序事务
在只读事务中，不能使用任何DML操作，为了用户可以取得更新前的数据，且允许执行DML操作，可以使用顺序
事务。
--设置会话中顺序事务
SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 事务处理集。
注意由于是顺序事务，可以执行相关的DML操作，但对时间点要求是顺序的。
不管是顺序事务还是只读事务，设置事务是顺序事务或者是只读事务的语句必须是事务开始的第一条语句,如：
SQL> update emp set sal=3200 where ename='SCOTT';update emp set sal=3200 where ename='SCOTT' *第 1 行出现错误:ORA-01456: 不能在 READ ONLY 事务处理中执行插入/删除/更新操作SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;SET TRANSACTION ISOLATION LEVEL SERIALIZABLE*第 1 行出现错误:ORA-01453: SET TRANSACTION 必须是事务处理的第一个语句
17. 分组
数据分组主要由GROUP BY子句、分组函数以及HAVING子句组成。
GROUP BY 用于指定要分组的哪些列，而分组函数(如:SUM、COUNT等)用与显示统计结果，HAVING则用于筛选结果。
17.1 分组函数
MAX：获得列或表达式的最大值
MIN: 获得列或表达式的最小值
AVG: 获得列或表达式的平均值
SUM: 获得列或表达式的所有项之和
COUNT: 获得结果的总行数
VARIANCE: 获得列或表达式的方差，该函数只适用于数字类型。公式:( SUM(expr)2-SUM(expr)2/COUNT(expr) )/(COUNT(expr)-1)
STDDEV:获得列或表达式的标准方差。按方差的平方根来计算，只有一行数据时返回0.
注意分组函数只能在SELECT、ORDER BY和HAVING子句中，不能出现在WHERE和GROUP BY子句中。
使用分组函数时，除了COUNT(*)外，其他分组函数都会忽略NULL行。
执行SELECT时，如果列同时包含列、表达式和分组函数，那么这些列和表达式必须出现在GROUP BY子句中。
使用分组函数时，可以指定DISTINCT和ALL(默认)，ALL表示统计所有行；DISTINCE表示去掉重复的行。
--最大值与最小值函数SQL> SELECT MAX(sal) ,MIN(sal) FROM emp; MAX(SAL) MIN(SAL)---------- ---------- 5000 800 --平均值和求和函数SQL> SELECT AVG(sal) ,SUM(sal) FROM emp; AVG(SAL) SUM(SAL)---------- ---------- 2087.5 29225 --COUNT(*)行数总计SQL> SELECT COUNT(*) FROM emp; COUNT(*)---------- 14 --分组函数忽略NULL行,原本是14行，其他10行为NULL值 SQL> SELECT COUNT(comm) from emp;COUNT(COMM)----------- 4 --求方差和标准方差 SQL> SELECT VARIANCE(sal) 方差，STDDEV(sal) 标准方差 FROM emp; 方差 标准方差---------- ---------- 1429687.5 1195.6954 --使用DISTINCE去重复行SQL> SELECT COUNT(DISTINCT deptno) FROM emp;COUNT(DISTINCTDEPTNO)--------------------- 3SQL> SELECT COUNT(deptno) FROM emp;COUNT(DEPTNO)------------- 14
17.2 GROUP BY和HAVING
GROUP BY进行分组，HAVING则对分组结果进行筛选。
--对单列分组,表示补同部门所有工资的平均,求不同部门内工资的最大数SQL> SELECT deptno,AVG(sal),MAX(sal) FROM emp 2 GROUP BY deptno; DEPTNO AVG(SAL) MAX(SAL)---------- ---------- ---------- 30 1566.66667 2850 20 2215 3200 10 2916.66667 5000 --多列分组,表示不同部门不同职位上工资的平均值和最大值SQL> SELECT deptno,job,AVG(sal),MAX(sal) FROM emp 2 GROUP BY deptno,job; DEPTNO JOB AVG(SAL) MAX(SAL)---------- --------- ---------- ---------- 20 CLERK 1700 3200 30 SALESMAN 1400 1600 20 MANAGER 2975 2975 30 CLERK 950 950 10 PRESIDENT 5000 5000 30 MANAGER 2850 2850 10 CLERK 1300 1300 20 ANALYST 3000 3000 10 MANAGER 2450 2450--使用HAVING进行筛选,求不同部门内的平均工资和最大工资--,筛选结果为平均工资小于3000的记录SQL> SELECT deptno,AVG(sal),MAX(sal) FROM emp 2 GROUP BY deptno 3 HAVING AVG(sal)<3000; DEPTNO AVG(SAL) MAX(SAL)---------- ---------- ---------- 30 1566.66667 2850 20 2215 3200 10 2916.66667 5000
注意1:有GROUP BY和SELECT时，除分组函数外,GROUP BY语句中的列名应该和SELECT的一致，也就是SELECT有的
列名，GROUP BY也一定要有。
SQL> SELECT deptno,job,AVG(sal),MAX(sal) FROM emp 2 GROUP BY deptno;SELECT deptno,job,AVG(sal),MAX(sal) FROM emp *第 1 行出现错误:ORA-00979: 不是 GROUP BY 表达式 注意2：有GROUP BY、HAVING、ORDER BY时候，ORDER BY子句应放在最后。SQL> SELECT deptno,AVG(sal),MAX(sal),FROM emp 2 GROUP BY deptno 3 ORDER BY deptno 4 HAVING AVG(sal)<3000;SELECT deptno,AVG(sal),MAX(sal),FROM emp *第 1 行出现错误:ORA-00936: 缺失表达式
17.3 ROLLUP和CUBE
当使用GROUP BY的时候，只会生成列的相应数据统计。当要求小计或合计(横向、纵向统计结果)的时候，
需要用到ROLLUP和CUBE。
--对比使用ROLLUP和不使用ROLLUP,发现它会生成横向小计统计和数据统计--ROLLUP是分组后求总计 等价于--SELECT deptno,job,AVG(sal) FROM emp--GROUP BY deptno,job--UNION ALL--SELECT deptno,NULL,AVG(sal) FROM emp--GROUP BY deptno--UNION ALL--SELECT NULL,NULL,AVG(sal) FROM emp-- NULL只是为了UNION ALL的需要SQL> SELECT deptno,job,AVG(sal) FROM emp 2 GROUP BY ROLLUP(deptno,job) 3 ORDER BY deptno; DEPTNO JOB AVG(SAL)---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 2916.66667 20 ANALYST 3000 20 CLERK 1700 20 MANAGER 2975 20 2215 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 1400 30 1566.66667 2087.5已选择13行。--没有ROLLUP分组统计的SQL> SELECT deptno,job,AVG(sal) FROM emp 2 GROUP BY deptno,job 3 ORDER BY deptno; DEPTNO JOB AVG(SAL)---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 3000 20 CLERK 1700 20 MANAGER 2975 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 1400已选择9行。SQL> SELECT deptno,job,AVG(sal) FROM emp GROUP BY deptno,job 2 UNION ALL 3 SELECT deptno,NULL,AVG(sal) FROM emp GROUP BY deptno 4 UNION ALL 5 SELECT NULL,NULL,AVG(sal) FROM emp 6 ORDER BY deptno; DEPTNO JOB AVG(SAL)---------- --------- ---------- 10 CLERK 1300 10 2916.66667 10 MANAGER 2450 10 PRESIDENT 5000 20 2215 20 MANAGER 2975 20 CLERK 1700 20 ANALYST 3000 30 SALESMAN 1400 30 CLERK 950 30 MANAGER 2850 30 1566.66667 2087.5已选择13行。
使用CUBE不仅可以生成数据统计及横向小计统计，而且可以生成纵向小计统计结果
--可以看出CUBE的结果是包含了ROLLUP的结果的,它在ROLLUP基础上增加了纵向小计SQL> SELECT deptno,job,AVG(sal) FROM emp 2 GROUP BY CUBE(deptno,job) 3 ORDER BY deptno; DEPTNO JOB AVG(SAL)---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 2916.66667 20 ANALYST 3000 20 CLERK 1700 20 MANAGER 2975 20 2215 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 1400 30 1566.66667 ANALYST 3000 CLERK 1470 MANAGER 2758.33333 PRESIDENT 5000 SALESMAN 1400 2087.5 已选择18行。
17.4 GROUPING
GROUPING函数用于确定统计结果是否用到了特定的列。如果返回0则表示统计结果使用了该列，返回1则表示没有使用该列。
--显示某列结果是否使用了该列进行统计，0表示使用了，1表示未使用SQL> SELECT deptno,job,AVG(sal),GROUPING(deptno),GROUPING(job) FROM emp 2 GROUP BY CUBE(deptno,job) 3 ORDER BY deptno; DEPTNO JOB AVG(SAL) GROUPING(DEPTNO) GROUPING(JOB)---------- --------- ---------- ---------------- ------------- 10 CLERK 1300 0 0 10 MANAGER 2450 0 0 10 PRESIDENT 5000 0 0 10 2916.66667 0 1 20 ANALYST 3000 0 0 20 CLERK 1700 0 0 20 MANAGER 2975 0 0 20 2215 0 1 30 CLERK 950 0 0 30 MANAGER 2850 0 0 30 SALESMAN 1400 0 0 30 1566.66667 0 1 ANALYST 3000 1 0 CLERK 1470 1 0 MANAGER 2758.33333 1 0 PRESIDENT 5000 1 0 SALESMAN 1400 1 0 2087.5 1 1已选择18行。
17.5 GROUPING SETS
使用GROUPING SETS可以合并多个分组的结果,如:
--求不同部门内工资的总和SQL> SELECT deptno,SUM(sal) FROM emp 2 GROUP BY deptno; DEPTNO SUM(SAL)---------- ---------- 30 9400 20 11075 10 8750 --求不同职位上工资的总和SQL> SELECT job,SUM(sal) FROM emp 2 GROUP BY job;JOB SUM(SAL)--------- ----------CLERK 7350SALESMAN 5600PRESIDENT 5000MANAGER 8275ANALYST 3000 --使用GROUPING SETS合并这2个分组,既显示了不同职位上的工资总和也显示了不同部门内工资的总和SQL> SELECT deptno,job,SUM(sal) FROM emp 2 GROUP BY GROUPING SETS(deptno,job); DEPTNO JOB SUM(SAL)---------- --------- ---------- CLERK 7350 SALESMAN 5600 PRESIDENT 5000 MANAGER 8275 ANALYST 3000 30 9400 20 11075 10 8750已选择8行。