PLSQL--记录

1.基于表和基于游标的记录：

基于表的记录：

DECLARE
    course_rec course%ROWTYPE;
BEGIN
    SELECT * INTO course_rec FROM course WHERE course_no = 25;
    dbms_output.put_line('Course No: ' || course_rec.course_no);
    dbms_output.put_line('Course Description: ' || course_rec.description);
    dbms_output.put_line('Course Prereqquisite: ' ||
                         course_rec.prerequisite);
END;

基于游标的记录：

DECLARE
    CURSOR student_cur IS
        SELECT first_name, last_name, registration_date
          FROM student
         WHERE rownum <= 4;
    student_rec student_cur%ROWTYPE;--基于游标的再声明，注意在游标后面
BEGIN
    OPEN student_cur;
    LOOP
        FETCH student_cur
            INTO student_rec;
        EXIT WHEN student_cur%NOTFOUND;
        dbms_output.put_line('Name: ' || student_rec.first_name || ' ' ||
                             student_rec.last_name);
        dbms_output.put_line('Registration Date :' ||
                             student_rec.registration_date);
    END LOOP;
END;

基于用户定义的记录：

DECLARE
    CURSOR course_cur IS
        SELECT * FROM course WHERE rownum <= 4;
    TYPE course_type IS RECORD(
        course_no     NUMBER(38),
        description   VARCHAR2(50),
        cost          NUMBER(9, 2),
        prerequisite  NUMBER(8),
        created_by    VARCHAR2(30),
        created_date  DATE,
        modified_by   VARCHAR2(30),
        modified_date DATE);
    course_rec1 course%ROWTYPE; --table_based record
    course_rec2 course_cur%ROWTYPE; --cursor-based record
    course_rec3 course_type; --user-define record
BEGIN
    SELECT * INTO course_rec1 FROM course WHERE course_no = 10;
    OPEN course_cur;
    LOOP
        FETCH course_cur
            INTO course_rec2;
        EXIT WHEN course_cur%NOTFOUND;
    END LOOP;
    course_rec1 := course_rec2;
    course_rec3 := course_rec2;
END;

嵌套记录：

DECLARE
    TYPE name_type IS RECORD(
        first_name VARCHAR2(15),
        last_name  VARCHAR2(30));
    TYPE person_type IS RECORD(
        NAME   name_type,
        street VARCHAR2(50),
        city   VARCHAR2(25),
        state  VARCHAR2(2),
        zip    VARCHAR2(5));
    person_rec person_type;
BEGIN
    SELECT first_name, last_name, street_address, city, state, zip
      INTO person_rec.name.first_name,
           person_rec.name.last_name,
           person_rec.street,
           person_rec.city,
           person_rec.state,
           person_rec.zip
      FROM student
      JOIN zipcode
     USING (zip)
     WHERE rownum < 2;
    dbms_output.put_line('Name: ' || person_rec.name.first_name || ' ' ||
                         person_rec.name.last_name);
    dbms_output.put_line('Street: ' || person_rec.street);
    dbms_output.put_line('City: ' || person_rec.city);
    dbms_output.put_line('State: ' || person_rec.state);
    dbms_output.put_line('Zip: ' || person_rec.zip);
END;

DDECLARE
    TYPE last_name_type IS TABLE OF student.last_name%TYPE INDEX BY BINARY_INTEGER;
    TYPE zip_info_type IS RECORD(
        zip           VARCHAR2(5),
        last_name_tab last_name_type);
    CURSOR name_cur(p_zip VARCHAR2) IS
        SELECT last_name FROM student WHERE zip = p_zip;
    zip_info_rec zip_info_type;
    v_zip        VARCHAR2(5) := '&svz_zip';
    v_counter    INTEGER := 0;
BEGIN
    zip_info_rec.zip := v_zip;
    dbms_output.put_line('Zip: ' || zip_info_rec.zip);
    FOR name_rec IN name_cur(v_zip)
    LOOP
        v_counter := v_counter + 1;
        zip_info_rec.last_name_tab(v_counter) := name_rec.last_name;
        dbms_output.put_line('Names(' || v_counter || '):' ||
                             zip_info_rec.last_name_tab(v_counter));
    END LOOP;
END;

用FOR循环得到所有学生的邮政编码：

DECLARE
    TYPE last_name_type IS TABLE OF student.last_name%TYPE INDEX BY BINARY_INTEGER;
    TYPE zip_info_type IS RECORD(
        zip           VARCHAR2(5),
        last_name_tab last_name_type);
    CURSOR zip_cur IS
        SELECT zip, COUNT(*) FROM student GROUP BY zip HAVING COUNT(*) > 1;
    CURSOR name_cur(p_zip VARCHAR2) IS
        SELECT last_name FROM student WHERE zip = p_zip;
    zip_info_rec zip_info_type;
    v_counter    INTEGER;
