Oracle 視圖view

標籤：資料   視圖   .com   targe   nbsp   sel   oracle   blank   table   

在我看來，oracle的視圖就是用於將多個表的關聯查詢結果映射成【暫存資料表】，視圖與系統資料表中的資料是即時對應的。

我們可以像動作表的查詢一樣來操作視圖查詢。

視圖寫法：

CREATE OR REPLACE FORCE VIEW  V_TEM(   ID,   NAME)AS   SELECT ID,NAME FROM TABLE

項目中的一個案例，將一個自關聯的結構表（行轉多列）體現組織圖：市-區縣-網格-鄉鎮-渠道

用到的知識點：

1、SYS_CONNECT_BY_PATH(欄位,間隔符)    樣本：SYS_CONNECT_BY_PATH(ID, ‘|‘)

2、SUBSTR()

3、INSTR()

備忘：兩函數對待字串的位置的索引是從1開始的（填寫0和1都是代表從第1個位置開始）

 sql代碼：

DROP VIEW TLS.V_CHANNEL_PATH;CREATE OR REPLACE FORCE VIEW TLS.V_CHANNEL_PATH(   ID,   PARENT_ID,   NAME,   ID_LEVEL,   TYPE,   IN_USE,   STATUS_TIME,   LEVEL_ID,   BANK_NAME,   ACCOUNT_NAME,   ACCOUNT_NO,   GROUP_ID,   CLASS,   CHANNEL_TEL,   QX_CHANNEL_ID,   PQ_CHANNEL_ID,   XZ_CHANNEL_ID,   QX_CHANNEL_NAME,   PQ_CHANNEL_NAME,   XZ_CHANNEL_NAME)AS   SELECT ID,          a.parent_id,          NAME,          id_level,          TYPE,          in_use,          status_time,          level_id,          bank_name,          account_name,          account_no,          GROUP_ID,          CLASS,          channel_tel,          SUBSTR (a.path_id,                    INSTR (path_id,                           ‘|‘,                           1,                           1)                  + 1,                    INSTR (path_id,                           ‘|‘,                           1,                           2)                  - INSTR (path_id,                           ‘|‘,                           1,                           1)                  - 1)             qx_channel_id,          SUBSTR (a.path_id,                    INSTR (path_id,                           ‘|‘,                           1,                           2)                  + 1,                    INSTR (path_id,                           ‘|‘,                           1,                           3)                  - INSTR (path_id,                           ‘|‘,                           1,                           2)                  - 1)             pq_channel_id,          SUBSTR (a.path_id,                    INSTR (path_id,                           ‘|‘,                           1,                           3)                  + 1,                    INSTR (path_id,                           ‘|‘,                           1,                           4)                  - INSTR (path_id,                           ‘|‘,                           1,                           3)                  - 1)             xz_channel_id,          SUBSTR (a.path_name,                    INSTR (path_name,                           ‘|‘,                           1,                           1)                  + 1,                    INSTR (path_name,                           ‘|‘,                           1,                           2)                  - INSTR (path_name,                           ‘|‘,                           1,                           1)                  - 1)             qx_channel_name,          SUBSTR (a.path_name,                    INSTR (path_name,                           ‘|‘,                           1,                           2)                  + 1,                    INSTR (path_name,                           ‘|‘,                           1,                           3)                  - INSTR (path_name,                           ‘|‘,                           1,                           2)                  - 1)             pq_channel_name,          SUBSTR (a.path_name,                    INSTR (path_name,                           ‘|‘,                           1,                           3)                  + 1,                    INSTR (path_name,                           ‘|‘,                           1,                           4)                  - INSTR (path_name,                           ‘|‘,                           1,                           3)                  - 1)             xz_channel_name     FROM (    SELECT a.*,                      SUBSTR (SYS_CONNECT_BY_PATH (ID, ‘|‘), 2) || ‘|‘ path_id,                      SUBSTR (SYS_CONNECT_BY_PATH (NAME, ‘|‘), 2) || ‘|‘                         path_name                 FROM tl_channel a           START WITH ID = 1           CONNECT BY PRIOR ID = parent_id) a;

View Code

 

Oracle 視圖view