Question

oracle 动态SQL是否可以用子查询呢?请帮忙解答!

下面是我使用的一个语句!
sql_stat:='select * from (select rownum as rn,t.* from emp t ) where rn > :no1 and rn <= :no2 ';
execute immediate sql_stat bulk collect into emp_table using l_begin,l_end ;

Answer 1

可以,例
--创建TTT表
CREATE TABLE TTT AS SELECT LEVEL ID FROM DUAL CONNECT BY LEVEL<100;
--创建SUB_TTT表
CREATE TABLE SUB_TTT AS SELECT 1 ID,'A' DOC FROM DUAL UNION SELECT 2 ID,'B' DOC FROM DUAL UNION SELECT 3 ID,'C' DOC FROM DUAL;

--在SUB_TTT表中计算ID小于指定值的个数，并且这些ID要在TTT表中出现
DECLARE
CNT NUMBER := 0;
SQL_STR VARCHAR2(100) := 'SELECT COUNT(1) CC FROM SUB_TTT A WHERE EXISTS(SELECT 1 FROM TTT B WHERE A.ID=B.ID AND B.ID<:ID)';
BEGIN
EXECUTE IMMEDIATE SQL_STR
INTO CNT
USING 5;
DBMS_OUTPUT.PUT_LINE(CNT);
END;