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,他告诉我有语法错误(仅此而已)。
我没有看到语法错误。有人能帮助我吗?我会很感激的。
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
语句:fiddle