Oracle 树结构复杂场景模拟---------》如果登陆人是公司领导那么就无所谓过滤责任部门,如果登陆人非公司级领导,那么就当前登陆人的部门为责任部门查询自己部门的数据

      1. 表设计,首先确定的是树结构表,假设是某一个公司确定是一个项目主项信息,包含字段有,责任部门,责任员工,编辑状态 ,计划类型,编制状态,完成情况

CREATE TABLE BAI_PROJECT_PLAN(

    BAI_PROJECT_PLAN_ID VARCHAR2(32) PRIMARY KEY ,

    BAI_PROJECT_PLAN_P_ID VARCHAR2(32) ,

    BAI_ROOT_PROJECT_PLAN_ID VARCHAR2(32) ,

    BAI_PROJECT_PLAN_NAME VARCHAR2(225) ,

    HOST_DEPT_NAME VARCHAR2(225) ,

    HOST_DEPT_CODE VARCHAR2(32) ,

    PROJECT_PLAN_STATUS VARCHAR(2),

    BAI_PLAN_TYPE VARCHAR2(2)

);

 

insert into BAI_PROJECT_PLAN

  (BAI_PROJECT_PLAN_ID,

   BAI_PROJECT_PLAN_P_ID,

   BAI_ROOT_PROJECT_PLAN_ID,

   BAI_PROJECT_PLAN_NAME,

   HOST_DEPT_NAME,

   HOST_DEPT_CODE,

   PROJECT_PLAN_STATUS,

   BAI_PLAN_TYPE)

values

  ('A36DABCA0616496BBD016F787613E22D',

   'root',

   'A36DABCA0616496BBD016F787613E22D',

   '投资计划测试1207',

   '信息技术部',

   '4147',

   '7',

   '1');

 

insert into BAI_PROJECT_PLAN

  (BAI_PROJECT_PLAN_ID,

   BAI_PROJECT_PLAN_P_ID,

   BAI_ROOT_PROJECT_PLAN_ID,

   BAI_PROJECT_PLAN_NAME,

   HOST_DEPT_NAME,

   HOST_DEPT_CODE,

   PROJECT_PLAN_STATUS,

   BAI_PLAN_TYPE)

values

  ('20171207193248967000100000500000',

   '20171207193248967000100000300000',

   'A36DABCA0616496BBD016F787613E22D',

   '工艺主项1',

   '信息技术部',

   '4147',

   '7',

   '1');

 

insert into BAI_PROJECT_PLAN

  (BAI_PROJECT_PLAN_ID,

   BAI_PROJECT_PLAN_P_ID,

   BAI_ROOT_PROJECT_PLAN_ID,

   BAI_PROJECT_PLAN_NAME,

   HOST_DEPT_NAME,

   HOST_DEPT_CODE,

   PROJECT_PLAN_STATUS,

   BAI_PLAN_TYPE)

values

  ('20171207193248967000100000600000',

   '20171207193248967000100000200000',

   'A36DABCA0616496BBD016F787613E22D',

   '辅楼',

   '企业发展部',

   '4021',

   '7',

   '1');

 

insert into BAI_PROJECT_PLAN

  (BAI_PROJECT_PLAN_ID,

   BAI_PROJECT_PLAN_P_ID,

   BAI_ROOT_PROJECT_PLAN_ID,

   BAI_PROJECT_PLAN_NAME,

   HOST_DEPT_NAME,

   HOST_DEPT_CODE,

   PROJECT_PLAN_STATUS,

   BAI_PLAN_TYPE)

values

  ('366C12DFC2A241D7AECBA60553A6624E',

   'A36DABCA0616496BBD016F787613E22D',

   'A36DABCA0616496BBD016F787613E22D',

   '投资计划基本信息',

   '信息技术部',

   '4147',

   '7',

   '1');

 

insert into BAI_PROJECT_PLAN

  (BAI_PROJECT_PLAN_ID,

   BAI_PROJECT_PLAN_P_ID,

   BAI_ROOT_PROJECT_PLAN_ID,

   BAI_PROJECT_PLAN_NAME,

   HOST_DEPT_NAME,

   HOST_DEPT_CODE,

   PROJECT_PLAN_STATUS,

   BAI_PLAN_TYPE)

values

  ('20171207193248967000100000300000',

   'A36DABCA0616496BBD016F787613E22D',

   'A36DABCA0616496BBD016F787613E22D',

   'RX-2017-0736立项测试1201-007',

   '信息技术部',

   '4147',

   '7',

   '1');

 

insert into BAI_PROJECT_PLAN

  (BAI_PROJECT_PLAN_ID,

   BAI_PROJECT_PLAN_P_ID,

   BAI_ROOT_PROJECT_PLAN_ID,

   BAI_PROJECT_PLAN_NAME,

   HOST_DEPT_NAME,

   HOST_DEPT_CODE,

   PROJECT_PLAN_STATUS,

   BAI_PLAN_TYPE)

