分别浏览记录的每一列 [英] Browsing each column of a record separately

问题描述

我有一个表，其中有很多列.由于某些原因，我需要从中获取数据，然后分别对每个列值进行一些处理.所以我想做这样的事情:

I have a table that has a lot of columns. For some reason, I need to fetch data from it and then do some stuff on each column value separately. So I'd like to do something like this :

SELECT record FROM table
FOREACH field of the record
    do some stuff

做某事"部分应根据列名做某事.

The "do some stuff" part shall do something according to the column name.

有没有一种简便的方法可以在PL/SQL中执行这种浏览?

Is there an easy way to perform such a browse in PL/SQL ?

谢谢！

推荐答案

不幸的是，您不能像这样在记录字段中循环.但是，您可以使用DBMS_SQL遍历所有返回的列并执行所需的任何操作.在下面基于该线程的简单示例中进行检查:使用DBMS_SQL遍历记录的各个列

Unfortunately, you can't loop like this through fields of a record. However, you could use DBMS_SQL to loop through all the returned columns and do whatever you need. Check my simple example below based on this thread: Loop through columns of a record with DBMS_SQL

CREATE TABLE my_iter_tab_test (
  id NUMBER,
  name VARCHAR2(20),
  salary NUMBER
);

INSERT INTO my_iter_tab_test VALUES (1, 'Smith', 5000);
INSERT INTO my_iter_tab_test VALUES (2, 'Brown', 6000);

COMMIT;

DECLARE
  v_cur NUMBER;
  v_temp NUMBER;
  v_col_cnt NUMBER;
  v_desc_tab_rec DBMS_SQL.DESC_TAB;
  v_ret NUMBER;
  v_v_val VARCHAR2(4000);
BEGIN
  v_cur := DBMS_SQL.OPEN_CURSOR;

  DBMS_SQL.PARSE(v_cur, 'SELECT * FROM my_iter_tab_test', DBMS_SQL.NATIVE);

  v_temp := DBMS_SQL.EXECUTE(v_cur);

  DBMS_SQL.DESCRIBE_COLUMNS(v_cur, v_col_cnt, v_desc_tab_rec);

  FOR v_i IN 1..v_col_cnt
  LOOP
    dbms_output.put_line(v_desc_tab_rec(v_i).col_name);
    DBMS_SQL.DEFINE_COLUMN(v_cur ,v_i, v_v_val, 2000);
  END LOOP;

  LOOP
    v_ret := DBMS_SQL.FETCH_ROWS(v_cur);
    EXIT WHEN v_ret = 0;

    FOR v_i IN 1..v_col_cnt
    LOOP
      DBMS_SQL.COLUMN_VALUE(v_cur, v_i, v_v_val);
      DBMS_OUTPUT.PUT_LINE(v_desc_tab_rec(v_i).col_name || ' : ' || v_v_val);
    END LOOP;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(v_cur);
END;

输出:

ID
NAME
SALARY
ID : 1
NAME : Smith
SALARY : 5000
ID : 2
NAME : Brown
SALARY : 6000

我猜您会在最终解决方案中将DEFINE_COLUMN更改为适当的类型.

I guess you'll want to change the DEFINE_COLUMN to proper types in your final solution.

这篇关于分别浏览记录的每一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！