如何在Oracle SQL工具中批量执行多个DECLARE语句？

一、直接执行多个独立PL/SQL块的方法 #

不同SQL工具处理多块PL/SQL的操作略有差异，以下是常用工具的执行方式：

1. Oracle SQL Developer / PL/SQL Developer #

• 把所有PL/SQL块复制到编辑器中，每个块末尾加上/作为结束标记（确保每个块被单独解析）
• 点击执行按钮（或按F5），工具会自动按顺序执行每个块

格式化后的可执行代码：

DECLARE 
    v_count NUMBER; 
BEGIN 
    SELECT COUNT(*) INTO v_count FROM DTLS WHERE LOGONID = 'l@k.com'; 
    IF v_count = 0 THEN 
        INSERT INTO DTLS (LOGONID, FIRSTNAME, LASTNAME, ROLENAME, EMAIL, CID ) 
        VALUES ('l@k.com', 'Ls', 'Dva', '805ser', 'l@k.com'); 
    ELSE 
        UPDATE DTLS SET ROLENAME = '80ar User' WHERE LOGONID = 'l@k.com';  
    END IF;
END;
/

DECLARE 
    v_count NUMBER; 
BEGIN 
    SELECT COUNT(*) INTO v_count FROM DTLS WHERE LOGONID = 'n@s.com'; 
    IF v_count = 0 THEN 
        INSERT INTO DTLS (LOGONID, FIRSTNAME, LASTNAME, ROLENAME, EMAIL, CID ) 
        VALUES ('n@s.com', 'Pt', 'Ns', '509User', 'n@s.com'); 
    ELSE 
        UPDATE DTLS SET ROLENAME = '50_User' WHERE LOGONID = 'n@s.com';  
    END IF;
END;
/

DECLARE 
    v_count NUMBER; 
BEGIN 
    SELECT COUNT(*) INTO v_count FROM DTLS WHERE LOGONID = 'J@y.com'; 
    IF v_count = 0 THEN 
        INSERT INTO DTLS (LOGONID, FIRSTNAME, LASTNAME, ROLENAME, EMAIL, CID) 
        VALUES ('J@y.com', 'Jn', 'Re', '300nly', 'J@y.com'); 
    ELSE 
        UPDATE DTLS SET ROLENAME = '300ly' WHERE LOGONID = 'J@y.com';  
    END IF;
END;
/

DECLARE 
    v_count NUMBER; 
BEGIN 
    SELECT COUNT(*) INTO v_count FROM DTLS WHERE LOGONID = 'a@r.com'; 
    IF v_count = 0 THEN 
        INSERT INTO DTLS (LOGONID, FIRSTNAME, LASTNAME, ROLENAME, EMAIL, CID) 
        VALUES ('a@r.com', 'A', 'rczyk', '300ser', 'a@r.com'); 
    ELSE 
        UPDATE DTLS SET ROLENAME = '300' WHERE LOGONID = 'a@r.com';  
    END IF;
END;
/

2. SQL*Plus #

• 将上述格式化后的代码保存为.sql文件
• 在SQL*Plus中执行命令 @文件名.sql，工具会按顺序执行所有块

二、优化方案：简化代码提升效率 #

重复的PL/SQL块可以合并或改用更高效的语法，减少冗余：

1. 合并为单个PL/SQL块 #

把所有逻辑放在同一个BEGIN-END块中，避免重复声明变量：

DECLARE 
    v_count NUMBER; 
