函数里面的SQL语句，怎样使用传入的参数作为条件表达式？ 有例子不抽象，请进

我想把'A1', 'C1', 'D1'作为参数传给PLSQL函数，然后放到IN语句里面，但是好像没有数据，我试过两种方法：
方法0， 直接传递 

 '(''' || 'A1' || ''', ''' || 'D1' || ''')' 

给函数；

select f1( '(''' || 'A1' || ''', ''' || 'D1' || ''')') from dual 

結果就是没结果

方法1，传递'A1, C1, D1'，我再在函數中分隔字符串，拼成IN的条件，还是不行；

我还拼接成了SELECT UNION的形式，还是不行。

注意我在函數里面拼接好的条件表达式，经过测试，样子就是一个正确的调教表达式

语句的思路是没有问题的，最后面我给出了能够找到结果的语句，函数里面用的也是这个语句，只不过我想把SELECT子句的条件表达式用参数传递进行，请问如何解决，谢谢。

以下两句话可以正常执行，已测试：

  SELECT DOT_NET_NAME
      FROM SYS_FILE_ACCESS_LEVEL
     WHERE LEVEL_ID = (SELECT MAX(LEVEL_ID)
             FROM SYS_FILE_ROLE_LEVEL
            WHERE FILE_ID = 2
              AND ROLE_CODE IN ('A1', 'C1', 'D1')
      );

  SELECT DOT_NET_NAME
      FROM SYS_FILE_ACCESS_LEVEL
     WHERE LEVEL_ID = (SELECT MAX(LEVEL_ID)
             FROM SYS_FILE_ROLE_LEVEL
            WHERE FILE_ID = 2
              AND ROLE_CODE IN (SELECT 'A1' FROM DUAL UNION ALL 
SELECT 'D1' FROM DUAL UNION ALL 
SELECT 'C1' FROM DUAL)
      );

8 个解决方案

#1

把完整的函数代码贴下。

#2

你安照ms sql 的写个类似的oracle 动态的看看，行不行

--> 测试数据: #TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
GO 
create table #TB (N1 int,N2 varchar(4),N3 int,N4 varchar(4))
insert into #TB
select 12,'茶碗',4,'李四' union all
select 15,'水缸',4,'李四' union all
select 8,'锅',4,'李四' union all
select 53,'锅2',5,'王1'


DECLARE @S VARCHAR(50)
SET @S='''水缸'',''锅''' 

set @s='select * from #TB where n2 in ('+@s+')' 
--print @s 

exec(@s)

N1          N2   N3          N4
----------- ---- ----------- ----
15          水缸   4           李四
8           锅    4           李四

(2 行受影响)

#3

引用 2 楼 js_szy 的回复:

你安照ms sql 的写个类似的oracle 动态的看看，行不行
SQL code
--> 测试数据: #TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
GO 
create table #TB (N1 int,N2 varchar(4),N3 int,N4 varchar(4))
insert into #TB
se……

我的Oracle代码如下：
测试结果，放在PLSQL里面好像还是不行

CREATE TABLE TB (N1 number ,N2 VARCHAR2(4),N3 number,N4 VARCHAR2(4));

INSERT INTO TB
SELECT 12,'茶碗',4,'李四' FROM dual UNION ALL
SELECT 15,'水缸',4,'李四' FROM dual UNION ALL
SELECT 8,'锅',4,'李四'   FROM dual  UNION ALL
SELECT 53,'锅2',5,'王1' FROM dual;


SET serveroutput ON;

DECLARE
  v_in  VARCHAR2(20);
  v_out varchar2(10);
BEGIN 
  v_in :='''水缸'',''锅''';
  SELECT MAX(n4) INTO v_out FROM tb WHERE n2 IN (v_in);
  dbms_output.put_line('out is : ' || v_out);
end;

SELECT MAX(n4) FROM tb WHERE n2 IN ('水缸','锅');

#4

drop table tb;
CREATE TABLE TB (N1 number ,N2 VARCHAR2(4),N3 number,N4 VARCHAR2(4));

INSERT INTO TB
SELECT 12,'茶碗',4,'李四' FROM dual UNION ALL
SELECT 15,'水缸',4,'李四' FROM dual UNION ALL
SELECT 8,'锅',4,'李四'   FROM dual  UNION ALL
SELECT 53,'锅2',5,'王1' FROM dual;


SET serveroutput ON;

DECLARE
  v_in  VARCHAR2(20);
  v_out varchar2(10);
  v_sql varchar2(500);
BEGIN 
  v_in :='''水缸'',''锅''';
  --SELECT MAX(n1) INTO v_out FROM tb WHERE n2 IN (v_in);
  v_sql :='SELECT MAX(n4) from tb where n2 in('||v_in||')';    --把上面这句换成动态的
  execute immediate v_sql into v_out;
  dbms_output.put_line('out is : ' || v_out);
end;

 


out is : 李四 
PL/SQL 过程已成功完成。

#5

引用 4 楼 js_szy 的回复:

SQL code
drop table tb;
CREATE TABLE TB (N1 number ,N2 VARCHAR2(4),N3 number,N4 VARCHAR2(4));

INSERT INTO TB
SELECT 12,'茶碗',4,'李四' FROM dual UNION ALL
SELECT 15,'水缸',4,'李四' FROM dual UNION ALL
……

正解！！！！

#6

引用 4 楼 js_szy 的回复:

SQL code
drop table tb;
CREATE TABLE TB (N1 number ,N2 VARCHAR2(4),N3 number,N4 VARCHAR2(4));

INSERT INTO TB
SELECT 12,'茶碗',4,'李四' FROM dual UNION ALL
SELECT 15,'水缸',4,'李四' FROM dual UNION ALL
SELE……

你好，谢谢，我用你的方法测试通过了，而且我注意到v_sql里面不能带分号。

不过关于动态SQL，我还有个问题想请教你，ORACLE的文档提到了execute v_sql into *** using ** 的語法，见下面的例子，但是我自己没有测试成功，请问你是怎样解决的，再次谢谢。

CREATE TABLE TB (N1 number ,N2 VARCHAR2(4),N3 number,N4 VARCHAR2(4));

INSERT INTO TB
SELECT 12,'茶碗',4,'李四' FROM dual UNION ALL
SELECT 15,'水缸',4,'李四' FROM dual UNION ALL
SELECT 8,'锅',4,'李四'   FROM dual  UNION ALL
SELECT 53,'锅2',5,'王1' FROM dual;

SET serveroutput ON;

DECLARE
  v_in  VARCHAR2(20);
  v_out varchar2(10);
  v_sql varchar2(500);
BEGIN 
  v_in :='''水缸'',''锅''';
  --SELECT MAX(n1) INTO v_out FROM tb WHERE n2 IN (v_in);
  v_sql :='SELECT MAX(n4) from tb where n2 in(:v_para)';    --把上面这句换成动态的
  dbms_output.put_line(v_sql);
  execute immediate v_sql into v_out using v_in;
  dbms_output.put_line('out is : ' || v_out);
end;

#7

v_sql :='SELECT MAX(n4) from tb where n2 in(:v_para)'; 

你这个:v_para 全放引号里，就不是变量，是字符了哦

#8

引用 7 楼 js_szy 的回复:

SQL code
v_sql :='SELECT MAX(n4) from tb where n2 in(:v_para)'; 


你这个:v_para 全放引号里，就不是变量，是字符了哦

不过ORACLE的文档里是这样用的啊，http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#CHDGJEGD

见例子7-3

附上一个ORACLE文档里面的例子

DECLARE
   plsql_block VARCHAR2(500);
   new_deptid  NUMBER(4);
   new_dname   VARCHAR2(30) := 'Advertising';
   new_mgrid   NUMBER(6) := 200;
   new_locid   NUMBER(4) := 1700;
BEGIN
   plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';
   EXECUTE IMMEDIATE plsql_block
      USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/