基本知识

-- 寻找分区脚本
    select count(1)
      -- into v_count
      from user_tab_partitions t
     where t.table_name = upper(p_table_name)
       and t.partition_name = upper(p_partition_name);

新增分区：alter table p_table_name add partition p_partition_name values (p_partition_value);
清空分区：alter table dwmgr.ui_wk_sop_dp_fcst truncate partition PART_V12021112201 ;
删除分区：alter table dwmgr.ui_wk_sop_dp_fcst drop partition PART_V12021112201 ;

新增主键：alter table ESPMGR.MST_SKU_V0 add constraint MST_SKU_V0_PK primary key (ITEM, LOC);
新增分区表主键：alter table ui_dy_cp_inner_report_line add constraint pk_ui_dy_cp_inner_report_line primary key (plan_version,line_id) using index tablespace dw_ts_idx local;
删除主键：alter table ui_dy_cp_inner_report_line drop constraint pk_ui_dy_cp_inner_report_line ;

创建索引：create index idx_ui_dp_rpt_line_01          on ui_dp_rpt_line            (line_id, plan_id)                   tablespace dw_ts_idx local;
          create index idx_ui_dy_cp_inner_rpt_line_01 on ui_dy_cp_inner_report_line(plan_version,header_id,bucket_type) tablespace dw_ts_idx local;

删除索引：drop index index_name ;
修改表：

　　添加新字段：
　　alter table 表名 add(字段名 字段类型 默认值 是否为空);
　　alter table user add(age number(6));
　　alter table user add (course varchar2(30) default '空' not null);

　　修改字段：
　　alter table 表名 modify (字段名 字段类型 默认值 是否为空);
　　alter table user modify(age number(8));

　　修改字段名：
　　alter table 表名 rename  column  列名 to 新列名;
　　alter table user rename column course to newcourse;

　　删除字段：
　　alter table 表名 drop column 字段名;
　　alter table user drop column course;

    删除序列
    DROP SEQUENCE sequence_name;

-- 批量插入数据
insert into dwmgr.tmp_1490597_20211210_sunny(SELLER,ITEM)
select 'O-010219' as SELLER,'110010220759' as ITEM from dual;
commit;

跨实例授权：-- Grant/Revoke object privileges
grant select on sys.v_$mystat to dwmgr;
grant select on sys.v_$session to dwmgr;
grant select, insert, update, delete on in_plan_version_calendar to espmgr;
grant select on in_plan_version_calendar to outmgr;

trunc(sysdate,'d' )  -- 返回当周的第一天的日期，这个第一天，oracle 默认是星期天
trunc(sysdate,'ww')  -- 返回上个周五的日期，如果 sysdate 是周五，则输出 sysdate 日期
trunc(sysdate,'iw')  -- 返回当周的周一的日期

trunc(sysdate,'yyyy') -- 返回当年第一天的日期 或者 trunc(sysdate,'yy')
trunc(sysdate,'mm'  ) -- 返回当月第一天的日期
trunc(sysdate,'dd'  ) -- 返回当天年月日的日期

-- hints
/*+ PARALLEL(N) */
/*+ index(表名,索引名) */

-- 拿到当前时间所在月的每一天的数据
select trunc(sysdate,'mm') + rownum - 1 as dated
  from dual
  connect by rownum <= to_char(last_day(sysdate),'dd')
order by 1
;

