首页 新闻 搜索 专区 学院

oracle Execute Immediate(sql语句)

0
悬赏园豆:15 [已解决问题] 解决于 2018-04-11 14:24
Execute Immediate(sql语句),执行后返回多条数据,怎么遍历其数据

yjcd的主页 yjcd | 初学一级 | 园豆:119
提问于:2018-04-10 11:42
< >
分享
最佳答案
0

Example 1:

Output:
A
B
C

DECLARE 
TYPE var_typ IS TABLE OF VARCHAR2(4000);
  cVars var_typ;
  cVar VARCHAR2(4000);
BEGIN
  EXECUTE IMMEDIATE ' 
SELECT ''A'' cc FROM dual
UNION
SELECT ''B'' cc FROM dual
UNION
SELECT ''C'' cc FROM dual' BULK COLLECT INTO cVars;
  FOR i IN 1 .. cVars.COUNT
  LOOP
    cVar := cVars(i);
    dbms_output.put_line (cVar);
  END LOOP;
END;
/

 

Example 2:

Output:
A C
B D
C E

DECLARE
TYPE rec IS
  RECORD
  (
    col1 VARCHAR2(30),
    col2 VARCHAR2(30) );
TYPE rec_table IS TABLE OF rec;
  results rec_table;
  tRow rec;
BEGIN
  EXECUTE IMMEDIATE ' 
SELECT ''A'',''C'' FROM dual
UNION
SELECT ''B'',''D'' FROM dual
UNION
SELECT ''C'',''E'' FROM dual' BULK COLLECT INTO results;
  FOR i IN 1 .. results.COUNT
  LOOP
    tRow := results(i);
    dbms_output.put_line(tRow.col1 || ' ' || tRow.col2);
  END LOOP;
END;
/
收获园豆:15
alvinloong | 菜鸟二级 |园豆:247 | 2018-04-10 14:04
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册