SQL Reference - FUNCTION

CREATE FUNCTION function_name ([parameter datatype,…]) RETURNS datatype [LANGUAGE {SQL | language_name}] [DETERMINISTIC | NOT DETERMINISTIC] [SQL DATA ACCESS {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}] [COMMENT ‘string’] BEGIN function_body; END;

function_name: The name of the function to be created.
parameter datatype: A comma separated list of the parameters that the function will use, each followed by its datatype.
returns datatype: The datatype of the value that the function will return.
language {sql | language_name}: Defines the language that the function is written in. Default is SQL.
deterministic | not deterministic: Specifies whether the function always produces the same result for the same input parameters.
sql data access {contains sql | no sql | reads sql data | modifies sql data}: Determines the type of SQL statements that can be included in the function.
comment ‘string’: An optional parameter that allows you to add a descriptive comment to the function.
begin function_body; end: The body of the function which contains the SQL statements to be executed.

Example

CREATE FUNCTION SquareArea(side_length INT)
RETURNS INT
DETERMINISTIC
BEGIN
   RETURN side_length * side_length;
END;
```sql
SELECT SquareArea(5);

Output

Explanation

In the given example, a function named SquareArea is created which calculates the area of a square. The function accepts integer side_length as an argument and returns the area by multiplying the side length with itself.

The function is then called by passing 5 as the length of the side, resulting in an output of 25.

CREATE [ OR REPLACE ] FUNCTION function_name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, …] ] ) [ RETURNS rettype | RETURNS TABLE ( column_name column_type [, …] ) ] [ LANGUAGE { SQL | language_name } ] [ TRANSFORM { FOR TYPE type_name } [, …] ] [ WINDOW ] [ VOLATILE | STABLE | IMMUTABLE ] [ CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT ] [ SECURITY { INVOKER | DEFINER } ] [ COST execution_cost ] [ ROWS result_rows ] [ SET configuration_parameter { TO value | = value | FROM CURRENT } ] [ PARALLEL { UNSAFE | RESTRICTED | SAFE } ] [ AS ‘definition’ ] [ AS ‘obj_file’, ‘link_symbol’ ]

or replace: This parameter determines whether the function should be replaced if it exists already.
function: Determines the action to be implemented.
function_name: The unique identifier or name of the function.
argmode: Determines the mode of argument which could be IN, OUT, INOUT, and VARIADIC.
argname: Defines the argument or parameter name used in the function.
argtype: Specifies the data type of an argument.
default | = default_expr: Sets a default value for an argument if no value is supplied.
returns rettype: Describes the data type that the function will return.
returns table(column_name column_type [, …]): Indicates that the function returns a set of rows (a table); where column_name and column_type define the returned table.
language {sql | language_name}: Defines the programming language that the function is implemented in.
transform {for type type_name} [, …]: Provides a specific transformation for a type, used for SQL functions.
window: Specifies that the function is a window function.
volatile | stable | immutable: These are the volatility classifications which states how the function behave when dealing with the database.
called on null input | returns null on null input | strict: Specifies the behavior of the function when null value is passed to the function.
security {invoker | definer}: Determines the security model that the function will operate under.
cost execution_cost: Gives the planner an estimated cost to execute the function.
rows result_rows: Gives the planner an estimated number of rows the function will return.
set configuration_parameter {to value | = value | from current}: Changes the value of all per-session configuration parameters listed.
parallel {unsafe | restricted | safe}: This states if the function can be run in parallel mode or not.
as ‘definition’: Contains a string constant defining the function.
as ‘obj_file’, ‘link_symbol’: Is used when creating a function implemented in a shared object file. The shared object file must exist and the link symbol it contains will be looked up to execute at run time.

Example

CREATE FUNCTION greater_number(int, int)
RETURNS int AS $$
BEGIN
  IF $1 > $2 THEN
    RETURN $1;
  ELSE
    RETURN $2;
  END IF;
END;
$$ LANGUAGE plpgsql;

SELECT greater_number(4, 6);

Output

 greater_number
----------------
              6

Explanation

The function greater_number is created to determine the larger number between two inputs. The function takes two integer inputs and returns an integer value which is the greater number. In the provided example, the function greater_number is called with input values 4 and 6. The system executes the function and returns 6 which is the greater value among the inputs.

CREATE FUNCTION [schema_name.] function_name ( [ @parameter1 datatype [= default] [READONLY] ] , [ @parameter2 datatype [= default] [READONLY] ] …)RETURNS return_datatype[ WITH <function_option> [ ,…n ] ]AS BEGIN — Function body … RETURN scalar_expressionEND;

schema_name: This parameter optionally specifies the name of the schema to which the new function belongs.
function_name: This parameter denotes the unique name that is given to the function.
@parameter1, @parameter2,…: These represent the parameters that the function will use. They are variable declarations that include a name, a data type, and an optional default value. If specified, the READONLY keyword means that the parameter cannot be modified by the function.
datatype: This refers to the data type of a parameter. It determines the kind of data the parameter can hold.
default: This optional parameter specifies the default value for the function parameter. If no argument is passed in the function call for this parameter, the default value is used.
return_datatype: This denotes the data type of the value the function returns.
function_option: This optional parameter allows you to specify attributes of the function like ENCRYPTION, SCHEMABINDING, and so on.
begin and end: These define the start and the end of the function body.
function body: This is the part of the function where the logic is defined.
return scalar_expression: This parameter concludes the function by returning a single value of a scalar data type. This returned value can be any scalar expression.

Example

CREATE FUNCTION dbo.fnExample(@num1 INT, @num2 INT)
RETURNS INT
AS
BEGIN
   RETURN (@num1 + @num2)
END

Now you can use the function like this:

SELECT dbo.fnExample(3, 4) AS Result;

Output

Result
-------
7

Explanation

In this example, a simple SQL Server function is created. The function, named fnExample, takes two integers as parameters, adds them together, then returns the result. In the execution part of the example, the function is being called with 3 and 4 as the input parameters. The return value of 7 is then output under the column labeled ‘Result’.

CREATE [OR REPLACE] FUNCTION function_name [(parameter1 [type1], …)] RETURN return_datatype [{IS | AS}] BEGIN function_logic;RETURN return_value; EXCEPTION exception_handling; END [function_name];

create [or replace] function function_name: The CREATE FUNCTION statement is used to create and define a function in the database. The ‘OR REPLACE’ clause allows the modification of an existing function.
[(parameter1 [type1], …)]: This component is optional and used to declare parameters before they are used within the function. Each parameter can have a name and the datatype.
return return_datatype: Defines the datatype of the value the function will return. It must be declared before the BEGIN clause.
{is | as}: These are optional keywords used before the BEGIN keyword. They do not change the functionality of the function.
begin: This keyword signifies the start of the body of the function. The logic of the function is written following this keyword.
function_logic: Written between BEGIN and RETURN keywords, contains the SQL operations that the function performs.
return return_value: The RETURN keyword specifies the output value of a function. The return value must match the return datatype declared.
exception exception_handling: After the RETURN keyword, this part is used to handle exceptions that can occur when the function is executed. This is optional.
end [function_name]: Marks the end of the function. Optionally, the function name can follow the END keyword for readability.

Example

CREATE FUNCTION calculate_total_price
   (unit_price_in IN NUMBER, quantity_in IN NUMBER)
   RETURN NUMBER IS
   total_price NUMBER;
   BEGIN
      total_price := unit_price_in * quantity_in;
      RETURN total_price;
   END;
/

DECLARE
   unit_price_per_item NUMBER := 200;
   quantity NUMBER := 3;
   total_price NUMBER;
BEGIN
   total_price := calculate_total_price(unit_price_per_item, quantity);
   DBMS_OUTPUT.put_line('Total price: ' || total_price);
END;
/

Output

Total price: 600

Explanation

In the example, a function named calculate_total_price is created, receiving two parameters: unit_price_in and quantity_in. The function calculates the total price by multiplying unit_price_in by quantity_in, and that result is returned.

Next, in the anonymous PL/SQL block, the function is called by giving it the necessary inputs (unit price and quantity). The result, which is the total price, is stored in the variable total_price, and finally outputted using DBMS_OUTPUT.put_line.