请教动态的查询(查询条件不确定)

[jaysonchou]

例如:

在procedure中,期望

select * from table where abc = efg;

abc 和 efg 都是procedure传进来的参数,就是说column和value都不确定,
这个怎么去实现？

期待达人能给具体讲讲.
感激不尽

/bow

[yingtju]

sql :='select * from table where '||abc||' = '||efg;
execute immediate sql;

[chunyu.wu]

CREATE OR REPLACE PROCEDURE WUCYTEST(
a VARCHAR2 DEFAULT NULL
,b VARCHAR2 DEFAULT NULL
)
IS
stmt_sql VARCHAR2(2048) DEFAULT NULL;
TYPE count_type IS REF CURSOR;
count_cur count_type;
db_num VARCHAR2(20);
BEGIN
stmt_sql := ' SELECT ENAME ';
stmt_sql := stmt_sql || ' FROM SCOTT.EMP ';
stmt_sql := stmt_sql || ' WHERE '||a||' = '||b||' ';

OPEN count_cur FOR stmt_sql;
LOOP
FETCH count_cur
INTO db_num;
EXIT WHEN count_cur%NOTFOUND;
END LOOP;
CLOSE count_cur;
DBMS_OUTPUT.put_line(db_num);

END WUCYTEST;

当输入参数a=EMPNO,b=7788时，
结果为：SCOTT

[jaysonchou]

谢谢，楼上，想问如果用dbms_sql实现，并且察看是否返回的有结果应当怎么去作？