oracle 浅认识

最新推荐文章于 2022-10-10 17:01:41 发布

不混吃的熊

最新推荐文章于 2022-10-10 17:01:41 发布

阅读量179

点赞数

本文链接：https://blog.csdn.net/weixin_42676300/article/details/106760184

版权

鉴于前段时间出差，积压了大量的内容需要提纯花费了一些时间，在这次出差中，干了echart,bootstarp,speed.js，还有强行用了oracle。我将一一对这些技术进行总结。
首先讲数据库oracle 。讲就讲清楚，在此特意说一下oracel相对mysql最大的不同应该就是事务了。
首先先说一些用过的函数

to_char(to_date(SJ, 'yyyy-mm-dd'),'yyyymm')

1．字符串转日期
2．日期对应格式

A LEFT JOIN B ON A.A=B.A
LEFT JOIN ON

左表保留+右表关联
A全 B看条件

RIGHT JOIN ON 
A RIGHT JOIN B ON A.A=B.A

左表关联+右表保留
B全 A看条件

FULL JOIN ON 
A RIGHT JOIN B ON A.A=B.A

左表保留右表保留其他空行
B全 A全连接看条件

oracle
运算符
时间处理运算符

**SELECT SYSDATE,TRUNC(SYSDATE),**

to_char(SYSDATE,'yyyy-mm-dd')

,–时间格式年-月-日

to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss') ,

–时间格式年-月-日时：分：秒

TRUNC(SYSDATE,'yyyy')

, --取指定年的第一天格式年/1/1

SYSDATE+1,SYSDATE-1,
add_months(SYSDATE+1,-2)

–-返回指定月，自动跨越年月

FROM dual

Nvl(x,x_temp)

如果x为null那么取 x_temp

NULLIF(x,x_temp)

如果x=x_temp 那么返回NULL否则返回x

NVL2(x,y,x_y_temp)

如果x为空，试试y如果x,y都为空，那么返回y_temp

Oracle对NULL的处理
除了
nvl, nvl2,判断null,
IS NOT NULL IS NULL ，判断不为空
以外任何oracle运算符不处理和空的判断
NULL 在聚合函数中的影响
Oracle中聚合函数名称函数的作用
Count 用来求有效数据的数量（对null不敏感）
Max 用来求给定数据中最大的那一个数据（对null不敏感）
Min 用来求给定数据中最小的那一个数据（对null不敏感）
Avg 用来求给定数据的平均值（对null不敏感）
Sum 用来求给定数据的总和（对null不敏感）
Variance 用来求给定数据的标准差（对null不敏感）
Stddev 用来求给定数据的方差（对null不敏感）

如果数据条

看看数据库对null的处理

/*聚合函数*/
SELECT JH,COUNT(1),COUNT(jh),
COUNT( DISTINCT jh),COUNT(sj),COUNT(*),MAX(YL),MIN(YL),SUM(YL), 
AVG(YL) FROM temp_study
GROUP BY JH

在这里我们可以看到a 23/4是不可能等于7.6的
这个结果是23/3出来的。
然后我们看到这个min 是对null彻底不敏感的。
AVG也是排null项然后进行处理的。Max项看不出来，但是应该也是不做处理的。
为了检验

我们加入c-1 -2两条数据
然后再用聚合函数查一次

这个null够不够明显。
所以很多时候忽略空，我们无法进行准确的聚合，或者清空为null数据，或者使用nvl,nvl2进行处理。
NULL 是否能被Like运算符识别

SELECT * FROM temp_study
SELECT * FROM temp_study WHERE sj LIKE '%'

Like是识别不到null这个字段的。

NULL 在 in 和 not in 中的影响
SELECT * FROM temp_study WHERE sj IN (‘2017-06-22’,NULL)
In 的内部机理

SELECT * FROM temp_study where 
(sj = '2017-06-22'  OR  sj =  NULL  OR sj = NULL)

查询结果为

同样这个null还是不在计算中
怎么样才能计算到这个null？

SELECT * FROM temp_study WHERE /*sj IN ('2017-06-22',NULL)*/ 
 (sj = '2017-06-22'   OR sj IS NULL)

讲一下not in

SELECT * FROM temp_study WHERE NOT( sj  IN ('2017-06-22',NULL))

