Oracle 视图view

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle 视图view，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含1993字，纯文字阅读大概需要3分钟。

内容图文

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

标签：数据   视图   .com   targe   nbsp   sel   oracle   blank   table   

本文系统来源：http://www.cnblogs.com/senyier/p/6612495.html

内容总结

以上是互联网集市为您收集整理的Oracle 视图view全部内容，希望文章能够帮你解决Oracle 视图view所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---