在Oracle存储过程中使用DECLARE会导致语法错误

bvjxkvbb 于 2022-11-03 发布在 Oracle

关注(0)|答案(1)|浏览(355)

create or replace PROCEDURE "SYNC_EMPLOYEES_WITH_HR" (
  EMPLOYEE_NUMBER IN NUMBER
) AS

Declare middle_initial varchar(200);
Declare first_initial varchar(200);
Declare last_initial varchar(200);
Declare TERMINATION_DATE Date;
Declare network_Id varchar(200);

BEGIN    

    select @first_initial = first_name,@middle_initial =middle_initial,@last_initial =last_name, @network_Id = network_id, @TERMINATION_DATE = termination_date from human_resources where employee_number = EMPLOYEE_NUMBER;

    update Employee set first_name = @first_initial,middle_initial = @middle_initial,last_name = @last_initial,user_id = @network_Id, inactive_date = @TERMINATION_DATE where employee_number = EMPLOYEE_NUMBER;

END

我没有访问Oracle PL/SQL的权限，因此我需要将代码交给DBA，他告诉我有语法错误（仅此而已）。
我没有看到语法错误。有人能帮助我吗？我会很感激的。

oracle

来源：https://stackoverflow.com/questions/74131058/using-declare-in-stored-procedure-for-oracle-results-in-syntax-error

1条答案

按热度按时间

soat7uwm1#

您的过程在Oracle中的语法无效，如下所示：

DECLARE开始PL/SQL块的变量声明部分（不在每个变量之前使用）;
@不是变量的有效前缀。
您需要使用SELECT value1, value2 INTO variable1, variable2 FROM ...，而不是SELECT variable1 = column1, variable2 = column2 FROM ...。
where employee_number = EMPLOYEE_NUMBER类似于WHERE 1 = 1，因为employee_number是从操作两侧的本地SQL作用域中获取的，而不是一个来自本地SQL作用域，另一个来自外部PL/SQL作用域;您需要将PL/SQL变量命名为与列名不同的名称。

您可以将其简化为一条MERGE语句：

CREATE PROCEDURE SYNC_EMPLOYEES_WITH_HR (
  P_EMPLOYEE_NUMBER IN EMPLOYEE.EMPLOYEE_NUMBER%TYPE
) AS
BEGIN    
  MERGE INTO Employee dst
  USING (
    SELECT employee_number,
           first_name,
           middle_initial,
           last_name,
           network_id, 
           termination_date
    FROM   human_resources
    WHERE  employee_number = P_EMPLOYEE_NUMBER
  ) src
  ON (dst.employee_number = src.employee_number)
  WHEN MATCHED THEN
    UPDATE 
    SET first_name       = src.first_name,
        middle_initial   = src.middle_initial,
        last_name        = src.last_name,
        network_id       = src.network_id, 
        termination_date = src.termination_date;
END;
/

fiddle

赞(0）回复(0）举报 2022-11-03

我来回答

在Oracle存储过程中使用DECLARE会导致语法错误

1条答案

相关问题

热门标签

最新问答