User-Defined Functions «Prev  Next»

Create Oracle Function with Syntax

  1. CREATE FUNCTION GET_NEWS (I_DATE IN DATE) RETURN VARCHAR2 AS...
    

    Function can be used within a SQL query.
  2. CREATE FUNCTION FIRST_LAST (ANYNAME IN VARCHAR2) RETURN BOOLEAN AS...
    
    Function cannot be used within a SQL query.
  3. CREATE FUNCTION PUT_HERE RETURN NUMBER AS...
    
    Function requires no parameters.
  4. CREATE FUNCTION GOLDEN (ICOLOR IN BOOLEAN) AS...
    

    Function has a syntax error.

Subprograms: Procedures and Functions

There are two types of subprograms called procedures and functions, which can accept parameters and be invoked (called).
The SQL CREATE PROCEDURE statement lets you create standalone procedures that are stored in the database.
The SQL CREATE FUNCTION statement lets you create standalone functions that are stored in an Oracle database. These stored (schema level) subprograms can be accessed from SQL. As shown in Example 2, a subprogram is like a miniature program, beginning with a header followed by an
  1. optional declarative part,
  2. an executable part, and
  3. an optional exception-handling part.



Example 2: Creating a Stored Subprogram

-- including OR REPLACE is more convenient when updating a subprogram
CREATE OR REPLACE PROCEDURE award_bonus (emp_id NUMBER, bonus NUMBER) AS
commission REAL;
comm_missing EXCEPTION;
BEGIN -- executable part starts here
SELECT commission_pct / 100 INTO commission FROM employees
WHERE employee_id = emp_id;
IF commission IS NULL THEN
RAISE comm_missing;
ELSE
UPDATE employees SET salary = salary + bonus*commission
WHERE employee_id = emp_id;
END IF;
EXCEPTION -- exception-handling part starts here
WHEN comm_missing THEN
DBMS_OUTPUT.PUT_LINE('This employee does not receive a commission.');
commission := 0;
WHEN OTHERS THEN
NULL; -- for other exceptions do nothing
END award_bonus;
/
CALL award_bonus(150, 400);

When called, this procedure accepts an employee Id and a bonus amount. It uses the Id to select the employee's commission percentage from a database table and, at the same time, convert the commission percentage to a decimal amount. Then, it checks the commission amount. If the commission is null, an exception is raised; otherwise, the employee's salary is updated.