在PLSQL中检索列名和数据类型 [英] Retrieving Column Names and Data types in PLSQL

问题描述

我正在编写一个PL SQL块，该块将检索数据库中表的所有列和数据类型.我能够获取列，但不能获取数据类型.寻找好的方法的建议.任何帮助，将不胜感激.我的代码如下

I am writing a PL SQL block that retrieves all the columns and the data types of the tables in the database. I am able to get the columns , but not the datatypes. Looking for suggestions for a good approach. Any help would be appreciated. My code is as follows

ACCEPT p_1 PROMPT 'Please enter the Table Name'

DECLARE
    v_table_name    VARCHAR2(40) :='&p_1';
    -- First cursor 
    CURSOR get_tables IS    
        SELECT DISTINCT table_name 
        FROM user_tables 
        WHERE UPPER(table_name) = UPPER(v_table_name);
    --Second cursor 
    CURSOR get_columns IS
        SELECT DISTINCT column_name
        FROM user_tab_columns
        WHERE table_name = v_table_name;
    v_column_name   VARCHAR2(100);
    -- Third Cursor
    CURSOR get_types IS
        SELECT data_type 
        FROM user_tab_columns
        WHERE table_name = v_table_name;

    v_data_type user_tab_columns.data_type%type;
BEGIN
    -- Open first cursor
    OPEN get_tables;
    FETCH get_tables INTO v_table_name;
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('Table = ' || v_table_name );
    DBMS_OUTPUT.PUT_LINE('=========================');
    CLOSE get_tables;
    -- Open second cursor
    OPEN get_columns;
    FETCH get_columns INTO v_column_name;
    WHILE get_columns%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE('  ' || v_column_name);
        FETCH get_columns INTO v_column_name;
    END LOOP;
    CLOSE get_columns;
    --Open Third Cursor
    OPEN get_types;
    FETCH get_types into v_data_type;
    WHILE get_types%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(' ' || v_data_type );
        FETCH get_types into v_data_type;
    END LOOP;

    CLOSE get_types;     
END;

我的错误状态为PLS-00371:最多允许一个"V_DATA_TYPE"声明

My error states PLS-00371: at most one declaration for 'V_DATA_TYPE' is permitted

推荐答案

不是PLSQL专家，但这是我的主见.

Not a PLSQL guru but here's my grain.

Select data_type from user_tab_columns where TABLE_NAME = 'YourTableName'

对Eric的支持，请检查此线程及其答案.

Props to Eric, check this thread and his answer.

请记住，您可以使用DESC命令来描述Oracle表，视图，同义词，包或函数.它将为您提供名称，data_type和lengh. 并且，如果这确实对您有用，那么您应该能够获取所有表的数据，尽管我不是游标的忠实拥护者，但您应该做得很好.

Remember you can use DESC command to describe an Oracle Table, View, Synonym, package or Function. It will give you name, data_type and lengh. And if this actually works for you, you should be able to get the data for all of your tables, although I'm not a huge fan of cursors, you should do fine.

尝试一下:

-- Open second cursor
            OPEN get_columns;
            LOOP
            FETCH get_columns INTO v_column_name, v_data_type;
            EXIT WHEN get_columns%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE('  ' || v_column_name);
            END LOOP;
            CLOSE get_columns;
    END LOOP;

但是要小心为v_data_type变量选择的数据类型.

But be careful on the datatype you've chosen for v_data_type variable.

这篇关于在PLSQL中检索列名和数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！