現在是一個查詢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))
uj5u.com熱心網友回復：
那你的問題呢？uj5u.com熱心網友回復：
沒看懂啊，你這個sql是啥意思？這也不會回傳條件呀uj5u.com熱心網友回復：
create or replace procedure find_emp_id(p_emp_id in varchar2) asv_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;
uj5u.com熱心網友回復：
把這個sql改成存盤程序,傳入emp_id ,根據id判斷where后面接的查詢條件.已經解決了.
uj5u.com熱心網友回復：
恩,灰常感謝,雖然不是我想要的,但是感謝你們的幫助
uj5u.com熱心網友回復：
把這個sql改成存盤程序,已經解決了
uj5u.com熱心網友回復：
好的，解決了就好 ；
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/117734.html
標籤：開發
下一篇：關于oracle在包存盤問題