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;