Saturday, 26 July 2014

undefined undefined



PL/SQL Functions



What is a Function in PL/SQL?

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.

General Syntax to create a function is

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;

1) Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.
2) The execution and exception section both should return a value which is of the datatype defined in the header section.

For example, let’s create a frunction called ''employer_details_func' similar to the one created in stored proc

1> CREATE OR REPLACE FUNCTION employer_details_func
2> RETURN VARCHAR(20);
3> IS
5> emp_name VARCHAR(20);
6> BEGIN
7> SELECT first_name INTO emp_name
8> FROM emp_tbl WHERE empID = '100';
9> RETURN emp_name;
10> END;
11> /


In the example we are retrieving the ‘first_name’ of employee with empID 100 to variable ‘emp_name’.
The return type of the function is VARCHAR which is declared in line no 2.
The function returns the 'emp_name' which is of type VARCHAR as the return value in line no 9.
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.

Related Posts:

  • Utility / Math related queries Utility / Math related queries Convert number to words More info: Converting number into words in Oracle SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL; Output: one thousand five hundred twenty-six Find st… Read More
  • ORACLE-FORU - ORACLE DBMS_PROFILER DBMS_PROFILER The DBMS_PROFILER package was introduced in Oracle 8i to allow developers to profile the run-time behaviour of PL/SQL code,  Making it easier to identify performance bottlenecks which can then… Read More
  • ORACLE-FORU - Oracle FORALL Function in Bulk Collection. FORALL in Bulk Collection. The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch e… Read More
  • ORACLE-FORU - SQL%BULK_ROWCOUNT SQL%BULK_ROWCOUNT The SQL%BULK_ROWCOUNT cursor attribute gives granular information about the rows affected by each iteration of the FORALL statement. Every row in the driving collection has a corresponding row in the SQ… Read More
  • ORACLE-FORU - How to handle Exception in Bulk Collection Method. SAVE EXCEPTIONS and SQL%BULK_EXCEPTION We saw how the FORALL syntax allows us to perform bulk DML operations.  But what happens if one of those individual operations results in an exception? If there is… Read More

0 comments:

Post a Comment

Copyright © 2025 ORACLE-FORU - SQL, PL/SQL and ORACLE D2K(FORMS AND REPORTS) collections | Powered by Blogger
Design by N.Design Studio | Blogger Theme by NewBloggerThemes.com