05-prgma.sql

来自「《Oracle8i PL/SQL程序设计》附源码」· SQL 代码 · 共 75 行

REM 05-PRGMA.SQL
REM This file contains the PRAGMA RESTRICT_REFERENCES examples
REM used in Chapter 5 of "Oracle PL/SQL Programming".

REM This is version 1.0 of this file, updated 2/18/96.
REM Comments and questions should go to Scott Urman at
REM [e-mail].


REM *** Chapter 5: The FullName Function ***
CREATE OR REPLACE FUNCTION FullName (
  p_StudentID  students.ID%TYPE)
  RETURN VARCHAR2 IS

  v_Result  VARCHAR2(100);
BEGIN
  SELECT first_name || ' ' || last_name
    INTO v_Result
    FROM students
    WHERE ID = p_StudentID;

  RETURN v_Result;
END FullName;
/

REM *** Chapter 5: Using RESTRICT_REFERENCES in a Package ***
CREATE OR REPLACE PACKAGE StudentOps AS
  FUNCTION FullName(p_StudentID IN students.ID%TYPE)
    RETURN VARCHAR2;
  PRAGMA RESTRICT_REFERENCES(FullName, WNDS, WNPS, RNPS);

  /* Returns the number of History majors. */
  FUNCTION NumHistoryMajors
    RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES(NumHistoryMajors, WNDS, WNPS, RNPS);
END StudentOps;
/

CREATE OR REPLACE PACKAGE BODY StudentOps AS

  -- Packaged variable to hold the number of history majors.
  v_NumHist NUMBER;

  FUNCTION FullName(p_StudentID IN students.ID%TYPE)
    RETURN VARCHAR2 IS
    v_Result  VARCHAR2(100);
  BEGIN
    SELECT first_name || ' ' || last_name
      INTO v_Result
      FROM students
      WHERE ID = p_StudentID;

    RETURN v_Result;
  END FullName;

  FUNCTION NumHistoryMajors RETURN NUMBER IS
    v_Result NUMBER;
  BEGIN
    IF v_NumHist IS NULL THEN
      /* Determine the answer. */
      SELECT COUNT(*)
        INTO v_Result
        FROM students
        WHERE major = 'History';
      /* And save it for future use. */
      v_NumHist := v_Result;
    ELSE
      v_Result := v_NumHist;
    END IF;

    RETURN v_Result;
  END NumHistoryMajors;
END StudentOps;
/