sql - 使用多个员工不工作的过滤过程
问题描述
我正在尝试按员工过滤。如果我通过单个员工，它可以完美地工作，但是当我通过多个 emp 时它不起作用。我将 CTE 用于多个 emp 作为逗号分隔的字符串。我在下面附上了示例代码 - 任何帮助将不胜感激。我尝试使用upper（从dept_emp 中选择ALL_EMP）并且我得到单行子查询返回不止一行-如何让它为多个员工工作？
//full code here
Create or replace procedure Emp_Test(
v_empl IN VARCHAR2
)
AS
OPEN emp_recordset for
with dept_emp as
(select REGEXP_SUBSTR(v_empl, '[^,]+',+1,LEVEL) as ALL_EMP
FROM DUAL
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(v_empl, '[^,]+'))+1)
select r.name, r.city, e.manager, t.cross_dept, t.dtpt_num
from Employee r
join Dept t on r.emp_id = t.emp_id
where
(v_empl is null or
(case when r.emp_status = 'A' and t.cross_dept = 'DEV'
then emp.get_salary(r.v_empl)
else r.v_empl end) = upper(v_empl)
order by r.emp_id;
end Emp_Test;//procedure
v_empl IN VARCHAR2
--------
--------
with dept_emp as
(select REGEXP_SUBSTR(v_empl, '[^,]+',+1,LEVEL) as ALL_EMP
FROM DUAL
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(v_empl, '[^,]+'))+1)
-------- employee r //tbl
--------dept t
//this is working only for single emp, need to work for multiple emp which are in dept_emp CTE
//how can I make use of dept_emp so it can filter for multiple emp
where
(v_empl is null or
(case when r.emp_status = 'A' and t.cross_dept = 'DEV'
then emp.get_salary(r.v_empl)
else r.v_empl end) = upper(v_empl)
also tried this upper(select ALL_EMP from dept_emp) but I am getting single-row subquery returns more than one row error message解决方案
我认为，如果我正确理解了这个问题，您希望将过程的输入参数评估为单个值或它们的集合。您无法将 select 语句检索到的表中的单个值与值集合进行比较。那永远行不通。
在这种情况下，您可以尝试这样的事情
测试用例
SQL> create table emp ( emp_id number , name varchar2(100) , manager varchar2(100) , emp_status varchar2(1) ) ;
Table created.
SQL> insert into emp values ( 1 , 'John' , 'Bill' , 'A' ) ;
SQL> insert into emp values ( 2 , 'Mike' , 'Bill' , 'A' ) ;
SQL> insert into emp values ( 3 , 'Sara' , 'Bill' , 'A' ) ;
SQL> insert into emp values ( 4 , 'Dany' , 'Bill' , 'A' ) ;
SQL> insert into emp values ( 5 , 'Mila' , 'Anne' , 'B' ) ;
SQL> insert into emp values ( 6 , 'Jean' , 'Anne' , 'B' ) ;
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
SQL> commit ;
Commit complete.
SQL> create table dept ( dtpt_num number , cross_dept varchar2(3) ) ;
Table created.
SQL> insert into dept values ( 100 , 'DEV' ) ;
1 row created.
SQL> insert into dept values ( 200 , 'HRM' ) ;
1 row created.
SQL> col name for a20
SQL> col manager for a20
SQL> select * from emp ;
EMP_ID NAME MANAGER E
---------- -------------------- -------------------- -
1 John Bill A
2 Mike Bill A
3 Sara Bill A
4 Dany Bill A
5 Mila Anne B
6 Jean Anne B
6 rows selected.
SQL> select * from dept ;
DTPT_NUM CRO
---------- ---
100 DEV
200 HRM
现在，让我们构建一个演示函数，根据 id 获取随机薪水
SQL> create or replace function get_salary ( pempid in number )
return number deterministic
is
out_sal number;
begin
select round(dbms_random.value(1000,3000),0) into out_sal from dual ;
return out_sal ;
end;
/
Function created.
让我们在 emp 表中添加一个新列，以便按部门连接两个表
SQL> alter table emp add dept_id number ;
Table altered.
SQL> update emp set dept_id = ( case when EMP_STATUS = 'A' then 100 else 200 end ) ;
6 rows updated.
SQL> commit ;
Commit complete.
SQL> select * from emp
2 ;
EMP_ID NAME MANAGER E DEPT_ID
---------- -------------------- -------------------- - ----------
1 John Bill A 100
2 Mike Bill A 100
3 Sara Bill A 100
4 Dany Bill A 100
5 Mila Anne B 200
6 Jean Anne B 200
6 rows selected.
我们现在可以连接两个表并使用演示函数来获得随机工资
SQL> select e.emp_id , e.name, e.manager, d.cross_dept, d.dtpt_num , get_salary(e.emp_id) as salary
from emp e
join Dept d on e.DEPT_ID = d.dtpt_num ;
EMP_ID NAME MANAGER CRO DTPT_NUM SALARY
---------- -------------------- -------------------- --- ---------- ----------
1 John Bill DEV 100 2129
2 Mike Bill DEV 100 1215
3 Sara Bill DEV 100 2930
4 Dany Bill DEV 100 1347
5 Mila Anne HRM 200 1664
6 Jean Anne HRM 200 1770
6 rows selected.
现在，让我们构建一个过程，该过程将考虑输入参数可能是单个员工或用逗号分隔的列表并将结果存储在临时表中（仅出于演示目的，您也可以使用sys_refcursor对象)
SQL> create table tmp_results as
select e.emp_id , e.name, e.manager, d.cross_dept, d.dtpt_num , get_salary(e.emp_id) as salary
from emp e
join Dept d on e.DEPT_ID = d.dtpt_num
where 1 = 2 ;
Table created.
create or replace procedure emp_test ( pemployee in varchar2 )
is
v_counter_records pls_integer;
v_num_of_values pls_integer;
curr_val varchar2(10);
begin
v_counter_records := regexp_count ( pemployee , ',' , 1 , 'i' );
if v_counter_records = 0
then
insert into tmp_results
select e.emp_id , e.name, e.manager, d.cross_dept, d.dtpt_num , get_salary(e.emp_id) as salary
from emp e
join Dept d on e.DEPT_ID = d.dtpt_num
where e.emp_id = to_number(pemployee) ;
else
v_num_of_values := v_counter_records + 1;
for rec in 1 .. v_num_of_values
loop
curr_val := regexp_substr( pemployee, '[^,]+', 1 , rec );
insert into tmp_results
select e.emp_id , e.name, e.manager, d.cross_dept, d.dtpt_num , get_salary(e.emp_id) as salary
from emp e
join Dept d on e.DEPT_ID = d.dtpt_num
where e.emp_id = to_number(curr_val);
end loop;
end if;
end;
/
Procedure created.
SQL> exec emp_test( '1,2,3' );
PL/SQL procedure successfully completed.
SQL> select * from tmp_results ;
EMP_ID NAME MANAGER CRO DTPT_NUM SALARY
---------- -------------------- -------------------- --- ---------- ----------
1 John Bill DEV 100 2386
2 Mike Bill DEV 100 1531
3 Sara Bill DEV 100 2202
注意事项
- 我的示例只是向您展示如何拆分在过程输入中用作单个参数的参数列表。
- 当有多个值时，您还可以更改查询以使用
IN而不是评估条件。= - 我没有为该函数包含任何特殊逻辑，因为出于示例的目的，不需要它。
- 您也可以使用您的
WITH声明来获取稍后要比较的员工列表，但在您的问题中并不清楚您何时使用该 CTE 结构。
推荐阅读
- xaml - 有没有办法在详细信息更改时自动更改主详细信息项目中选定的菜单页面？
- node.js - 节点js猫鼬在嵌套混合属性中找到？
- php - WordPress 使所有帖子类型和分类法都适用于分页索引
- python - 恢复被 Python/Spyder 覆盖的脚本
- php - 使用jquery ajax成功登录后如何集成记住我？
- node.js - nodejs高cpu使用率系统调用
- c++ - 对自定义元素向量进行排序时出现意外（至少对我而言）行为
- python - 在 Flask Admin 的自定义视图中禁用缓存
- java - NoClassDefFoundError: javax/mail/Session
- javascript - 声音（例如哔声）使用 createOsculator 创建不适用于 android（8.0 版）