创建 Oracle 包正文时出错 [英] Errors creating Oracle package body

问题描述

我的包中有以下主体，用于在 HR 模式中执行一些 CRUD 操作:

I have the following body from my package, used to do some CRUD operations in HR schema:

CREATE SEQUENCE emp_sequence;

CREATE OR REPLACE PACKAGE BODY employee_crud AS

   PROCEDURE create_emp(p_last_name IN employees.last_name%type, p_first_name IN    employees.first_name%type, 
                             p_email IN employees.email%type, p_hire_date IN  employees.hire_date%type, 
                             p_job_id IN employees.job_id%type) AS


   BEGIN
     SELECT emp_sequence.NEXTVAL INTO id FROM dual;
     INSERT INTO employees(last_name, first_name, email, hire_date, job_id)
     VALUES (emp_seq.nextval, p_last_name, p_first_name, p_email, p_hire_date, p_job_id);
   EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
     dbms_output.put_line('error');
   END create_emp;


   PROCEDURE erase_emp(p_employee_id IN employees.employee_id%type) AS
   BEGIN
     DELETE FROM employees
     WHERE employee_id = p_employee_id;
   EXCEPTION
   WHEN  NO_DATA_FOUND THEN
     dbms_output.put_line('Error');
   END erase_emp;


   PROCEDURE upd_emp(p_employee_id IN employees.employee_id%type, p_salary IN  employees.salary%type, 
                                  p_email IN employees.email%type, p_department_id IN  employees.department_id%type) AS
   BEGIN
     UPDATE employees
     SET  employee_id = p_employee_id,
          salary = p_salary,
          email = p_email,
          department_id = p_department_id
     WHERE employee_id = p_employee_id;
   EXCEPTION
   WHEN NO_DATA_FOUND THEN
     dbms_output.put_line('error');
   END upd_emp;


   PROCEDURE read_emp(p_employee_id IN employees.employee_id%type, p_last_name OUT  employees.last_name%type, 
                                 p_first_name OUT employees.first_name%type, p_email OUT  employees.email%type, 
                                 p_hire_date OUT  employees.hire_date%type, p_job_id OUT  employees.job_id%type, 
                                 p_salary OUT employees.salary%type) AS
   BEGIN
     SELECT employee_id, last_name, first_name, email, hire_date, job_id, salary
     INTO  p_last_name, p_first_name, p_email, p_hire_date, p_job_id, p_salary
     FROM EMPLOYEES
     WHERE employee_id = p_employee_id;
   EXCEPTION
   WHEN NO_DATA_FOUND THEN
     dbms_output.put_line('Error');
   END read_emp;

END employee_crud;
/

创建规范(这是正确的)后，我创建了一个序列来帮助添加员工.但是编译包体，却显示如下错误:

After creating the specification (which is correct), I create a sequence to help adding employees. But compiling the body of the package, it shows the following errors:

LINE/COL ERROR
-------- ----------------------------------------------------
10/5     PL/SQL: SQL Statement ignored
10/38    PLS-00201: 'ID' must be declared
10/41    PL/SQL: ORA-00904: : identifier is not valid
11/5     PL/SQL: SQL Statement ignored
11/17    PL/SQL: ORA-00913: too many values
49/5     PL/SQL: SQL Statement ignored
51/5     PL/SQL: ORA-00947: insufficient values

我应该怎么做才能更正这些错误?我从我的代码中更改了一些名称，因为这是一项任务，我在尝试不出错的情况下遇到了麻烦.

What should I do to correct these errors? I changed some names from my code because it's an assignment and I having troubles trying to do it without errors.

推荐答案

10/38    PLS-00201: 'ID' must be declared

您正在将数据选择到尚未声明的局部变量 ID 中.如果你想声明一个局部变量，你可以在 AS 和 BEGIN

You're selecting data into a local variable ID that hasn't been declared. If you want to declare a local variable, you'd do so in the declaration section between the AS and the BEGIN

   PROCEDURE create_emp(p_last_name IN employees.last_name%type, 
                        p_first_name IN    employees.first_name%type, 
                        p_email IN employees.email%type, 
                        p_hire_date IN  employees.hire_date%type, 
                        p_job_id IN employees.job_id%type) 
   AS
     id integer;
   BEGIN
     SELECT emp_sequence.NEXTVAL INTO id FROM dual;

