PLSQL--函数

概述：函数是另一种存储代码，非常类似于过程。两者之间重要的区别是，函数是能够返回单个值得PLSQL语句块。函数可以接收一个或者多个参数，也可以不接收参数，但是函数的可执行部分必须有一个返回子句。必须在函数的头部声明返回值的数据类型。与过程一样，函数不是独立的执行的，它必须在某些上下文中才能使用。可以把函数看作是一个语句段。函数的输出必须赋给一个变量，或者在select语句中使用。

函数未必使用参数，但必须有一个返回值：

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 runing show_description');
END;

2.函数返回Bolean值:

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;

测试以上函数：

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 invalid.');
    END IF;
END;

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;

练习：

1.编写一个名为new_student_id的存储函数，没有传人参数，会返回studen.student_id%type，当往CTA应用程序插入一个新的学生时，会使用所返回的值。使用公司student_id.nextval计算返回值。

CREATE OR REPLACE FUNCTION new_student_id RETURN student.student_id%TYPE AS
    v_student_id student.student_id%TYPE;
BEGIN
    SELECT student_id_seq.nextval INTO v_student_id FROM dual;
    RETURN(v_student_id);
END;

2.编写一个名为zip_dose_not_exist的函数，传人参数是zipcode.zip%type，返回一个Bolean值。如果所传人的邮政编码不存在，则该函数返回TURE。如果所传人的邮政编码存在，则返回FALSE。

CREATE OR REPLACE FUNCTION zipcode_does_not_exist(i_zipcode IN zipcode.zip%TYPE)
    RETURN BOOLEAN AS
    v_dummy CHAR(1);
BEGIN
    SELECT NULL INTO v_dummy FROM zipcode WHERE zip = i_zipcode;
    --Meaning the zipcode does exist
    RETURN FALSE;
EXCEPTION
    WHEN OTHERS THEN
        --The select statement above will cause an exception to be raised if zipcode is not in the database
        RETURN TRUE;
END zipcode_does_not_exist;

测试以上函数：

DECLARE
    cons_zip zipcode.zip%TYPE := '&sv_zipcode';
    e_zipcode_is_not_valid EXCEPTION;
BEGIN
    IF zipcode_does_not_exist(cons_zip)
    THEN
        RAISE e_zipcode_is_not_valid;
    ELSE
        --An insert of an instructor's record which make sure of the checked zipcode might go here.
        NULL;
    END IF;
EXCEPTION
    WHEN e_zipcode_is_not_valid THEN
        raise_application_error(-20003,
                                'Could not find zipcode ' || cons_zip || '.');
END;

3.编写一个函数：对于指定教师，决定他/她所教授的班级数量。如果这个数字大于或者等于3，则返回说明该教师需要休假的信息。否则的话，返回展示该教师教授多少班级的信息。

CREATE OR REPLACE FUNCTION instructor_status(i_first_name IN instructor.first_name%TYPE,
                                             i_last_name  IN instructor.last_name%TYPE)
    RETURN VARCHAR2 AS
    v_instructor_id instructor.instructor_id%TYPE;
    v_section_count NUMBER;
    v_status        VARCHAR2(100);
BEGIN
    SELECT instructor_id
      INTO v_instructor_id
      FROM instructor
     WHERE first_name = i_first_name
       AND last_name = i_last_name;
    SELECT COUNT(*)
      INTO v_section_count
      FROM section
     WHERE instructor_id = v_instructor_id;
    IF v_section_count >= 3
    THEN
        --dbms_output.put_line(i_first_name || ' ' || i_last_name);
        v_status := 'The instructor ' || i_first_name || ' ' || i_last_name ||
                    ' is teaching ' || v_section_count ||
                    ' and needs a vaction.';
    ELSE
        v_status := 'The_instructor ' || i_first_name || ' ' || i_last_name ||
                    ' is teaching ' || v_section_count || ' courses.';
    END IF;
    RETURN v_status;
EXCEPTION
    WHEN no_data_found THEN
        --Note that either of the select statement can raise this exception
        v_status := 'The instructor ' || i_first_name || ' ' || i_last_name ||
                    ' is not shown to be teaching' || 'any course.';
        RETURN v_status;
    WHEN OTHERS THEN
        v_status := 'There has been in an error in the function.';
        RETURN v_status;
END;

测试以上函数：

SQL> select instructor_status(first_name,last_name) from instructor;

INSTRUCTOR_STATUS(FIRST_NAME,LAST_NAME)
--------------------------------------------------------------------------------
The instructor Fernand Hanks is teaching 9 and needs a vaction.
The instructor Tom Wojick is teaching 14 and needs a vaction.
The instructor Nina Schorin is teaching 10 and needs a vaction.
The instructor Gary Pertez is teaching 10 and needs a vaction.
The instructor Anita Morris is teaching 10 and needs a vaction.
The instructor Todd Smythe is teaching 10 and needs a vaction.
The instructor Marilyn Frantzen is teaching 10 and needs a vaction.
The instructor Charles Lowry is teaching 9 and needs a vaction.
The_instructor Rick Chow is teaching 0 courses.
The_instructor Irene Willig is teaching 0 courses.
10 rows selected.