to_char(sysdate,'ww') : 返回的是一年中的第几周，ww 的算法为每年1月1日为第一周开始，date+6为每一周结尾
to_char(sysdate,'iw') : 返回的是一年中的第几周，iw 的算法为星期一至星期日算一周，且每年的第一个星期一为第一周，
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
to_char(sysdate,'ss'  ) -- 取当前时间秒部分
to_char(sysdate,'mi'  ) -- 取当前时间分钟部分
to_char(sysdate,'HH24') -- 取当前时间秒小时部分
to_char(sysdate,'DD'  ) -- 取当前时间日期部分
to_char(sysdate,'MM'  ) -- 取当前时间月部分
to_char(sysdate,'YYYY') -- 取当前时间年部分
to_char(sysdate,'w'   ) -- 取当前时间是一个月中的第几周(从1日开始算)
to_char(sysdate,'ww'  ) -- 取当前时间是一年中的第几周（从1.1开始算）
to_char(sysdate,'iw'  ) -- 取当前时间是一年中的第几周（按实际日历的）
to_char(sysdate,'d'   ) -- 取当前时间是一周的第几天，从星期天开始，周六结束
to_char(sysdate,'day' ) -- 取当前日是星期几，和数据库设置的字符集有关，会输出'Tuesday'
to_char(sysdate,'ddd' ) -- 当前日是一年中的第几天

-- 查表表字段
select a.OWNER
      ,a.TABLE_NAME
      ,a.COLUMN_NAME
      ,case when a.NULLABLE = 'Y' then '可为空' when a.NULLABLE = 'N' then '不可为空' else '@' end as NULLABLE_flag
      ,a.COLUMN_ID as "字段序号"
      ,a.CHAR_LENGTH as "字符串类型长度"
  from all_tab_columns a
 where 1 = 1
   --and a.COLUMN_NAME like upper('%attri%')    -- 字段名
   and a.TABLE_NAME = upper('ui_lookup_value')  -- 表名
   and a.OWNER = upper('DWMGR')                 -- 实例名
;
-- 查看键的表格
select * from user_constraints
;
-- 有外键的表
删除主表的记录时，你必先删除子表的记录才能够删主表的记录
删除子表的记录时，你可以直接删除
;
-- 查外键
select rcc.owner,rcc.table_name 子表,rcc.constraint_name,rcc.column_name,c.table_name 父表,c.constraint_name,cc.column_name
  from user_constraints  c, -- 父表
       user_cons_columns cc,
       user_cons_columns rcc -- 子表
  where   1 = 1
    and c.owner =upper('abppmgr' )
  --and   rcc.table_name='R5HOME'
  and (c.TABLE_NAME = upper('mst_sop_dmr') or rcc.TABLE_NAME = upper('mst_sop_dmr')) -- 表名
  and   c.constraint_type='R'
  and   c.owner=cc.owner
  and   c.constraint_name=cc.constraint_name
  and   c.r_owner=rcc.owner
  and   c.r_constraint_name=rcc.constraint_name
  and   cc.position=rcc.position
  order   by   c.constraint_name,cc.position
;
-- 查索引
select a.TABLE_NAME,a.INDEX_NAME,b.COLUMN_NAME,b.COLUMN_POSITION
  from user_indexes a,
       user_ind_columns b
 where a.TABLE_NAME = b.TABLE_NAME
   and a.INDEX_NAME = b.INDEX_NAME
   and a.TABLE_NAME = upper('ui_pegging_rpt')
 order by a.INDEX_NAME,b.COLUMN_POSITION
;
-- 查存储过程的相关信息
select *
  from user_source  a
 where a.TEXT like '%vendor_id%'
 order by 4


