英文:
append condition to where clause(pl/sql) where the condition is given as input varchar2
问题
我有一个名为Employee的表，我需要根据某个条件来获取员工的数量，条件将以varchar2的形式提供。
请忽略任何语法错误。
procedure getEmpCount( pCondition in varchar2)
begin
-- pCondition可以是任何条件，比如"employee_salary > 30000"
select count(*) from Employee where employee_age > 35 and **pCondition**
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
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
答案1
得分: 5
一种选择是动态SQL，这是一个不好的主意，因为它根本不可伸缩，并且容易受到SQL注入攻击。如果我是你，我不会这样做。无论如何，这是如何做的：
过程：
SQL> create or replace procedure getEmpCount( pCondition in varchar2)
2 is
3 l_cnt number;
4 begin
5 --pCondition可以是任何东西，比如"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 /
过程已创建。
示例数据：
SQL> select deptno, ename, sal from emp where deptno = 10;
DEPTNO ENAME SAL
---------- ---------- ----------
10 CLARK 2450
10 KING 10000
10 MILLER 1300
测试：
SQL> set serveroutput on
SQL> exec getempcount('sal > 2000');
count = 2
PL/SQL procedure successfully completed.
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:
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>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论