Lab 13.1 Creating and Using Functions | Oracle PL[s]SQL by Example

Lab Objectives

After this Lab, you will be able to:

Create Stored Functions

Make Use of Functions

Invoke Functions in SQL Statements

Write Complex Functions

Function Basics

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.

Function Syntax

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 .

FOR EXAMPLE

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