如果您打算这样做，您需要在 INSERT 语句中使用局部变量 id 而不是调用 emp_sequence.nextval 直接.不过，就我个人而言，我会去掉局部变量，去掉最初的 SELECT，然后在 INSERT<中调用 emp_sequence.nextval/code> 语句.

If you're going to do that, you'd want to use the local variable id in your INSERT statement rather than calling emp_sequence.nextval directly. Personally, though, I'd get rid of the local variable, get rid of the initial SELECT, and just make the emp_sequence.nextval call in your INSERT statement.

11/17    PL/SQL: ORA-00913: too many values

无论您如何操作，您都需要 INSERT 中的列数与您指定的 VALUES 的数量相匹配.

Regardless of how you do it, though, you'd need the number of columns in your INSERT to match the number of VALUES you specify.

   PROCEDURE create_emp(p_last_name IN employees.last_name%type, 
                        p_first_name IN    employees.first_name%type, 
                        p_email IN employees.email%type, 
                        p_hire_date IN  employees.hire_date%type, 
                        p_job_id IN employees.job_id%type) 
   AS
     id integer;
   BEGIN
     SELECT emp_sequence.NEXTVAL INTO id FROM dual;
     INSERT INTO employees(employee_id, last_name, first_name, email, hire_date, job_id)
       VALUES (id, p_last_name, p_first_name, p_email, p_hire_date, p_job_id);
  END create_emp;

或者如果你想直接调用序列

Or if you want to call the sequence directly

   PROCEDURE create_emp(p_last_name IN employees.last_name%type, 
                        p_first_name IN    employees.first_name%type, 
                        p_email IN employees.email%type, 
                        p_hire_date IN  employees.hire_date%type, 
                        p_job_id IN employees.job_id%type) 
   AS
   BEGIN
     INSERT INTO employees(employee_id, last_name, first_name, email, hire_date, job_id)
       VALUES ( emp_sequence.NEXTVAL, p_last_name, p_first_name, p_email, p_hire_date, p_job_id);
  END create_emp;

同样，对于您的下一个错误，您提取的变量数应与您选择的列数相匹配

Similarly, for your next error, the number of variables that you fetch into should match the number of columns that you're selecting

51/5     PL/SQL: ORA-00947: insufficient values

在您的 read_emp 中，您选择了 7 项内容并尝试将它们放入 6 个变量中.假设您不想返回 employee_id，不要费心选择它.

In your read_emp, you're selecting 7 things and trying to put them into 6 variables. Assuming that you don't want to return the employee_id, don't bother selecting it.

   PROCEDURE read_emp(p_employee_id IN employees.employee_id%type, 
                      p_last_name OUT  employees.last_name%type, 
                      p_first_name OUT employees.first_name%type, 
                      p_email OUT  employees.email%type, 
                      p_hire_date OUT  employees.hire_date%type, 
                      p_job_id OUT  employees.job_id%type, 
                      p_salary OUT employees.salary%type) 
   AS
   BEGIN
     SELECT last_name, first_name, email, hire_date, job_id, salary
     INTO  p_last_name, p_first_name, p_email, p_hire_date, p_job_id, p_salary
     FROM EMPLOYEES
     WHERE employee_id = p_employee_id;
   END read_emp;

虽然您可以编写这样的 read_emp 过程，但通常创建一个返回 employees%rowtype 记录的函数会更有意义.

While you can write a read_emp procedure like this, it would generally make more sense to create a function that returns an employees%rowtype record instead.

您的例外条款应该被删除.充其量，他们正在丢弃错误堆栈，该堆栈会告诉人们失败的原因和位置.最坏的情况是，它们隐藏了错误(您永远不应该假设任何人都会看到您写入 dbms_output 的任何内容)，并导致调用代码相信某些操作成功了而没有成功.

Your exception clauses should be removed. At best, they are discarding the error stack that would tell a person what failed and where. At worst, they're hiding the errors (you should never assume that anyone will ever see anything that you write to dbms_output) and causing the calling code to believe that some operation succeeded when it didn't.

这篇关于创建 Oracle 包正文时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！