Examples Using RETURNING INTO

This section includes examples using the RETURNING INTO clause.

• Using the RETURNING INTO Clause with a Record

• Using BULK COLLECT INTO with the RETURNING INTO Clause

See RETURNING INTO Clause for an overview.

Using the RETURNING INTO Clause with a Record

The following example uses ttIsql to run a SQL script that uses a RETURNING INTO clause to return data into a record. The example gives a raise to a specified employee, returns the employee's name and new salary into a record, then outputs the data from the record. For reference, the original salary is shown before running the script.

Command> SELECT SALARY,LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = 100;
< 24000, King >
1 row found.

Command> run ReturnIntoWithRecord.sql;
 
CREATE TABLE emp_temp AS SELECT * FROM employees;
107 rows inserted.
 
DECLARE
   TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE,
                          salary employees.salary%TYPE);
   emp_info EmpRec;
   emp_id NUMBER := 100;
BEGIN
   UPDATE emp_temp SET salary = salary * 1.1
      WHERE employee_id = emp_id
      RETURNING last_name, salary INTO emp_info;
   DBMS_OUTPUT.PUT_LINE
      ('Just gave a raise to ' || emp_info.last_name ||
       ', who now makes ' || emp_info.salary);
   ROLLBACK;
END;
/
 
Just gave a raise to King, who now makes 26400
 
PL/SQL procedure successfully completed.

Using BULK COLLECT INTO with the RETURNING INTO Clause

The following example uses ttIsql to run a SQL script that uses a RETURNING INTO clause with BULK COLLECT to return data into nested tables, a type of PL/SQL collection.

The example deletes all the employees from a specified department, then, using one nested table for employee IDs and one for last names, outputs the employee ID and last name of each deleted employee. For reference, the IDs and last names of employees in the department are also displayed before execution of the script.

Command> select employee_id, last_name from employees where department_id=30;
< 114, Raphaely >
< 115, Khoo >
< 116, Baida >
< 117, Tobias >
< 118, Himuro >
< 119, Colmenares >
6 rows found.
Command> run ReturnIntoWithBulkCollect.sql;
 
CREATE TABLE emp_temp AS SELECT * FROM employees;
107 rows inserted.
 
DECLARE
   TYPE NumList IS TABLE OF employees.employee_id%TYPE;
   enums NumList;
   TYPE NameList IS TABLE OF employees.last_name%TYPE;
   names NameList;
BEGIN
   DELETE FROM emp_temp WHERE department_id = 30
     RETURNING employee_id, last_name
     BULK COLLECT INTO enums, names;
   DBMS_OUTPUT.PUT_LINE
      ('Deleted ' || SQL%ROWCOUNT || ' rows:');
   FOR i IN enums.FIRST .. enums.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE
         ('Employee #' || enums(i) || ': ' || names(i));
   END LOOP;
END;
/
Deleted 6 rows:
Employee #114: Raphaely
Employee #115: Khoo
Employee #116: Baida
Employee #117: Tobias
Employee #118: Himuro
Employee #119: Colmenares
 
PL/SQL procedure successfully completed.