将条件附加到 WHERE 子句（PL/SQL），其中条件作为输入的 VARCHAR2 给定。

huangapple
go评论132阅读模式
英文:

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

问题

我有一个名为Employee的表，我需要根据某个条件来获取员工的数量，条件将以varchar2的形式提供。

请忽略任何语法错误。

  1. procedure getEmpCount( pCondition in varchar2)
  2. begin
  3.     -- pCondition可以是任何条件，比如"employee_salary > 30000"
  4.     
  5.     select count(*) from Employee where employee_age > 35 and **pCondition**
  6. end

如何使上述查询工作。提前感谢。

英文:

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

  1. procedure getEmpCount( pCondition in varchar2)
  2. begin
  3.     --pCondition can be anything say "employee_salaray >30000"
  5.     select count(*) from Employee where employee_age > 35 and **pCondition**
  7. end

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

答案1

得分: 5

一种选择是动态SQL，这是一个不好的主意，因为它根本不可伸缩，并且容易受到SQL注入攻击。如果我是你，我不会这样做。无论如何，这是如何做的：

过程：

  1. SQL> create or replace procedure getEmpCount( pCondition in varchar2)
  2.   2  is
  3.   3    l_cnt number;
  4.   4  begin
  5.   5    --pCondition可以是任何东西，比如"employee_salaray > 30000"
  6.   6
  7.   7    execute immediate 'select count(*) from emp where deptno = 10 and ' ||
  8.   8                      pCondition into l_cnt;
  9.   9
  10.  10    dbms_output.put_line('count = ' || l_cnt);
  11.  11  end;
  12.  12  /

过程已创建。

示例数据：

  1. SQL> select deptno, ename, sal from emp where deptno = 10;
  3.     DEPTNO ENAME             SAL
  4. ---------- ---------- ----------
  5.         10 CLARK            2450
  6.         10 KING            10000
  7.         10 MILLER           1300

测试：

  1. SQL> set serveroutput on
  2. SQL> exec getempcount('sal > 2000');
  3. count = 2
  5. PL/SQL procedure successfully completed.
  7. SQL>
英文:

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:

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

Sample data:

  1. SQL> select deptno, ename, sal from emp where deptno = 10;
  3.     DEPTNO ENAME             SAL
  4. ---------- ---------- ----------
  5.         10 CLARK            2450
  6.         10 KING            10000
  7.         10 MILLER           1300

Testing:

  1. SQL> set serveroutput on
  2. SQL>
  3. SQL> exec getempcount('sal > 2000');
  4. count = 2
  6. PL/SQL procedure successfully completed.
  8. SQL>

huangapple
  • 本文由 发表于 2020年1月3日 19:25:29
  • 转载请务必保留本文链接：https://go.coder-hub.com/59577774.html
  • oracle
  • plsql
  • sql
  • where-clause
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定