oracle菜鸟日记7

1. 注意在oracle中的00，01这类的词，这类都属于数字型
比如： grouping（deptno） || grouping（job）=‘00或者01’


2. 对于列名是汉字的可以加双引号表示，而如果要在列中使用汉字则目前我还真不知道怎么做。。。。。


3. 高级分组：
   grouping（分组列）如果分组列不为null则返回0，为null则返回1，grouping里面的参数只能是一个，如果对于多个分组列，则可以用 || 进行连接表示
不同的情况，比如  =‘000’，‘001’，‘110’之类的
举例：
SQL> select deptno,job,case when grouping(deptno)||grouping(job) = '00'
  2                              then 'sum'
  3                         when grouping(deptno)||grouping(job) = '10'
  4                              then 'job sum'
  5                                         when grouping(deptno)||grouping(job) = '0
  6                                             then 'deptno sum'
  7                                         when grouping(deptno)||grouping(job) = '1
  8                                             then 'deptno and job sum'
  9                                             end "小计"，
 10                                             sum(sal) as total
 11  from emp
 12  group by cube (deptno,job)
 13  order by grouping(job),grouping(deptno);


    DEPTNO JOB       小计                    TOTAL
---------- --------- ------------------ ----------
        10 MANAGER   sum                      2450
        30 MANAGER   sum                      2850
        30 CLERK     sum                       950
        20 MANAGER   sum                      2975
        20 ANALYST   sum                      6000
        20 CLERK     sum                      1900
        10 PRESIDENT sum                      5000
        30 SALESMAN  sum                      5600
        10 CLERK     sum                      1300
           SALESMAN  job sum                  5600
           CLERK     job sum                  4150


    DEPTNO JOB       小计                    TOTAL
---------- --------- ------------------ ----------
           ANALYST   job sum                  6000
           MANAGER   job sum                  8275
           PRESIDENT job sum                  5000
        10           deptno sum               8750
        30           deptno sum               9400
        20           deptno sum              10875
                     deptno and job sum      29025


已选择18行。


  group by cube（a,b）  有2的n次方种分组形式， (a，b)，（a），（b），（）
  group by rollup（a，b） 有n+1种分组形式， （a，b），（a），（）  从右向左依次卷积，
  group by sets （(a，b)，（a），（b），（）） 指定分组形式




  grouping_id() 返回分组的二进制形式的值，里面的参数可以是多个分组列，比如 grouping_id（a，b，c） 当是010情况时，则返回2，100时返回4


  group by后面不能跟嵌套子查询


4.ora_rowscn 伪列表示每一张表上的行scn（系统改变号），而scn_to_timestamp（ora_rowscn） 则是将scn转换成对应的系统时间
例如：SQL> select empno,sal,scn_to_timestamp(ora_rowscn) bx from emp;


     EMPNO        SAL BX
---------- ---------- -----------------------------------------
      7369       1000 31-3月 -10 12.01.02.000000000 上午
      7499       1600 31-3月 -10 12.01.02.000000000 上午
      7521       1250 31-3月 -10 12.01.02.000000000 上午
      7566       2975 31-3月 -10 12.01.02.000000000 上午
      7654       1250 31-3月 -10 12.01.02.000000000 上午
      7698       2850 31-3月 -10 12.01.02.000000000 上午
      7782       2450 31-3月 -10 12.01.02.000000000 上午
      7788       3000 31-3月 -10 12.01.02.000000000 上午
      7839       5000 31-3月 -10 12.01.02.000000000 上午
      7844       1500 31-3月 -10 12.01.02.000000000 上午
      7876       1100 31-3月 -10 12.01.02.000000000 上午
      7900        950 31-3月 -10 12.01.02.000000000 上午
      7902       3000 31-3月 -10 12.01.02.000000000 上午
      7934       1300 31-3月 -10 12.01.02.000000000 上午


已选择14行。


5. (日期1-日期2) year to month 表示将日期差精确到年月，不到30天不向前面的月进一，不满12，不向前面的年进一。


6.translate(a,b,c)函数，在删除参数a中的字符时，一定要最少指明一个被替换的值，就算被替换的值在a中不出现（只是形式），但也要有，如果任何b的
替换参数为null，则真个结果也为null；
例如：
  SQL> select translate('abcd*efg','*','') a from dual
  2  /


A
-




