Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Creating and Dropping Functions
Oracle Tips by Burleson
 

Functions are created using a command like the one shown in Listing 5.4.

Listing 5.4 Creating a function.

CREATE OR REPLACE
FUNCTION Raise_Salary (nBaseSalary   IN     number,
                       nRaisePercent IN     number)
 
  RETURN number;
 
IS
 
BEGIN
  RETURN (nBaseSalary * nRaisePercent);
END;
/

Using the OR REPLACE clause indicates that Oracle should replace an existing function of the same name if it exists. If the object exists and the OR REPLACE clause isnít used, an error will occur. The / instructs SQL*Plus to execute the CREATE command.

Functions are dropped from inside SQL*Plus using the DROP command:

DROP FUNCTION Raise_Salary;

Local Functions

Functions may also be declared within another block of stored PL/SQL code. This is somewhat unusual. Most functions are created as standalone objects to provide greater modularity. Listing 5.5 illustrates the definition of a function as part of a procedureís declarations.

Listing 5.5 Declaring a local function within a procedure.

PROCEDURE Annual_Review
 
IS
 
   iPerformanceRating  integer;
   iWarningsIssued     integer;
   nBaseSalary         number;
   nOntimeRating       number;
   nTotalRaisePercent  number;
   nTotalBonus         number;
 
   --
   -- The total number of working days in the year. This is
   -- calculated as follows:
   --
   --     104 weekend days
   --      10 paid holidays (11 in leap year)
   --      10 sick days
   --
   TOTAL_WORKING_DAYS  CONSTANT integer := 241;
 
   --
   -- Any employee working for the company for over one year.
   --
   CURSOR All_Employees_cur
   IS
   SELECT employee_num, eff_hire_date, base_salary,
          late_days, warnings, performance_rating
   FROM   EMPLOYEES
   WHERE    (to_date (SYSDATE, 'YYYY')
          - to_date (eff_hire_date, 'YYYY')) > 1;
 
    FUNCTION Raise_Salary (nBaseSalary  IN     number,
                           nRaiseAmount IN     number)
 
    RETURN number
 
    IS
 
    BEGIN
      RETURN (nBaseSalary * nRaiseAmount);
    END;
 
BEGIN
  FOR All_Employees_rec IN All_Employees_cur LOOP
      --
      -- Initialize the variables each time through the
      -- loop (once for each employee).
      --
      iPerformanceRating := All_Employees_rec.performance_rating;
      iWarningsIssued    := All_Employees_rec.warnings;
      nBaseSalary        := All_Employees_rec.base_salary;
      nOntimeRating      := 0;
      nTotalRaisePercent := 0.0;
      nIncreasedSalary   := 0.0;
      nTotalBonus        := 0.0;
 
      --
      -- Calculate the number of days that the employee was on time
      -- for work. If this percentage is above 98%, the employee
      -- earns a .5% pay raise.
      --
      nOntimeRating := ( TOTAL_WORKING_DAYS
                    - All_Employees_rec.late_days);
      nOntimeRating := (nOntimeRating / TOTAL_WORKING_DAYS) * 100;
 
      IF (nOntimeRating > 98) THEN
         nTotalRaisePercent := nTotalRaisePercent + 0.005;
         --
         -- Perfect attendance gets a higher bonus percentage too!
         --
         IF (nOntimeRating = 100) THEN
            nTotalRaisePercent := nTotalRaisePercent + 0.001;
         END IF;
      END IF;
 
     .
     .
     .
 
      --
      -- Store the outcome of the analysis in the EMPLOYEES table.
      --
      UPDATE EMPLOYEES
      SET    base_salary = nIncreasedSalary,
             xmas_bonus  = nTotalBonus
      WHERE  CURRENT OF All_Employees_cur;
   END LOOP;
END;

Local functions are accessible only to the procedure or function that declares the local object. Local functions can also reference constants, variables, datatypes, and user-defined exceptions defined within the containing procedure or function. In Listing 5.5, the Raise_Salary() function is accessible only to the procedure Annual_Review() and can access all the variables and constants defined within the procedure.

If a function needs to be referenced from more than one stored object, local definitions arenít appropriate. For maintenance and testing purposes, itís probably better to define most (if not all) objects as standalone objects.

 

           
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter