Perform validation from 2 tables in PL/SQL

Question

I'm not sure what step should I perform here is it procedure, function, or what? So my goal is to check weather if those data exists in another table. For example in HR schema, I have to validate if the department_name =AD VP from DEPARTMENTS table exists in department_name from employees table.

So how can I perform that validation in plsql?

score 0 · Answer 1 · answered Sep 18 '22 at 18:56

That's a function.

I don't have HR schema so Scott's will do; this function checks whether department whose deptno column value is equal to function parameter's value exists - if so, it returns 1; otherwise, it returns 0.

I chose to return number. Another option is to return Boolean which is also a good choice - however, as Boolean can't be used in pure SQL (but only in PL/SQL), I'm returning a number in this example.

This is a function:

SQL> create or replace function f_dept_exists_01 (par_deptno in number)
  2    return number
  3  is
  4    l_cnt number;
  5  begin
  6    select count(*)
  7      into l_cnt
  8      from dept
  9      where deptno = par_deptno;
 10
 11    return case when l_cnt > 0 then 1
 12                else 0
 13           end;
 14  end;
 15  /

Function created.

Contents of the dept table:

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Department 10 exists, so function returns 1:

SQL> select f_dept_Exists_01(10) result from dual;

    RESULT
----------
         1

Department 99 doesn't exist, so function returns 0:

SQL> select f_dept_Exists_01(99) result from dual;

    RESULT
----------
         0

SQL>

Now, adjust it to your data model.

Perform validation from 2 tables in PL/SQL

1 Answers1