Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

Lab 13.1 Exercise Answers

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.

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;

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.

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;

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.

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'.

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;

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).

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.