Functions oracle (pl/sql)

1. FUNCTIONS
ORACLE (PL/SQL)



TOPIC PAGE NO.
DEFINITION 2
SYNTAX 2
METHODS TO CREATE FUNCTION
1. INDIVIDUALLY
2. WITHIN PL/SQL BLOCK
2,5
HOW TO EXECUTE FUNCTION 3-4
How to use functions with database table. 6
How to check errors. 7
FUNCTION WITH PARAMETER AS USER
DEFINED VALUES.
8
SOME EXAMPLES
1. FACTORIALPAGE-7
2. MAXIMUM OF TWO VALUES PAGE2
3. MULTIPLICATION. PAGE-5
4. PERCENTAGE OF STUDENT PAGE6

2. FUNCTIONS
ORACLE (PL/SQL)

DEFINITION
A function is a named PL/SQL Block which is similar to a procedure. The major difference
between a procedure and a function is, a function must always return a value, but a procedure
may or may not return a value.

SYNTAX
CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN return_datatype;
IS
Declaration_section
BEGIN
Execution_section
Return return_variable;
EXCEPTION
exception section
Return return_variable;
END;


METHODS TO CREATE FUNCTIONS
1. FUNCTION CAN BE CREATED INDIVIDUALLY TO BE USED LATER ON ANY TIME.
2. FUNCTION CAN BE CREATED AS EMBEDDED PART OF PL/SQL.

FOR EX:
FIRST METHOD OF CREATING FUNCTION
FUNCTIONT O FIND GREATEST OF TWO VALUES (VALUES ARE PASSED AS ARGUMENT TO THE FUNC)


NOTE: RESULT OF THIS FUNCTION IS SHOWN AT PAGE -4

3. FUNCTIONS
ORACLE (PL/SQL)


How to execute a PL/SQL Function?
A function can be executed in the following ways.
1) Since a function returns a value we can assign it to a variable.
employee_name := employer_details_func;
If ‘employee_name’ is of datatype varchar we can store the name of the employee by assigning the
return type of the function to it.
2) As a part of a SELECT statement
SELECT employer_details_func FROM dual;
3) In a PL/SQL Statements like,
dbms_output.put_line(employer_details_func);
This line displays the value returned by the function.

USING SQL QUERY



USING PL/SQL (WITHOUT USING VARIABLE)

4. FUNCTIONS
ORACLE (PL/SQL)


USING PL/SQL (USING VARIABLE)



PICTURE SHOWING THE SECOND METHOD OF CREATING A FUNCTION.












DECLARE (PL/SQL)


BEGIN (PL/SQL)
END (PL/SQL)
FUNCTION
IS ( DECLARATION)
BEGIN (EXECUTION)
END

5. FUNCTIONS
ORACLE (PL/SQL)

SECOND METHOD OF CREATING A FUNCTION
FUNCTION TO FIND PRODUCT OF TWO VALUES(VALUES ARE PASSED AS ARGUMENT TO
FUNCTION)
An example of pl/sql function embedded in pl/sql code

NOTE: we need not to execute the function, the function calling process is done itself in the begin part
of pl/sql block.
We have used result variable, which stores the value returned by function.

6. FUNCTIONS
ORACLE (PL/SQL)

Another example of same method

USING FUNCTIONS WITH DATABASE TABLE


Now we will create a function that finds percentage of student based on student id passed as argument
to the function. The details of student are stored in std table of database.

7. FUNCTIONS
ORACLE (PL/SQL)



HOW TO CHECK ERRORS:
SHOW ERRORS FUNCTION <<FUNCTION NAME>>
FOR EX: SHOW ERRORS FUNCTION employer_details_func

Same for procedure ( show errors procedure <<procedure name>>

SOME EXAMPLES
FUNCTION TO FIND FACTORIAL OF NUMBER

DECLARE
num number;
factorial number;
















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



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;

8. FUNCTIONS
ORACLE (PL/SQL)

FUNCTIONS ON USER DEFINED VALUES