存储Oracle存储过程ComplexProc时报错，匿名块执行正常

核心问题定位 #

你创建存储过程失败的关键原因是语法错误：Oracle中定义无参数的存储过程时，不能写空括号()，必须去掉括号。原代码里的CREATE OR REPLACE PROCEDURE ComplexProc () IS不符合Oracle语法规范，这也是匿名块能运行但存储过程创建失败的核心原因。

修正后的完整代码 #

CREATE OR REPLACE PROCEDURE ComplexProc IS
    cursor emp is
    SELECT Ssn, Dno, Fname, Minit, Lname, Salary FROM Employee e WHERE e.Ssn = (SELECT Essn FROM Works_on wo
                                                                                                    WHERE Pno NOT IN ( SELECT p.Pnumber FROM Project p INNER JOIN Department d ON p.Dnum = d.Dnumber
                                                                                                                      INNER JOIN Employee e ON e.Dno = d.Dnumber WHERE e.Ssn = wo.Essn)
                                                                                                    AND Essn IN (SELECT Ssn FROM Employee e 
                                                                                                                WHERE e.Salary > ( SELECT Avg(Salary) FROM Employee e2 WHERE e.Dno = e2.DNo )
                                                                                                                AND (SELECT Count(Dependent_name) FROM Dependent d WHERE e.Ssn = d.Essn) >= 2)
                                                                                                    GROUP BY Essn HAVING Sum(Hours) >= 10);
                                                                                    
    emp_rec emp%rowtype;
    emp_NumDependents INT;
    emp_NonDepHours INT;
    emp_DepHours INT;

begin

    open emp;
    fetch emp into emp_rec; -- initial fetch

    emp_NumDependents := 0;
    while emp%found loop        -- still more data to fetch

        SELECT Count(Dependent_name) INTO emp_NumDependents FROM Dependent d WHERE d.Essn = emp_rec.Ssn;

        SELECT Sum(Hours) INTO emp_NonDepHours FROM Works_on wo
        WHERE wo.Pno NOT IN (SELECT p.Pnumber FROM Project p WHERE p.Dnum = emp_rec.Dno)
        AND wo.Essn = emp_rec.Ssn;

        SELECT Sum(Hours) INTO emp_DepHours FROM Works_on wo
        WHERE wo.Pno IN (SELECT p.Pnumber FROM Project p WHERE p.Dnum = emp_rec.Dno)
        AND wo.Essn = emp_rec.Ssn;
      
        dbms_output.put_line(emp_rec.Fname||' '||emp_rec.Minit||' '||emp_rec.Lname||' '||emp_rec.Salary||' '||emp_NumDependents||' '||emp_NonDepHours||' '||emp_DepHours);
        fetch emp into emp_rec;
    end loop;  
    close emp;
end;
/

额外注意事项 #

• 存储过程的声明段直接跟在IS/AS后面，不需要加declare关键字，这是匿名块和存储过程的语法区别，你之前尝试添加declare反而会导致语法错误。
• IS和AS在Oracle存储过程定义中完全等价，修改这个不会解决你的问题。
• 创建存储过程属于DDL操作，Oracle会自动提交事务，末尾的commit是多余的，可以删除。

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