SQL & PL/SQL

Hi,

I have been tasked with writing an Oracle Function to query a set of tables to locate data that best matches what a user needs.

If data is not returned in a given query, we need the procedure to loop and run a modified query. If that query does not give data, we continue to loop and refine until we reach a point where we have expended all of our filtering levels.

I am new to writing PL SQL and have been doing a lot of googling trying to figure out how to do this, but I am stuck :(

I have not been able to figure out how to continue looping in the Procedure when there are no results in the record set.

It is probably something simple, I just haven't been able to figure it out.

HERE'S WHAT SEEMS TO BE THE PERTINENT PART OF THE CODE:

OPEN v_cursor FOR v_sql;
DBMS_OUTPUT.PUT_LINE(v_sql);

--THIS IS THE PORTION I AM HAVING PROBLEMS WITH:**
--If there are results I want to set the following**
--haveResultsOrAllQueriesDone := TRUE;**
--Otherwise, I want to leave haveResultsOrAllQueriesDone := FALSE; and continue to loop**
RETURN v_cursor;
END LOOP;

Here is my complete code so far:



HERE IS THE COMPLETE FUNCTION AS I HAVE IT SO FAR:

create or replace
FUNCTION LOCATEACONSULTANT_function
(
p_action IN VARCHAR2,
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_exact_match IN VARCHAR2,
p_country_code IN VARCHAR2,
p_state IN VARCHAR2,
p_city IN VARCHAR2,
p_zip_or_postalcode IN VARCHAR2,
p_language_code IN VARCHAR2)
RETURN types.ref_cursor
AS
v_cursor types.ref_cursor;
v_temp_cursor types.ref_cursor;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DECLARE
type ref_cursor
IS
REF
CURSOR;
cursor_locateAConsultant ref_cursor;
--
v_select VARCHAR2(3000);
v_from VARCHAR2(2000);
v_where VARCHAR2(4000);
v_sql VARCHAR2(4000);
--
stateMapped ref_zip_mappings.state%type;
countyMapped ref_zip_mappings.county%type;
cityMapped ref_zip_mappings.city%type;
--
haveQueriedLanguage BOOLEAN := FALSE;
haveQueriedZip BOOLEAN := FALSE;
haveQueriedCity BOOLEAN := FALSE;
haveQueriedCounty BOOLEAN := FALSE;
haveQueriedState BOOLEAN := FALSE;
haveResultsOrAllQueriesDone BOOLEAN := FALSE;
haveDoneAllQueries BOOLEAN := FALSE;
tempLikeZip VARCHAR2(100);
BEGIN
v_select := 'SELECT customers.customer_id, customers.last_name, customers.first_name, ';
v_select := v_select || ' customer_contacts.email_address, addresses.state, addresses.city, ';
v_select := v_select || ' customer_contacts.web_site, customer_languages.language_code, customer_titles.pqv ';
--
v_from := ' FROM customers, customer_addresses, addresses, customer_contacts, customer_titles, customer_languages';
--
v_where := ' WHERE customers.customer_id = customer_addresses.customer_id ';
v_where := v_where || ' AND customer_addresses.address_id = addresses.address_id ';
v_where := v_where || ' AND customers.customer_id = customer_contacts.customer_id ';
v_where := v_where || ' AND customers.customer_id = customer_titles.customer_id ';
v_where := v_where || ' AND customers.preferred_language_code = customer_languages.language_code ';
v_where := v_where || ' AND UPPER(addresses.country_code) = UPPER(''' || p_country_code || ''') ';
v_where := v_where || ' AND UPPER(customers.exclude_locate) = '||''''||'N'||''''||'';
v_where := v_where || ' AND UPPER(customer_titles.customer_type) = '||''''||'CON'||''''||'';
v_where := v_where || ' AND UPPER(customer_titles.dp_id) LIKE '||''''||'CN%'||''''||'';
-- Line below assumes that 1 is the billing_address_type
v_where := v_where || ' AND customer_addresses.address_type_code = 1 ';
v_where := v_where || ' AND ';
v_where := v_where || ' ( ';
v_where := v_where || ' UPPER(customer_contacts.web_site) LIKE '||''''||'%MYARBONNE.COM%'||''''||' ';
v_where := v_where || ' OR ';
v_where := v_where || ' UPPER(customer_contacts.web_site) LIKE '||''''||'%MYARBONNE.COM.AU%'||''''||' ';
v_where := v_where || ' OR ';
v_where := v_where || ' UPPER(customer_contacts.web_site) LIKE '||''''||'%MYARBONNE.CA%'||''''||' ';
v_where := v_where || ' OR ';
v_where := v_where || ' UPPER(customer_contacts.web_site) LIKE '||''''||'%MYARBONNE.UK%'||''''||' ';
v_where := v_where || ' ) ';
--
IF (UPPER(p_action) =UPPER('search')) THEN
v_where := v_where || ' AND UPPER(addresses.state) = UPPER(trim(''' || p_state || ''')) ';
IF UPPER(p_exact_match) = UPPER('y') THEN
v_where := v_where || ' AND UPPER(customers.first_name) = UPPER(trim(''' || p_first_name || ''')) ';
ELSE
v_where := v_where || ' AND UPPER(SOUNDEX(customers.first_name)) = UPPER(SOUNDEX(trim(''' || p_first_name || '''))) ';
END IF;
IF UPPER(p_last_name) <> '' THEN
IF UPPER(p_exact_match) = UPPER('y') THEN
v_where := v_where || ' AND UPPER(customers.last_name) = UPPER(trim(''' || p_last_name || ''')) ';
ELSE
v_where := v_where || ' AND UPPER(SOUNDEX(customers.last_name)) = UPPER(SOUNDEX(trim(''' || p_last_name || ''')) ';
END IF;
END IF;
IF UPPER(p_city) <> '' THEN
v_where := v_where || ' AND UPPER(addresses.city) = UPPER(''' || p_city || ''') ';
END IF;
IF UPPER(p_zip_or_postalcode) <> '' THEN
v_where := v_where || ' AND UPPER(addresses.zip_or_postalcode) = UPPER(''' || p_zip_or_postalcode || ''') ';
END IF;
END IF;
--
WHILE (haveResultsOrAllQueriesDone = FALSE)
LOOP
IF (p_action = 'locate') THEN
IF (haveQueriedLanguage = FALSE) THEN
IF (p_language_code <> '') THEN
v_where := v_where|| ' AND UPPER(customer_languages.language_code) = UPPER(''' || p_language_code || ''') ';
haveQueriedLanguage := TRUE;
END IF;
--ELSE
-- haveQueriedLanguage := FALSE;
END IF;
IF (haveQueriedZip = FALSE) THEN
v_where := v_where|| ' AND UPPER(addresses.zip_or_postalcode) = UPPER(''' || p_zip_or_postalcode || ''') ';
IF (haveQueriedLanguage = FALSE) THEN
haveQueriedZip := TRUE;
END IF;
ELSIF (haveQueriedCity = FALSE) THEN
IF (p_city <> '') THEN
v_where := v_where|| ' AND UPPER(addresses.city) = UPPER('||''''|| p_city ||''''||') ' ;
ELSE
IF (p_country_code = 'UK') THEN
tempLikeZip := SUBSTR(p_zip_or_postalcode,( LENGTH(TRIM(p_zip_or_postalcode)) - 2 ));
v_where := v_where || ' AND UPPER(addresses.zip_or_postalcode) LIKE UPPER(TRIM('||'''%'||tempLikeZip ||''''||')) ' ;
ELSE
OPEN cursor_locateAConsultant FOR 'SELECT city FROM ref_zip_mappings where UPPER(zip_or_postalcode) = UPPER(TRIM('||''''|| p_zip_or_postalcode ||''''||')) ' ;
-- OPEN v_temp_cursor FOR 'SELECT city FROM ref_zip_mappings where UPPER(zip_or_postalcode) = UPPER(TRIM('||''''|| p_zip_or_postalcode ||''''||')) ' ;
LOOP
FETCH cursor_locateAConsultant INTO cityMapped;
EXIT
WHEN cursor_locateAConsultant%NOTFOUND;
END LOOP;
CLOSE cursor_locateAConsultant;
-- RETURN v_temp_cursor;
v_where := v_where || ' AND UPPER(addresses.city) = UPPER('||''''|| cityMapped ||''''||') ' ;
END IF;
END IF;
haveQueriedCity := TRUE;
ELSIF haveQueriedCounty = FALSE THEN
IF (p_country_code = 'UK') THEN
tempLikeZip := UPPER(SUBSTR(p_zip_or_postalcode,( LENGTH(TRIM(p_zip_or_postalcode)) - 3 )));
v_where := v_where|| ' AND UPPER(addresses.zip_or_postalcode) LIKE '||'''%'||tempLikeZip ||''''||' ' ;
ELSE
OPEN cursor_locateAConsultant FOR 'SELECT county FROM ref_zip_mappings where UPPER(zip_or_postalcode) = UPPER('||''''|| p_zip_or_postalcode ||''''||') ' ;
LOOP
FETCH cursor_locateAConsultant INTO countyMapped;
EXIT
WHEN cursor_locateAConsultant%NOTFOUND;
END LOOP;
CLOSE cursor_locateAConsultant;
v_where := v_where|| ' AND UPPER(addresses.county) = UPPER('||''''|| countyMapped ||''''||') ' ;
END IF;
haveQueriedCounty := TRUE;
END IF;


ELSIF haveQueriedState = FALSE THEN
IF (p_state <> '') THEN
v_where := v_where|| ' AND UPPER(addresses.state) = UPPER('||''''|| p_state ||''''||') ' ;
ELSE
IF (p_country_code = 'UK') THEN
tempLikeZip := UPPER(SUBSTR(p_zip_or_postalcode,( LENGTH(TRIM(p_zip_or_postalcode)) - 4 )));
v_where := v_where|| ' AND UPPER(addresses.zip_or_postalcode) LIKE '||'''%'||tempLikeZip ||''''||' ' ;
ELSE
OPEN cursor_locateAConsultant FOR 'SELECT state FROM ref_zip_mappings where UPPER(zip_or_postalcode) = UPPER('||''''|| p_zip_or_postalcode ||''''||') ' ;
LOOP
FETCH cursor_locateAConsultant INTO stateMapped;
EXIT WHEN cursor_locateAConsultant%NOTFOUND;
END LOOP;
CLOSE cursor_locateAConsultant;
v_where := v_where|| ' AND UPPER(addresses.state) = UPPER('||''''|| stateMapped ||''''||') ' ;
END IF;
haveQueriedState := TRUE;
END IF;

END IF;
v_sql := v_select || v_from || v_where;
OPEN v_cursor FOR v_sql;
DBMS_OUTPUT.PUT_LINE(v_sql);

--THIS IS THE PORTION I AM HAVING PROBLEMS WITH:**
--If there are results I want to set the following**
--haveResultsOrAllQueriesDone := TRUE;**
--Otherwise, I want to leave haveResultsOrAllQueriesDone := FALSE; and continue to loop**
RETURN v_cursor;
END LOOP;
--
END;
END;