现在是一个查询SQL 写成一个存储过程 存储过程的内容是 传入一个emp_id 返回一个字符串
字符串的内容就是查询脚本的where后面的条件,
逻辑就是 我要通过一个存储过程 获取符合当前传入的人的判断条件 然后把条件拼成一个where条件,多个条件用or隔开 取全集
select *
from t_pms_bu_project_info t
where (instr(t.project_responsible, '#emp_id#') > 0 or
(t.project_responsible_dept in
('11729', '9115', '12942', '568', '687', '8745') and
'#emp_id#' = '8669806') or exists
(select 1
from t_pms_bu_project_info a
where a.project_code = t.project_code
and ((a.PROJECT_RESPONSIBLE_TYPE = '1' and
('#emp_id#' in ('8601494', '8610963') or
(a.project_type_class = '1' and
'#emp_id#' in
('8662888', '8671434', '8671555', '8610692', '8610737')))) or
(a.PROJECT_RESPONSIBLE_TYPE = '2' and
'#emp_id#' in ('8671342', '8671976')))
or (a.PROJECT_RESPONSIBLE_TYPE = '3' and
'#emp_id#' in ('8671899', '8676026'))) or exists
(select 1
from t_Eap_Sys_Role_User m
where m.role_id in ('PMS_ROLE_ZZ005',
'PMS_ROLE_ZZ010',
'PMS_ROLE_ZZ019',
'PMS_ROLE_ZZ020')
and m.user_id = '#emp_id#') or exists
(select 1
from t_bs_employee bs
inner join v_bs_leader cl
on bs.last_office = cl.ORGANIZATION_CODE
or bs.last_department = Cl.ORGANIZATION_CODE
where instr(t.project_responsible, bs.employee_id) > 0
and cl.EMPLOYEE_CODE = '#emp_id#') or exists
(select 1
from t_pms_bu_project_info info
inner join t_pms_bu_budget_info bi
on info.wbs_code = bi.wbs_code
and bi.year_budget_type = '2'
and bi.arrival_fee > 0
inner join t_eim_bd_lookup lb
on 1 = 1
and lb.lookup_class = 'PMS_SYS_056'
and lb.lookup_code = '#emp_id#'
and info.project_type = '5'
where info.project_code = t.project_code) or exists
(select 1
from t_eap_sys_role_user u
inner join t_bs_employee ue
on u.user_id = ue.employee_id
and u.user_id = '#emp_id#'
left join t_eap_sys_organization o
on o.parent_org_id = ue.last_department
where ((t.project_responsible_dept = ue.last_office and
u.role_id = 'PMS_ROLE_ZZ008') or
(t.project_responsible_dept = o.org_id and
u.role_id = 'PMS_ROLE_ZZ003'))) or exists
(select 1
from t_eap_sys_organization o
inner join t_pms_bd_resp rp
on rp.resp_unit = o.org_id
or rp.resp_unit = o.parent_org_id
where rp.resp_user = '#emp_id#'
and rp.is_enable = '1'
and t.project_responsible_dept = o.org_id))
字符串的内容就是查询脚本的where后面的条件,
逻辑就是 我要通过一个存储过程 获取符合当前传入的人的判断条件 然后把条件拼成一个where条件,多个条件用or隔开 取全集
select *
from t_pms_bu_project_info t
where (instr(t.project_responsible, '#emp_id#') > 0 or
(t.project_responsible_dept in
('11729', '9115', '12942', '568', '687', '8745') and
'#emp_id#' = '8669806') or exists
(select 1
from t_pms_bu_project_info a
where a.project_code = t.project_code
and ((a.PROJECT_RESPONSIBLE_TYPE = '1' and
('#emp_id#' in ('8601494', '8610963') or
(a.project_type_class = '1' and
'#emp_id#' in
('8662888', '8671434', '8671555', '8610692', '8610737')))) or
(a.PROJECT_RESPONSIBLE_TYPE = '2' and
'#emp_id#' in ('8671342', '8671976')))
or (a.PROJECT_RESPONSIBLE_TYPE = '3' and
'#emp_id#' in ('8671899', '8676026'))) or exists
(select 1
from t_Eap_Sys_Role_User m
where m.role_id in ('PMS_ROLE_ZZ005',
'PMS_ROLE_ZZ010',
'PMS_ROLE_ZZ019',
'PMS_ROLE_ZZ020')
and m.user_id = '#emp_id#') or exists
(select 1
from t_bs_employee bs
inner join v_bs_leader cl
on bs.last_office = cl.ORGANIZATION_CODE
or bs.last_department = Cl.ORGANIZATION_CODE
where instr(t.project_responsible, bs.employee_id) > 0
and cl.EMPLOYEE_CODE = '#emp_id#') or exists
(select 1
from t_pms_bu_project_info info
inner join t_pms_bu_budget_info bi
on info.wbs_code = bi.wbs_code
and bi.year_budget_type = '2'
and bi.arrival_fee > 0
inner join t_eim_bd_lookup lb
on 1 = 1
and lb.lookup_class = 'PMS_SYS_056'
and lb.lookup_code = '#emp_id#'
and info.project_type = '5'
where info.project_code = t.project_code) or exists
(select 1
from t_eap_sys_role_user u
inner join t_bs_employee ue
on u.user_id = ue.employee_id
and u.user_id = '#emp_id#'
left join t_eap_sys_organization o
on o.parent_org_id = ue.last_department
where ((t.project_responsible_dept = ue.last_office and
u.role_id = 'PMS_ROLE_ZZ008') or
(t.project_responsible_dept = o.org_id and
u.role_id = 'PMS_ROLE_ZZ003'))) or exists
(select 1
from t_eap_sys_organization o
inner join t_pms_bd_resp rp
on rp.resp_unit = o.org_id
or rp.resp_unit = o.parent_org_id
where rp.resp_user = '#emp_id#'
and rp.is_enable = '1'
and t.project_responsible_dept = o.org_id))
v_sql varchar2(4000);
recode_t_pms_bu_project_info t_pms_bu_project_info%rowtype;
begin
v_sql:='select * into recode_t_pms_bu_project_info from t_pms_bu_project_info t
where (instr(t.project_responsible,'||p_emp_id||') > 0 or ' ........
--把后面的语句同样改造一下拼接即可,然后
execute immediate v_sql;
end;