建表语句如下（部门表，产品表）：
create table dept(
DEPT_ID NUMBER(2) NOT NULL, --部门ID
PARENT_ID NUMBER(2) , --上级部门ID
DEPT_NAME VARCHAR2(10) --部门名称
);
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (1,null,'山东1' );
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (2,1 ,'山东1-2' );
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (3,1 ,'山东1-3' );
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (4,2 ,'山东1-2-4');
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (5,2 ,'山东1-2-5');
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (6,3 ,'山东1-3-6');
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (7,3 ,'山东1-3-7');
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (8,3 ,'山东1-3-8');
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (9,null,'山西1' );
insert into dept (DEPT_ID,PARENT_ID,DEPT_NAME) values (10,9 ,'山西1-2' );
create table product(
PRODUCT_ID NUMBER(2) NOT NULL, --部门ID
DEPT_ID NUMBER(2) NOT NULL, --部门ID
NAME VARCHAR2(50) --产品名称
);
insert into product (PRODUCT_ID,DEPT_ID,NAME) values (1,6,'产品1');
insert into product (PRODUCT_ID,DEPT_ID,NAME) values (2,7,'产品2');
insert into product (PRODUCT_ID,DEPT_ID,NAME) values (3,8,'产品3');
insert into product (PRODUCT_ID,DEPT_ID,NAME) values (4,2,'产品4');
insert into product (PRODUCT_ID,DEPT_ID,NAME) values (5,10,'产品5');
commit;
想要达到的查询效果：
在查询到某个一级部门下所有的产品的情况下同时把产品所属部门全部查询出来，效果如下：
DEPT_NAME DEPT_NAME DEPT_NAME PRODUCT_ID NAME
'1' '1-3' '1-3-6' 1 '产品1'
'1' '1-3' '1-3-7' 2 '产品2'
'1' '1-3' '1-3-8' 3 '产品3'
'1' '1-2' 4 '产品4'