Print

Basic Oracle Function Structure

What?
I need to refer to how to create a function in Oracle PL/SQL and sites on the net just attempt to overcomplicate everything and have forgotten how it is to be new to Oracle. I need a function in it's simplest form and if I want to torture my successors, I'll complicate the function myself.

How?
Functions are supposed to return a single value, which is all I need in this case. In my example, I need to submit an Active Directory (AD) username and receive a student ID number instead, all with the aim to improve performance on some SSRS queries which accept either an ID number or an AD name as user parameters.

Syntax:
copyraw 
CREATE [OR REPLACE] FUNCTION function_name
    [ (parameter [,parameter]) ]

    RETURN return_datatype

IS | AS
    [declaration_section]

BEGIN
    executable_section

[EXCEPTION
    exception_section]

END [function_name];
  1.  CREATE [OR REPLACE] FUNCTION function_name 
  2.      [ (parameter [,parameter]) ] 
  3.   
  4.      RETURN return_datatype 
  5.   
  6.  IS | AS 
  7.      [declaration_section] 
  8.   
  9.  BEGIN 
  10.      executable_section 
  11.   
  12.  [EXCEPTION 
  13.      exception_section] 
  14.   
  15.  END [function_name]; 

Example: My example accepts an Active Directory (AD) username and returns the student ID:
copyraw 
CREATE OR REPLACE FUNCTION fn_get_sref_from_adname
    (ad_username IN varchar2)
    RETURN number
IS
    student_no number;

    cursor c1 is
    select student_reference
      from student_accounts_table
      where student_ad_account = trim(lower(ad_username));
BEGIN
    open c1;
    fetch c1 into student_no;

    if c1%notfound then
        student_no := 0;
    end if;

    close c1;

RETURN student_no;

EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

END fn_get_sref_from_adname;
  1.  CREATE OR REPLACE FUNCTION fn_get_sref_from_adname 
  2.      (ad_username IN varchar2) 
  3.      RETURN number 
  4.  IS 
  5.      student_no number; 
  6.   
  7.      cursor c1 is 
  8.      select student_reference 
  9.        from student_accounts_table 
  10.        where student_ad_account = trim(lower(ad_username)); 
  11.  BEGIN 
  12.      open c1; 
  13.      fetch c1 into student_no; 
  14.   
  15.      if c1%notfound then 
  16.          student_no := 0; 
  17.      end if; 
  18.   
  19.      close c1; 
  20.   
  21.  RETURN student_no; 
  22.   
  23.  EXCEPTION 
  24.  WHEN OTHERS THEN 
  25.        raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); 
  26.   
  27.  END fn_get_sref_from_adname; 

To run this:
copyraw 
SELECT fn_get_sref_from_adname('f2574687') from dual where rownum
  1.  SELECT fn_get_sref_from_adname('f2574687') from dual where rownum 

Additional:
Accessible by other users?
copyraw 
CREATE PUBLIC SYNONYM my_function FOR base_table_owner.my_function;
GRANT EXECUTE ON my_function TO another_user;
  1.  CREATE PUBLIC SYNONYM my_function FOR base_table_owner.my_function; 
  2.  GRANT EXECUTE ON my_function TO another_user; 
Category: Oracle PL/SQL :: Article: 409