如下如表T
- SQL code
表结构如下：A B ---------------------- ---------- 1 AAA 2 BBB 3 CCC 创建和插入语句如下：CREATE TABLE T(A NUMBER,B VARCHAR2(10))INSERT INTO T VALUES(1,'AAA')INSERT INTO T VALUES(2,'BBB')INSERT INTO T VALUES(3,'CCC')
动态执行语句为：
- SQL code
BEGIN EXECUTE IMMEDIATE 'SELECT * FROM T';END;
问题：
请问如何能够将动态执行语句的结果显示出来？
可不可以提供一些EXECUTE IMMEDIATE的学习资料？
谢谢大家了！！！
------解决方案--------------------
多行应该要使用动态游标了吧..
- SQL code
DECLARE type c_curref is ref cursor;r_curref c_curref;rec_t t%ROWTYPE;BEGIN OPEN r_curref FOR 'SELECT * FROM T'; LOOP FETCH r_curref INTO rec_t; EXIT WHEN r_curref%NOTFOUND; Dbms_Output.put_line(rec_t.a||' '||rec_t.b); END LOOP; CLOSE r_curref;END;6 PL/SQL block, executed in 0 sec. 1 AAA 2 BBB 3 CCC Total execution time 0 sec.
------解决方案--------------------
EXECUTE IMMEDIATE...
- SQL code
DECLARE v_a t.a%TYPE;v_b t.b%TYPE;BEGIN EXECUTE IMMEDIATE 'SELECT * FROM T WHERE ROWNUM=1' INTO v_a,v_b; Dbms_Output.put_line(v_a||' '||v_b);END;
------解决方案--------------------
EXECUTE IMMEDIATE 'SELECT * FROM T';
还可以这样用
v_val NUMBER(5);
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM T ' INTO v_val;
如果SQL文里有2个字段，查询结果是一条记录，那么也可以INTO AAAA, BBBB