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

Lab 13.1 Exercises

13.1.1 Create Stored Functions

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

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

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;

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;