Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: Create Function Jump to:  
Category: >> Oracle PL/SQL >> Create Function Bookmark and Share

<< lastnext >>

Snippet Name: Create Function

Description: Some examples of user-created functions.

Also see:
» FUNCTIONS: Deterministic
» FUNCTIONS: Nested Functions
» FUNCTIONS: IF statement
» FUNCTIONS: date/time
» FUNCTIONS: Sample functions
» FUNCTIONS: drop
» FUNCTIONS: Recompile
» FUNCTIONS: DEBUG mode
» FUNCTIONS: IN OUT parameter
» FUNCTIONS: with output parameters
» FUNCTIONS: with parameters
» FUNCTIONS: without parameters
» FUNCTIONS: Create function
» FUNCTIONS: special restrictions
» FUNCTIONS: System Privileges
» IN Function
» Built-In Functions: CASE
» Built-In Functions: DECODE
» SUBST and INSTR together
» INSTR (InString)
» SUBSTR (SubString)
» String Functions: REVERSE
» String Functions: LENGTH
» String Functions: INSTR
» String Functions: CONCAT
» String Functions: CHAR
» String Functions: INITCAP
» String Functions: LOWER
» String Functions: UPPER
» Date Functions: NUMTOYMINTERVAL

Comment: (none)

Language:
Highlight Mode: PLSQL
Last Modified: March 01st, 2009

REM
REM CREATE stored functions required FOR the applications discussed IN 
REM "Developing Oracle Forms Applications."
REM
 
PROMPT
PROMPT CREATE the functions used BY the applicationS.
PROMPT 
 
CREATE OR REPLACE FUNCTION  Days_Between (first_dt   DATE,
                                  second_dt  DATE)
RETURN NUMBER IS
 dt_one NUMBER;
 dt_two NUMBER;
BEGIN
 dt_one := TO_NUMBER(TO_CHAR(first_dt, 'DDD'));
 dt_two := TO_NUMBER(TO_CHAR(second_dt, 'DDD'));
 
 RETURN (dt_two - dt_one);
END Days_Between;
 
/
 
 
CREATE OR REPLACE FUNCTION Compute_Movie_Revenue (from_dt  DATE,
                           to_dt    DATE,
                           movie_id NUMBER)
RETURN NUMBER IS
  days     NUMBER;
  revenue     NUMBER := 0;
 
  CURSOR movie_rentals IS
    SELECT RETURN_DT, RENT_DT, DAILY_RATE
    FROM RENTALS
    WHERE RENT_DT >= from_dt AND
      RETURN_DT <= to_dt AND
      RENTALS.TAPE_ID IN (
        SELECT TAPES.TAPE_ID
        FROM TAPES
        WHERE TAPES.MOVIE_ID = movie_id);
BEGIN
  FOR rental IN movie_rentals LOOP
    days := Days_Between(rental.RENT_DT, rental.RETURN_DT);
    revenue := revenue + days * rental.DAILY_RATE;  
  END LOOP;
 
  RETURN revenue;
 
  EXCEPTION
    WHEN OTHERS THEN 
    RAISE_APPLICATION_ERROR(-20002,
                 'Movie revenue cannot be computed.');
END Compute_Movie_Revenue;
 
/
 
CREATE OR REPLACE FUNCTION Compute_Customer_Revenue (from_dt     DATE,
                              to_dt          DATE,
                              customer_id NUMBER)
RETURN NUMBER IS
  days     NUMBER;
  revenue     NUMBER := 0;
 
  CURSOR customer_rentals IS
    SELECT RETURN_DT, RENT_DT, DAILY_RATE
    FROM RENTALS
    WHERE RENT_DT >= from_dt AND
      RETURN_DT <= to_dt AND
      RENTALS.CUSTOMER_ID = customer_id;
BEGIN
  FOR rental IN customer_rentals LOOP
    days := Days_Between(rental.RETURN_DT, rental.RENT_DT);
    revenue := revenue + days * rental.DAILY_RATE;  
  END LOOP;
 
  RETURN revenue;
 
  EXCEPTION
    WHEN OTHERS THEN 
    RAISE_APPLICATION_ERROR(-20001,
      Customer revenue cannot be computed.');
END Compute_Customer_Revenue;
 
/
 
CREATE OR REPLACE FUNCTION Get_Sequence_Id RETURN NUMBER IS
  seq_id     NUMBER;
BEGIN
  SELECT MRD_SEQ.NEXTVAL
  INTO seq_id
  FROM DUAL;
 
  RETURN seq_id;
END;
 
/


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 77 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?