PLS-00103：预期出现以下情况之一时遇到符号";"：案例 符号"case"已替换为";"继续

Question

我的包体有问题

这是代码：

CREATE OR REPLACE PACKAGE BODY emp_data IS
    PROCEDURE open_emp_cur_var(cv_emp IN OUT rt_emp, p_your_choice IN NUMBER)
    IS 
        BEGIN 
            CASE
                when p_your_choice=1 then open cv_emp for SELECT * FROM employees;
                else OPEN cv_emp for SELECT * FROM employees WHERE salary > 8000;
            end;
    end;
end open_emp_cur_var;
END emp_data; 

它返回我它是用错误编译的。

这是它返回我的两个错误。

8/16      PLS-00103: Encountered the symbol ";" when expecting one of the following:     case The symbol "case" was substituted for ";" to continue. 
11/1      PLS-00103: Encountered the symbol "END"

Answer 1

你的第一个END需要END CASE;你有一个额外的END：

CREATE OR REPLACE PACKAGE BODY emp_data IS
    PROCEDURE open_emp_cur_var(cv_emp IN OUT rt_emp, p_your_choice IN NUMBER)
    IS 
    BEGIN 
        CASE
            WHEN p_your_choice=1 THEN
                OPEN cv_emp for SELECT * FROM employees;
            ELSE
                OPEN cv_emp for SELECT * FROM employees WHERE salary > 8000;
        END CASE;
    END open_emp_cur_var;
END emp_data; 
/

Answer 2

如果您不熟悉CASE，可以使用IF..END IF

此外，您可能正在寻找一个REFCURSOR OUT变量。

CREATE OR REPLACE PACKAGE  emp_data IS
 PROCEDURE open_emp_cur_var (
          cv_emp OUT SYS_REFCURSOR , p_your_choice IN NUMBER
     );
     END;
     /
CREATE OR REPLACE PACKAGE BODY emp_data IS
     PROCEDURE open_emp_cur_var (
          cv_emp  OUT SYS_REFCURSOR, p_your_choice IN NUMBER
     ) IS
     BEGIN
          IF p_your_choice = 1 THEN
               OPEN cv_emp FOR SELECT *
                               FROM employees;
          ELSE OPEN cv_emp FOR SELECT *
                              FROM employees
                              WHERE salary > 8000;
     END IF;
     END;
END emp_data;
/