如何动态创建具有表数据类型的变量? [英] How to dynamically create a variable with the data type of table?

问题描述

在下面的查询中，select_string可以返回任何数量的列和行以及任何可能的数据类型.

In the following query select_string can return any number of columns and rows with any possible data types.

Execute immediate 'select_string' into v_table_variable;

例如select_string可以是'select name, last name from student'或'select date, subject, address , phone from booking'，依此类推.

For example select_string could be 'select name, last name from student' or 'select date, subject, address , phone from booking' and so on.

有谁知道我如何定义v_table_variable，以便立即执行我想要的立即执行?！我打算在之后进行循环以从该变量读取值.

Does anyone has any idea how I can define the v_table_variable so that the execute immediate is run as I want?! I intend to make a loop afterwards to read the values from this variable.

非常感谢！

推荐答案

全面"的动态版本就是这个版本(未经测试):

The "full blown" dynmamic version would be this one (not tested):

DECLARE  
  v_stmt_str       VARCHAR2(200);
  v_cur            INTEGER;
  v_rows_processed INTEGER;

  col_cnt     INTEGER;
  rec_tab     DBMS_SQL.DESC_TAB;
  rec         DBMS_SQL.DESC_REC;

  num_var NUMBER;
  string_var VARCHAR2(4000);
  date_var DATE;
  -- .. some more variables if needed 
BEGIN
  v_cur := DBMS_SQL.OPEN_CURSOR; -- open cursor 
  v_stmt_str := 'SELECT whatever from ....';
  DBMS_SQL.PARSE(v_cur, v_stmt_str, DBMS_SQL.NATIVE); 
  DBMS_SQL.DESCRIBE_COLUMNS(v_cur, col_cnt, rec_tab);  

  FOR c in 1..col_cnt LOOP
     rec := rec_tab(c);
     IF rec.col_type = DBMS_TYPES.TYPECODE_NUMBER THEN
         DBMS_SQL.DEFINE_COLUMN(v_cur, c, num_var); 
     ELSIF rec.col_type = DBMS_TYPES.TYPECODE_VARCHAR2 THEN
         DBMS_SQL.DEFINE_COLUMN(v_cur, c, string_var, rec.col_max_len); 
     ELSIF rec.col_type = DBMS_TYPES.TYPECODE_DATE THEN
         DBMS_SQL.DEFINE_COLUMN(v_cur, c, date_var); 
     -- .. some more data types if needed
     END IF;
  END LOOP;

  -- Execute
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur);  
  LOOP 
    -- Fetch a row 
    IF DBMS_SQL.FETCH_ROWS(v_cur) > 0 THEN 
      FOR c in 1..col_cnt LOOP
        rec := rec_tab(c);
        IF rec.col_type = DBMS_TYPES.TYPECODE_NUMBER THEN
            DBMS_SQL.COLUMN_VALUE(v_cur, c, num_var); 
        ELSIF rec.col_type = DBMS_TYPES.TYPECODE_VARCHAR2 THEN
            DBMS_SQL.COLUMN_VALUE(v_cur, c, string_var); 
        ELSIF rec.col_type = DBMS_TYPES.TYPECODE_DATE THEN
            DBMS_SQL.COLUMN_VALUE(v_cur, c, date_var); 
        -- .. some more data types if needed
        END IF;
      END LOOP;
      -- Process: do something with num_var or string_var or date_var values
    ELSE
      EXIT; 
    END IF; 
  END LOOP; 
  DBMS_SQL.CLOSE_CURSOR(v_cur); -- close cursor
END;
/

但是正如评论中已经提到的那样，如果您确实需要如此动态的信息，请检查您的需求-可能不是.

But as already stated in comments, check your requirements if you really need it such dynamic - probably not.

检查Oracle文档编码动态SQL 和<其他示例，请参见href ="http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm" rel ="nofollow"> DBMS_SQL .有各种各样的方法可以提供不同的动态层次".

Check Oracle documentation Coding Dynamic SQL and DBMS_SQL for other examples. There are various methods providing different "levels" of dynamic.

这篇关于如何动态创建具有表数据类型的变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！