Creating a Function

Syntax:

CREATE [OR REPLACE] FUNCTION function_name [(?this part is same as the
                                                                                             procedure?)] RETURN return_type {IS|AS}
        Function_body;

        RETURN expression;

Inside the function body, the RETURN statement is used to return control to the caller with a value.

? A function is very similar to a procedure.
? Both can be stored in the database or declared within a block (a local function)
? However, function should return a value (or can return multiple values through parameters like procedure) -- good style is to return only single value, use procedure if multiple values or no values should be returned.

FUNCTION balance (acct_id INTEGER) RETURN REAL IS
         acct_bal  REAL;
BEGIN
         SELECT bal INTO acct_bal FROM accts WHERE acctno = acct_id;

         RETURN acct_bal;
END balance;

Example 2: function sal_ok, which determines if an employee salary is out of range:

FUNCTION sal_ok (salary REAL, title REAL) RETURN BOOLEAN IS
       min_sal  REAL;
       max_sal  REAL;
BEGIN
       SELECT losal, hisal INTO min_sal, max_sal
         FROM sals WHERE job = title;

       RETURN (salary = min_sal) AND (salary <= max_sal);
END sal_ok;

Example 3: The following two examples show the difference between procedure and function on how they are declared and called.

We create a procedure ?add_new_student? and store it into database.

CREATE OR REPLACE PROCEDURE add_new_student( p_firstName students.firstName%TYPE,
               p_lastName students.lastName%TYPE,
               p_major students.major%TYPE DEFAULT ?phycology?) AS
BEGIN
              INSERT INTO students
                     VALUES(student_sequence.nextval, p_firstName, p_lastName, p_major);
              COMMIT;
END add_new_student;
 

For testing the procedure ?add_new_student?, in the another PL/SQL block, we call the procedure ?add_new_student? as follow:

BEGIN
           add_new_student(?Barbara?, ?Blues?);
END;

We create a function ?almostFull? and store it into database.

CREATE OR REPLACE FUNCTION almostFull(
                            p_department classes.department%TYPE,
                            p_course classes.course%TYPE) RETURN BOOLEAN IS

                            v_currStudents  NUMBER;                    -- declarations
                            v_maxStudents  NUMBER;
                            v_returnValue  BOOLEAN;
BEGIN
                           SELECT current_students, max_students INTO v_currentStudents, v_maxStudents
                           FROM classes
                           WHERE department = p_department AND course = p_course;

                           IF v_currentStudents >= v_maxStudents
                                 v_returnValue = TRUE;
                           ELSE v_returnValue = FALSE;
                           END IF         

                           RETURN v_returnValue;
END almostFull;
 

In the another PL/SQL block, we call the function as follow:

BEGIN
        IF allmostFull(?Computer Science?, ?Oracle?) THEN
           DBMS_OUTPUT.PUT_LINE(?Class is Full?);
        ELSE DBMS_OUTPUT.PUT_LINE(?Class is not Full yet?);
END;

An example of Local function

DECLARE
        CURSOR c_allStudents IS
              SELECT firstName, lastName  FROM students;

        v_formattedName VARCHAR2(50);

       -- Function definition should come last    ? all variables should be defined in the above
       FUNCTION formatName(p_firstName IN VARCHAR2, p_lastName IN VARCHAR2)
                    RETURN VARCHAR2 IS
       BEGIN
                    RETURN p_firstName || ? ? || p_lastName;
       END formatName;

-- Begin main block here
BEGIN

          FOR v_studentRecord IN c_allStudents LOOP
                    v_formattedName := formatName(v_studentRecord.firstName, v_studentRecord.lastName);
                    INSERT INTO temp_table(fname) VALUES (v_formattedName);
         END LOOP;

         COMMIT;
END;