Functions

---

There are three function keys in the ORA_EMP database:

 CREATE OR REPLACE FUNCTION AccountFull (  p_dept_code accounts.dept_code%TYPE,  p_acct_id     accounts.acct_id%TYPE) RETURN BOOLEAN IS  v_CurrentEmployees NUMBER;  v_MaxEmployees     NUMBER;  v_ReturnValue     BOOLEAN;  v_FullPercent     CONSTANT NUMBER := 90; BEGIN  -- Get the current and maximum employees for the requested  -- acct_id.  SELECT current_employees, max_employees  INTO   v_CurrentEmployees, v_MaxEmployees  FROM   accounts  WHERE dept_code = p_dept_code    AND acct_id = p_acct_id;  -- If the account is more full than the percentage given by  -- v_FullPercent, return TRUE. Otherwise, return FALSE.  IF (v_CurrentEmployees / v_MaxEmployees * 100) > v_FullPercent   THEN     v_ReturnValue := TRUE;  ELSE     v_ReturnValue := FALSE;  END IF;   RETURN v_ReturnValue;  END AccountFull;  CREATE OR REPLACE FUNCTION AVERAGEBAND (   p_Department IN employees.department%TYPE,   p_ACCT_ID IN employees.ACCT_ID%TYPE)  RETURN CHAR AS    v_AverageBAND CHAR(1);    v_NumericBAND NUMBER;   v_NumberEmployees NUMBER;  BEGIN  /* First we need to see how many employees there are for     this account. If there aren't any, we need to raise an error. */   SELECT COUNT(*)     INTO v_NumberEmployees     FROM employees     WHERE department = p_Department       AND acct_id = p_ACCT_ID;   IF v_NumberEmployees = 0 THEN     RAISE_APPLICATION_ERROR(-20001, 'No employees exist for ' ||       p_Department || ' ' || p_ACCT_ID);   END IF;   /* Since bands are stored as letters, we can't use the AVG      function directly on them. Instead, we can use the DECODE      function to convert the bands to numeric values,      and take the average of those. */   SELECT AVG(DECODE(band, '1', 1,                           '2', 2,                           '3', 3,                            '4', 4,                           '5', 5))      INTO v_NumericBAND     FROM employees     WHERE department = p_Department     AND acct_id = p_ACCT_ID;   /* v_NumericBAND now contains the average band, as a number from      1 to 5. We need to convert this back into a letter. The DECODE      function can be used here as well. Note that we are SELECTing       the result into v_AverageBand rather than assigning to it,      because the DECODE function is only legal in an SQL statement. */   SELECT DECODE(ROUND(v_NumericBAND), 5, 'A',                                       4, 'B',                                       3, 'C',                                       2, 'D',                                       1, 'E')     INTO v_AverageBand FROM dual;   RETURN v_AverageBand;  END AverageBand;  CREATE OR REPLACE FUNCTION COUNTPROJECTS   (   /* Returns the number of projects in which the employee      identified by p_emp_ID is currently engaged */   p_empID IN employees.emp_ID%TYPE)  RETURN NUMBER AS   v_TotalProjects NUMBER;    -- Total number of projects    v_AccountProjects NUMBER;    -- projects for one account    CURSOR c_DeptAccts IS       SELECT dept_code, acct_id        FROM employees        WHERE emp_id = p_empID;   BEGIN    FOR v_AccountRec IN c_DeptAccts LOOP      -- Determine the projects for this account.      SELECT num_projects         INTO v_AccountProjects        FROM accounts        WHERE dept_code = v_AccountRec.dept_code        AND acct_id = v_AccountRec.acct_id;      -- Add it to the total so far.      v_Totalprojects := v_Totalprojects + v_AccountProjects;    END LOOP;    RETURN v_Totalprojects;   ND CountProjects;   CREATE OR REPLACE FUNCTION MAXPROJECTS        (p_dept_code IN employees.dept_code%TYPE)    RETURN NUMBER   IS    CURSOR projcur IS       SELECT MAX(current_projects) maxprojects        FROM employees       WHERE p_dept_code = dept_code;     projrec projcur%ROWTYPE;   BEGIN    OPEN projcur;     FETCH projcur INTO projrec;    RETURN projrec.maxprojects;  END maxprojects;  / 

---