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.

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;
                            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;                          

                          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;

Calling Stored Procedures at SQL*Plus prompt
Procedures or Functions are normally called within executable or exception-handling section. However, we can call a stored procedure at SQL*PLUS prompt with EXECUTE command as follows:

EXECUTE procedure_name(arg1, arg2, ..)

Subprogram locations
• Subprograms can be stored in the data dictionary (in the form of object code, also called p-code) as well as within the declarative section of a block (in this case, it is called, Local subprogram).
• Related data dictionary views: user_objects, user_source, user_errors

Procedure/Function and Dependencies
• Procedures/functions are dependent on a table if the procedures/functions access the table. What does it mean? It means that if the table structure is modified, all the dependent procedure/functions need to be recompiled using ALTER command.

Commands related to Procedures and Functions
To recompile a procedure:
ALTER PROCEDURE procedure_name COMPILE

To drop a procedure/function:
DROP PROCEDURE procedure_name;
DROP FUNCTION function_name;

To grant an execute right on a procedure to a user ‘scott’:
GRANT EXECUTE ON procedure_name TO scott;