SELECT * FROM temp_study WHERE sj!='2017-06-22'  AND sj !=NULL
 AND sj != '2016-01-22' AND sj != NULL

尤其注意
sj !=NULL 之前说过在所有的oracle中这样是无法判断为null的所以就为false 啥也查不出来。所以not in 无法出来In的补集。

NULL和+——*、
都是无法运算的

SELECT 1*NULL, 1+NULL,1/NULL,1-NULL FROM dual


Select * from where ** groupby ** having xx order by xxx

执行层次 from – where – group by – having –select – order by oracle语句
非oracle的可能 order by 在select 前面
因为oracle 引进了一个 select * from dual 引入了一个虚拟表，也就是select可能也能改变表的结构。

层次

SELECT a.*,b.*
FROM cspj_d_djxx a /*＃号表*/
INNER  JOIN xb_ys_kfqk b ON a.qkdm=b.qkdm  
WHERE 1=1
AND a.jh LIKE 'AT%'
AND b.qkmc LIKE '塔河%'

限定集在集合表之后

SELECT * FROM 
(SELECT * FROM cspj_d_djxx
WHERE jh LIKE 'AT%'
) a 
INNER JOIN (
SELECT * FROM xb_ys_kfqk 
WHERE qkmc LIKE '塔河%')b
ON a.qkdm=b.qkdm

限定集在子查询

Left Join 的区别

SELECT a.*,b.*
FROM cspj_d_djxx a /*££ºÅ±í*/
INNER  JOIN xb_ys_kfqk b ON a.qkdm=b.qkdm  
WHERE 1=1
AND a.jh LIKE 'AT%'
AND b.qkmc LIKE 'ËþºÓ%'

SELECT * FROM 
(SELECT * FROM cspj_d_djxx
WHERE jh LIKE 'AT%'
) a 
INNER JOIN (
SELECT * FROM xb_ys_kfqk 
WHERE qkmc LIKE 'ËþºÓ%')b
ON a.qkdm=b.qkdm

差不多知道到这就OK了
看看oracle 新特性

MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
    UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
INSERT (a,b) VALUES(T2.a,T2.b);

带判断的插入语句或插，或更新。还是比较强大的。

一个一般的存储过程
业务级别

CREATE OR REPLACE PROCEDURE PRO_CSPJ_TJ_QK_YDCL(STR VARCHAR) 
AS
BEGIN

 delete from CSPJ_TJ_QK_YDCL WHERE NY = str; 
  MERGE INTO CSPJ_TJ_QK_YDCL T1
  USING (
         SELECT QKDM,
                 MAX(QKMC) AS QKMC,
                 EJDWDM,
                 NY,
                 SUM(HSYCYL) AS YCYL,
                 SUM(YL) AS CS_YCYL
           FROM CSPJ_D_DJXX
           LEFT JOIN (
                      SELECT CQ.JH AS JH,
                       CQ.NY AS NY, HSYCYL, 
                       CSY.YL AS YL
                        FROM CWBASE3_9999.V_ZY_DJCLYB@ZYKJ CQ
                        LEFT JOIN (
                                   SELECT JH, SUM(YL) AS YL, NY
                                     FROM (SELECT JH,
                                                   TO_CHAR(TO_DATE(SJ, 'yyyy-mm-dd'),
                                                           'yyyymm') AS NY,
                                                   NVL(YL, 1) AS YL
                                              FROM CSPJ_C_YXQ_MX
                                             WHERE 1 = 1 
                                            ) T
                                    GROUP BY JH, NY
                                   ) CSY
                          ON CQ.JH = CSY.JH
                         AND CQ.NY = CSY.NY
                       WHERE 1 = 1
                         AND CQ.NY = str
                      ) YL
             ON YL.JH = CSPJ_D_DJXX.JH
             WHERE QKDM IS NOT NULL AND NY IS NOT NULL
          GROUP BY QKDM, NY, EJDWDM
         ) T2
  ON (T1.QKDM = T2.QKDM AND T1.NY = T2.NY)
  /*更新*/
  WHEN MATCHED THEN
    UPDATE
       SET T1.YCYL    = T2.YCYL,
           T1.LRSJ    = SYSDATE,
           T1.CS_YCYL = T2.CS_YCYL,
           T1.EJDWDM  = T2.EJDWDM
    /*插入*/
  WHEN NOT MATCHED THEN
    INSERT
      (T1.QKDM, T1.QKMC, T1.EJDWDM, T1.NY, T1.YCYL, T1.CS_YCYL, T1.LRSJ)
    VALUES
      (T2.QKDM, T2.QKMC, T2.EJDWDM, T2.NY, T2.YCYL, T2.CS_YCYL, SYSDATE);
      commit;
