ORACLE学习笔记

软件

DATAGRIP

快捷键

• 复制黏贴： Ctrl + C / Ctrl + V
• 撤回： Ctrl + Z
• 大小写批量修改：Ctrl + Shift + U
• 多游标选中： Ctrl + Shift + Alt + 鼠标左键

运行设置

自动提交还是手动提交（仅限于增删改查）

SQL语句笔记

创建物化视图

CREATE MATERIALIZED VIEW OPEN_MO_V
    COMPRESS BASIC NOLOGGING --表压缩节约磁盘空间
AS ...

手动刷新物化视图

BEGIN
    DBMS_MVIEW.REFRESH(
            LIST => 'SCP.AC2_V',
            METHOD => 'COMPLETE',
            REFRESH_AFTER_ERRORS => TRUE
        );
END;

更新表内字段

UPDATE OPEN_MO_HIST
SET OPEN_MO_HIST.PRODUCT_LINE = (SELECT MATERIAL_MASTER_V.PRODUCT_LINE
                                 FROM MATERIAL_MASTER_V
                                 WHERE MATERIAL_MASTER_V.MATERIAL = OPEN_MO_HIST.MATERIAL
                                   AND MATERIAL_MASTER_V.PLANT_CODE = OPEN_MO_HIST.PLANT_CODE)
WHERE PRODUCT_LINE IS NULL
  AND EXISTS(SELECT MATERIAL_MASTER_V.PRODUCT_LINE
             FROM MATERIAL_MASTER_V
             WHERE MATERIAL_MASTER_V.MATERIAL = OPEN_MO_HIST.MATERIAL
               AND MATERIAL_MASTER_V.PLANT_CODE = OPEN_MO_HIST.PLANT_CODE);

查询表依赖关系

select *
from DBA_DEPENDENCIES where OWNER = 'SCP' -- SCP AS SCHEMA NAME

高级NVL

SELECT COALESCE(NULL,NULL,3,4,5) FROM dual

输出：3

多核并行处理

SELECT  /*+ materialize parallel(6) */ --多核处理（6核）
* 
FROM dual

输出：3

补齐上一个不为空的值（Oracle 赛高！！）

下面是代码原文和结果集

with a as (select DATE$
           from scp.SY_CALENDAR
           where year = '2022'
             and WEEK_NO in ('23','24')),
     b as (select DATE$, 1 as num
           from SY_CALENDAR
           WHERE YEAR = '2022'
             and WEEK_NO in ('23','24')
             AND DATE$ = DATE '2022-06-07'),
     c as (select DATE$, 2 as num
           from SY_CALENDAR
           WHERE YEAR = '2022'
             and WEEK_NO in ('23','24')
             AND DATE$ = DATE '2022-06-12'),
     d as (SELECT a.DATE$, nvl(b.num, c.num)as num
           FROM A
                    LEFT JOIN B ON A.DATE$ = B.DATE$
                    left join c on a.DATE$ = c.DATE$
           order by DATE$)
select DATE$,num,last_value(num ignore nulls) over(order by DATE$) from d