目录
递归查询实现·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、下面循序渐进式的进行编写 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 字段 END 语法 2: CASE |
--方式一。最简单的用法
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）统计当前环境的表结构与部标规范的差异。
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;快速批量生成百万测试数据
create table t_test(
id nvarchar2(200),
name nvarchar2(200),
age integer,
password nvarchar2(200)
);
-- 插入600w的模拟数据
INSERT INTO t_test (id, name, age, password)
SELECT
'id'||FLOOR(DBMS_RANDOM.value(0, 10000)), -- 生成随机的ID
'User'||FLOOR(DBMS_RANDOM.value(0, 10000)), -- 生成随机的用户名
FLOOR(DBMS_RANDOM.value(18, 118)), -- 生成随机的年龄 (18-117)
'user'||FLOOR(DBMS_RANDOM.value(0, 10000)) ||'@example.com'-- 生成随机的邮箱地址
FROM dual CONNECT BY LEVEL <=6000000;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;
dblink 跨库操作
1、dblink（全称database link）是Oracle数据库中用于连接不同数据库实例的一种机制，用户可以在一个数据库实例中直接查询或操作另一个数据库实例中的表、视图或存储过程、函数等等。
2、dblink 主要有以下几个方面的作用：
-
跨数据库操作：允许用户在一个数据库实例中直接访问并操作另一个数据库实例中的数据。
-
简化数据管理：通过dblink可以方便地管理和维护分布在多个数据库中的数据，而无需在每个数据库中重复相同的数据操作。
-
提高效率：通过dblink可以减少数据传输的延迟，提高数据处理的效率。
3、比如从一个库将数据迁移到另一个库，不用导出、导入，直接使用 dblink 速度很快。
4、Oracle 中的存储过程、函数、触发器等等，都不能直接跨库操作，必须借助 dblink 链接才行。
完整操作查看：doc/csdn/md/Oracle数据库dblink使用介绍.md · 汪少棠/material - Gitee.com。
扫一扫
2323
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
