|
As Solution Using SQL
and a Bind Variable |
The business problem is
a simple one to state. Don't return a result set unless the number
of rows to be returned meets some threshold. For example ... If
there are fewer than 15 rows ... return NULL.
First a solution in SQL: |
CREATE TABLE t AS
SELECT rownum rn
FROM all_objects
WHERE rownum <= 10;
var limit NUMBER
BEGIN
:limit := 6;
END;
/
SELECT *
FROM (
SELECT rn, COUNT(*) OVER () cnt
FROM t)
WHERE cnt >= :limit;
BEGIN
:limit := 60;
END;
/ |
| Second a solution in PL/SQL (that
isn't just embedding the first one in an anonymous block. |
DECLARE
TYPE ProgID IS TABLE OF airplanes.program_id%TYPE
INDEX BY BINARY_INTEGER;
pid_t ProgID;
TYPE OrdDat IS TABLE OF airplanes.order_date%TYPE
INDEX BY BINARY_INTEGER;
ord_t OrdDat;
BEGIN
SELECT program_id, order_date
BULK COLLECT INTO pid_t, ord_t
FROM airplanes
WHERE program_id = '737'
AND customer_id = 'DAL'
AND line_number LIKE '%47%';
IF pid_t.COUNT < 400 THEN
FOR i IN 1..pid_t.COUNT LOOP
dbms_output.put_line(ord_t(i));
END LOOP;
ELSE
NULL;
END IF;
END;
/ |
| If you have faced a business problem
similar to this and need a solution ... send your idea for a new How
Can I? |
