Lab 13.1 Creating and Using Functions

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 13.  Functions


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 context. You can think of it as a sentence fragment. A function has output that a stand-alone executable in the way that a procedure is: It must be used in some 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.

graphics/intfig03.gif 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; 

Lab 13.1 Exercises

13.1.1 Create Stored Functions

a)

Put the create script for the function in the preceding example into a text file. Open SQL*Plus, log into the student schema, and run the script from the preceding example. What do you expect to see? Explain the function line by line.

b)

Create another function using the following script. Explain what is happening in this function. Pay close attention to the method of creating the Boolean return.

 -- ch13_01b.sql, version 1.0  CREATE OR REPLACE FUNCTION id_is_good    (i_student_id IN NUMBER)    RETURN BOOLEAN  AS    v_id_cnt NUMBER;  BEGIN    SELECT COUNT(*)      INTO v_id_cnt      FROM student     WHERE student_id = i_student_id;    RETURN 1 = v_id_cnt;  EXCEPTION    WHEN OTHERS    THEN      RETURN FALSE;  END id_is_good; 

13.1.2 Make Use of Functions

In this exercise, you will learn how to make use of the stored functions that you created in Exercise 13.1.1.

a)

Use the following anonymous block to run the function. When prompted, enter 350. Then try other numbers. What is produced?

 SET SERVEROUTPUT ON  DECLARE    v_description VARCHAR2(50);  BEGIN    v_description := show_description(&sv_cnumber);    DBMS_OUTPUT.PUT_LINE(v_description);   END; 
b)

Now create a similar anonymous block to make use of the function id_is_good. Try running it for a number of different IDs.

13.1.3 Invoke Functions in SQL Statements

a)

Now you will try another method of using a stored function. Before you type the following SELECT statement, think about what the function show_description is doing. Will this statement produce an error? If not, then what will be displayed?

 SELECT course_no, show_description(course_no)    FROM course; 

13.1.4 Write Complex Functions

a)

Create the function with the following script. Before you execute the function, analyze this script and explain line by line what the function will perform. When could you use this function?

 -- ch13_01c.sql, version 1.0  CREATE OR REPLACE FUNCTION new_instructor_id     RETURN instructor.instructor_id%TYPE  AS     v_new_instid instructor.instructor_id%TYPE;  BEGIN     SELECT INSTRUCTOR_ID_SEQ.NEXTVAL       INTO v_new_instid       FROM dual;     RETURN v_new_instid;  EXCEPTION     WHEN OTHERS     THEN        DECLARE           v_sqlerrm VARCHAR2(250)              := SUBSTR(SQLERRM,1,250);        BEGIN           RAISE_APPLICATION_ERROR(-20003,                'Error in     instructor_id: '||v_sqlerrm);      END;  END new_instructor_id; 

Lab 13.1 Exercise Answers

13.1.1 Answers

a)

Put the create script for the function in the preceding example into a text file. Open SQL*Plus, log into the student schema, and run the script from the preceding example. What do you expect to see? Explain the function line by line.

A1:

Answer: When a function has been compiled without errors, the SQL*Plus session will return

 Function created. 

which indicates that the function was successfully compiled. The script is for the function show_description. The function heading indicates that the function takes in a parameter of the number datatype and returns a VARCHAR2. The function makes use of a VARCHAR2(5) variable called v_description. The function gives the variable the value of the description of the course, whose number is passed into the function. The return value is then the variable. There are two exceptions. The first is the WHEN NO_DATA_FOUND exception, the one most likely to occur. The second exception is the WHEN OTHERS exception, which is being used as a catchall for any other error that may occur. It is important for you to note that the RETURN clause is one of the last statements in the function. The reason is that the program focus will return to the calling environment once the RETURN clause is issued.

b)

Create another function using the following script. Explain what is happening in this function. Pay close attention to the method of creating the Boolean return.

 -- ch13_01b.sql, version 1.0  CREATE OR REPLACE FUNCTION id_is_good    (i_student_id IN NUMBER)    RETURN BOOLEAN  AS    v_id_cnt NUMBER;  BEGIN    SELECT COUNT(*)      INTO v_id_cnt      FROM student     WHERE student_id = i_student_id;    RETURN 1 = v_id_cnt;  EXCEPTION    WHEN OTHERS    THEN      RETURN FALSE;  END id_is_good; 
A2:

Answer: The function id_is_good is a check to see if the ID passed in exists in the database. The function takes in a number (which is assumed to be a student ID) and returns a BOOLEAN value. The function uses the variable v_id_cnt as a means to process the data. The SELECT statement determines a count of the number of students with the numeric value that was passed in. If the student is in the database, because the student_id is the primary key, the value of v_id_cnt will be 1. If the student is not in the database, the SELECT statement will throw the focus down to the exception section, where the function returns a value of FALSE. The function makes use of a very interesting method to return TRUE. If the student is in the database, then v_id_cnt will equal 1, thus the code RETURN 1 = v_id_cnt will actually return a value of TRUE when v_id_cnt equals 1.

