PL/SQL高级特性

最新推荐文章于 2025-04-22 17:02:59 发布

原创最新推荐文章于 2025-04-22 17:02:59 发布 · 129 阅读

0 ·

CC 4.0 BY-SA版权

PL/SQL 专栏收录该内容

9 篇文章

订阅专栏

本文介绍了一个使用Oracle PL/SQL创建的学生对象类型，该对象包括了成员函数和过程用于处理学生信息，如姓名格式化、专业变更及学分更新等。此外还展示了不同类型集合的声明与初始化，包括索引表、嵌套表及可变数组。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.对象类型


CREATE OR REPLACE TYPE Student AS OBJECT (
  ID              NUMBER(5),
  first_name      VARCHAR2(20),
  last_name       VARCHAR2(20),
  major           VARCHAR2(30),
  current_credits NUMBER(3),

  -- Returns the first and last names, separated by a space.
  MEMBER FUNCTION FormattedName
    RETURN VARCHAR2,PRAGMA RESTRICT_REFERENCES(FormattedName, RNDS, WNDS, RNPS, WNPS),

  -- Updates the major to the specified value in p_NewMajor.
  MEMBER PROCEDURE ChangeMajor(p_NewMajor IN VARCHAR2),PRAGMA RESTRICT_REFERENCES(ChangeMajor, RNDS, WNDS, RNPS, WNPS),

  -- Updates the current_credits by adding the number of
  -- credits in p_CompletedClass to the current value.
  MEMBER PROCEDURE UpdateCredits(p_CompletedClass IN Class),PRAGMA RESTRICT_REFERENCES(UpdateCredits, RNDS, WNDS, RNPS, WNPS),

  -- ORDER function used to sort students.ORDER 
  MEMBER FUNCTION CompareStudent(p_Student IN Student) RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY Student AS
  MEMBER FUNCTION FormattedName RETURN VARCHAR2 IS
  BEGIN
    RETURN first_name || ' ' || last_name;
  END FormattedName;

  MEMBER PROCEDURE ChangeMajor(p_NewMajor IN VARCHAR2) IS
  BEGIN
    major := p_NewMajor;
  END ChangeMajor;

  MEMBER PROCEDURE UpdateCredits(p_CompletedClass IN Class) IS
  BEGIN
    current_credits := current_credits + p_CompletedClass.num_credits;
  END UpdateCredits;

  ORDER MEMBER FUNCTION CompareStudent(p_Student IN Student) RETURN NUMBER IS
  BEGIN
    -- First compare by last names
    IF p_Student.last_name = SELF.last_name THEN
      -- If the last names are the same, then compare first name
      IF p_Student.first_name < SELF.first_name THEN
        RETURN 1;
      ELSIF p_Student.first_name > SELF.first_name THEN
        RETURN -1;
      ELSE
        RETURN 0;
      END IF;
    ELSE
      IF p_Student.last_name < SELF.last_name THEN
        RETURN 1;
      ELSE
        RETURN -1;
      END IF;
    END IF;
  END CompareStudent;
END;

2.集合


DECLARE
  TYPE t_IndexBy IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  TYPE t_Nested IS TABLE OF NUMBER;
  TYPE t_Varray IS VARRAY(10) OF NUMBER;
  v_IndexBy t_IndexBy;
  v_Nested t_Nested;
  v_Varray t_Varray;
BEGIN
  v_IndexBy(1) := 1;
  v_IndexBy(2) := 2;
  v_Nested := t_Nested(1, 2, 3, 4, 5);
  v_Varray := t_Varray(1, 2);
END;