Creating functions in Oracle

A function is a named PL/SQL block or a subprogram. A function must return a value back to the caller. A functions can be stored in the database as a schema object for a repeated execution. Function is called as part of a SQL expression or as part of a PL/SQL expression.

Syntax of stored procedure

CREATE OR REPLACE FUNCTION Function_Name

(parameter_name1 IN datatype1,

  parameter_name2 IN datatype2,

  .......................................................................)

RETURN datatype
IS/AS

          variable declarations;

          constant declarations;

BEGIN

         PL/SQL statements block;

EXCEPTION

        exception PL/SQL block;

END;

NOTE:

1. You can only use IN parameter.

2. Variable and constant declarations is optional.

3. Exception is also an optional statement in function.

Example#1
Step 1 : Creating a function to display "Hello World".

SQL> CREATE OR REPLACE FUNCTION func_DisplayMsg

             RETURN varchar2
             IS

                    v_name varchar2(15);

             BEGIN
                    v_name:='Hello World';

                    return v_name;
             END;
             /

Step 2 : How to execute or call a function.

SQL> SET SERVEROUTPUT ON

NOTE : On Set serveroutput to display message on sql prompt before executing function.

SQL> DECLARE

                    msg varchar2(15);

            BEGIN
                    msg:=func_DisplayMsg();

                    dbms_output.put_line(msg);
             END;
             /

Result : Hello World