Oracle中将表内多行文本列转为单行后创建新表的PL/SQL实现问询

实现方案 #

你要处理的多行address本质是字段里包含了回车符（CHR(13)）和换行符（CHR(10)），直接替换这些特殊字符就能实现单行展示，分两种实现方式：

1. 纯SQL实现（优先推荐，无需PL/SQL）

直接修改你的建表语句，用内置函数处理address字段即可：

CREATE TABLE StudentTest AS
SELECT 
    id,
    name,
    -- 依次替换回车、换行符，合并连续空格，去掉首尾空白
    TRIM(REGEXP_REPLACE(REPLACE(REPLACE(address, CHR(13), ' '), CHR(10), ' '), '\s+', ' ')) AS address
FROM Student
WHERE id = 1;

2. PL/SQL 实现

如果要求必须用PL/SQL逻辑处理，可以用匿名块实现：

DECLARE
    v_id Student.id%TYPE;
    v_name Student.name%TYPE;
    v_raw_addr Student.address%TYPE;
    v_processed_addr Student.address%TYPE;
BEGIN
    -- 读取原表数据
    SELECT id, name, address INTO v_id, v_name, v_raw_addr
    FROM Student WHERE id = 1;

    -- 处理地址字段的换行符
    v_processed_addr := TRIM(REGEXP_REPLACE(REPLACE(REPLACE(v_raw_addr, CHR(13), ' '), CHR(10), ' '), '\s+', ' '));

    -- 建表并插入处理后的数据，若表已存在可先加DROP逻辑，生产环境谨慎操作
    EXECUTE IMMEDIATE 'CREATE TABLE StudentTest (id NUMBER, name VARCHAR2(100), address CLOB)';
    EXECUTE IMMEDIATE 'INSERT INTO StudentTest VALUES (:1, :2, :3)' USING v_id, v_name, v_processed_addr;
    COMMIT;
END;
/

注意事项 #

• 如果address是CLOB类型，上述函数处理逻辑完全兼容，无需调整
• 生产环境如果需要给StudentTest加索引、约束等属性，建议先显式定义表结构再插入数据，不要用CREATE TABLE AS隐式继承字段属性

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