BEGIN
    FOR zip_rec IN zip_cur
    LOOP
        zip_info_rec.zip := zip_rec.zip;
        dbms_output.put_line('Zip: ' || zip_info_rec.zip);
        v_counter := 0;
        FOR name_rec IN name_cur(zip_info_rec.zip)
        LOOP
            v_counter := v_counter + 1;
            zip_info_rec.last_name_tab(v_counter) := name_rec.last_name;
            dbms_output.put_line('Names(' || v_counter || '):' ||
                                 zip_info_rec.last_name_tab(v_counter));
        END LOOP;
        dbms_output.put_line('------------------------------');
    END LOOP;
END;

记录的集合：

DECLARE
    CURSOR name_cur IS
        SELECT first_name, last_name FROM student WHERE rownum <= 4;
    TYPE name_type IS TABLE OF name_cur%ROWTYPE INDEX BY BINARY_INTEGER;
    name_tab  name_type;
    v_counter INTEGER := 0;
BEGIN
    FOR name_rec IN name_cur
    LOOP
        v_counter := v_counter + 1;
        name_tab(v_counter).first_name := name_rec.first_name;
        name_tab(v_counter).last_name := name_rec.last_name;
        dbms_output.put_line('First Name(' || v_counter || '): ' || name_tab(v_counter)
                             .first_name);
        dbms_output.put_line('Last Name(' || v_counter || '): ' || name_tab(v_counter)
                             .last_name);
    END LOOP;
END;

修改上述脚本，不在使用联合数组，而使用嵌套表：

DECLARE
    CURSOR name_cur IS
        SELECT first_name, last_name FROM student WHERE rownum <= 4;
    TYPE name_type IS TABLE OF name_cur%ROWTYPE;
    name_tab  name_type:=name_type();
    v_counter INTEGER := 0;
BEGIN
    FOR name_rec IN name_cur
    LOOP
        v_counter := v_counter + 1;
name_tab.extend;
        name_tab(v_counter).first_name := name_rec.first_name;
        name_tab(v_counter).last_name := name_rec.last_name;
        dbms_output.put_line('First Name(' || v_counter || '): ' || name_tab(v_counter)
                             .first_name);
        dbms_output.put_line('Last Name(' || v_counter || '): ' || name_tab(v_counter)
                             .last_name);
    END LOOP;
END;

修改以上脚本，使用变长数组：

DECLARE
    CURSOR name_cur IS
        SELECT first_name, last_name FROM student WHERE rownum <= 4;
    TYPE name_type IS VARRAY(4) OF name_cur%ROWTYPE;
    name_tab  name_type := name_type();
    v_counter INTEGER := 0;
BEGIN
    FOR name_rec IN name_cur
    LOOP
        v_counter := v_counter + 1;
        name_tab.extend;
        name_tab(v_counter).first_name := name_rec.first_name;
        name_tab(v_counter).last_name := name_rec.last_name;
        dbms_output.put_line('First Name(' || v_counter || '): ' || name_tab(v_counter)
                             .first_name);
        dbms_output.put_line('Last Name(' || v_counter || '): ' || name_tab(v_counter)
                             .last_name);
    END LOOP;
END;

修改以上脚本，不在使用基于游标的记录，而是使用用户定义的记录：

DECLARE
    CURSOR name_cur IS
        SELECT first_name, last_name, COUNT(*) total
          FROM student
          JOIN enrollment
         USING (student_id)
         GROUP BY first_name, last_name;
    TYPE student_rec_type IS RECORD(
        first_name  VARCHAR2(15),
        last_name   VARCHAR2(30),
        enrollments INTEGER);
    TYPE name_type IS TABLE OF student_rec_type INDEX BY BINARY_INTEGER;
    name_tab  name_type;
    v_counter INTEGER := 0;
BEGIN
    FOR name_rec IN name_cur
    LOOP
        v_counter := v_counter + 1;
        name_tab(v_counter).first_name := name_rec.first_name;
        name_tab(v_counter).last_name := name_rec.last_name;
        name_tab(v_counter).enrollments := name_rec.total;
        IF v_counter <= 4
        THEN
            dbms_output.put_line('First Name(' || v_counter || '): ' || name_tab(v_counter)
                                 .first_name);
            dbms_output.put_line('Last Name(' || v_counter || '): ' || name_tab(v_counter)
                                 .last_name);
            dbms_output.put_line('Enrollments(' || v_counter || '): ' || name_tab(v_counter)
                                 .enrollments);
            dbms_output.put_line('---------------------------------');
        END IF;
    END LOOP;
END;