Collections:
Assign Data from a Deleted Row to Variables in Oracle
How To Assign Data of the Deleted Row to Variables in Oracle?
✍: FYIcenter.com
If a DELETE statement is deleting a single row, you can assign column values of the deleted row to variables by using the RETURNING clause, which an extension of DELETE statements for PL/SQL. The tutorial script shows you how to do this:
CREATE TABLE emp_temp AS SELECT * FROM employees; DECLARE fname VARCHAR2(10); lname VARCHAR2(10); BEGIN DELETE FROM emp_temp WHERE employee_id = 100; RETURNING first_name, last_name INTO fname, lname; DBMS_OUTPUT.PUT_LINE('Name deleted = ' || fname || ' ' || lname); -- This will not work because multiple rows deleted -- DELETE FROM emp_temp WHERE employee_id > 100; -- RETURNING first_name, last_name INTO fname, lname; END; / Name deleted = Steven King
Similar to SELECT ... INTO, RETURNING ... INTO will not work if multiple rows are deleted.
⇒ What Is a RECORD in PL/SQL in Oracle
2018-09-13, 1405🔥, 0💬
Popular Posts:
What Is Oracle in Oracle? Oracle is a company. Oracle is also a database server, which manages data ...
How To Divide Query Output into Multiple Groups with the GROUP BY Clause in SQL Server? Sometimes, y...
How To Drop an Index in Oracle? If you don't need an existing index any more, you should delete it w...
Where to find answers to frequently asked questions on Transaction Management: Commit or Rollback in...
How to execute statements under given conditions in SQL Server Transact-SQL? How to use IF ... ELSE ...