Update records with a select statement-CodePudding

I have a cursor with v_emp_id & v_in_term variables. I need to assign v_in_term either yes or no values for each record based on whether or not the person is in a particular query. I'm not able to join the employees table into my cursor query. The general logic to it is like this:

if v_emp_id in (select emp_id from employees)  
then v_in_term := 'Yes';                        
else v_in_term := 'No'; 
end if;

It says I can't use a query here. How could I change this?

[Error] Compilation (906: 42): PLS-00405: subquery not allowed in this context

CodePudding user response：

You cannot use IN with a subquery (or EXISTS) in a PL/SQL IF statement. Instead, you can use SELECT ... INTO and either your query:

DECLARE
  v_emp_id  EMPLOYEES.EMP_ID%TYPE := 1;
  v_in_term VARCHAR2(3);
BEGIN
  SELECT CASE
         WHEN v_emp_id in (select emp_id from employees)
         THEN 'Yes'
         ELSE 'No'
         END
  INTO   v_in_term
  FROM   DUAL;
  
  DBMS_OUTPUT.PUT_LINE(v_in_term);
END;
/

or EXISTS:

DECLARE
  v_emp_id  EMPLOYEES.EMP_ID%TYPE := 1;
  v_in_term VARCHAR2(3);
BEGIN
  SELECT CASE
         WHEN EXISTS(SELECT 1 FROM employees WHERE emp_id = v_emp_id)
         THEN 'Yes'
         ELSE 'No'
         END
  INTO   v_in_term
  FROM   DUAL;
  
  DBMS_OUTPUT.PUT_LINE(v_in_term);
END;
/

db<>fiddle here

CodePudding user response：

A bit more compact way is to use count(*)

declare
  v_emp_id  EMP.EMP_ID%TYPE := 2;
  v_in_term VARCHAR2(3);
begin
  select decode(count(*),0,'no','yes') into v_in_term from emp
  where emp_id = v_emp_id;
  dbms_output.put_line(v_in_term); 
end;
/

count(*) returns zero if no record is found in the table, which is mapped to no in the decode. All other results are mapped to yes, so it works even in cases you do not check with primary key..