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, 1415🔥, 0💬
Popular Posts:
What is dba.FYIcenter.com Website about? dba.FYIcenter.com is a Website for DBAs (database administr...
Where to find answers to frequently asked questions on CREATE, ALTER and DROP Statements in MySQL? H...
What To Do If the StartDB.bat Failed to Start the XE Instance in Oracle? If StartDB.bat failed to st...
How To Start Instance with a Minimal Initialization Parameter File in Oracle? The sample initializat...
How To Convert Characters to Numbers in Oracle? You can convert characters to numbers by using the T...