Functions

Example of creating a function and placing it in our program.
Used to avoid repeated records in reports.Where it fetched different vales for same fields.

CREATE OR REPLACE FUNCTION xx_salesname_proj (p_project_id IN NUMBER)

RETURN VARCHAR2
IS
v_full_name VARCHAR2 (240);
v_final_name VARCHAR2 (240);
CURSOR c1 (r_project_id NUMBER)
IS
SELECT DISTINCT full_name
FROM per_all_people_f
WHERE person_id  IN (
SELECT person_id
FROM pa_credit_receivers pcr, pa_projects_all pp
WHERE pcr.project_id = pp.project_id
AND pp.project_id = r_project_id);
v_index NUMBER;
BEGIN
v_full_name := '';
v_final_name := '';
v_index := 0;
OPEN c1 (p_project_id);
LOOP
FETCH c1
INTO v_full_name;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.put_line (v_full_name);
v_index := v_index + 1;
IF v_index = 1
THEN
v_final_name := v_full_name;
ELSE
v_final_name := v_final_name ','
v_full_name;
END IF;
END LOOP;
CLOSE c1;
RETURN v_final_name;
END;
/

In our program call from query

xxri_salesname_proj (pp.project_id) sales_person_name