END PRO_CSPJ_TJ_QK_YDCL;

Oracle 要学习的东西很多。引用一些逼的概念。

块清除（block cleanouts）

在事务需要修改数据时，必须分配ITL事务槽，必须锁定该行，必须分

配回滚段事务槽和回滚空间记录前镜像。当事务提交时，oracle需要将回滚段上

的事务表信息标记为非活动，以便空间可以重用；那么还有ITL事务信息和锁定信

息需要清除，以记录提交。由于oracle在数据块上存储了ITL和锁定等事务信息，

所以oracle必须在事务提交之后清除这些事务数据，这就是块清除。块清除主要

清除的数据有行级锁、ITL信息（包括提交标志、SCN等）。如果提交时修改过的

数据块仍然在buffer cache之中，那么oracle可以清除ITL信息，这叫做快速块清

除，如果事务提交时修改过的数据块已经被写回到数据文件上，oracle会等到下

次访问该block时再来清除ITL锁定信息，这就是延迟块清除。

DML执行过程

update undotest set object_type=‘VIEW’ where object_type=‘PROCEDURE’;

检查shared pool中是否存在同样的语句。假设存在。重用运行计划，运行扫描运算，假设不存在。运行硬解析生成运行计划
依据运行计划中的扫描运算，检查undotest表中的相关数据块是否存在buffer cache中，假设不存在则读取到内存中
检查数据块中符合object_type='PROCEDURE’条件的记录，假设没有符合条件的行记录，则结束语句。假设存在则进入下一步
以当前模式（current）获取符合object_type='PROCEDURE’条件的数据块，准备进行更新
5.在回滚表空间的对应回滚段头的事务表上分配事务槽，这个动作须要记录redo日志
从回滚段数据块上创建object_type='PROCEDURE’的前映像数据，这个动作也要记录redo日志
改动object_type=‘VIEW’ ，这是DML操作的数据变更，而须要记录redo日志
用户提交时，在redo日志中记录提交信息，将回滚段头上的事务表和回滚段数据块标记为非活动，清除改动数据块上的事务信息（也可能延迟清除）。同一时候必须确保整个事务的redp日志写到磁盘上的日志文件
注意：假设最后用户回滚了事务，oracle从回滚段中将前映像数据提取出来，覆盖被更新的数据块。这个回滚动作本身也须要产生redo日志，因此。我们要知道回滚的代价很昂贵。

事务

Oracle对事务的处理
oracle中的事务是以sql的开始作为事务的开始,
sql的commit/rollback/ddl作为事务的结束.
事务不同于连接,一个连接建立之后,可以有多个事务.
简单来说不管你做什么最后最好都以commint结尾
oracle使用语句savepoint sp_begintran开启显式事务，锁本身和事务是没有关系的，只要是数据库的操作都会产生锁。处于事务中的SQL语句只有这个事务提交（commit）之后，事务中的SQL语句影响的表记录上的锁才会释放。锁常见有共享锁（select语句产生）和排它锁（DML语句产生），如果一个表上加载有共享锁，还可以叠加共享锁，但不能叠加排它锁。如果一个表上加载有排他锁，就什么锁都不能加了，也就是说如果DML语句占用过多的时间，这些数据库效率就不高，就需要优化，当然select语句性能低了也不行。
每个存储过程可以不用显式事务，它本身就为你开启了一个隐式事务，如果需要开启显示事版务，就通过savepoint sp_begintran开启，无论是不是显式还是隐式事务，你都得权通过commit work提交事务，通过exception捕捉SQL语句异常，在异常发生时需要回滚事务（rollback work）。
所以你可以人为任意数据库的CUD都会隐似的带着一个事务。R也就是select虽然不会携带事务，但是会带着共享锁。
根据CUD的特性恢复方式
对于任何DML操作而言，必须同时处理数据块和撤销块，并且还会生成重做信息

