Oracle 中 start with 递归查询、case when 条件表达式、rowid 伪列去重、level 级次、synonym 同义词

目录需求分析准备测试表与测试数据Oracle 中 start with...connect by prior 语句实现递归查询需求分析1、开发中经常会有这种需求实现：2、通常前端显示需要的数据格式如下：var zNodes =[{ id:1, pId:0, name:"湖南省", open:true},{ id:11, pId:1, name:......

蚩尤后裔

4374人浏览 · 2019-07-11 15:01:37

蚩尤后裔 · 2019-07-11 15:01:37 发布

递归查询·需求分析

递归查询·准备数据

递归查询实现·start with...connect by prior

递归查询·需求分析

1、开发中经常会有这种需求实现，如下所示是 JQuery-Ztree 树插件的效果：

2、如上所示的前端 tree 需要的数据格式如下（关系也很简单，就是自己的 id 以及显示的 name，与自己父节点 id）：

var zNodes =[
    { id:1, pId:0, name:"湖南省", open:true},
    { id:11, pId:1, name:"长沙市"},
    { id:111, pId:11, name:"芙蓉区"},
    { id:112, pId:11, name:"天心区"},
    { id:113, pId:11, name:"岳麓区"},
    { id:114, pId:11, name:"开福区"},
    { id:115, pId:11, name:"雨花区"},
    { id:116, pId:11, name:"望城区"},
    { id:12, pId:1, name:"娄底市"},
    { id:121, pId:12, name:"娄星区"},   
    { id:122, pId:12, name:"冷水江市"},
    { id:123, pId:12, name:"涟源市"},
    { id:124, pId:12, name:"双峰县"},
    { id:125, pId:12, name:"新化县"},
    { id:1251, pId:125, name:"白溪镇"},
    { id:1252, pId:125, name:"洋溪镇"},
    { id:1253, pId:125, name:"吉庆镇"},
    { id:1254, pId:125, name:"曹家镇"},
    { id:2, pId:0, name:"广东省", open:true},
    { id:21, pId:2, name:"深圳市"},
    { id:211, pId:21, name:"罗湖区"}, 
    { id:212, pId:21, name:"福田区"},
    { id:213, pId:21, name:"南山区"},
    { id:214, pId:21, name:"宝安区"},
    { id:215, pId:21, name:"坪山区"},
    { id:216, pId:21, name:"龙岗区"}
];

3、本文介绍的重点不是前端如何实现树形菜单的显示，而是后台如何查询数据库这种数据。

递归查询·准备数据

1、注意根据上面前端树结构的需要，后台返回的数据必须有 id，pId，以及 name，至于 menu_level（层级）字段可有可无，可以根据实际业务决定。

2、前端使用的树不同，数据格式可能也会大同小异，如下所示字段名称并没有和上面 tree 插件要求的属性名称完全一样，这只需要查询的 as 一下别名即可。

准备数据脚本：Oracle 新建地区级联数据.sql

SQL 运行完成后，Oracle 数据库数据如下：

递归查询实现·start with...connect by prior

1、connect by 是结构化查询中用到的，其基本语法是：

select … from tablename where 条件1 start with 条件2 connect by 条件3 order by 列 ;

条件1 是过滤条件，用于对返回的所有记录进行过滤筛选
条件2 是根结点/起始节点的限定语句
条件3 是连接条件，用于递归迭代的关联。使用 prior 表示上一条记录，比如 "CONNECT BY PRIOR id = parentId"，表示上/前一条记录的 id 是后一条记录的 parentId

2、下面循序渐进式的进行编写 SQL：

--查找树中的所有顶级父节点(1级菜单)
select * from scott_menu sm where sm.parentId = 0;
select * from scott_menu sm where sm.menu_level= 1;

--查找某个节点（如id为1）的直属子节点（所有儿子，不包括孙子）
select * from scott_menu sm where sm.parentId=1;

--查找某个节点下的所有子节点（包括所有子孙后代）。如下所示查询 id=1 的菜单下的所有子孙节点
select * from scott_menu sm start with sm.id=1 connect by  prior sm.id = sm.parentId;--前一条记录的 id 是后一条记录的 parentId

--查找所有1级菜单下的全部子孙节点，即查询整颗树，这也是实际中最常见的操作，有了上面的基础，现在则轻而易举了。
--将起始节点设置为所有的一级菜单即可。这查询出来的数据完全符合前端页面的格式，只需要后台再封装成 json 返回给页面即可
select * from scott_menu sm start with sm.parentId=0 connect by  prior sm.id = sm.parentId;
select * from scott_menu sm start with sm.menu_level=1 connect by  prior sm.id = sm.parentId;

--可以对递归查询的结果进行过滤与排序，如下所示，查询整颗树中的区节点，并以id倒叙
select * from scott_menu sm where sm.title like '%区%' start with sm.parentId = 0 connect by  prior sm.id = sm.parentId order by id desc;

--需求：查询 "白溪镇" 以及所在的上级 市、省份
select * from scott_menu sm start with sm.title = '白溪镇' connect by sm.id = prior sm.parentId;
--起始第一条数据为 '白溪镇'，如何会递归查询下一条的 parentId 等于自己 id 的记录，以此类推

--查询某个节点（如 id= 16）的兄弟节点（亲兄弟，有同一个父节点）
select * from scott_menu sm where exists (select * from scott_menu sm2 where sm.parentId=sm2.parentId and sm2.id=16);

从上面查询整颗树的结果可知，只需要在后台封装好前端所需要的 json 格式的数据返回，前端即可显示。

case when 条件表达式

1、条件表达式格式（Oracle 与 Mysql 通用的写法）(注意：返回值的数据类型必须一致，即不能返回值1是 number，而返回值2确实 char)：

语法 1:

   CASE 字段
   WHEN 条件1 THEN 返回值1
   WHEN 条件2 THEN 返回值2
   ...
   ELSE 返回的默认值                 --没有提供默认值，且上面条件都没有匹配时，返回 null

END

语法 2:

   CASE
   WHEN 条件 1 THEN 返回值1
   WHEN 条件2 THEN 返回值2
   ...
   ELSE 返回的默认值
   END

--方式一。最简单的用法
select t.stuid,t.stuname,case t.gender when '男' then 1 when '女' then 0 else -1 end from student t;
--两者结果完全一样
--方式二，使用条件查询，此时 case 后面不要再加 字段
select t.stuid,t.stuname,case when t.gender = '男' then 1 when t.gender = '女' then 0 else -1 end from student t;

--如果性别为 "男" 则返回 1，否则返回本来的值。注意返回的数据类型必须一致
select t.stuid,t.stuname,case when t.gender = '男' then '1' else t.gender end from student t;

-- 薪资(sal) >= 5000 的加薪 10%，小于 5000的加薪 20%
update emp t set t.sal = case when  t.sal >= 5000 then t.sal * 1.1 else t.sal * 1.2 end;

SELECT T.*
,(case when (t.empno in(7499,7902) or t.empno in(7788,7839)) then 1 else 2 end) is_selectd
 FROM emp t;

2、Oracle 独有的函数写法：decode(字段,if1,then1,if2,then2,....,defaultValue)

select * from emp;--查询所有
--将姓名 "SMITH" 改为 "张无忌"，"ALLEN" 改为 "郭靖"，"WARD" 改为 "李白"，其余的默认为无名
--case when then 写法是 Oracle 、Mysql 通用的写法
select case ename 
       when 'SMITH' then '张无忌'
       when 'ALLEN' then '郭靖'
       when 'WARD' then '李白'
       else '无名' end
       from emp;
--将姓名 "SMITH" 改为 "张无忌"，"ALLEN" 改为 "郭靖"，"WARD" 改为 "李白"，其余的默认为无名
select decode(ename,'SMITH','张无忌','ALLEN','郭靖','WARD','李白','无名') from emp;

-- 标记有无奖金,两种方式结果完全一样
SELECT T.*,
       CASE WHEN COMM IS NULL THEN '无' ELSE '有' END AS IS_HAVE_COMM1,
       DECODE(COMM, NULL, '无','有') AS IS_HAVE_COMM2
FROM EMP T;

3、示例：结合子查询

create or replace view vw_bas_flow_version_rule as
select FID, REMARK,
    -- 月份版本配置是否被使用
    case when exists (SELECT 1 FROM bas_audit_bill t2 where t2.fid=t.fid and rownum <= 1) then '是' else '否' end as isUsed
from bas_flow_version_rule t ;

示例1）统计当前环境的表结构与部标规范的差异。

示例2）数据迁移时设置默认值(迁移平台部门信息到单位基本信息表.sql )。

4、where 条件中使用 case when。


--查询薪水(sal)大于 2k的员工，如果职务(mgr)为空，则还要求它补助(comm)必须为空或者为0
SELECT T.*, rowid
  FROM emp T
 where t.sal > 2000
   and case
         when t.mgr is null then
          case
            when comm is null or comm = 0 then
             1
            else
             2
          end
         else
          1
       end = 1;

rowid 伪列删除表中重复数据

1、oracle 数据库的伪列 rowid 表示该条数据在 oracle 数据库中的物理存储位置，值为长度18的字符串(如 AAATRXAAGAAAK1XAAA)。oracle 内部通常就是使用它来访问数据的，它可以唯一标识一行数据。伪列只能查询，不能增删改。

2、rowid 伪列默认不显示，像 rownum 一样需要显示指定，如 select t.*,rowid from student t ;

3、和 rownum 行号不同的是，rowid 不但可以作为 select 的 where 条件，还可以作为 update、delete 等操作的 where 条件，如：delete from student t where t.rowid = 'AAATRXAAGAAAK1XAAA';

4、触发器中可以使用 :new.rowid、:old.rowid 的方式获取数据的 rowid。

5、所以生产中有一个常见的操作就是用 rowid 来删除表中完全重复的数据，下面先准备测试数据：

--创建学生表
create table STUDENT (
  stuid      VARCHAR2(16) not null,
  stuname    VARCHAR2(16) not null,
  gender     VARCHAR2(2) not null,
  age        NUMBER(8) not null,
  stuaddress VARCHAR2(50),
  enrolldate DATE
);

--插入数据
insert into student values('1','张三丰','男',108,'武当派开山祖师',to_date('2019-08-25 09:25:33','yyyy-mm-dd hh24:mi:ss'));
insert into student values('1','张三丰','男',108,'武当派开山祖师',to_date('2019-08-25 09:25:33','yyyy-mm-dd hh24:mi:ss'));
insert into student values('2','郭襄','女',56,'峨嵋派开山祖师',to_date('2015-06-25 15:00:33','yyyy-mm-dd hh24:mi:ss'));
insert into student values('2','郭襄','女',56,'峨嵋派开山祖师',to_date('2015-06-25 15:00:33','yyyy-mm-dd hh24:mi:ss'));
insert into student values('3','杨不悔','女',27,'明教右使千金',to_date('2020-09-21 11:45:20','yyyy-mm-dd hh24:mi:ss'));

删除表中重复数据行方式一（推荐方式）：rowid

--根据多个字段进行分组，然后删除重复数据，min(rowid) 表示保留最先入库的数据, max(rowid) 表示保留最后入库的数据
delete from student
 where (stuid, stuname) in (select stuid, stuname
                              from student
                             group by stuid, stuname
                            having count(*) > 1)
   and rowid not in (select min(rowid)
                       from student
                      group by stuid, stuname
                     having count(*) > 1);

-- 对指定单位下的人员根据身份证进行去重，对于重复数据，只保留最后入库的最新数据
DELETE FROM bas_person_info
 WHERE mof_div_code = '430000000'
   AND agency_code = '003001'
   AND is_deleted = 2
   AND iden_no IN (SELECT iden_no
                     FROM bas_person_info
                    WHERE mof_div_code = '430000000'
                      AND agency_code = '003001'
                      AND is_deleted = 2
                    GROUP BY iden_no
                   HAVING COUNT(*) > 1)
   AND ROWID NOT IN (SELECT MAX(ROWID)
                       FROM bas_person_info
                      WHERE mof_div_code = '430000000'
                        AND agency_code = '003001'
                        AND is_deleted = 2
                      GROUP BY iden_no
                     HAVING COUNT(*) > 1);

删除表中重复数据行方式二：先取后删再插

--先使用 distnct 关键字进行去重查询，去除结果集中重复的数据行。如果需要对整个表进行去重，则省略 where 条件即可！
select distinct * from student t where t.stuid < 3 order by stuid;
--新建临时表(student_temp)，并将去重结果存入进去
--使用 order by 关键字的目的是让后面从临时表再重新插回目标表的时候，数据仍然保持和原来一样的顺序.
create table student_temp as select distinct * from student t where t.stuid < 3 order by stuid;
delete from student t where t.stuid < 3;--然后删除目标表（student）中的所有重复数据
insert into student select * from student_temp;--最后将临时表(student_temp)的数据再插入到目标表(student)中.
drop table student_temp;--删除临时表 student_temp

删除指定单位指定人员类型下重复的身份证人员,保留最后入库的人员数据。

level 级次（伪列）

1、level 关键字用于描述数据之间的等级(级次)关系，如：人员职务、单位信息、部门信息、地区、菜单等具有树型结构的上下级关系。

2、level 关键字必须和 connect by 语法配合使用，级次从 1开始。(员工表与部门表测试数据)，它也属于一个伪列，类似 rowid、rownum。

--生成 1-100
select level from dual connect by level <= 100;
--生成100个随机UUID值
SELECT sys_guid() FROM DUAL CONNECT BY LEVEL <= 100;

--生成后100天的日期(前100天也是同理  )
SELECT trunc(SYSDATE + LEVEL) FROM DUAL CONNECT BY LEVEL <= 100;

-- 生成 201301  ——> 201312 连续的 12 个月份
select '2013' || lpad(level, 2, 0) ymd from dual connect by level <= 12;

-- 生成  2023-01-01 ——>  2023-01-31 连续的 31 天
SELECT to_char(TO_DATE('2023-01-01', 'yyyy-mm-dd') + LEVEL - 1, 'yyyy-mm-dd') FROM dual
CONNECT BY LEVEL <= TO_DATE('2023-01-31', 'yyyy-mm-dd') - TO_DATE('2023-01-01', 'yyyy-mm-dd') + 1;


--逐个截取字符串中每个字符
SELECT SUBSTR('嗨 Level', LEVEL, 1) FROM DUAL CONNECT BY LEVEL <= LENGTH('嗨 Level');

--查询工号为7839的员工，及其下属员工，使用 level 标记它们的级次。
select level, t.* from emp t start with empno = 7839 connect by prior empno = mgr;
--只查询第二级的员工信息，菜单，机构树也是同理
select level lv, t.* from emp t where LEVEL=2 start with empno = 7839 connect by prior empno = mgr;

synonym 同义词

1、同义词 (Synonym) 是数据库对象的一个别名，Orale 可以为表、视图、序列、过程函数、程序包等指定一个别名。

2、同义词缩短对象名字的长度，可以使用更加简短的同义词直接访问源对象。

3、方便访问其它用户的对象，当定义为公用[pulic]同义词时，同一个数据库中其他用户也都能直接访问。

4、比如为 emp 表设置了同义词 syno_emp，则可以直接使用 syno_emp 对 emp 表进行增删改查。

创建同义词

create [or replace] [public] synonym 同义词名称 for 源对象名称;

-- public表示共用同义词，可以被所有用户使用，否则就是专用同义词，只能被自己使用。

删除同义词

drop [public] synonym 同义词名称;

查询同义词

SELECT T.* FROM user_synonyms T;

SELECT T.* FROM all_synonyms T;

SELECT T.* FROM vw_emp T; 
--创建同义词
create synonym v_e for vw_emp;
SELECT T.* FROM v_e T; 

--创建同义词
create synonym syno_emp for emp;
--使用同义词
UPDATE syno_emp T SET t.comm = 2000 WHERE t.empno = 1;
DELETE from syno_emp t where t.empno=1;