J'essaye de filtrer par employé. Si je dépasse un seul employé, cela fonctionne parfaitement, mais lorsque je passe plusieurs emp, cela ne fonctionne pas. J'utilise CTE pour plusieurs emp comme une chaîne séparée par des virgules. J'ai joint un exemple de code ci-dessous - toute aide sera très appréciée. J'ai essayé d'utiliser upper (sélectionnez ALL_EMP à partir de dept_emp) et j'ai obtenu une sous-requête à une seule ligne renvoyant plus d'une ligne - comment le faire fonctionner pour plus d'un employé?

//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
1
user13079741 24 août 2020 à 15:57

2 réponses

Meilleure réponse

Je pense que si j'ai bien compris la question, vous voulez évaluer le paramètre d'entrée de la procédure soit comme une valeur unique, soit comme une collection d'entre eux. Vous ne pouvez pas comparer une valeur unique dans une table récupérée par une instruction SELECT avec une collection de valeurs. Cela ne fonctionnera jamais.

Dans ce cas, vous pouvez essayer quelque chose comme ça

Cas de test

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

Maintenant, construisons une fonction de démonstration pour obtenir un salaire aléatoire basé sur l'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.

Ajoutons une nouvelle colonne à la table emp afin de joindre les deux tables par département

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.

Nous pouvons maintenant joindre les deux tables et utiliser la fonction démo pour obtenir un salaire aléatoire

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.

Maintenant, construisons une procédure qui prendra en considération le moment où le paramètre d'entrée peut être soit un seul employé, soit une liste d'entre eux divisée par une virgule et stockons les résultats dans une table temporaire (juste à des fins de démonstration, vous pouvez utiliser un {{X0 }} également)

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

Considérations

  • Mon exemple est juste pour vous montrer comment diviser une liste de paramètres utilisés comme un seul dans l'entrée de la procédure.
  • Vous pouvez également modifier la requête pour utiliser IN au lieu de = pour évaluer la condition lorsqu'il y a plus d'une valeur unique.
  • Je n'ai pas inclus de logique particulière pour la fonction, car aux fins de l'exemple, elle n'était pas nécessaire.
  • Vous pouvez également utiliser votre instruction WITH pour obtenir une liste d'employés à comparer ultérieurement, mais votre question n'était pas claire lorsque vous utilisiez cette construction CTE.
1
Roberto Hernandez 29 août 2020 à 12:28

Vous devez utiliser in (select upper(ALL_EMP) from dept_em). La fonction upper attend une valeur unique, vous ne pouvez donc pas l'appliquer pour une sélection retournant plusieurs lignes. Pour la même raison, vous devez utiliser in au lieu de = dans la clause where.

0
Pavel Gatnar 31 août 2020 à 06:46