在ACID中，A、C、I要求生成撤销，D则要求生成重做

INSERT：

    撤销段记录插入记录的rowid,如果需要撤销，则根据rowid将该记录删除即可

UPDATE:

    撤销段记录被更新字段的原始值，撤销时将原始值覆盖新值即可

DELETE：

    撤销段记录整行的数据，撤销时执行反向操作将该记录插入原表

假设员工scott的薪水为3000那么：
1）在t1时间，在session1查询可以得到这个结果
2）在t2时间session2进行更新，将scott的薪水增加3000，并为提交
3）在t3时间session1再次查询，注意此时，oracle不会允许其他用户看到未提交数据，所以此时，oracle需要通过回滚段记录的前镜像进行一致读，将3000恢复出来供给用户，这是一致性读的作用。
4）在t4时间，session2提交更改，此时数据修改被永久化；
5）在t5时间，其他用户再次查询，将会看到变化后的数据，也就是4000.
oracle内部使用scn作为时钟，在这里查询结果集就是根据scn来进行判断的，每个数据块头部都会记录一个提交scn，当数据更改提交后，提交scn同时被修改，这个scn在查询时可以用来进行一致性读判断。
理解一下提交的概念。只有当事务提交之后，才会认为语句已经处理。当然数据库本身有自动提交机制。所以在拼接单挑oracle数据的时候就很正常。
在undo段头的转储文件中，dba指向的就是包含这个事务的前镜像的数据块地址。前十位代表文件号，后22位代表block号。转储文件中的irb:指的是回滚段中记录的最近未提交变更开始之处，如果回滚，这是起始的搜索点。rci：该参数代表的是undo chain（同一事务中的多次修改，根据chain连接关联）的下一个偏移量。在x$BH中class字段中4表示的是段头，1表示的是数据块，state为3的就是一致读构造的前镜像。
事务的内部流程
1）首先当一个事务开始时，需要在回滚段事务表上分配一个事务槽
2）在数据块头部获取一个ITL事务槽，该事务槽指向回滚段头的事务槽
3）在修改数据之前，需要记录前镜像信息，这个信息以undo record的形式存储在回滚段中，回滚段头事务槽指向该记录。
4）锁定修改行，修改行锁定位指向ITL事务槽。
5）数据修改可以进行。

Oracle实现并发的机制
共享锁：如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。（注：共享锁是表级的，比如Select会对表加共享锁）
排他锁：如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的锁。获准排他锁的事务既能读数据，又能修改数据。（注：排他锁一般是行级的，比如DML操作 insert update delete，在执行DML操作时分两步加锁，先加共享锁，后加排他锁。在添加排他锁后，不能添加任何锁直至锁释放 commit或者rollback）
锁往往跟着并发是密切相关的，学过语言的朋友应该都知道，抢占式才能本质的提升程序执行的效率。
数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。
共享锁无法阻止其他用户读取和修改表中的数据，只能阻止其他用户使用ALTER TABLE命令改变指定表的结构或用DROP TABLE命令删除指定表。多个用户可以同时获得相同数据上的共享锁。
执行DML语句时可以获得排他锁，并且正在被修改的所有记录都会获得排他锁。在你启动的事务内，排他锁阻止其他用户获得正在处理的数据上的排他锁，直至执行COMMIT或ROLLBACK语句。这样，两个用户就无法同时更新相同的数据。某个用户试图更新被另一个用户锁定的数据时，第一个用户必须等待至去除排他锁后才能进行操作。

不混吃的熊

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 浅认识

鉴于前段时间出差，积压了大量的内容需要提纯花费了一些时间，在这次出差中，干了echart,bootstarp,speed.js，还有强行用了oracle。我将一一对这些技术进行总结。首先讲数据库oracle 。讲就讲清楚，在此特意说一下oracel相对mysql最大的不同应该就是事务了。首先先说一些用过的函数to_char(to_date(SJ, 'yyyy-mm-dd'),'yyyymm')1．字符串转日期2．日期对应格式A LEFT JOIN B ON A.A=B.ALEFT JOIN
复制链接

扫一扫