Factorial Program In Pl Sql

In this section, we will see how to find factorial of a number using the PL/SQL. In PL/SQL code, some group of commands is arranged within a block of the related declaration of statements. Factorial of a number is basically multiplication of all integers from 1 to n, where n is the given number. Write a Pl/SQL block to obtain factorial of a number. Answer: Factorial number: The factorial of a non-negative integer 'n' is denoted by n&excl. It is the product of all positive integers less then or equal to 'n'.

When I call the function using select statement select fact(5) from dual; I get output as 1.Can you guys please help me from the below code create or replace function fact(num in number) return. In PL/SQL code groups of commands are arranged within a block. A block group related declarations or statements. In declare part, we declare variables and between begin and end part, we perform the operations. Here, first, we take three variables num, fact, and temp and assign the value in num variable (i.e which number factorial we want).

Prerequisite – PL/SQL introduction
In PL/SQL code groups of commands are arranged within a block. A block group related declarations or statements.
In declare part, we declare variables and between begin and end part, we perform the operations.

Here, first, we take three variables num, fact, and temp and assign the value in num variable (i.e which number factorial we want).
And then after we assign fact variable to 1.

Examples:

Below is the required implementation:

-- and temp of datatype number

fact number := 1;

-- with the help of while loop

loop

temp:= temp-1;

endloop;

dbms_output.put_line('factorial of '|| num || ' is '|| fact);

end;

-- Program End

Output :

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

Creating a Function

A standalone function is created using the CREATE FUNCTION statement. The simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows −

Where,

function-name specifies the name of the function.
[OR REPLACE] option allows the modification of an existing function.
The optional parameter list contains name, mode and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure.
The function must contain a return statement.
The RETURN clause specifies the data type you are going to return from the function.
function-body contains the executable part.
The AS keyword is used instead of the IS keyword for creating a standalone function.

Example

The following example illustrates how to create and call a standalone function. This function returns the total number of CUSTOMERS in the customers table.

We will use the CUSTOMERS table, which we had created in the PL/SQL Variables chapter −

When the above code is executed using the SQL prompt, it will produce the following result −

Calling a Function

While creating a function, you give a definition of what the function has to do. To use a function, you will have to call that function to perform the defined task. When a program calls a function, the program control is transferred to the called function.

A called function performs the defined task and when its return statement is executed or when the last end statement is reached, it returns the program control back to the main program.

To call a function, you simply need to pass the required parameters along with the function name and if the function returns a value, then you can store the returned value. Following program calls the function totalCustomers from an anonymous block −

When the above code is executed at the SQL prompt, it produces the following result −

Example

The following example demonstrates Declaring, Defining, and Invoking a Simple PL/SQL Function that computes and returns the maximum of two values.