Oracle 使用游标读取第 n 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3264643/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle read nth column using a cursor
提问by mehmet6parmak
can i read column data in a cursor giving the index of the column?
我可以在给出列索引的游标中读取列数据吗？
thanks...
谢谢...
From the questioner's comments : "I need to create a generic procedure which will read a table or view (name of the view or table is the argument of the proc) and encrypt the data in the column and then will write the encrypted data to a OS file. "
来自提问者的评论：“我需要创建一个通用过程来读取表或视图（视图或表的名称是 proc 的参数）并加密列中的数据，然后将加密的数据写入操作系统文件。”
回答by Gary Myers
This should give you a head start. Just plug in your encryption code for the column number you want. I've used VARCHAR everywhere. If you want dates and numbers (or more exotic datatypes), then you'll need to handle the conversion.
这应该给你一个良好的开端。只需为您想要的列号插入您的加密代码。我到处都使用过 VARCHAR。如果您想要日期和数字（或更特殊的数据类型），那么您需要处理转换。
create or replace function qry_dump
(p_tab_name in varchar2, p_rownum in number default 5)
return tab_char_4000 AUTHID CURRENT_USER pipelined is
v_line varchar2(2000);
v_col_cnt INTEGER;
v_ind NUMBER;
rec_tab dbms_sql.desc_tab;
v_tab dbms_sql.varchar2a;
v_temp VARCHAR2(32000);
v_cursor NUMBER;
v_clause VARCHAR2(200);
begin
--
-- Initial values
-- v_ind := 1;
v_temp := 'select * from '||p_tab_name||' where rownum <= '||nvl(p_rownum,5);
--
-- Identify the columns in the target and build the new query
--
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor, v_temp, dbms_sql.native);
dbms_sql.describe_columns(v_cursor, v_col_cnt, rec_tab);
--
FOR v_pos in 1..rec_tab.LAST LOOP
v_line := rec_tab(v_pos).col_name;
dbms_sql.define_column( v_cursor, v_pos, v_line, 2000);
END LOOP;
v_ind := dbms_sql.execute( v_cursor );
--
-- Fetch each row from the result set
--
LOOP
v_ind := DBMS_SQL.FETCH_ROWS( v_cursor );
EXIT WHEN v_ind = 0;
pipe row( '=============================================================');
--
-- Go through each column and display it
--
FOR v_col_seq IN 1 .. rec_tab.COUNT LOOP
-- Get the value
dbms_sql.column_value( v_cursor, v_col_seq, v_line );
pipe row( rpad(rec_tab(v_col_seq).col_name,35)||'>'||v_line);
END LOOP;
END LOOP;
return;
end qry_dump;
/
select * from table(qry_dump('DEPT',3));
回答by Gary Myers
Additional answer for CLOBs. Simpler code as I've hardcoded the table/column_name. The main difference is that v_line is now a CLOB and that the final parameter (length) is dropped from the call to DEFINE_COLUMN since it is only relevant to VARCHAR2.
CLOB 的附加答案。更简单的代码，因为我对 table/column_name 进行了硬编码。主要区别在于 v_line 现在是一个 CLOB 并且最后一个参数（长度）从对 DEFINE_COLUMN 的调用中删除，因为它只与 VARCHAR2 相关。
If you are dealing in very large CLOBs (eg 10s or 100s MB plus), then I can foresee other challenges (memory, performance...).
如果您正在处理非常大的 CLOB（例如 10s 或 100s MB 以上），那么我可以预见其他挑战（内存、性能......）。
create or replace function clob_dump
return tab_char_4000 AUTHID CURRENT_USER pipelined is
v_line clob;
v_col_cnt INTEGER;
v_ind NUMBER;
rec_tab dbms_sql.desc_tab;
v_cursor NUMBER;
begin
--
-- Identify the columns in the target and build the new query
--
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor, 'select sql_fulltext from gm_c where rownum <= 5', dbms_sql.native);
dbms_sql.describe_columns(v_cursor, v_col_cnt, rec_tab);
--
FOR v_pos in 1..rec_tab.LAST LOOP
v_line := rec_tab(v_pos).col_name;
dbms_sql.define_column( v_cursor, v_pos, v_line);
END LOOP;
v_ind := dbms_sql.execute( v_cursor );
--
-- Fetch each row from the result set
--
LOOP
v_ind := DBMS_SQL.FETCH_ROWS( v_cursor );
EXIT WHEN v_ind = 0;
pipe row( '=============================================================');
--
-- Go through each column and display it
--
FOR v_col_seq IN 1 .. rec_tab.COUNT LOOP
-- Get the value
dbms_sql.column_value( v_cursor, v_col_seq, v_line );
pipe row( rpad(rec_tab(v_col_seq).col_name,35)||'>'||substr(v_line,1,100));
END LOOP;
END LOOP;
return;
end clob_dump;
/
select * from table(clob_dump);
