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];
- CREATE [OR REPLACE] FUNCTION function_name
- [ (parameter [,parameter]) ]
- RETURN return_datatype
- IS | AS
- [declaration_section]
- BEGIN
- executable_section
- [EXCEPTION
- exception_section]
- 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;
- 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;
To run this:
copyraw
SELECT fn_get_sref_from_adname('f2574687') from dual where rownum
- 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;
- CREATE PUBLIC SYNONYM my_function FOR base_table_owner.my_function;
- GRANT EXECUTE ON my_function TO another_user;
Category: Oracle PL/SQL :: Article: 409
