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.
2018-09-13, 653👍, 0💬
Popular Posts:
How To Concatenate Two Text Values in Oracle? There are two ways to concatenate two text values toge...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...
How To Provide Values to Stored Procedure Parameters in SQL Server Transact-SQL? If a stored procedu...
How To Install PHP on Windows in MySQL? The best way to download and install PHP on Windows systems ...
How To Enter Unicode Character String Literals in SQL Server Transact-SQL? Unicode characters are mu...