PL/SQL functions

Question

I have to implement the classic number swap program using functions in PL/SQL (Receive two number values from user and display them swapped). However, I don't seem to be able to return two numbers using functions in PL/SQL. I wanted to know if there's any way I can return two values from a function in PL/SQL or if there's some other way to write this code altogether?

SET SERVEROUTPUT ON;

DECLARE 
a NUMBER;
b NUMBER;

FUNCTION numSwap(num1 IN OUT NUMBER ,num2 IN OUT NUMBER )IS 
temp_num NUMBER;

BEGIN

temp_num := num1;
num1 := num2;
num2 := temp;
END;

BEGIN

a := &a;
b := &b;

DBMS_OUTPUT.PUT_LINE('First Number = ' || a);
DBMS_OUTPUT.PUT_LINE('Second Number = ' || b);

--After swapping values 
DBMS_OUTPUT.PUT_LINE('After swapping the values');
numSwap(a,b);

--Displaying the results
DBMS_OUTPUT.PUT_LINE('First Number = ' || a);
DBMS_OUTPUT.PUT_LINE('Second Number = ' || b);

END;

EDIT: To clarify, I realize that this is more or less the syntax for a procedure, But I wanted to know if there's any way to execute this same program using Functions

Answer 1

Elaborating on MT0's example...

A function returns only one value. You could solve it by using a record (or possibly a collection) that holds two values. Please note that the record type is only declared inside the scope of the PLSQL block.

declare

   a number;
   b number;
   type two_numbers is record(
      a number,
      b number);

  l_two_numbers two_numbers;

   function swap(p_two_numbers two_numbers) return two_numbers is
    l_return two_numbers;
   begin
      l_return.a:=p_two_numbers.b;
      l_return.b:=p_two_numbers.a;

      return l_return;
   end;

begin
   a := &a;
   b := &b;


   l_two_numbers.a:=a;
   l_two_numbers.b:=b;

   dbms_output.put_line('First Number = ' || l_two_numbers.a);
   dbms_output.put_line('Second Number = ' || l_two_numbers.b);

   l_two_numbers := swap(l_two_numbers);

   dbms_output.put_line('First Number = ' || l_two_numbers.a);
   dbms_output.put_line('Second Number = ' || l_two_numbers.b);
end;

Answer 2

Use a procedure:

DECLARE 
  a NUMBER;
  b NUMBER;
  PROCEDURE numSwap(num1 IN OUT NUMBER ,num2 IN OUT NUMBER )
  IS 
    temp_num NUMBER := num1;
  BEGIN
    num1 := num2;
    num2 := temp_num;
  END;
BEGIN
  a := &a;
  b := &b;

  DBMS_OUTPUT.PUT_LINE('First Number = ' || a);
  DBMS_OUTPUT.PUT_LINE('Second Number = ' || b);

  numSwap(a,b);

  DBMS_OUTPUT.PUT_LINE('First Number = ' || a);
  DBMS_OUTPUT.PUT_LINE('Second Number = ' || b);
END;
/

Answer 3

You can use an oracle type if you want to still call the a function (if you need to use in SELECT: Try the below

set serveroutput on

create or replace type t_swap
is object(first_number number, second_number number);
/

create or replace
function f_num_swap(p_first_number in number, p_second_number in number) return t_swap
is
  v_swap t_swap;
begin
  v_swap := t_swap(p_second_number, p_first_number);
  return v_swap;
end;
/

-- You can access from select
select f_num_swap(1, 2).first_number, f_num_swap(1, 2).second_number from dual;

-- Or you can access from PL/SQL block
declare
  v_swap t_swap;
begin
  v_swap := f_num_swap(1, 2);
  dbms_output.put_line('First Nubmer Swapped: '||v_swap.first_number);
  dbms_output.put_line('Second Nubmer Swapped: '||v_swap.second_number);
end;
/