Oracle/PL SQl:将查询结果传递给另一个更新

Question

嗨，我正试图基于一个查询结果使用PL/SQL进行更新。存储结果并将此结果发送到更新查询，但得到

ORA-06550: line 2, column 24: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: := ( ; not null range default character

表：

create table person(id number(10),stat char(1));
insert into person(id,stat)values(123,'Y');
insert into person(id,stat)values(123,'Y');
insert into person(id,stat)values(345,'Y');
commit;

查询：

DECLARE
deptid  person.id%TYPE;
BEGIN
SELECT id INTO deptid 
FROM person WHERE id = 345;
IF SQL%FOUND THEN 
Update person set stat='N' where id=deptid;
commit;
DBMS_OUTPUT.PUT_LINE('Dept Id: ' || deptid ||);
END IF;
END;
/

我在这里做错了什么？

Answer 1

在UPDATE:中使用RETURNING子句

DECLARE
deptid  person.id%TYPE;
BEGIN
UPDATE person
SET    stat = 'N'
WHERE  id = 123
RETURNING MAX(id) INTO deptid;

DBMS_OUTPUT.PUT_LINE('Dept Id: ' || deptid);
END;
/

对于您的测试数据：

create table person(id,stat) AS
SELECT 123, 'Y' FROM DUAL UNION ALL
SELECT 123, 'Y' FROM DUAL UNION ALL
SELECT 345, 'Y' FROM DUAL;

然后PL/SQL块输出：

Dept Id: 123

该表包含以下值：

ID|STAT--：|：---123|N123|N345|Y

db<gt；小提琴这里

---

如果你真的想使用SELECT语句，那么你需要确保它只返回一行：

DECLARE
deptid  person.id%TYPE;
BEGIN
SELECT id
INTO   deptid
FROM   person
WHERE  id = 123
AND    ROWNUM = 1; -- FETCH FIRST ROW ONLY; 
UPDATE person
SET    stat = 'N'
WHERE  id = deptid;

DBMS_OUTPUT.PUT_LINE('Dept Id: ' || deptid);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE( 'No Dept Id!' );
END;
/

db<gt；小提琴这里

Answer 2

您作为示例发布的查询出现错误，导致代码失败。请删除dbms_output命令中deptid之后和"("之前的多余管道。

DBMS_OUTPUT.PUT_LINE('Dept Id: ' || deptid);