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