如何在PL/SQL中获取指定表的所有约束并导出表结构？

Nice work on getting the column details working so far! To add constraint information to your PL/SQL procedure, you'll want to leverage Oracle's data dictionary views—these are built-in tables that store metadata about your database objects. Let's walk through how to do this, and integrate it into a complete procedure that writes everything to a SQL file.

1. Understanding Oracle's Constraint Metadata #

Oracle stores constraint details in two core views you'll use:

• USER_CONSTRAINTS: Contains high-level info about constraints (name, type, table it applies to)
• USER_CONS_COLUMNS: Maps constraints to the specific columns they affect

For non-null constraints, you can also check USER_TAB_COLUMNS (the NULLABLE flag) or filter USER_CONSTRAINTS for check constraints that enforce NOT NULL.

Common constraint types you'll encounter:

• P: Primary Key
• U: Unique Constraint
• F: Foreign Key
• C: Check Constraint (includes implicit NOT NULL constraints)
• R: Referential Constraint (legacy alias for foreign keys)

2. Query to Fetch Constraints #

Here's a query to pull all constraints for a given table:

SELECT 
    c.constraint_name,
    c.constraint_type,
    cc.column_name,
    c.search_condition,
    c.r_owner,
    c.r_constraint_name
FROM 
    user_constraints c
JOIN 
    user_cons_columns cc ON c.constraint_name = cc.constraint_name
WHERE 
    c.table_name = UPPER(p_table_name) -- Oracle stores table names in uppercase
ORDER BY 
    c.constraint_type, cc.position;

3. Complete PL/SQL Procedure #

Below is a full procedure that combines your existing column logic with constraint fetching, and writes everything to a SQL file using the UTL_FILE package:

CREATE OR REPLACE PROCEDURE generate_table_details(
    p_table_name IN VARCHAR2, 
    p_output_dir IN VARCHAR2, 
    p_output_file IN VARCHAR2
) IS
    v_cursor_id INTEGER;
    v_total_columns INTEGER;
    TYPE col_desc_tab IS TABLE OF DBMS_SQL.DESC_TAB;
    v_rec_tab col_desc_tab;
    v_nr_col INTEGER;
    v_data_type VARCHAR2(100);
    v_file_handle UTL_FILE.FILE_TYPE;
    
    -- Cursor to fetch constraint details
    CURSOR c_constraints IS
        SELECT 
            c.constraint_name,
            c.constraint_type,
            cc.column_name,
            c.search_condition,
            c.r_owner,
            c.r_constraint_name
        FROM 
            user_constraints c
        JOIN 
            user_cons_columns cc ON c.constraint_name = cc.constraint_name
        WHERE 
            c.table_name = UPPER(p_table_name)
        ORDER BY 
            c.constraint_type, cc.position;
    
    v_constraint_name VARCHAR2(30);
    v_constraint_type CHAR(1);
    v_col_name VARCHAR2(30);
    v_search_condition VARCHAR2(1000);
    v_r_owner VARCHAR2(30);
    v_r_constraint_name VARCHAR2(30);
    
    -- Helper function to get referenced table for foreign keys
    FUNCTION get_referenced_table(p_r_owner VARCHAR2, p_r_constraint_name VARCHAR2) RETURN VARCHAR2 IS
        v_ref_table VARCHAR2(30);
    BEGIN
        SELECT table_name INTO v_ref_table
        FROM user_constraints
        WHERE owner = p_r_owner AND constraint_name = p_r_constraint_name;
        RETURN v_ref_table;
    END get_referenced_table;
    
