To the point guide to PL/SQL

PL/SQL function is same as a procedure where function returns a value. By using CREATE FUNCTION statement user can function block.

Syntax:

CREATE [OR REPLACE] FUNCTION function_name
   [ (parameter [,parameter]) ]

   RETURN return_datatype

IS | AS

   [declaration_section]

BEGIN
   executable_section

[EXCEPTION
   exception_section]

END [function_name];

Let's check on some important parts of above syntax

• function-name: specifies the function name.
• OR REPLACE: For modifying an existing function.
• Return: A return statement is a must for the function.
• The AS keyword: this keyword is used if you want above to be a standalone function.

Example

CREATE or REPLACE FUNCTION function_Example(id_num in number) 
RETURN varchar2
IS
    ename varchar2(30);
BEGIN
    select employee_name into ename from employee where employee_no = id_num;
    return ename;
END;
/

Results

SQL>@ function_Example 
Function created.

PL/SQL procedure successfully completed.

PL/SQL Program to Calling Function:

To use a created function, user needs to call the function to perform the specified task. The control is transferred to the function which is called by another program. When called function completes its task the program control returns back to the main program.

Example

CREATE or REPLACE FUNCTION TEST_SWAPPING AS
RETURN number
      first NUMBER := 5;
      second NUMBER := 10;
 FUNCTION SWAPPING(a IN NUMBER, b IN NUMBER) AS
       Temp NUMBER;
    BEGIN
       Temp := first;
       first := second;
       first:= Temp;
       RETURN Temp;
  END;
  BEGIN     
     SWAPPING(first, second);
     DBMS_OUTPUT.PUT_LINE('First  = ' || TO_CHAR(first));
     DBMS_OUTPUT.PUT_LINE('Second  = ' || TO_CHAR(second));
  END;
 /

Result

SQL>@ TEST_SWAPPING 
first  = 10
second  = 5
PL/SQL procedure successfully completed.

To Drop Function:

User can drop function of PL/SQL by using DROP FUNCTION statements.

SQL>DROP TEST_SWAPPING;

Function dropped.

In next part of this guide we will quickly explore what Cursors are and how to work with them with examples.