背景:
假设有如下文件夹表T_FOLDER,其DDL语句如下,根据文件夹主键ID获取此文件夹所在的目录串(格式为/id1/id2)?
create table t_folder
(
id number(15) primary key,
name varchar2(32),
parent_id number(15)
);
注:parent_id表示文件夹的父文件夹,parent_id为0表示根文件夹,根文件夹不在表T_FOLDER中存储
数据准备(500万):
insert into t_folder(id, name, parent_id) values(1, 'name_1', 0);
insert into t_folder(id, name, parent_id) values(2, 'name_2', 0);
insert into t_folder(id, name, parent_id) values(3, 'name_3', 0);
insert into t_folder(id, name, parent_id) values(4, 'name_4', 2);
insert into t_folder(id, name, parent_id) values(5, 'name_5', 2);
insert into t_folder(id, name, parent_id) values(6, 'name_6', 3);
insert into t_folder(id, name, parent_id) values(7, 'name_7', 4);
commit;
declare
begin
for i in 8 .. 5000000 loop
insert into t_folder (id, name, parent_id) values (i, 'name_' || i, 4);
if i mod 8 = 0 then
commit;
end if;
end loop;
end;
/
方法一:(208.422(s))
select sys_connect_by_path(id, '/')
from t_folder
where id=7
start with parent_id = 0
connect by prior id = parent_id;
方法二:(0.047(s))
create or replace function f_folder_dir(v_id number) return varchar2 is
v_parent_id number;
v_folder_dir varchar2(4000) := '/' || v_id;
v_tmp_id number := v_id;
begin
loop
select parent_id into v_parent_id
from t_folder
where id = v_tmp_id;
exit when v_parent_id = 0;
v_tmp_id := v_parent_id;
v_folder_dir := '/' || v_parent_id || v_folder_dir;
end loop;
return v_folder_dir;
end;
/
-- 采用f_folder_dir自定义函数, 产生目录串,耗时: 0.047(s)
select f_folder_dir(7) from dual;