PL/SQL学习四(事务控制及分组)

最新推荐文章于 2021-04-09 04:23:26 发布

Cryking

最新推荐文章于 2021-04-09 04:23:26 发布

阅读量4k

点赞数 2

分类专栏： oracle PLSQL学习系列文章标签： manager sql oracle null 数据库 database

本文链接：https://blog.csdn.net/edcvf3/article/details/7894010

版权

oracle 同时被 2 个专栏收录

92 篇文章 3 订阅

订阅专栏

PLSQL学习系列

26 篇文章 13 订阅

订阅专栏

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
----------
      1000

SQL> UPDATE EMP
  2  SET SAL = SAL + 200
  3  WHERE LOWER(ENAME) = 'scott';

已更新 1 行。

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2
With the Partitioning, OLAP, Data Mining and Real Appl

C:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on 星期一 8月

Copyright (c) 1982, 2007, Oracle.  All rights reserved

SQL> conn scott/tigger
已连接。
SQL> select sal from emp where ename='SCOTT';

       SAL
----------
      1200

关闭数据库前，需要对事物进行处理(回退或提交)

SQL> shutdown immediate;
ORA-01097: 无法在事务处理过程中关闭 - 请首先提交或回退

      16.3 事务回退
      保存点是事务中的一个点，它可以用来取消部分事务。当一个事务结束时，会自动删除该事务内所定义的
      所有保存点。当使用ROLLBACK命令时，它可以回退到指定保存点的状态。
      1.设置保存点
      有两种方法设置,如下:

SQL> savepoint a;

保存点已创建。

SQL> UPDATE EMP
  2  SET SAL = SAL *1.4
  3  WHERE LOWER(ENAME) = 'scott';

已更新 1 行。
--创建保存点b
SQL> 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)
---------------------
                    3

SQL> 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           7350
SALESMAN        5600
PRESIDENT       5000
MANAGER         8275
ANALYST         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行。

Cryking

关注

2
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
PL/SQL学习四(事务控制及分组)

16.事务控制事务是用来确保数据库数据的一致性，它由一组相关的DML语句组成。该组DML语句要么全部成功提交, 要么全部取消。数据库事务主要由INSERT，UPDATE,DELETE和SELECT...FOR UPDATE语句组成。当执行COMMIT或ROLLBACK语句时，当前事务结束。 16.1 事务和锁当执行事务操作的时候,oracle...
复制链接

扫一扫