BEGIN 
    -- 处理l@k.com
    SELECT COUNT(*) INTO v_count FROM DTLS WHERE LOGONID = 'l@k.com'; 
    IF v_count = 0 THEN 
        INSERT INTO DTLS (LOGONID, FIRSTNAME, LASTNAME, ROLENAME, EMAIL, CID ) 
        VALUES ('l@k.com', 'Ls', 'Dva', '805ser', 'l@k.com'); 
    ELSE 
        UPDATE DTLS SET ROLENAME = '80ar User' WHERE LOGONID = 'l@k.com';  
    END IF;

    -- 处理n@s.com
    SELECT COUNT(*) INTO v_count FROM DTLS WHERE LOGONID = 'n@s.com'; 
    IF v_count = 0 THEN 
        INSERT INTO DTLS (LOGONID, FIRSTNAME, LASTNAME, ROLENAME, EMAIL, CID ) 
        VALUES ('n@s.com', 'Pt', 'Ns', '509User', 'n@s.com'); 
    ELSE 
        UPDATE DTLS SET ROLENAME = '50_User' WHERE LOGONID = 'n@s.com';  
    END IF;

    -- 处理J@y.com
    SELECT COUNT(*) INTO v_count FROM DTLS WHERE LOGONID = 'J@y.com'; 
    IF v_count = 0 THEN 
        INSERT INTO DTLS (LOGONID, FIRSTNAME, LASTNAME, ROLENAME, EMAIL, CID) 
        VALUES ('J@y.com', 'Jn', 'Re', '300nly', 'J@y.com'); 
    ELSE 
        UPDATE DTLS SET ROLENAME = '300ly' WHERE LOGONID = 'J@y.com';  
    END IF;

    -- 处理a@r.com
    SELECT COUNT(*) INTO v_count FROM DTLS WHERE LOGONID = 'a@r.com'; 
    IF v_count = 0 THEN 
        INSERT INTO DTLS (LOGONID, FIRSTNAME, LASTNAME, ROLENAME, EMAIL, CID) 
        VALUES ('a@r.com', 'A', 'rczyk', '300ser', 'a@r.com'); 
    ELSE 
        UPDATE DTLS SET ROLENAME = '300' WHERE LOGONID = 'a@r.com';  
    END IF;
END;
/

2. 使用MERGE语句（更高效） #

Oracle的MERGE语句可直接实现“不存在则插入，存在则更新”的逻辑，无需先查询计数，减少表访问次数：

-- 处理l@k.com
MERGE INTO DTLS t
USING (SELECT 'l@k.com' AS logonid FROM dual) s
ON (t.LOGONID = s.logonid)
WHEN NOT MATCHED THEN
    INSERT (LOGONID, FIRSTNAME, LASTNAME, ROLENAME, EMAIL, CID)
    VALUES ('l@k.com', 'Ls', 'Dva', '805ser', 'l@k.com')
WHEN MATCHED THEN
    UPDATE SET ROLENAME = '80ar User';

-- 处理n@s.com
MERGE INTO DTLS t
USING (SELECT 'n@s.com' AS logonid FROM dual) s
ON (t.LOGONID = s.logonid)
WHEN NOT MATCHED THEN
    INSERT (LOGONID, FIRSTNAME, LASTNAME, ROLENAME, EMAIL, CID)
    VALUES ('n@s.com', 'Pt', 'Ns', '509User', 'n@s.com')
WHEN MATCHED THEN
    UPDATE SET ROLENAME = '50_User';

-- 处理J@y.com
MERGE INTO DTLS t
USING (SELECT 'J@y.com' AS logonid FROM dual) s
ON (t.LOGONID = s.logonid)
WHEN NOT MATCHED THEN
    INSERT (LOGONID, FIRSTNAME, LASTNAME, ROLENAME, EMAIL, CID)
    VALUES ('J@y.com', 'Jn', 'Re', '300nly', 'J@y.com')
WHEN MATCHED THEN
    UPDATE SET ROLENAME = '300ly';

-- 处理a@r.com
MERGE INTO DTLS t
USING (SELECT 'a@r.com' AS logonid FROM dual) s
ON (t.LOGONID = s.logonid)
WHEN NOT MATCHED THEN
    INSERT (LOGONID, FIRSTNAME, LASTNAME, ROLENAME, EMAIL, CID)
    VALUES ('a@r.com', 'A', 'rczyk', '300ser', 'a@r.com')
WHEN MATCHED THEN
    UPDATE SET ROLENAME = '300';
/

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