SQL> select translate('abcd*efg','x*','6') a from dual
2  /


A
-------
abcdefg      




7.欧洲国家的货币一般用逗号作为小叔部分的分隔符；


8.约束延期：我们可以设置约束延期验证，当对于父子关系的两张表来说，我们向其中一个添加数据时，先向父表添加再向子表添加这个顺序是没有问题的，
但是，显示生产环境中，顺序不是我们可以控制的，所以当先向子表添加再向父表添加时，就会出错，应为添加在子表中的值在父表中没有出现，这时候
我们就需要进行约束延期，等到commit的时候再开始检查约束，而不是在添加的过程中进行检查约束。
方法： 先删除约束，再重建延期约束
   （1） alter table table_name drop constraint constraint_name；
   （2） alter table table_name add constraint foreign key（子列） references table_name2（父列）
         initially deferred deferrable enable；


9.一个&的意思 是每次碰到这个变量，都问问你是啥
两个&，那么只问你一次，以后这个变量都按这个值处理


10.对于ntile分桶，ntile（n）over（partition by / order by），按照后面的分组或者排序的结果将表分成n份，比如十行数据分成3份，则按照分组
或者排序后的结果，将前三行分到一号桶，中间三行分到2号桶，最后4行分到3号桶。
 
11.对于width_bucket(a,b,c,d)函数，具有4个参数，a参数表示依据那一列进行分组，b参数表示lower bound，c参数表示upper bound，d参数表示分多少份
比如 width_bucket(sum（sal），0，50000，10)，就表示按照sum（sal）的值进行分组，0到50000之间分成10组，也就是说0-5000，5001-10000，..
依次类推，sum(sal)的结果在哪个桶的区间内就分到几号桶中。


12.只要没有聚集函数，那么分组列会返回重复行


13.floor（n），向下取整，只能得到整数，trunc（n，m），默认向下取整，但实际有两个参数，参数m表示取到小数点右边第几位，负数表示小数点左边
第几位，而round（n）,它是支持4舍五入的。
例如：
SQL> select round(11.6) from dual;


ROUND(11.6)
-----------
         12


SQL> select trunc(11.6) from dual;


TRUNC(11.6)
-----------
         11


SQL> select floor(11.6) from dual;


FLOOR(11.6)
-----------
         11


SQL> select trunc(11.678,2) from dual;


TRUNC(11.678,2)
---------------
          11.67


SQL> select trunc(12221.678,-2) from dual;


TRUNC(12221.678,-2)
-------------------
              12200




14.事务的读一致性
  系统默认的事务环境是读写，读写环境：当有两个用户同时访问同一个数据的时候，其中一个用户先查询了一行，然后另一个用户修改了那一行并提交，第一个
用户再查询那一行的时候，他会发现不是原来的数据了，有时候我们希望数据保持一致性，那么我们就该将事务环境设置为只读，这样我们就会一直得到我们
期望的数据，而不会因为别的用户的提交而改变我们的查询结果，
  set transaction read only ;(只读)
  set transaction read write;(读写)


15.设置事务的隔离级别：
 隔离级别会允许或者不允许：脏读，幻读（增加数据行并提交了），不可重复读（被删除并提交了）
set transaction isolation level [read uncommit][read commit][repeatable read][serializable]
如果设置隔离级别过高，那么付出的代价也会很大，因为需要额外的锁请求，事务的吞吐量也会受限，所以序列化事务更容易产生死锁。




16.to_number（）只能将字符型的纯数字转换成数字型，对于字母，标点符号，货币单位，全都不能转
   to_char（）可以将数字型，日期，字符，全部转为字符型


17.alter session set nls_sort=binary_ci；
   alter session set nls_comp=linguistic；
   这两个组合允许搜索不区分大小写


18.写包和包体的时候最好都带上or replace，因为有可能报错：已经存在某某名字；


19.alter index index_name unusable；（使索引失效）
   alter index index_name rebuild；（使索引生效）重建索引；


19.alter table table_name disable constraint constraint_name;(使约束失效)
   alter table table_name enable constraint constraint_name;（使约束有效）   


20.alter trigger trigger_name disable；（使触发器失效）
   alter trigger trigger_name enable；（使触发器有效）
   alter table table_name disable alltrigger ；（使表上的所有触发器失效）
   alter table table_name enable alltrigger ；（使表上的所有触发器有效）