Functions are another type of stored code and are very similar to procedures. The significant difference is that a function is a PL/SQL block that returns a single value. Functions can accept one, many, or no parameters, but a function must have a return clause in the executable section of the function. The datatype of the return value must be declared in the header of the function. A function is not a stand-alone executable in the way that a procedure is: It must be used in some context. You can think of it as a sentence fragment. A function has output that needs to be assigned to a variable, or it can be used in a SELECT statement.
The syntax for creating a function is as follows :
CREATE [OR REPLACE] FUNCTION function_name (parameter list) RETURN datatype IS BEGIN <body> RETURN ( return_value ); END;
The function does not necessarily have any parameters, but it must have a RETURN value declared in the header, and it must return values for all the varying possible execution streams. The RETURN statement does not have to appear as the last line of the main execution section, and there may be more than one RETURN statement (there should be a RETURN statement for each exception). A function may have IN, OUT, or IN OUT parameters, but you rarely see anything except IN parameters since it is bad programming practice to do otherwise .
-- ch13_01a.sql ver 1.0 CREATE OR REPLACE FUNCTION show_description (i_course_no course.course_no%TYPE) RETURN varchar2 AS v_description varchar2(50); BEGIN SELECT description INTO v_description FROM course WHERE course_no = i_course_no; RETURN v_description; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN('The Course is not in the database'); WHEN OTHERS THEN RETURN('Error in running show_description'); END;