p_str := '(''123'',''456'')'; execute immediate 'select * from c where c.a in '||p_str;
SQL> declare 2 v_sql varchar2(1000); 3 v_str varchar2(128) :='(10,20)'; 4 v_sum number(10); 5 begin 6 v_sql :='select sum(sal) from emp where deptno in '||v_str; 7 execute immediate v_sql 8 into v_sum; 9 dbms_output.put_line('10,20号部门的所有员工的总工资为:'||v_sum); 10 end; 11 /
10,20号部门的所有员工的总工资为:19725
PL/SQL procedure successfully completed
--动态sql,可以这么写: SQL> create or replace procedure select_ename(empno_in varchar2) 2 as 3 v_sql varchar2(1000); 4 v_ename varchar2(20); 5 begin 6 v_sql :='select ename from emp where empno=:a'; 7 execute immediate v_sql 8 into v_ename 9 using empno_in; 10 dbms_output.put_line('empno为:'||empno_in||' 的员工的名字为:'||v_ename); 11 end select_ename; 12 /
Procedure created
SQL> exec select_ename('7788');
empno为:7788 的员工的名字为:SCOTT
PL/SQL procedure successfully completed
SQL> set serveroutput on; SQL> SQL> select * from c;A -------------------- 123 789 456SQL> DECLARE 2 pstr VARCHAR2(128) := '123,456'; 3 BEGIN 4 FOR cc IN (SELECT a 5 FROM c 6 WHERE c.a IN 7 (SELECT regexp_substr(pstr, '[^,]+', 1, LEVEL) 8 FROM dual 9 CONNECT BY LEVEL <= 10 length(pstr) - length(REPLACE(pstr, ',')) + 1)) LOOP 11 dbms_output.put_line(cc.a); 12 END LOOP; 13 END; 14 / 123 456PL/SQL 过程已成功完成。
如果select * from c where c.a='123'都查不出数据,但是数据库确实存在123的数据,那估计是不是里面还存在一些空格,或者数据库里是全角的123.
p_str varchar2(128);
p_str := ('123','456');
select * from c where c.a in p_str;红色的部分查不出数据(数据库中确实有)
p_str varchar2(128);--你明明定义了一个字符类型
p_str := ('123','456');--但是你却赋值为数组类型,这里就是错误的,
p_str := '(''123'',''456'')';
execute immediate 'select * from c where c.a in '||p_str;
SQL> declare
2 v_sql varchar2(1000);
3 v_str varchar2(128) :='(10,20)';
4 v_sum number(10);
5 begin
6 v_sql :='select sum(sal) from emp where deptno in '||v_str;
7 execute immediate v_sql
8 into v_sum;
9 dbms_output.put_line('10,20号部门的所有员工的总工资为:'||v_sum);
10 end;
11 /
10,20号部门的所有员工的总工资为:19725
PL/SQL procedure successfully completed
--动态sql,可以这么写:
SQL> create or replace procedure select_ename(empno_in varchar2)
2 as
3 v_sql varchar2(1000);
4 v_ename varchar2(20);
5 begin
6 v_sql :='select ename from emp where empno=:a';
7 execute immediate v_sql
8 into v_ename
9 using empno_in;
10 dbms_output.put_line('empno为:'||empno_in||' 的员工的名字为:'||v_ename);
11 end select_ename;
12 /
Procedure created
SQL> exec select_ename('7788');
empno为:7788 的员工的名字为:SCOTT
PL/SQL procedure successfully completed
SQL>
SQL> select * from c;A
--------------------
123
789
456SQL> DECLARE
2 pstr VARCHAR2(128) := '123,456';
3 BEGIN
4 FOR cc IN (SELECT a
5 FROM c
6 WHERE c.a IN
7 (SELECT regexp_substr(pstr, '[^,]+', 1, LEVEL)
8 FROM dual
9 CONNECT BY LEVEL <=
10 length(pstr) - length(REPLACE(pstr, ',')) + 1)) LOOP
11 dbms_output.put_line(cc.a);
12 END LOOP;
13 END;
14 /
123
456PL/SQL 过程已成功完成。