Oracle views View

In my opinion, the view of Oracle is used to map the results of the associated query of multiple tables into a "temporary table", and the view is in real time corresponding to the data in the system tables.

We can manipulate a view query just like a query on a table.

View notation:

CREATE OR REPLACE VIEW   V_tem (   ID,   NAME) as   SELECTfromTABLE

A case in the project that reflects the organizational structure of a self-correlating structure table (row to column): City-District-grid-Township-channel

Knowledge points to use:

1, Sys_connect_by_path (field, spacer) Example: Sys_connect_by_path (ID, ' | ')

2, SUBSTR ()

3, INSTR ()

Note: The index of the position where the two functions treat the string is starting at 1 (both 0 and 1 are represented starting from the 1th position)

SQL code:

DROP VIEWTLS. V_channel_path;CREATE OR REPLACEForceVIEWTLS. 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_n AME, Pq_channel_name, Xz_channel_name) as   SELECTID, 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(SELECTA.*, SUBSTR (Sys_connect_by_path (ID,'|'),2)|| '|'path_id, SUBSTR (Sys_connect_by_path (NAME,'|'),2)|| '|'path_name fromTl_channel a START withId= 1CONNECT byPRIOR ID=PARENT_ID) A;

View Code

Oracle views View