How TO Create PlSql Functions With Example | Recurssion in Pl/SQL | PL-SQL Full Course Tutorial 2023

Опубликовано: 07 Октябрь 2022
на канале: Parnika Tutorials

290

In this video, I have discussed about functions in pl/sql and recursive function with examples
"How TO Create PlSql Functions With Example | Recurssion in Pl/SQL | PL-SQL Full Course Tutorial 2023

#recursion #plsql #oraclesql #plsqlfunction #plsqlrecurssion #plsqlbasics #plsqlprocedures #plsqlcursor #plsqlfunctions #plsqltriggers #plsqlvssql #oracleplsql #PLSQLtutorialforbeginners #PLSQLtutorialinenglish #plsqlinterview #plsqlinenglish #parnikatutorials #plsql2023 #plsqlfullcourse2023 #plsqlfullcrashcourse2023 #plsqlcrashcourse #plsqlcourse2023

01:13 - how to create function
02:11 - how to create a function in pls
03:06 - create a function
04:21 - syntax of creating function
04:49 - function should have a return statement
08:15 - create or replace function
23:14 - recursive function"

A function is same as a procedure except that it returns a value.

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 −

CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
(Function_body)
END [function_name];

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.

CREATE OR REPLACE FUNCTION p1
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total FROM emp;
RETURN total;
END;
/

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.

DECLARE
c number(2);
BEGIN
c := p1();
dbms_output.put_line('Total no. of Customers: ' || c);
END;
/

A recursive function is simply one that calls itself.

Create FUNCTION fact(x number)
RETURN number
IS
f number;
BEGIN
IF x=0 THEN
f := 1;
ELSE
f := x * fact(x-1);
END IF;
RETURN f;
END;

Function calling:
DECLARE
num number;
factorial number;
BEGIN
num:= 6;
factorial := fact(num);
dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;
/

Social media Links:
Instagram:   / parnikatutorials
Website: http://parnikatutorials.in/
Email id: [email protected]
To get the regular updates:
Telegram link: https://t.me/Parnikatutorials
Facebook: https://m.facebook.com/profile.php?id...
Linkedin:   / parnika-tutorials-a8a9831b2
Pinterest:   / parnikatutorials0892
Playlists:
Virtual Coffee with Jagadeesh:
   • VIRTUAL COFFEE WITH JAGADEESH
Digital Logic Design:
   • Digital Logic Design
Computer Organization and Architecture:
   • ABOUT PARNIKA TUTORIALS
C Programming:
   • L 1: WHAT IS AN ALGORITHM AND CHARACT...
Data Structures:
   • L 1: Uncover the Benefits of Linked L...

Theory of Computation:
   • ABOUT PARNIKA TUTORIALS

Compiler Design:
   • ABOUT PARNIKA TUTORIALS
Operating Systems:    • PROCESS STATE DIAGRAM | LONG TERM, SH...

Databases:    • ABOUT PARNIKA TUTORIALS

Computer Networks:
   • ABOUT PARNIKA TUTORIALS
For GATE PYQs and much more explore:
   / parnikatutorials