a****l 发帖数: 120 | 1 今天在做一个project,需要做个schedule把query run出来的结果export到excel里面
去。目前有个问题,出现,不知道怎么解决。特来请教。
因为数据量大,用了bulk collect into。
目前的问题出现在于
在UTL_FILE.PUT_LINE的时候,不知道如何把column name 传入变量中。比如
-- 定义type
type column_table_type is table of user_tab_columns.column_name%type;
column_table column_table_type;
type emp_table_type is table of SCOTT.EMP%ROWTYPE;
emp_table emp_table_type;
-- 获得column name
SELECT column_name BULK COLLECT INTO column_table from all_tab_columns where
TABLE_NAME='EMP';
UTL_FILE.PUT_LINE(v_fh,'');
FOR j in column_table.first..column_table.last LOOP
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,''||column_table(j) ||'
');
UTL_FILE.PUT_LINE(v_fh,'');
END LOOP;
UTL_FILE.PUT_LINE(v_fh,'');
-- 获得所有数据
SELECT * BULK emp_table INTO FROM SCOTT.EMP;
FOR i in emp_table.first..emp_table.last loop
/*
这里是遍历输出所有的数据
如果使用
emp_table(i).ename;
...
emp_table(i).deptno;
完全是可以输出所有的数据的
但是问题在于
如果column有几十个,几百个,code会很难看
*/
/*
因此,我想用
emp_table(i).column_table(j)
去解决
总是得到错误
说column_table没有declare
仔细想想也是应该的
因为他直接读取的是table里面的定义
*/
/*
也想到用
execute immediate 'BEGIN dbms_output.put_line(''||emp_table(i).column_table(
j)||''); END;';
也报错,说SMITH没有declare
因为SMITH是emp_table(i).column_table(j)
得出来的结果
*/
END LOOP;
所以,求教下。
该如何在plsql集合中,传递参数?
比如我想得到的结果就是
遍历
emp_table(i).column_table(j)
出现
emp_table(i).ename;
emp_table(i).deptno;
emp_table(i).sal
...
顺便说下
普通的cursor,太慢了。。我用了普通的cursor一条条的读取,一条条的分析
用
DBMS_SQL.FETCH_ROWS
DBMS_SQL.DESCRIBE_COLUMNS
DBMS_SQL.COLUMN_VALUE
速度太慢了,比用bulk慢了一倍多的时间;
用ssis,我也可以,但是那台服务器没有sqlserver...
另外我用sqldeveloper,也可以完成,但是要手动...
现在就这个我实在弄不明白,一个新人,希望给点指引.
谢谢 | B*****g 发帖数: 34098 | 2 太长,先不要说有什么错误,说说你想做什么
【在 a****l 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 今天在做一个project,需要做个schedule把query run出来的结果export到excel里面 : 去。目前有个问题,出现,不知道怎么解决。特来请教。 : 因为数据量大,用了bulk collect into。 : 目前的问题出现在于 : 在UTL_FILE.PUT_LINE的时候,不知道如何把column name 传入变量中。比如 : -- 定义type : type column_table_type is table of user_tab_columns.column_name%type; : column_table column_table_type; : type emp_table_type is table of SCOTT.EMP%ROWTYPE; : emp_table emp_table_type;
| a****l 发帖数: 120 | 3 我想用plsql实现在collection中,如java的
for (i=0;i++,i<30)
{
for (j=0;j++;j<30)
(
m = i + j;
)
}
两个collection, emp_table, column_table
我想输出
for i in column_table.first..column_table.last LOOP
for j in emp_table.first..column_table.last loop
--就是下面这个总是出错
UTL_FILE.PUT_LINE(emp_table(j).column_table(i));
end loop;
end loop; | B*****g 发帖数: 34098 | 4 emp_table(j).column_table(i)里面的那个点是什么意思?
大概看了一下,你就是想把emp table的data搞出来?不是太喜欢在oracle里搞这个,
不过这有个例子
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_questio
我肯定会用java之类的方法做,如果非要用sql,我比较喜欢用
SELECT DBMS_XMLGEN.getXML('SELECT * FROM emp') FROM DUAL
【在 a****l 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 我想用plsql实现在collection中,如java的 : for (i=0;i++,i<30) : { : for (j=0;j++;j<30) : ( : m = i + j; : ) : } : 两个collection, emp_table, column_table : 我想输出
| a****l 发帖数: 120 | 5 谢谢beijing的回复.你给的网页就是我原来用的方法.可是我的数据太多了.所以我想用
bulk collect into放到集合里面,然后再从集合一次性处理.
还有那个点,因为一个plsql 集合里面,他会根据column name 把数据取出来,比如我有
个plsql集合叫做emp_table,那么我可以用emp_table(i).ename,吧名字都从集合拿出来.
但是我现在的问题是,如果column_name很多,我可以一个一个的写,但是有点傻;所以我
想用第二个遍历,把column_name取出来,然后在用取出来的值,带入到emp_table(i)的后
面,就变成了
emp_table(i).column_name(i)
我现在的问题是,如何在集合里面替换掉后面必须存在的column_table,比如用两个遍历
谢谢 | B*****g 发帖数: 34098 | 6 你是想做emp_table(i)||'.'||column_name(i)吧
来.
【在 a****l 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 谢谢beijing的回复.你给的网页就是我原来用的方法.可是我的数据太多了.所以我想用 : bulk collect into放到集合里面,然后再从集合一次性处理. : 还有那个点,因为一个plsql 集合里面,他会根据column name 把数据取出来,比如我有 : 个plsql集合叫做emp_table,那么我可以用emp_table(i).ename,吧名字都从集合拿出来. : 但是我现在的问题是,如果column_name很多,我可以一个一个的写,但是有点傻;所以我 : 想用第二个遍历,把column_name取出来,然后在用取出来的值,带入到emp_table(i)的后 : 面,就变成了 : emp_table(i).column_name(i) : 我现在的问题是,如何在集合里面替换掉后面必须存在的column_table,比如用两个遍历 : 谢谢
| B*****g 发帖数: 34098 | 7 要是一定用plsql,我会先生成一个column的合并string,
比如说
cstring := 'id || '',''||name || '',''|| age || .....';
dstring := 'SELECT ' || ctring ||' FROM emp;'
execute immediate dtring bulk collect into dClobTable;
来.
【在 a****l 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 谢谢beijing的回复.你给的网页就是我原来用的方法.可是我的数据太多了.所以我想用 : bulk collect into放到集合里面,然后再从集合一次性处理. : 还有那个点,因为一个plsql 集合里面,他会根据column name 把数据取出来,比如我有 : 个plsql集合叫做emp_table,那么我可以用emp_table(i).ename,吧名字都从集合拿出来. : 但是我现在的问题是,如果column_name很多,我可以一个一个的写,但是有点傻;所以我 : 想用第二个遍历,把column_name取出来,然后在用取出来的值,带入到emp_table(i)的后 : 面,就变成了 : emp_table(i).column_name(i) : 我现在的问题是,如何在集合里面替换掉后面必须存在的column_table,比如用两个遍历 : 谢谢
| a****l 发帖数: 120 | 8 谢谢,这个我也测试了,出错啊。
FOR i in emp_table.first..emp_table.last loop
FOR m in 1..column_table.count LOOP
dbms_output.put_line(emp_table(i) || '.' || column_table(m));
END LOOP;
END LOOP;
--------------------------------------错误如下:
ORA-06550: line 47, column 42:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 47, column 21:
PL/SQL: Statement ignored
【在 B*****g 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 你是想做emp_table(i)||'.'||column_name(i)吧 : : 来.
| a****l 发帖数: 120 | 9 我在select里面用*就可以了,因为是要选出所有的。
但是,我需要在集合输出的时候,输出所有的值。
比如
collection a, 里面有 id ,name,number。。。
一般来说,我可以用
for i in a.first..a.last loop
dbms_output.put_line(a(i).id);
dbms_output.put_line(a(i).name);
dbms_output.put_line(a(i).number);
....
end loop
但是如果,table有几十个column,那么有没有办法可以循环的把column的名字放到集
合,即a(i)的后面,而不需要用手去写各个具体的名字?
例如,一个for 循环
for i in a.first..a.last loop
/*
这里是个循环,循环输出column的名字?
*/
end loop
【在 B*****g 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 要是一定用plsql,我会先生成一个column的合并string, : 比如说 : cstring := 'id || '',''||name || '',''|| age || .....'; : dstring := 'SELECT ' || ctring ||' FROM emp;' : execute immediate dtring bulk collect into dClobTable; : : 来.
| d****n 发帖数: 12461 | 10 sql只有行是可以遍历的,列以及表都是专属的,无法静态遍历,只能用动态sql解释生
成。
【在 a****l 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 我在select里面用*就可以了,因为是要选出所有的。 : 但是,我需要在集合输出的时候,输出所有的值。 : 比如 : collection a, 里面有 id ,name,number。。。 : 一般来说,我可以用 : for i in a.first..a.last loop : dbms_output.put_line(a(i).id); : dbms_output.put_line(a(i).name); : dbms_output.put_line(a(i).number); : ....
| a****l 发帖数: 120 | 11 可以稍微解释的详细点吗?或者给个例子吗
你是说,生成一个string,然后用execute immediate执行?
还是有其他的方法呢?
谢谢
【在 d****n 的大作中提到】![](/moin_static193/solenoid/img/up.png) : sql只有行是可以遍历的,列以及表都是专属的,无法静态遍历,只能用动态sql解释生 : 成。
| B*****g 发帖数: 34098 | 12 你想的的那个基本没戏,所以才要在select的时候把逻辑搞好。
【在 a****l 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 我在select里面用*就可以了,因为是要选出所有的。 : 但是,我需要在集合输出的时候,输出所有的值。 : 比如 : collection a, 里面有 id ,name,number。。。 : 一般来说,我可以用 : for i in a.first..a.last loop : dbms_output.put_line(a(i).id); : dbms_output.put_line(a(i).name); : dbms_output.put_line(a(i).number); : ....
| d****n 发帖数: 12461 | 13 其实你这个要求就是要在列上做一个遍历指针。问题是遍历指针只能对同一数据类型有
效,这个放在所有语言里都是差不多的。
所以在sql里面对于列的改动都是ddl,对于行的改动只要dml就可以了。
要做到“遍历列”的功能,只能用key-value方法。
【在 a****l 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 可以稍微解释的详细点吗?或者给个例子吗 : 你是说,生成一个string,然后用execute immediate执行? : 还是有其他的方法呢? : 谢谢
| c*****l 发帖数: 312 | 14 如果xml query对你来说太慢的话, 你要考虑excel file也是有limit 的。你确定你
的方法可行? |
|