PLSQL实现新增出版社并更新书籍空值后按指定格式查询数据

涉及表结构（字段翻译） #

• author（作者表）字段：authorid（作者ID）、firstname（名）、lastname（姓）
• book（书籍表）字段：author_id（关联作者ID）、bookid（书籍ID）、publisherid（关联出版社ID）、title（书籍标题）
• publisher（出版社表）字段：publisherid（出版社ID）、publishername（出版社名称）

完整实现PLSQL块 #

SET SERVEROUTPUT ON;
DECLARE
    -- 存储新插入的出版社ID
    v_new_pubid publisher.publisherid%TYPE;
BEGIN
    -- 1. 插入新出版社，返回生成的出版社ID
    INSERT INTO publisher (publisherid, publishername)
    VALUES (seq_publisher_id.NEXTVAL, 'ttk publisher') -- seq_publisher_id替换为实际的出版社主键序列名
    RETURNING publisherid INTO v_new_pubid;

    -- 2. 更新book表中出版社ID为空的记录
    UPDATE book
    SET publisherid = v_new_pubid
    WHERE publisherid IS NULL;

    -- 3. 按指定格式输出本次更新的记录
    DBMS_OUTPUT.PUT_LINE(RPAD('BOOKID', 18, '.') || RPAD('TITLE', 18, '.') || RPAD('publisher', 22, '.') || 'Author');
    DBMS_OUTPUT.PUT_LINE('');
    FOR rec IN (
        SELECT 
            b.bookid,
            SUBSTR(b.title, 1, 10) AS short_title,
            p.publishername,
            a.firstname || ' ' || a.lastname AS author
        FROM book b
        JOIN author a ON b.author_id = a.authorid
        JOIN publisher p ON b.publisherid = p.publisherid
        WHERE b.publisherid = v_new_pubid
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(
            RPAD(rec.bookid, 18, '.') || 
            RPAD(rec.short_title, 18, '.') || 
            RPAD(rec.publishername, 22, '.') || 
            rec.author
        );
    END LOOP;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

注意事项 #

• 代码中seq_publisher_id需替换为实际环境中publisher表主键对应的序列名，若主键为非自增手动赋值，可自行修改INSERT语句的publisherid取值逻辑
• 输出对齐的填充长度可根据实际字段取值长度微调RPAD函数的第二个参数，即可适配不同长度的内容展示

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