在预期以下情况之一时遇到符号"END"： 开始函数杂注过程

Question

我在尝试创建包含过程的包时遇到错误。我需要创建一个全局游标以及 2 个过程和一个函数。

以下是我试图回答的问题。 编写一个名为 cursor_state 的包，该包将全局游标声明为 员工和部门。光标应选择每个员工的名字和姓氏、部门名称和员工的工资。包还应包含三个公共过程：第一个打开游标;第二个具有 IN 参数类型为 NUMBER，用于获取并显示行数以及循环计数器的当前值。第三个过程关闭游标。请记住，在每个过程中尝试打开或关闭光标之前，请先测试光标的状态。

和我想出的代码

CREATE OR REPLACE PACKAGE cursor_state IS
CURSOR emp_curs IS
SELECT first_name, last_name, department_name, salary
FROM employees, departments
WHERE employees.department_id = departments.department_id;
PROCEDURE open_curs;
FUNCTION fetch_rows RETURN BOOLEAN;
PROCEDURE close_curs;
END cursor_state;

这是身体的创造

CREATE OR REPLACE PACKAGE BODY cursor_state IS 
PROCEDURE open_curs IS 
BEGIN 
IF NOT emp_curs%ISOPEN THEN OPEN emp_curs; 
END IF;
END open_curs; 
FUNCTION fetch_rows(n NUMBER := 1) RETURN BOOLEAN IS 
emp_first employees.first_name%TYPE, 
emp_last employees.last_name%TYPE, 
emp_dept employees.department_name%TYPE, 
emp_salary employees.salary%TYPE;

BEGIN 
FOR count IN 1 .. n LOOP 
FETCH emp_curs INTO emp_first, emp_last, emp_dept, emp_salary; 
EXIT WHEN emp_curs%NOTFOUND; 
DBMS_OUTPUT.PUT_LINE((emp_first), (emp_last), (emp_dept), (emp_salary)); 
END LOOP; 
RETURN emp_curs%FOUND; 
END fetch_rows; 
PROCEDURE close_curs IS 
BEGIN 
IF emp_curs%ISOPEN
THEN CLOSE emp_curs;
END IF;
END close_curs; 
END cursor_state;

我尝试了很多次尝试更改分号和结束语句的不同位置。 无济于事。

Answer 1

你的代码有太多愚蠢的错误，我已经纠正了它们。仔细观察变化。

包

CREATE OR REPLACE PACKAGE cursor_state IS
CURSOR emp_curs IS
SELECT e.first_name, e.last_name, d.department_name, e.salary
FROM employees e join departments d
ON e.department_id = d.department_id;
PROCEDURE open_curs;
FUNCTION fetch_rows(n NUMBER DEFAULT 1) RETURN BOOLEAN;
PROCEDURE close_curs;
END cursor_state;
/

封装正文

CREATE OR REPLACE PACKAGE BODY cursor_state IS
PROCEDURE open_curs IS
BEGIN
IF NOT emp_curs%isopen THEN
OPEN emp_curs;
END IF;
END open_curs;
FUNCTION  fetch_rows(n NUMBER DEFAULT 1) RETURN BOOLEAN IS
emp_first employees.first_name%TYPE;
emp_last employees.last_name%TYPE;
emp_dept departments.department_name%TYPE;
emp_salary employees.salary%TYPE;
BEGIN
FOR count IN 1 .. n LOOP  
FETCH emp_curs INTO
emp_first,emp_last,emp_dept,emp_salary;
EXIT WHEN emp_curs%notfound;
dbms_output.put_line(emp_first||','
||emp_last
||','
||emp_dept
||','
||emp_salary);
END LOOP;
RETURN emp_curs%found;
END fetch_rows;
PROCEDURE close_curs IS
BEGIN
IF emp_curs%isopen THEN
CLOSE emp_curs;
END IF;
END close_curs;
END cursor_state;
/

执行

SET SERVEROUTPUT ON
DECLARE
x BOOLEAN;
BEGIN
cursor_state.open_curs;
x:=cursor_state.fetch_rows(5);
cursor_state.close_curs;
END;
/

结果

Jennifer,Whalen,Administration,4400
Michael,Hartstein,Marketing,13000
Pat,Fay,Marketing,6000
Den,Raphaely,Purchasing,11000
Karen,Colmenares,Purchasing,2500

PL/SQL procedure successfully completed.

Answer 2

在此处使用分号(并使用部门类型，因为您将员工表与部门表联接)：

emp_first employees.first_name%TYPE;
emp_last employees.last_name%TYPE;
emp_dept departments.department_name%TYPE; 
emp_salary employees.salary%TYPE;

使用管道符号连接：

DBMS_OUTPUT.PUT_LINE(emp_first || ' ' || emp_last || ' ' || emp_dept || ' ' || emp_salary);

在包定义中定义与包正文中相同的参数：

FUNCTION fetch_rows(n NUMBER := 1) RETURN BOOLEAN;

然后尝试您的代码：

SET SERVEROUTPUT ON
DECLARE
l_ret BOOLEAN;
BEGIN
cursor_state.open_curs;
l_ret := cursor_state.fetch_rows(2);
cursor_state.close_curs;
END;
/

示例输出：

Steven King Executive 24000
Neena Kochhar Executive 17000
PL/SQL procedure successfully completed.