I
Imagine a table with products. For simplicity lets use just product name and description.
CREATE TABLE products (
product_id NUMBER(10),
product_name VARCHAR2(256),
product_desc VARCHAR2(4000),
--
CONSTRAINT pk_products
PRIMARY KEY (product_id)
);
--
DELETE FROM products;
INSERT INTO products
SELECT 1, 'First product name, one', 'Some description' FROM DUAL UNION ALL
SELECT 2, 'Second product name, two', 'Another description' FROM DUAL UNION ALL
SELECT 3, 'Third product, three', 'Other description' FROM DUAL;
--
COMMIT;
How would you search for a product with specific words in name and description?
If you want to search for products containing 'First' OR 'one' words in name, then it is quite easy (case search and sanitization of inputs not included).
WITH input AS (
SELECT /*+ MATERIALIZE */
'First:one' AS in_name,
'' AS in_desc
FROM DUAL
)
SELECT p.*
FROM products p
CROSS JOIN input
WHERE REGEXP_LIKE(p.product_name, '(' || REPLACE(input.in_name, ':', '|') || ')');
But what if you want to search for products with 'First' AND 'one' words? And what if you have multiple words? And if you don't know how many? And then if you want to combine multiple columns?
Here is my solution utilizing PL/SQL collections, APEX collections, APEX_STRING_UTIL API and possibly materialized view.
Package specification declaring some data types and APEX collection name for the results:
CREATE OR REPLACE PACKAGE product_app AS
v_search_results CONSTANT VARCHAR2(30) := 'SEARCH_RESULTS'; -- collection name
TYPE product_rec IS RECORD (
product_id products.product_id%TYPE,
product_name products.product_name%TYPE,
product_desc products.product_desc%TYPE
);
--
TYPE product_rows IS TABLE OF product_rec;
--
TYPE product_found IS TABLE OF products.product_id%TYPE;
PROCEDURE search_products (
in_name products.product_name%TYPE := NULL,
in_desc products.product_desc%TYPE := NULL
);
FUNCTION get_coll_search_results
RETURN VARCHAR2
RESULT_CACHE;
END;
/
And the body handling searches for product names and descriptions.
CREATE OR REPLACE PACKAGE BODY product_app AS
PROCEDURE search_products (
in_name products.product_name%TYPE := NULL,
in_desc products.product_desc%TYPE := NULL
)
AS
v_products product_rows;
v_search_words APEX_T_VARCHAR2;
v_found_words PLS_INTEGER;
v_found_rows_name product_found := product_found();
v_found_rows_desc product_found := product_found();
-- sanitize inputs
v_search_name CONSTANT products.product_name%TYPE := APEX_STRING_UTIL.GET_SLUG(in_name);
v_search_desc CONSTANT products.product_desc%TYPE := APEX_STRING_UTIL.GET_SLUG(in_desc);
-- calculate number of words
v_target_name CONSTANT PLS_INTEGER := REGEXP_COUNT(v_search_name, '-') + 1;
v_target_desc CONSTANT PLS_INTEGER := REGEXP_COUNT(v_search_desc, '-') + 1;
BEGIN
-- cleanup source data before searching
-- i would suggest to use materialized view or virtual/hidden columns
SELECT
p.product_id,
APEX_STRING_UTIL.GET_SLUG(p.product_name),
APEX_STRING_UTIL.GET_SLUG(p.product_desc)
BULK COLLECT INTO v_products
FROM products p;
-- search for product name
IF v_target_name > 0 THEN
v_search_words := APEX_STRING.SPLIT(v_search_name, '-');
--
FOR p IN 1 .. v_products.COUNT LOOP
v_found_words := 0;
--
FOR i IN 1 .. v_search_words.COUNT LOOP
-- for whole words you could use APEX_STRING_UTIL.PHRASE_EXISTS
IF INSTR(v_products(p).product_name, v_search_words(i)) > 0 THEN
v_found_words := v_found_words + 1;
END IF;
END LOOP;
-- if all words found
IF v_found_words = v_search_words.COUNT THEN
v_found_rows_name.EXTEND(1);
v_found_rows_name(v_found_rows_name.LAST) := v_products(p).product_id;
END IF;
END LOOP;
END IF;
-- search for product desc
IF v_target_desc > 0 THEN
v_search_words := APEX_STRING.SPLIT(v_search_desc, '-');
--
FOR p IN 1 .. v_products.COUNT LOOP
v_found_words := 0;
--
FOR i IN 1 .. v_search_words.COUNT LOOP
IF INSTR(v_products(p).product_desc, v_search_words(i)) > 0 THEN
v_found_words := v_found_words + 1;
END IF;
END LOOP;
-- if all words found
IF v_found_words = v_search_words.COUNT THEN
v_found_rows_desc.EXTEND(1);
v_found_rows_desc(v_found_rows_desc.LAST) := v_products(p).product_id;
END IF;
END LOOP;
END IF;
-- store results in collection if it match all searching conditions
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(v_search_results);
--
FOR p IN 1 .. v_products.COUNT LOOP
CONTINUE WHEN (NOT v_products(p).product_id MEMBER OF v_found_rows_name AND v_target_name IS NOT NULL);
CONTINUE WHEN (NOT v_products(p).product_id MEMBER OF v_found_rows_desc AND v_target_desc IS NOT NULL);
--
APEX_COLLECTION.ADD_MEMBER(
p_collection_name => v_search_results,
p_n001 => v_products(p).product_id
);
END LOOP;
END;
FUNCTION get_coll_search_results
RETURN VARCHAR2
RESULT_CACHE
AS
PRAGMA UDF;
BEGIN
RETURN v_search_results;
END;
END;
/
With the package in place you can search the products table:
(if you want to run this outside of APEX, you would need the APEX session)
BEGIN
product_app.search_products (
in_name => 'FIRST ONE',
in_desc => ''
);
END;
/
How to see the results?
SELECT p.*
FROM products p
JOIN apex_collections s
ON s.collection_name = product_app.get_coll_search_results()
AND s.n001 = p.product_id;
Comments
Post a Comment