视图和表名表注释查询

-- 视图
CREATE OR REPLACE VIEW V_OUT_ESP_DP_FCST_DLY AS
SELECT /*+  LEADING(s) */
       fd.item,
       fd.seller,
       fd.bucket_date,
       substr(t.bucket_desc, 1, 5) ||
       to_number(substr(t.bucket_desc, 6, 2)) AS bucket_desc,
       substr(t.plan_month_desc, 1, 5) ||
       to_number(substr(t.plan_month_desc, 6, 2)) AS plan_month_desc,
       i.prod_brand_desc,
       i.prod_categ_desc,
       i.item_desc,
       i.prod_model_desc,
       i.prod_series_desc,
       i.prod_project_name,
       s.seller_desc,
       s.region,
       s.region_desc,
       i.item_carrier_name_cn,
       nvl(a.accumulated_ship_qty, 0) AS accumulated_ship_qty,
       nvl(a.accumulated_ship_qty, 0) + nvl(fd1.total_week_qty, 0) AS total_qty,
       fd.week_qty,
       m.spu_code,
       m.spu_name
  FROM (SELECT /*+ USE_HASH_AGGREGATION */
               item,
               seller,
               bucket_date,
               SUM(nvl(gtmfcst, 0) + nvl(gtm_buff, 0) + nvl(dp_buff, 0)) AS week_qty
          FROM sopmgr.mid_sop_to_esp_fcst_data_dly d
         WHERE week_num > = 0
         GROUP BY item, seller, bucket_date) fd
  JOIN sopmgr.in_sop_seller_master s
    ON fd.seller = s.seller
  JOIN sopmgr.in_sop_item_master i
    ON fd.item = i.item
  JOIN dwmgr.ui_time_master t
    ON fd.bucket_date = t.bucket_date
  JOIN (SELECT /*+ index(d PK_MID_SOP_TO_ESP_FCST_DATA_DLY) */
               item,
               seller,
               SUM(nvl(gtmfcst, 0) + nvl(gtm_buff, 0) + nvl(dp_buff, 0)) AS total_week_qty
          FROM sopmgr.mid_sop_to_esp_fcst_data_dly d
         WHERE week_num > = 0
         GROUP BY item, seller) fd1
    ON fd.item = fd1.item
   AND fd.seller = fd1.seller
  LEFT JOIN (SELECT sku_code, organization_code, accumulated_ship_qty
               FROM dwmgr.in_accumlated_shipment
              WHERE status = 'ACTIVE'
                AND accumulated_by_date =
                    (SELECT MAX(accumulated_by_date) AS accumulated_by_date
                       FROM dwmgr.in_accumlated_shipment
                      WHERE status = 'ACTIVE')) a
    ON fd.item = a.sku_code
   AND fd.seller = a.organization_code
  LEFT JOIN dwmgr.in_sku_master m on fd.item = m.sku_code;
comment on table V_OUT_ESP_DP_FCST_DLY is '需求计划（产销协同决策后）';
comment on column V_OUT_ESP_DP_FCST_DLY.ITEM is 'SKU编码';
comment on column V_OUT_ESP_DP_FCST_DLY.SELLER is '国家代码';
comment on column V_OUT_ESP_DP_FCST_DLY.BUCKET_DATE is '整周开始日期';
comment on column V_OUT_ESP_DP_FCST_DLY.BUCKET_DESC is '整周描述';
comment on column V_OUT_ESP_DP_FCST_DLY.PLAN_MONTH_DESC is '月度描述';
comment on column V_OUT_ESP_DP_FCST_DLY.PROD_BRAND_DESC is '品牌';
comment on column V_OUT_ESP_DP_FCST_DLY.PROD_CATEG_DESC is '类型';
comment on column V_OUT_ESP_DP_FCST_DLY.ITEM_DESC is 'SKU描述';
comment on column V_OUT_ESP_DP_FCST_DLY.PROD_MODEL_DESC is '机型';
comment on column V_OUT_ESP_DP_FCST_DLY.PROD_SERIES_DESC is '产品系列';
comment on column V_OUT_ESP_DP_FCST_DLY.PROD_PROJECT_NAME is '项目名';
comment on column V_OUT_ESP_DP_FCST_DLY.SELLER_DESC is '国家名称';
comment on column V_OUT_ESP_DP_FCST_DLY.REGION is '战区代码';
comment on column V_OUT_ESP_DP_FCST_DLY.REGION_DESC is '战区名称';
comment on column V_OUT_ESP_DP_FCST_DLY.ITEM_CARRIER_NAME_CN is '运营商';
comment on column V_OUT_ESP_DP_FCST_DLY.ACCUMULATED_SHIP_QTY is '截止到当前发货量';
comment on column V_OUT_ESP_DP_FCST_DLY.TOTAL_QTY is '总量';
comment on column V_OUT_ESP_DP_FCST_DLY.WEEK_QTY is '本周数量';



SELECT a.owner       AS "schema 码",
       a.table_name  AS "表名",
       c.comments    AS "表注释",
       a.column_name AS "字段名",
       CASE
         WHEN a.data_type IN ('NUMBER', 'DATE') THEN
          a.data_type
         WHEN a.data_type = 'UROWID' THEN
          a.data_type || '(' || a.data_length || ')'
         ELSE
          a.data_type || '(' || a.char_length || ')'
       END        AS "字段类型",
       b.comments AS "字段注释"
  FROM all_tab_columns a
  LEFT JOIN user_col_comments b
    ON a.table_name = b.table_name
   AND a.column_name = b.column_name
  LEFT JOIN all_tab_comments c
    ON c.owner = a.owner
   AND c.table_name = a.table_name
 WHERE 1 = 1
   AND translate(a.table_name, '0123456789', '@@@@@@@@@@') NOT LIKE '%@%' -- 过滤备份表
   AND a.owner IN ('ODSMGR', 'DWMGR', 'SOPMGR', 'ESPMGR', 'OUTMGR')
 ORDER BY 1, 2, a.column_id