Jun 30, 2014  SQL> @e: plsql fact.sql. Enter value for n: 5. Old 3: n number(4):=&n; new 3: n number(4):=5; the factorial of 5 is:120. PL/SQL procedure successfully completed. Conclusion: a pl/sql program is successfully executed for finding factorial of a given number.

A function can be used as a part of SQL expression i.e. we can use them with select/update/merge commands. One most important characteristic of a function is that unlike procedures, it must return a value.

• Write a PL/SQL code to create and use process. Write a PL/SQL code to get the Fibonacci Sequence; Write a PL/SQL code to implement after row trigger. Write a PL/SQL code to calculate total sal of emp. Cursor in PL/SQL. Write a program in PL/SQL to print the following p. Write a program in PL/SQL to print the factorial o.
• PL/SQL Function To Compute The Factorial Of A Number 0 0 Nisheeth Ranjan Sep 18, 2011 Edit this post Following PL/SQL funtion can be used to compute the factorial of a number.

Syntax to create a function:

Example:

1. We have to find the total strength of students using functions present
   in different sections in a school

   create table section(s_id int, s_name varchar(20), strength int); // Inserting values into table insert into section values(1, 'computer science', 20); insert into section values(3, 'geeksforgeeks', 60); // Defining function returninteger total integer:=0; begin // calculating the sum and storing it in total returntotal; // closing function answer integer; begin dbms_output.put_line('Total strength of students is '|| answer);

   Output:

2. Now, let’s take an example to demonstrate Declaring, Defining and Invoking a simple PL/SQL
   function which will compute and return the reverse of a number.

   declare a int; n int; r int; // Defining function returnint z int; // function code n := x; while(n > 0) r := mod(n, 10); n := trunc(n / 10); returnz; end ; BEGIN dbms_output.put_line('the reverse of number is '|| c); END;

   Output:

3. Lets implement a recursive function to calculate the factorial of a number
   Recursive functions example:

   num int; FUNCTION factorial(x number) IS IF x = 0 THEN ELSE END IF; END; BEGIN answer := factorial(num); dbms_output.put_line(' Factorial of '|| num || ' is '|| answer);

   Output:

4. Exception handling can be done using exception block in functions but exception handling using try-catch block cannot be done.

   Example:

   a int; myexp exception; function sqroot(x int) answer float; begin ifx < 0 then // calculate square root answer := SQRT(x); end if; when myexp then dbms_output.put_line('square of negative number is number'); end; begin dbms_output.put_line('the value is '|| b);

   Output:

Advantages:

1. We can make a single call to the database to run a block of statements thus it improves the performance against running SQL multiple times. This will reduce the number of calls between the database and the application.
2. We can divide the overall work into small modules which becomes quite manageable also enhancing the readability of the code.
3. It promotes reusability.
4. It is secure since the code stays inside the database thus hiding internal database details from the application(user). The user only makes a call to the PL/SQL functions. Hence security and data hiding is ensured.

Recommended Posts:

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the 'Improve Article' button below.

In which scenario recursion can be used in PL/SQL

Pl/sql Tutorial

This will work but I want to know in real world when recursion is required in data base queries

1 Answer

Depends what you mean by 'required'. A recursive algorithm can always be rewritten as a loop and a loop can always be rewritten as a recursive algorithm (assuming that the language you're working with supports both loops and function calls). Some algorithms tend to be easier to implement via recursion others tend to be easier to implement via loops.

Using recursion to compute factorials in PL/SQL is a reasonable implementation choice. Using a loop would also be reasonable. If you want to do it in pure SQL rather than resorting to PL/SQL, you could also

Which of various reasonable implementations you use depends on a variety of factors-- what approach you're most comfortable with, the speed of the various implementations, etc.

Pl/sql Interview Questions

Create Function In Pl Sql

Not the answer you're looking for? Browse other questions tagged oraclerecursionplsql or ask your own question.