13.1.2 Answers

a)

Use the following anonymous block to run the function. When prompted, enter 350. Then try other numbers. What is produced?

 SET SERVEROUTPUT ON  DECLARE    v_description VARCHAR2(50);  BEGIN    v_description := show_description(&sv_cnumber);    DBMS_OUTPUT.PUT_LINE(v_description);   END; 
A1:

Answer: Since there is a lexical parameter of &cnumber in the PL/SQL block, the user will be prompted as follows:

 Enter value for cnumber: 

If you enter "350," you will see the following:

 old   4:   v_descript := show_description(&sv_cnumber);  new   4:   v_descript := show_description(350);  Intro to SQL  PL/SQL procedure successfully completed. 

This means that the value for &sv_cnumber has been replaced with 350. The function show_description returns a VARCHAR2 value, which is the course description for the course number that is passed in. The PL/SQL block initializes the v_description value with the return from the show_description function. This value is then displayed with the DBMS_OUTPUT package.

b)

Now create a similar anonymous block to make use of the function id_is_good. Try running it for a number of different IDs.

A2:

Answer: The following is one method of testing the id_is_good function:

 DECLARE     V_id number;  BEGIN     V_id := &id;     IF id_is_good(v_id)     THEN        DBMS_OUTPUT.PUT_LINE           ('Student ID: '||v_id||' is a valid.');     ELSE        DBMS_OUTPUT.PUT_LINE           ('Student ID: '||v_id||' is not valid.');     END IF;  END; 

This PL/SQL block evaluates the return from the function and then determines which output to project. Since the function id_is_good returns a Boolean, the easiest way to make use of this function is to run it and use the result (which will be either true or false) in an IF statement. Remember that when testing a Boolean function id_is_good the line 'IF id_is_good(v_id)' means if the function id_is_good for the variable will result in a return of 'true' then do the following. The ELSE will then cover if the function returns 'false'.

13.1.3 Answers

a)

Now you will try another method of using a stored function. Before you type the following SELECT statement, think about what the function show_description is doing. Will this statement produce an error? If not, then what will be displayed?

 SELECT course_no, show_description(course_no)    FROM course; 
A1:

Answer: This SELECT statement will be identical to the SELECT statement that follows:

 SELECT course_no, description     FROM course. 

Functions can be used in a SQL statement. In fact, you have been using them all along and may not have realized it. As a simple example, imagine using the function UPPER in a select statement.

 SELECT UPPER('bill') FROM DUAL; 

The Oracle-supplied function UPPER is a function that returns the upper case value of the parameter that was passed in.

Note that for a user-defined function to be called in a SQL expression it must be a ROW function, not a GROUP function, and the datatypes must be SQL datatypes. The datatypes cannot be PL/SQL datatypes like Boolean, table, or record. Additionally, the function is not allowed to have any DML (insert, update, delete).

graphics/intfig07.gif

Note that in order to use a function in a SQL select statement, the function must have a certain level of purity. This is accomplished with the PRAGMA RESTRICT_REFERENCES clause. This will be discussed in detail in the next chapter in the context of functions within packages.

Lab 13.1 Self-Review Questions

In order to test your progress, you should be able to answer the following questions.

Answers appear in Appendix A, Section 13.1.

1)

What are the distinguishing characteristics that make functions different from procedures? (check all that apply)

  1. _____ Functions require a PRAGMA RESTRICT clause.

  2. _____ Functions only take IN parameters.

  3. _____ Functions are stored in the database.

  4. _____ Functions require a return value.

2)

The parameters of a function must be labeled IN or the function will not compile successfully.

  1. _____ True

  2. _____ False

3)

Which statement(s) will cause control to return to the calling environment in a function? (check all that apply)

  1. _____ The raising of an exception

  2. _____ The initialization of an OUT parameter

  3. _____ Writing to a database table

  4. _____ The RETURN statement

4)

IN OUT parameters are permissible in functions.

  1. _____ True

  2. _____ False

  3. _____ The function will compile with an IN OUT parameter, but it is not advisable to use them.

5)

If a function declares a user-defined exception but never explicitly raises the exception, which of the following will be true?

  1. _____ The function will not be able to compile.

  2. _____ The function will fail a purity level check.

  3. _____ The exception will never be raised.

  4. _____ As long as the exception has a RETURN clause, there is no error in having a user-defined exception and not calling it.


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net