CODEWITHSUNDEEP
sqloracle-databaseplsqlwhere-clause
Abhijeet Raj
Abhijeet Raj

Reputation: 3

append condition to where clause(pl/sql) where the condition is given as input varchar2

I have a table Employee and I need to know the count of employees based on some condition which will be in form of varchar2

please ignore any syntax error

procedure getEmpCount( pCondition in varchar2)
begin
    --pCondition can be anything say "employee_salaray >30000"

    select count(*) from Employee where employee_age > 35 and **pCondition**

end

how can I make the above query work. Thanks in advance

Upvotes: 0

Views: 646

Answers (1)

Littlefoot
Littlefoot

Reputation: 142788

One option is dynamic SQL which is a bad idea as it doesn't scale at all and is prone to SQL injection. If I were you, I wouldn't do it. Anyway, here's how:

Procedure:

SQL> create or replace procedure getEmpCount( pCondition in varchar2)
  2  is
  3    l_cnt number;
  4  begin
  5    --pCondition can be anything say "employee_salaray >30000"
  6
  7    execute immediate 'select count(*) from emp where deptno = 10 and ' ||
  8                      pCondition into l_cnt;
  9
 10    dbms_output.put_line('count = ' || l_cnt);
 11  end;
 12  /

Procedure created.

Sample data:

SQL> select deptno, ename, sal from emp where deptno = 10;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
        10 KING            10000
        10 MILLER           1300

Testing:

SQL> set serveroutput on
SQL>
SQL> exec getempcount('sal > 2000');
count = 2

PL/SQL procedure successfully completed.

SQL>

Upvotes: 5

Related Questions