PL/SQL Function

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.  

PL/SQL function is a named block that returns a value. PL/SQL functions are also known as subroutines or subprograms. To create a PL/SQL function, you use the following syntax:

 

  1. CREATE [OR REPLACE] FUNCTION {function_name} [(
  2.    {parameter_1} [IN] [OUT] {parameter_data_type_1},
  3.    {parameter_2} [IN] [OUT] {parameter_data_type_2},...
  4.    {parameter_N} [IN] [OUT] {parameter_data_type_N} )]
  5.     RETURN {return_datatype} IS
  6. --the declaration statements
  7. BEGIN
  8.    -- the executable statements
  9.    RETURN {return_data_type};
  10.    EXCEPTION
  11.     -- the exception-handling statements
  12. END;
  13. /
     
    The {function_name} is the name of the function. Function name should start with a verb for example function convert_to_number.
    {parameter_name} is the name of parameter being passed to function along with parameter’s data type {parameter_data_type}. There are three modes for parameters: IN,OUT and IN OUT.

    The IN mode is the default mode. You use the IN mode when you want the formal parameter is read-only. It means you cannot alter its value in the function. The IN parameter behaves like a constant inside the function. You can assign default value to the IN parameter or make it optional.

    The OUT parameters return values to the caller of a subprogram. An OUT parameter cannot be assigned a default value therefore you cannot make it optional. You need to assign values to the OUT parameter before exiting the function or its value will be NULL. From the caller subprogram, you must pass a variable to the OUT parameter.

    In the IN OUT mode, the actual parameter is passed to the function with initial values. And then inside the function, the new value is set for the IN OUT parameter and returned to the caller. The actual parameter must be a variable.
The function must have at least one RETURN statement in the execution part. The RETURN clause in the function header specifies the data type of returned value.

Examples of PL/SQL Function

We are going to create a function that parses a string and returns a number if the string being passed is a number otherwise it returns NULL.
  1. CREATE OR REPLACE FUNCTION try_parse(
  2.     iv_number IN VARCHAR2)
  3.   RETURN NUMBER IS
  4. BEGIN
  5.    RETURN TO_NUMBER(iv_number);
  6.    EXCEPTION
  7.      WHEN OTHERS THEN
  8.         RETURN NULL;
  9. END;
     

    The input parameter is iv_number that is a varchar2 type. 
    We can pass any string to the function try_parse()
    We use built-in function to_number to convert a string into a number. 
    If any exception occurs, the function will return NULL in the exception
    section of the function block. 

1 comments:

  1. Thanks for the very informative blog and I extremely grateful that you perform this piece of writing very simply, I mean to say that it's quite simple to read and understand. Web Design Bangalore | Web Designing Companies Bangalore

    ReplyDelete

SQL BASIC

  • Sql-Overview
  • Sql-Sysntax
  • Sql-Normalization
  • RDBMS Concept
  • Sql-Data Type
  • Sql-Operator
  • Sql-Expression
  • Create database
  • Sql-Delete
  • Sql-Select
  • Sql-Create
  • Sql-Like
  • Sql-Join
  • Sql-Insert
  • Sql-Drop Table
  • Sql-Wild Card
  • Sql-Order By
  • Sql-Group By
  • Sql-Index
  • Not Null Constraints
  • Transaction Control
  • Sql-Transaction
  • Sql-In
  • Sql-Distinct
  • Check Constraint
  • Sql Alias
  • Sql-Primary
  • Sql-Where
  • Sql-Update
  • Sql-Alias
  • Sql-Top-Rownum
  • Primary key vs Unique key
  • SQL Interview Question
  • PL/SQL BASIC

  • Variable
  • Block Structure
  • Function
  • Procedure
  • Nested Blog
  • If Statement
  • While Loop
  • For Loop
  • SEO

  • Introduction Seo
  • Top Social Bookmarking List
  • Directory Submission List
  • Classified Ads
  • Key Word Research
  • Html

  • Introduction Html
  • Introduction Css
  • Introduction Java Script
  • Unix

  • Unix
  • Software Testing

  • Software Testing
  • Computer Network

  • Computer Network
  •