Functions in Oracle (PL/SQL)

The basic syntax for creating a function in Oracle is as follows:

CREATE [OR REPLACE] FUNCTION function_name
  (parameter1 [IN | OUT | IN OUT] data_type,
   parameter2 [IN | OUT | IN OUT] data_type,
   ...
   parameterN [IN | OUT | IN OUT] data_type)
RETURN return_data_type
IS
  local_variable1 data_type;
  local_variable2 data_type;
  ...
  local_variableN data_type;
BEGIN
  -- Function logic goes here
  RETURN return_value;
END;

Let's break down the syntax:

• The CREATE [OR REPLACE] FUNCTION statement is used to create a new function or replace an existing one.

• function_name is the name of the function you want to create.

• parameter1, parameter2, etc. are the input parameters for the function. You can specify whether each parameter is IN, OUT or IN OUT (default is IN). The data type of each parameter should be specified as well.

• return_data_type is the data type of the value returned by the function.

• IS is used to begin the function's declaration block.

• local_variable1, local_variable2, etc. are local variables that can be used within the function. They must be declared with a data type.

• The function's logic goes in the block between the BEGIN and END statements.

• RETURN is used to return the value of the function. The data type of the value must match the return_data_type specified earlier.

That's the basic syntax for creating a function in Oracle. Once you've defined the function, you can call it in other parts of your code, just like any other function.

Once the function is created, now we need to call it:

DECLARE
  -- Declare variables and other program objects here
BEGIN
  -- PL/SQL code goes here
END;

Let's break down the syntax:

• The DECLARE keyword is used to begin the declaration section of the PL/SQL block.

• Variables and program objects are declared between the DECLARE and BEGIN keywords.

• Each variable or program object must be declared with a name and a data type.

• Multiple variables can be declared using a comma-separated list.

• After declaring the variables and program objects, the BEGIN keyword is used to begin the execution section of the PL/SQL block.

• The PL/SQL code that performs the desired functionality goes between the BEGIN and END keywords.

• The END keyword is used to terminate the PL/SQL block.

Note that the DECLARE block is optional in PL/SQL programs. If no variables or program objects need to be declared, you can simply begin with the BEGIN keyword. However, if you need to declare variables or program objects, you must use the DECLARE keyword to begin the declaration section.

We can also use declaration and function creation in single code, that is shown in example section.

Example:

Function to add two numbers:

Code:

declare
a number;
b number;
c number;
function adder(x in number, y in number)
return number is 
s number;
begin
s:= x+y;
return s;
end;
begin
a :=:a;
b :=:b;
c := adder(a,b);
dbms_output.put_line('Sum of ' || a || ' and ' || b || ' is: '|| c);
end;

This is a PL/SQL program that declares three variables (a, b, and c) and a function (adder).

The adder function takes two input parameters (x and y) and returns their sum as the output. This is achieved by declaring a local variable s, adding x and y, and then returning the value of s.

After declaring the variables and the function, the program sets the values of a and b using the := assignment operator. The adder function is called with a and b as input parameters, and the result is stored in the variable c.

Finally, the program uses the DBMS_OUTPUT.PUT_LINE procedure to display a message on the console. The message includes the values of a, b, and c using concatenation (||) to join the different pieces of text. The result is a message that displays the sum of a and b.