SQL Update, Insert and Delete manipulate data in the database. You need to query data set again to see what changed by these commands.



What if you do not have to query the database again? What if update, insert and delete statement returns the records manipulated?

Good feature right let’s see how to use Oracle PL SQL Returning Into clause.

As per Oracle,

The returning clause specifies the values return from DELETE, EXECUTE IMMEDIATE, INSERT, and UPDATE statements. You can retrieve the column values into individual variables or into collections. You cannot use the RETURNING clause for remote or parallel deletes. If the statement does not affect any rows, the values of the variables specified in the RETURNING clause are undefined

Let’s create table test_return_into to demonstrate the working of this feature.

CREATE TABLE test_return_into
(
name VARCHAR2(30),
city VARCHAR2(10)
);

Please note local variable selection is important here. If insert, update and delete are going to return more that one number of records, use collection type of records, else it will throw below exception,

ORA-01422: exact fetch returns more than requested number of rows

Example 1:- Insert with Oracle PL\SQL returning into

Let’s insert a record in table and capture inserted values in local variables.

SET serveroutput ON
DECLARE
l_name test_return_into.name%TYPE;
l_city test_return_into.city%TYPE;
BEGIN
INSERT
INTO
test_return_into
(
name,
city
)
VALUES
(
'John',
'New York'
)
RETURNING
name,
city
INTO
l_name,
l_city;
dbms_output.put_line('') ;
dbms_output.put_line('Name :- ' ||l_name ) ;
dbms_output.put_line('City :- ' || l_city) ;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unexpected Error :- ' || sqlerrm);
END;
/
Name :- John                                                                    
City :- New York                                                                
PL/SQL procedure successfully completed.
SQL> select * From test_return_into;
NAME                           CITY
------------------------------ ----------
John                           New York
1 row selected.

Example 2:- Update with Oracle PL\SQL returning into

Update returns newly updated values in the table.

SET serveroutput ON
DECLARE
l_name test_return_into.name%TYPE;
l_city test_return_into.city%TYPE;
BEGIN
UPDATE test_return_into
SET city = 'New Jersey'
WHERE name = 'John'
RETURNING
name,
city
INTO
l_name,
l_city;
dbms_output.put_line('') ;
dbms_output.put_line('Name :- ' ||l_name ) ;
dbms_output.put_line('City :- ' || l_city) ;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unexpected Error :- ' || sqlerrm);
END;
/
Name :- John                                                                    
City :- New Jersey                                                              
PL/SQL procedure successfully completed.
SQL> SELECT * FROM test_return_into;
NAME                           CITY                                             
------------------------------ ----------                                       
John                           New Jersey                                       
1 row selected.

Example 3:- Delete with Oracle PL\SQL returning into

SET serveroutput ON
DECLARE
l_name test_return_into.name%TYPE;
l_city test_return_into.city%TYPE;
BEGIN
DELETE
test_return_into
WHERE
name = 'John' RETURNING name,
city
INTO
l_name,
l_city;
dbms_output.put_line('') ;
dbms_output.put_line('Name :- ' ||l_name ) ;
dbms_output.put_line('City :- ' || l_city) ;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unexpected Error :- ' || sqlerrm);
END;
/
Name :- John                                                                    
City :- New Jersey                                                              
PL/SQL procedure successfully completed.
SQL> SELECT * FROM test_return_into;
no rows selected

Reference:

Oracle PL SQL Returning Into
Tagged on:

att

I am Oracle Certified Associate(OCA) working in Oracle Domain since last 15 Years. Started this blog to share knowledge about different Technologies mostly focussing on Oracle.