-- 重点学习点
1. --v_sql p_table_name p_partition_name p_partition_value 都是参数

   v_sql := 'alter table '||p_table_name||' add  partition '||p_partition_name||' values ('''||p_partition_value||''')';
execute immediate v_sql;

if v_count > 0 then
   v_sql := 'alter table '||p_table_name||' drop partition '||p_partition_name||' update global indexes';
   execute immediate v_sql;
end if;
      --  update global indexes 用这个是因为在对分区做 DDL 操作时，会使分区全局索引失效，需要加上关键字 update global indexes。

2.
  EXCEPTION
   WHEN OTHERS
   THEN
      p_err_code := sqlcode;
      v_msg      := substr(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace, 1, 4000);
      dwmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Failed', v_msg, p_oflow_id, p_oflow_name);

      raise_application_error(-20001, v_msg);

3.-- 这段是包体前面，具体的作用是什么 ？
create or replace package body PKG_IPLAN_LOG is

  TYPE type_session IS RECORD(
    session_id NUMBER,
    serial#    NUMBER,
    user_name  VARCHAR2(100));

  TYPE type_task_log IS RECORD(
    log_id     NUMBER,
    start_time NUMBER);

  TYPE type_tb_task_log IS TABLE OF type_task_log INDEX BY VARCHAR2(1000);

  gv_session  type_session;
  gv_task_log type_tb_task_log;

  PROCEDURE p_write_task_log(i_module_name IN VARCHAR2,
                             i_task_name   IN VARCHAR2,
                             i_status      IN VARCHAR2,
                             i_msg         IN VARCHAR2,
                             i_oflow_id    IN VARCHAR2 DEFAULT NULL,
                             i_oflow_name  IN VARCHAR2 DEFAULT NULL,
                             i_row_count   IN NUMBER DEFAULT NULL,
                             i_sql_text    IN VARCHAR2 DEFAULT NULL,
                             i_get_sql_id  IN VARCHAR2 DEFAULT 'N') IS
    PRAGMA AUTONOMOUS_TRANSACTION -- 这个是放在过程前面，作用是什么 ？
    v_session_id NUMBER;
    v_serial#    NUMBER;

    v_iplan_task_run_log iplan_task_run_log%ROWTYPE;

-- 4. sql%rowcount 的作用：记录修改的条数，当你进行删除，更新操作的时候，可以提示已删除\更新的行数，这个参数放在修改语句和commit之间 。

5. EXISTS 跟 IN 比，哪个是外大内小，外小内大 ？

6.     select listagg(t.column_name, ',') within group (order by column_name)
         into v_tab_cols
         from user_tab_columns t
        where t.table_name = upper(p_table_name);
解：https://blog.csdn.net/RuiKe1400360107/article/details/88855904?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163851587516780255268051%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=163851587516780255268051&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduend~default-1-88855904.first_rank_v2_pc_rank_v29&utm_term=listagg+within+group&spm=1018.2226.3001.4187

xmlagg
wm_concat


/*+  LEADING(s) */
/*+ USE_HASH_AGGREGATION */
/*+ index(d PK_MID_SOP_TO_ESP_FCST_DATA_DLY) */
/*+ APPEND */
-- 执行过程
DECLARE
  v_plan_version VARCHAR2(4000);
  p_parameter    VARCHAR2(64) := NULL;
  p_oflow_id     VARCHAR2(64) := NULL;
  p_oflow_name   VARCHAR2(64) := NULL;
  p_err_code     NUMBER := NULL;
BEGIN
  pkg_dw_rpt_demand.p_ui_dp_fcst_v1(p_parameter,
                                    p_oflow_id,
                                    p_oflow_name,
                                    p_err_code);

  pkg_dw_rpt_demand.p_ui_dp_fcst_v3(p_parameter,
                                    p_oflow_id,
                                    p_oflow_name,
                                    p_err_code);

END;

-- 嵌套循环，叠加字段
DECLARE
  v_plan_version VARCHAR2(30) := 'V32021120301';
  v_sql clob;
BEGIN

  FOR i IN 2 .. 26
  LOOP

    v_sql := 'update ui_wk_vs_gap_rpt_line1 set  bucket_qty' || i || '= ';

    FOR j IN i - 1 .. i
    LOOP

      v_sql := v_sql || 'bucket_qty' || j || '+';

    END LOOP;

    v_sql := v_sql ||
             '0 where bucket_type = ''ACC_GAP'' and plan_version = ''' ||v_plan_version || '''';

    dbms_output.put_line(v_sql);
    --EXECUTE IMMEDIATE v_sql;

  END LOOP;

END;


-- 2022-01-05
dense_rank 函数
dense_rank 函数的功能与 rank 函数类似，dense_rank 函数在生成序号时是连续的，而 rank 函数生成的序号有可能不连续。
dense_rank 函数出现相同排名时，将不跳过相同排名号，rank 值紧接上一次的 rank 值。
在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第三名，dense_rank() 是连续排序，有两个第一名时仍然跟着第二名。

-- 2022-01-06
如果误删并提交了，可以用这条语句找回误删的数据
insert into sopmgr.out_sop_fcst_to_esp_dly(plan_id,
                                           item,
                                           seller,
                                           customer,
                                           site,
                                           bucket_date,
                                           demand_qty,
                                           demand_type,
                                           demand_classification,
                                           client_type,
                                           country_type,
                                           need_date,
                                           create_date,
                                           order_header_num,
                                           order_line_num,
                                           order_line_id,
                                           ou_id,
                                           top_priority,
                                           sys_last_modified_by,
                                           sys_last_modified_date)
select * from sopmgr.out_sop_fcst_to_esp_dly as of timestamp to_timestamp('2022-01-06 11:00:00','yyyy-mm-dd hh24:mi:ss')
;
-- 2022-01-11 在 where  条件后面有这个，是啥意思 ？
log errors INTO err_in_rcv_transaction reject LIMIT unlimited;
log errors INTO err_in_supplier_commit_line reject LIMIT unlimited

2022-01-19
pkg_sop_load_master_to_abpp 的 gv_archive_count PLS_INTEGER;
2022-01-24 
1. merge into 后面的 insert 和 update 是否可以用 Hints ？
2. merge 的 update 数量，每 5000 条就commit 一次，如何写这种脚本？
3. 游标
DECLARE
  type t_row_id is table of dwmgr.ui_pegging_rpt.rowid%type;
  v_row_id t_row_id;
  
  -- 游标
  CURSOR C IS
    SELECT ROWID AS ROW_ID FROM dwmgr.ui_pegging_rpt;
    
BEGIN
  -- 打开游标
  OPEN C;
  LOOP
    -- 批量更新，一次更新10000条数据
    FETCH C BULK COLLECT
      INTO v_row_id LIMIT 10000;
    
    -- 使用 forall
    FORALL i IN 1 .. v_row_id.count
      UPDATE dwmgr.ui_pegging_rpt
         SET planner_code = 
       WHERE ROWID = v_row_id(i)
       ;
       
       -- 提交
       COMMIT;
       EXIT WHEN C%NOTFOUND;
       
     END LOOP;
     
     COMMIT;
     
     close C;
 end;
 /
4. START WITH  CONNECT BY
    merge into dwmgr.ui_pegging_rpt tar
    using (select t.dmdseqnum,
                  t.supplyitem as top_mfg_item
             from (select t.dmdseqnum,
                          t.supplyitem,
                          row_number() over(partition by t.dmdseqnum order by level) rn
                     from dwmgr.ui_pegging_rpt t
                    where exists (select 1
                                    from dwmgr.in_boms b
                                   where b.item_number = t.supplyitem)
                    START WITH t.plan_version = v_plan_version
                           AND t.parentsupplytype = 0 --顶层开始往下找
                           AND exists (select 1
                                         from dwmgr.tmp_pegging_top_item p
                                        where p.dmdseqnum = t.dmdseqnum
                                          and p.plan_version = v_plan_version
                                          and p.task_id = p_task_id
                                          and p.row_num >= p_start_num
                                          and p.row_num <= p_end_num)
                  CONNECT BY nocycle PRIOR t.dmdseqnum = t.dmdseqnum
                                 AND PRIOR t.supplyseqnum = t.parentsupplyseqnum
                                 AND PRIOR t.plan_version = t.plan_version) t
            where t.rn = 1) src
    on (tar.plan_version = v_plan_version and tar.dmdseqnum = src.dmdseqnum)
    when matched then
      update set tar.top_mfg_item = src.top_mfg_item;