BEGIN
    -- Open the output file for writing
    v_file_handle := UTL_FILE.FOPEN(p_output_dir, p_output_file, 'W');
    
    -- Write table header
    UTL_FILE.PUT_LINE(v_file_handle, '-- Auto-generated table details for: ' || UPPER(p_table_name));
    UTL_FILE.PUT_LINE(v_file_handle, 'CREATE TABLE ' || UPPER(p_table_name) || ' (');
    
    -- Fetch and write column details (enhanced version of your code)
    v_cursor_id := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor_id, 'SELECT * FROM ' || UPPER(p_table_name), DBMS_SQL.NATIVE);
    DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_id, v_total_columns, v_rec_tab);
    
    v_nr_col := v_rec_tab.FIRST;
    WHILE v_nr_col IS NOT NULL LOOP
        -- Map internal column type to readable SQL type
        CASE v_rec_tab(v_nr_col).col_type
            WHEN 1 THEN v_data_type := 'VARCHAR2(' || v_rec_tab(v_nr_col).col_max_len || ')';
            WHEN 2 THEN 
                IF v_rec_tab(v_nr_col).col_precision IS NOT NULL THEN
                    v_data_type := 'NUMBER(' || v_rec_tab(v_nr_col).col_precision || ',' || v_rec_tab(v_nr_col).col_scale || ')';
                ELSE
                    v_data_type := 'NUMBER';
                END IF;
            WHEN 12 THEN v_data_type := 'DATE';
            WHEN 96 THEN v_data_type := 'CHAR(' || v_rec_tab(v_nr_col).col_max_len || ')';
            WHEN 111 THEN v_data_type := 'CLOB';
            WHEN 100 THEN v_data_type := 'BLOB';
            ELSE v_data_type := 'UNKNOWN_TYPE(' || v_rec_tab(v_nr_col).col_type || ')';
        END CASE;
        
        -- Write column definition
        UTL_FILE.PUT(v_file_handle, '    ' || v_rec_tab(v_nr_col).col_name || ' ' || v_data_type);
        
        -- Add NOT NULL constraint if applicable
        DECLARE
            v_nullable CHAR(1);
        BEGIN
            SELECT nullable INTO v_nullable
            FROM user_tab_columns
            WHERE table_name = UPPER(p_table_name) AND column_name = v_rec_tab(v_nr_col).col_name;
            
            IF v_nullable = 'N' THEN
                UTL_FILE.PUT(v_file_handle, ' NOT NULL');
            END IF;
        END;
        
        -- Add comma if not the last column
        IF v_nr_col != v_rec_tab.LAST THEN
            UTL_FILE.PUT_LINE(v_file_handle, ',');
        ELSE
            UTL_FILE.PUT_LINE(v_file_handle);
        END IF;
        
        v_nr_col := v_rec_tab.NEXT(v_nr_col);
    END LOOP;
    
    DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
    
    -- Write constraints section
    UTL_FILE.PUT_LINE(v_file_handle, ',');
    OPEN c_constraints;
    LOOP
        FETCH c_constraints INTO v_constraint_name, v_constraint_type, v_col_name, v_search_condition, v_r_owner, v_r_constraint_name;
        EXIT WHEN c_constraints%NOTFOUND;
        
        -- Skip implicit NOT NULL check constraints (we already handled them)
        IF v_constraint_type = 'C' AND v_search_condition LIKE '%IS NOT NULL%' THEN
            CONTINUE;
        END IF;
        
        UTL_FILE.PUT(v_file_handle, '    ');
        -- Format constraint based on type
        CASE v_constraint_type
            WHEN 'P' THEN 
                UTL_FILE.PUT_LINE(v_file_handle, 'CONSTRAINT ' || v_constraint_name || ' PRIMARY KEY (' || v_col_name || ')');
            WHEN 'U' THEN 
                UTL_FILE.PUT_LINE(v_file_handle, 'CONSTRAINT ' || v_constraint_name || ' UNIQUE (' || v_col_name || ')');
            WHEN 'F' THEN 
                UTL_FILE.PUT_LINE(v_file_handle, 'CONSTRAINT ' || v_constraint_name || ' FOREIGN KEY (' || v_col_name || ') REFERENCES ' || get_referenced_table(v_r_owner, v_r_constraint_name));
            WHEN 'C' THEN 
                UTL_FILE.PUT_LINE(v_file_handle, 'CONSTRAINT ' || v_constraint_name || ' CHECK (' || v_search_condition || ')');
            WHEN 'R' THEN 
                UTL_FILE.PUT_LINE(v_file_handle, 'CONSTRAINT ' || v_constraint_name || ' FOREIGN KEY (' || v_col_name || ') REFERENCES ' || get_referenced_table(v_r_owner, v_r_constraint_name));
        END CASE;
        
        -- Add comma if there are more constraints coming
        IF c_constraints%FOUND THEN
            UTL_FILE.PUT_LINE(v_file_handle, ',');
        ELSE
            UTL_FILE.PUT_LINE(v_file_handle);
        END IF;
    END LOOP;
    CLOSE c_constraints;
    
    -- Close the table definition
    UTL_FILE.PUT_LINE(v_file_handle, ');');
    
    -- Clean up
    UTL_FILE.FCLOSE(v_file_handle);
    DBMS_OUTPUT.PUT_LINE('Success! Table details written to ' || p_output_dir || '/' || p_output_file);
    
EXCEPTION
    WHEN UTL_FILE.INVALID_PATH THEN
        DBMS_OUTPUT.PUT_LINE('Error: Invalid directory path specified');
        RAISE;
    WHEN UTL_FILE.INVALID_MODE THEN
        DBMS_OUTPUT.PUT_LINE('Error: Invalid file access mode');
        RAISE;
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Table ' || UPPER(p_table_name) || ' does not exist in your schema');
        RAISE;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
        IF UTL_FILE.IS_OPEN(v_file_handle) THEN
            UTL_FILE.FCLOSE(v_file_handle);
        END IF;
        RAISE;
END generate_table_details;
/

4. How to Use the Procedure #

1. Create a directory object (replace the path with a directory your Oracle user has access to):

   CREATE DIRECTORY SQL_OUTPUT AS '/home/oracle/sql_output/';
   

2. Grant permissions to your user:

   GRANT READ, WRITE ON DIRECTORY SQL_OUTPUT TO your_username;
   GRANT EXECUTE ON UTL_FILE TO your_username;
   

3. Call the procedure:

   EXEC generate_table_details('marks', 'SQL_OUTPUT', 'marks_table_details.sql');
   

5. Key Notes & Enhancements #

• Composite Constraints: The current code handles single-column constraints. For composite keys/constraints, you'll need to modify the cursor to group columns by constraint name and concatenate them.
• Data Types: Extend the CASE statement in the column logic to cover more Oracle data types (e.g., TIMESTAMP, INTERVAL).
• Cross-Schema Tables: If you need to fetch details for tables in other schemas, use ALL_CONSTRAINTS and ALL_CONS_COLUMNS instead of USER_* views, and add a schema parameter to the procedure.

内容的提问来源于stack exchange，提问作者Iustin Vlad