行/列： 2/10 PLS-00103：预期出现以下情况之一时遇到符号"JOBS_PKG"：

Question

我有这个包规范和正文:

CREATE OR REPLACE PACKAGE jobs_pkg
IS
PROCEDURE initialize;

FUNCTION get_minsalary(p_jobid VARCHAR2) RETURN NUMBER;

FUNCTION get_maxsalary(p_jobid VARCHAR2) RETURN NUMBER;

PROCEDURE set_minsalary(p_jobid VARCHAR2, pmin_salary NUMBER);

PROCEDURE set_maxsalary(p_jobid VARCHAR2, pmax_salary NUMBER);

END jobs_pkg;
------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY jobs_pkg
IS
TYPE jobs_tab_type IS TABLE OF jobs%rowtype INDEX BY jobs.job_id%type;
jobstab jobs_tab_type;
PROCEDURE initialize
IS
BEGIN
FOR rec IN (SELECT * FROM jobs)
LOOP
jobstab(rec.job_id) := rec;
END LOOP;
END;

FUNCTION get_minsalary(p_jobid VARCHAR2) 
RETURN NUMBER
IS
vmin_salary jobs.min_salary%type;
BEGIN
SELECT min_salary
INTO vmin_salary
FROM jobs
WHERE job_id = jobstab(p_jobid).job_id;

RETURN vmin_salary;
END get_minsalary;

FUNCTION get_maxsalary(p_jobid VARCHAR2) 
RETURN NUMBER
IS  vmax_salary jobs.max_salary%type;
BEGIN
SELECT max_salary
INTO vmax_salary
FROM jobs
WHERE job_id =  jobstab(p_jobid).job_id;

RETURN vmax_salary;
END get_maxsalary;

PROCEDURE set_minsalary(p_jobid VARCHAR2, pmin_salary NUMBER)
IS
BEGIN
UPDATE jobs
SET min_salary = pmin_salary
WHERE job_id = jobstab(p_jobid).job_id;
END set_minsalary;

PROCEDURE set_maxsalary(p_jobid VARCHAR2, pmax_salary NUMBER)
IS
BEGIN
UPDATE jobs
SET max_salary = pmax_salary
WHERE job_id = jobstab(p_jobid).job_id;
END set_maxsalary;

END jobs_pkg;

问题是，我需要实现之前插入或更新语句触发器使用调用语法来调用jobs_pkg。在执行DML操作之前，初始化过程以确保包状态是当前的，但是当我试图编译我的触发器时遇到了这个问题:Line/Col: 2/10 PLS-00103: Encountered the symbol "JOBS_PKG"

这是我的触发器:

CREATE OR REPLACE TRIGGER init_jobpkg_trg
BEFORE UPDATE OR INSERT ON jobs
FOR EACH ROW
BEGIN
CALL jobs_pkg.initialize;
END init_jobpkg_trg;

任何帮助将不胜感激。谢谢!

Answer 1

要从另一个pl/sql块(过程/函数/触发器/匿名块)中调用过程，您不需要EXEC或CALL关键字。只是"package.procedure;"会做。

CREATE OR REPLACE TRIGGER init_jobpkg_trg
BEFORE UPDATE OR INSERT ON jobs
FOR EACH ROW
BEGIN
jobs_pkg.initialize;
END init_jobpkg_trg;

请注意，您很可能会遇到其他问题。当更新表时，触发器处于开启状态，您可能会遇到MUTATING TABLE错误，因为您正在进入一个无限循环。因为当你更新表时，更新会触发触发器。等等。

Answer 2

CALL不是一个可识别的PL/SQL命令。相反，要调用过程，只需通过名称引用它。这意味着，基本上，你只需要删除CALL，像这样:

CREATE OR REPLACE TRIGGER init_jobpkg_trg
BEFORE UPDATE OR INSERT ON jobs
FOR EACH ROW
BEGIN
jobs_pkg.initialize;
END init_jobpkg_trg;
/

注意:你会注意到我在PL/SQL的末尾添加了一个'/' -尽管这个字符不需要一些(大多数?)对于像Toad或PL/SQL Developer这样的gui，最好总是在PL/SQL块之后添加它，因为SQL*Plus需要它，而缺少它会在运行脚本时导致问题。你也应该在你的CREATE OR REPLACE package [body] ...块后面添加一个。