PLSQL中用CONFIG_PRAM表对比并更新现有表列数据类型

Alright, let's walk through how to keep your CONFIG_PARAM table in sync with the actual column data types from your database tables (like test1). This is a common task when you have a metadata table that needs to reflect real-time schema changes, so here's a straightforward, database-specific approach:

First, you need to fetch the real data types of columns in your target table (e.g., test1). The exact query depends on your database system:

For Oracle #

Use the user_tab_columns system view to get column details:

SELECT 
    column_name AS colname,
    -- Build full data type string (e.g., NUMBER(6,2), VARCHAR2(50))
    data_type || CASE 
        WHEN data_type = 'NUMBER' AND (data_precision IS NOT NULL OR data_scale IS NOT NULL)
        THEN '(' || COALESCE(data_precision, '') || CASE WHEN data_scale IS NOT NULL THEN ',' || data_scale ELSE '' END || ')'
        WHEN data_type IN ('VARCHAR2', 'CHAR') THEN '(' || data_length || ')'
        ELSE ''
    END AS datatype
FROM user_tab_columns
WHERE table_name = 'TEST1'; -- Uppercase if your Oracle tables use case-sensitive names

For MySQL #

Query the information_schema.columns table:

SELECT 
    column_name AS colname,
    data_type || CASE 
        WHEN data_type IN ('int', 'decimal', 'float') THEN '(' || COALESCE(numeric_precision, '') || CASE WHEN numeric_scale IS NOT NULL THEN ',' || numeric_scale ELSE '' END || ')'
        WHEN data_type IN ('varchar', 'char', 'text') THEN '(' || COALESCE(character_maximum_length, '') || ')'
        ELSE ''
    END AS datatype
FROM information_schema.columns
WHERE table_schema = 'your_database_name' -- Replace with your DB name
  AND table_name = 'test1';

For SQL Server #

Combine sys.columns and sys.types to get full type info:

SELECT 
    c.name AS colname,
    t.name || CASE 
        WHEN t.name IN ('int', 'decimal', 'numeric') THEN '(' || c.precision || ',' || c.scale || ')'
        WHEN t.name IN ('varchar', 'char') THEN '(' || c.max_length || ')'
        ELSE ''
    END AS datatype
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE OBJECT_NAME(c.object_id) = 'test1';

Once you have the real column data types, use a merge or update query to fix mismatches in CONFIG_PARAM.

For Oracle (Using MERGE) #

This is the cleanest way to match and update in one step:

MERGE INTO CONFIG_PARAM cp
USING (
    -- The same metadata query from Step 1 goes here
    SELECT 
        column_name AS colname,
        data_type || CASE 
            WHEN data_type = 'NUMBER' AND (data_precision IS NOT NULL OR data_scale IS NOT NULL)
            THEN '(' || COALESCE(data_precision, '') || CASE WHEN data_scale IS NOT NULL THEN ',' || data_scale ELSE '' END || ')'
            WHEN data_type IN ('VARCHAR2', 'CHAR') THEN '(' || data_length || ')'
            ELSE ''
        END AS datatype
    FROM user_tab_columns
    WHERE table_name = 'TEST1'
) actual
ON (cp.colname = actual.colname)
WHEN MATCHED AND cp.datatype != actual.datatype THEN
    UPDATE SET cp.datatype = actual.datatype;

For MySQL (Using UPDATE JOIN) #

MySQL doesn't support MERGE, so use a join with UPDATE:

UPDATE CONFIG_PARAM cp
JOIN (
    -- Metadata query from Step 1 for MySQL
    SELECT 
        column_name AS colname,
        data_type || CASE 
            WHEN data_type IN ('int', 'decimal', 'float') THEN '(' || COALESCE(numeric_precision, '') || CASE WHEN numeric_scale IS NOT NULL THEN ',' || numeric_scale ELSE '' END || ')'
            WHEN data_type IN ('varchar', 'char', 'text') THEN '(' || COALESCE(character_maximum_length, '') || ')'
            ELSE ''
        END AS datatype
    FROM information_schema.columns
    WHERE table_schema = 'your_database_name'
      AND table_name = 'test1'
) actual ON cp.colname = actual.colname
SET cp.datatype = actual.datatype
WHERE cp.datatype != actual.datatype;

Run a quick check to ensure all mismatches are fixed:

-- Example for Oracle
SELECT 
    cp.colname,
    cp.datatype AS config_datatype,
    actual.datatype AS real_datatype
FROM CONFIG_PARAM cp
JOIN (
    SELECT column_name, data_type || CASE 
        WHEN data_type = 'NUMBER' AND (data_precision IS NOT NULL OR data_scale IS NOT NULL)
        THEN '(' || COALESCE(data_precision, '') || CASE WHEN data_scale IS NOT NULL THEN ',' || data_scale ELSE '' END || ')'
        WHEN data_type IN ('VARCHAR2', 'CHAR') THEN '(' || data_length || ')'
        ELSE ''
    END AS datatype
    FROM user_tab_columns
    WHERE table_name = 'TEST1'
) actual ON cp.colname = actual.column_name
WHERE cp.datatype != actual.datatype;

If the query returns no rows, your CONFIG_PARAM table is now in sync with the actual test1 table schema.

Quick Notes #

• Make sure your database user has the right permissions: SELECT on system metadata tables (like user_tab_columns or information_schema.columns) and UPDATE on CONFIG_PARAM.
• If CONFIG_PARAM tracks columns for multiple tables, add a table_name column to it, then include that in your ON clause (for MERGE) or join condition (for UPDATE) to avoid updating columns from other tables.
• Adjust the CASE statements to handle any special data types specific to your database (e.g., Oracle's TIMESTAMP, MySQL's DATETIME).

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