Hi Mattie,
Here's a quick example I grabbed that is lightly sprinkled with comments but they're enough to guide the reader. It's not a particularly complex function, but I hope you can see how it gives clues about what's going on without requiring the reader to fully understand the code. Please no one kill me, I realize this is an Oracle function.
CREATE OR REPLACE FUNCTION example.idt_f_get_constant_dt (
/*
| Unit Name: idt_f_get_constant_dt
| File Name: idt_functions.sql
| Purpose: Return a global constant, if a non-date value is encountered, return null
| Parameters: p_constant_nm IN VARCHAR2 the constant name requested
| p_site_cd IN VARCHAR2 the site code requested (defaults to GLOBAL)
| return OUT VARCHAR2 the constant value as a date. If a site
| constant is requested but not found,
| the global constant of the same name is
| returned. Null if no value is found or
| the value is not a date.
|
| Who: Date: Ver: Description (References):
| JTL 05/03/2006 1.0.0 Initial Build
| JTL 06/25/2006 1.14.0 Production Release of [system]
|
*/
p_constant_nm IN VARCHAR2,
p_site_cd IN VARCHAR2 := 'GLOBAL'
)
RETURN DATE
IS
v_data_type_cd VARCHAR2(10);
v_value_txt VARCHAR2(100);
v_value_dt DATE;
v_value_nbr NUMBER;
v_count_nbr NUMBER;
v_return_dt VARCHAR2(100);
BEGIN
/* determine the whether the constant exists */
SELECT COUNT(*)
INTO v_count_nbr
FROM example.idt_constant c
WHERE c.constant_nm = p_constant_nm
AND c.site_cd = p_site_cd;
IF v_count_nbr = 1 THEN
/* found constant, determine data type */
SELECT NVL(c.data_type_cd, 'STRING'),
c.value_txt,
c.value_dt,
c.value_nbr
INTO v_data_type_cd,
v_value_txt,
v_value_dt,
v_value_nbr
FROM example.idt_constant c
WHERE c.constant_nm = p_constant_nm
AND c.site_cd = p_site_cd;
/* set return value */
IF v_data_type_cd = 'STRING' THEN
v_return_dt := NULL;
ELSIF v_data_type_cd = 'DATE' THEN
v_return_dt := v_value_dt;
ELSIF v_data_type_cd = 'NUMBER' THEN
v_return_dt := NULL;
ELSE
v_return_dt := NULL;
END IF;
/* did not find the site-specific constant, so check for a global version */
ELSIF p_site_cd <> 'GLOBAL' THEN
v_return_dt :=
example.idt_f_get_constant_dt(p_constant_nm => p_constant_nm, p_site_cd => 'GLOBAL');
/* did not find the global constant */
ELSE
v_return_dt := NULL;
END IF;
/* return the value */
RETURN v_return_dt;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END idt_f_get_constant_dt;