values

  ('20171207193248967000100000400000',

   '20171207193248967000100000500000',

   'A36DABCA0616496BBD016F787613E22D',

   '中央空调',

   '经营管理部',

   '4025',

   '7',

   '1');

 

insert into BAI_PROJECT_PLAN

  (BAI_PROJECT_PLAN_ID,

   BAI_PROJECT_PLAN_P_ID,

   BAI_ROOT_PROJECT_PLAN_ID,

   BAI_PROJECT_PLAN_NAME,

   HOST_DEPT_NAME,

   HOST_DEPT_CODE,

   PROJECT_PLAN_STATUS,

   BAI_PLAN_TYPE)

values

  ('20171207193248967000100000100000',

   '20171207193248967000100000200000',

   'A36DABCA0616496BBD016F787613E22D',

   '主楼',

   '信息技术部',

   '4147',

   '7',

   '1');

 

insert into BAI_PROJECT_PLAN

  (BAI_PROJECT_PLAN_ID,

   BAI_PROJECT_PLAN_P_ID,

   BAI_ROOT_PROJECT_PLAN_ID,

   BAI_PROJECT_PLAN_NAME,

   HOST_DEPT_NAME,

   HOST_DEPT_CODE,

   PROJECT_PLAN_STATUS,

   BAI_PLAN_TYPE)

values

  ('20171207193248967000100000200000',

   '20171207193248967000100000300000',

   'A36DABCA0616496BBD016F787613E22D',

   '建筑主项',

   '经营管理部',

   '4025',

   '7',

   '1');

      1. 查询条件,如果公司领导是公司级领导就不过滤责任部门

SELECT BAI_PROJECT_PLAN_ID,

       BAI_PROJECT_PLAN_P_ID,

       BAI_ROOT_PROJECT_PLAN_ID,

       BAI_PROJECT_PLAN_NAME,

       HOST_DEPT_NAME,

       HOST_DEPT_CODE,

       PROJECT_PLAN_STATUS,

       BAI_PLAN_TYPE

  FROM BAI_PROJECT_PLAN

 WHERE BAI_PROJECT_PLAN_ID IN

       (SELECT BAI_PROJECT_PLAN_ID

          FROM (SELECT T.BAI_PROJECT_PLAN_ID, T.BAI_PROJECT_PLAN_P_ID

                  FROM BAI_PROJECT_PLAN T

                 WHERE T.BAI_ROOT_PROJECT_PLAN_ID =

                       'A36DABCA0616496BBD016F787613E22D') A

         START WITH A.BAI_PROJECT_PLAN_ID IN (SELECT P.BAI_PROJECT_PLAN_ID

                                                FROM BAI_PROJECT_PLAN P

                                               WHERE P.BAI_ROOT_PROJECT_PLAN_ID =

                                                     'A36DABCA0616496BBD016F787613E22D'

                                              --AND P.HOST_DEPT_CODE = '4147'

                                              )

        CONNECT BY PRIOR A.BAI_PROJECT_PLAN_P_ID = A.BAI_PROJECT_PLAN_ID)

      1. 查询条件,如果公司领导是非公司级领导就过滤责任部门

SELECT BAI_PROJECT_PLAN_ID,

       BAI_PROJECT_PLAN_P_ID,

       BAI_ROOT_PROJECT_PLAN_ID,

       BAI_PROJECT_PLAN_NAME,

       HOST_DEPT_NAME,

       HOST_DEPT_CODE,

       PROJECT_PLAN_STATUS,

       BAI_PLAN_TYPE

  FROM BAI_PROJECT_PLAN

 WHERE BAI_PROJECT_PLAN_ID IN

       (SELECT BAI_PROJECT_PLAN_ID

          FROM (SELECT T.BAI_PROJECT_PLAN_ID, T.BAI_PROJECT_PLAN_P_ID

                  FROM BAI_PROJECT_PLAN T

                 WHERE T.BAI_ROOT_PROJECT_PLAN_ID =

                       'A36DABCA0616496BBD016F787613E22D') A

         START WITH A.BAI_PROJECT_PLAN_ID IN

                    (SELECT P.BAI_PROJECT_PLAN_ID

                       FROM BAI_PROJECT_PLAN P

                      WHERE P.BAI_ROOT_PROJECT_PLAN_ID =

                            'A36DABCA0616496BBD016F787613E22D'

                        AND P.HOST_DEPT_CODE = '4147')

        CONNECT BY PRIOR A.BAI_PROJECT_PLAN_P_ID = A.BAI_PROJECT_PLAN_ID)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值