SQL Record-plsql function

PL/SQL functions

The PL/SQL function is the same as the procedure, except that the function has a return value. Therefore, all the discussions in the previous chapters apply to the function.

Create a function

Establishing a standalone function can be created using the CREATE FUNCTION statement. The CREATE OR REPLACE PROCEDURE statement simplifies the syntax as follows:

CREATE [ORREPLACE] FUNCTIONFunction_name[(Parameter_name[in || in Out] type [, return Return_datatype{ is |as}begin< function_body >end [function_name   

Over here

• Function-name the name of the specified function

• The [OR REPLACE] option allows you to modify an existing function

• An optional parameter list contains the names, patterns, and types of parameters. In indicates that the value will be passed externally and out represents that the parameter will be used to return a value outside the procedure

• A function must contain a return statement

• The return clause specifies the type of data to be returned in the function

• Function-body contains executable parts

• As keyword instead of the IS keyword used to create a separate function

Example:

The following example shows the creation and invocation of a separate function. The function returns the total number of customers in the Customers table. We will use the Customers table, which has been created in the section of the preceding PL/SQL variable:

Select * FromCustomers;+----+----------+-----+-----------+----------+|Id|NAME|Age|ADDRESS|SALARY|+----+----------+-----+-----------+----------+| 1 |Ramesh| 32 |Ahmedabad| 2000.00 || 2 |Khilan| 25 |Delhi| 1500.00 || 3 |Kaushik| 23 |Kota| 2000.00 || 4 |Chaitali| 25 |Mumbai| 6500.00 || 5 || 27 || 8500.00 ||  6 || 22 ||4500.00 |+----+----------+-----+-----------+----------+       

CREATE ORREPLACEFUNCTION Totalcustomersreturn number is  Total Number (2)  := 0; begin select Count (*) Span class= "PLN" > into Total from customers  return Total;end;                

When executed using SQL hints in the above code, it produces the following results:

Function created.

Call a function

When creating a function, a definition must be made before a function is given. To use a function, you must call the function to perform the specified task. When a program calls a function, the programmatic control is transferred to the function that is called.

The calling function defines the execution of a task, the return statement that is executed, or the last statement that is reached, and the control returns to the main program.

The calling function only needs to pass the necessary arguments and the function name, and if the function returns a value, the return value can be stored. The following program is called an anonymous block function Totalcustomers:

DECLARE   C number(2);  BEGIN: = totalcustomers(); dbms_output.  Put_Line(' Total No. of Customers: '| | c); END;  /

When the above code is executed at the SQL prompt, it produces the following results:

Total No. of Customers:6pl/sql procedure successfully completed.

Example:

Here's an example of what this means to declare, define, and invoke a simple PL/SQL function that calculates and returns the maximum of two values.

DECLAREA number;B number;C Number;FUNCTIONFindmax(XInchNumber,YInchNumber) RETURNNumberIsZ Number;BEGIN IFX>YThenZ:=X; ELSEZ:=Y; END IF; RETURNZ;END begin A:=23; B:=45; C := Findmaxa, B. Put_line ( Maximum of (23,45): '  | |  C); end;               

When the above code is executed at the SQL prompt, it produces the following results:

Maximum of (23,45): $ PL/SQL procedure successfully completed.

PL/SQL Recursive functions

We have seen that a program or subroutine can call another subroutine. When a subroutine calls itself, it is called a recursive call.

To illustrate this concept, let's calculate the factorial of a number. The factorial of a number n is defined as:

N!  = N* (n-1)!  = N* (n-1) * (n-2)!  ... = N* (n-1) * (n-2) * (n-3) ... 1                   

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

DECLARENum number;Factorial number;FUNCTIONFact(X number)RETURNNumberIsF number;BEGIN IFX=0 ThenF:= 1; ELSEF:=X*Fact(X-1); END IF;RETURNF;end;begin Num:= 6  factorial := Fact (num< Span class= "pun");  Dbms_output. ( ' factorial ' | |  num | |   ' is '  | |  Factorial); end;               

When the above code is executed at the SQL prompt, it produces the following results:

Factorial 6 is 720 PL/SQL procedure successfully completed.

SQL Record-plsql function