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:

  • Oracle date and time function related queries Date / Time related queries Get the first day of the month Quickly returns the first day of current month. Instead of current month you want to find first day of month where a date falls, replace SYSDATE with any date colu… Read More
  • Weblogic Server 11g 10.3.3 on MS Windows 7 Weblogic Server Installation 11g 10.3.3 on MS Windows 7 We download the Oracle Weblogic server from here. We started the installer and will see at the beginning the initial loading. Wait until the loading of the insta… Read More
  • Most Important Data dictionary Queries.. Data dictionary queries Check if a table exists in the current database schema A simple query that can be used to check if a table exists before you create it. This way you can make your create table script rerunnable. Just… Read More
  • Database administration queries Database administration queries Database version informationReturns the Oracle database version. SELECT * FROM v$version; Database default information Some system default information. SELECT username,profile,default_ta… Read More
  • 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

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