Factorial Program In 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. PL/SQL: For Loop. This loop is used when some statements in PL/SQL code block are to be repeated for a fixed number of times.


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.

  • PL/SQL has two types of subprograms called procedures and functions. Generally, you use a procedure to perform an action and a function to compute a value. Like unnamed or anonymous PL/SQL blocks, subprograms have a declarative part, an executable part, and an optional exception-handling part. The declarative part contains declarations of types.
  • 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.

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.

Factorial Program In Pl Sql

Factorial Program In Pl Sql Tutorial

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 :

Factorial Program In Pl Sql
Factorial Program In Pl SqlFactorial program in pl sql queries
  • PL/SQL Tutorial
  • PL/SQL Useful Resources
  • Selected Reading

In this chapter, we will discuss the functions in PL/SQL. A function is same as a procedure except that it returns a value. Therefore, all the discussions of the previous chapter are true for functions too.

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

Factorial Program In Pl Sql Server

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 −

Factorial Program In Pl Sql

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 −

Factorial program in pl sql tutorial

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.

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

PL/SQL Recursive Functions

We have seen that a program or subprogram may call another subprogram. When a subprogram calls itself, it is referred to as a recursive call and the process is known as recursion.

To illustrate the concept, let us calculate the factorial of a number. Factorial of a number n is defined as −

The following program calculates the factorial of a given number by calling itself recursively −

Factorial Program In Pl Sql Queries

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