首页 > 代码库 > Oracle 视图view
Oracle 视图view
在我看来，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;
Oracle 视图view
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。