Курсор, возвращающий слишком много дубликатов
Кажется, у меня проблема с моей процедурой. Это для поиска, и он получает поиск правильно, но возвращает мне один и тот же ответ слишком много раз до точки, где я получаю фатальную ошибку исчерпанной памяти. Это мой код:
FUNCTION pet_search(PETTYPE in VARCHAR2, BREED in VARCHAR2, COLOR in VARCHAR2, PETSIZE in VARCHAR2, TS in VARCHAR2, ENERGY in VARCHAR2, SPACE in VARCHAR2)
RETURN SYS_REFCURSOR
IS
pet_search_result SYS_REFCURSOR;
type_id NUMBER;
breed_id NUMBER;
color_id NUMBER;
size_id NUMBER;
TS_id NUMBER;
energy_id NUMBER;
space_id NUMBER;
BEGIN
select TP.pet_type_code into type_id
from dbadmin.petType TP
where TP.pet_type_name = PETTYPE;
select B.pet_race_code into breed_id
from dbadmin.petRace B
where B.pet_race_name = BREED;
select PC.pet_color_code into color_id
from dbadmin.PetColor PC
where PC.pet_color = COLOR;
select PS.pet_size_code into size_id
from dbadmin.PetSize PS
where PS.pet_size = PETSIZE;
select LS.pet_learn_code into TS_id
from dbadmin.petlearningskill LS
where LS.pet_learn_skill = TS;
select EN.pet_energy_code into energy_id
from dbadmin.petEnergy EN
where EN.pet_energy_level = ENERGY;
select SP.pet_space_code into space_id
from dbadmin.petSpace SP
where SP.pet_space = SPACE;
OPEN pet_search_result FOR select pet_type_name, pet_race_name, pet_cond_name, pet_energy_level, pet_learn_skill, vet_name, person_name, petlocation, petnotes, petabandondescription, pet_space, pet_treatment, pet_color, pet_sickness_name, pet_med_name
from pet, pettype, petrace, petCondition, petSize,petEnergy, petlearningskill, veterinary, person, petSpace, pettreatments, petcolor, petsickness, petmedicine
WHERE pettype.pet_type_code = type_id
AND petrace.pet_race_code = breed_id
AND pet.pet_color_code = color_id
AND pet.pet_size_code = size_id
AND pet.pet_learn_code = TS_id
AND pet.pet_energy_code = energy_id
AND pet.petspace_id = space_id;
RETURN pet_search_result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN null;
END;
END pet_search_package;
Вы видите мою ошибку?
Мы ценим любые предложения
Решение
Кажется, проблема в запросе select, если ваше предложение where не удовлетворяет точно всем условиям, то строки будут дублированы из-за присоединиться.
Ниже приведен пример, в котором говорится о проблеме и о том, как ее решить.
select pet_type_name, pet_race_name, pet_cond_name, pet_energy_level, pet_learn_skill, vet_name, person_name, petlocation, petnotes, petabandondescription, pet_space, pet_treatment, pet_color, pet_sickness_name, pet_med_name
from pet, pettype, petrace, petCondition, petSize,petEnergy, petlearningskill, veterinary, person, petSpace, pettreatments, petcolor, petsickness, petmedicine
WHERE pettype.pet_type_code = type_id
AND petrace.pet_race_code = breed_id
AND pet.pet_color_code = color_id
AND pet.pet_size_code = size_id
AND pet.pet_learn_code = TS_id
AND pet.pet_energy_code = energy_id
AND pet.petspace_id = space_id;
Например, вот две таблицы emp и dept,
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Этот запрос вернет только 1 строку, если выполнены все условия условия where.
SQL> select a.empno,b.deptno,a.ename from emp a,dept b where a.empno=7369 and b.deptno=20;
EMPNO DEPTNO ENAME
---------- ---------- ----------
7369 20 SMITH
Здесь он будет возвращать дубликаты, если условия, в которых предложение не соответствует полностью, или меньшее количество отличительных знаков.
SQL> select a.empno,b.deptno,a.ename from emp a,dept b where a.empno=7369 ;
EMPNO DEPTNO ENAME
--------- ---------- ----------
7369 10 SMITH
7369 20 SMITH
7369 30 SMITH
7369 40 SMITH
Если у вас есть ссылка между этими таблицами, то было бы целесообразно использовать ее в предложении where, а затем сравнить
Другие решения
Вы включаете дюжину или около того ссылочных таблиц данных в предложение FROM основного запроса. Но вы не ограничиваете их, чтобы они вернулись все их рядов. Кроме того, вы не присоединяете их к таблице данных, PET. Это приводит к перекрестному соединению или декартову произведению, которое производит одну строку для каждой комбинации записей из этих таблиц .
Вот ваш запрос переписан, чтобы было понятно, что происходит, а также, чтобы он работал правильно:
OPEN pet_search_result FOR
select pet_type_name
, pet_race_name
, pet_cond_name
, pet_energy_level
, pet_learn_skill
, vet_name
, person_name
, petlocation
, petnotes
, petabandondescription
, pet_space
, pet_treatment
, pet_color
, pet_sickness_name
, pet_med_name
from pet
join pettype TP
on pet.pet_type_code = TP.pet_type_code
join petrace B
on pet.pet_race_code = B.pet_race_code
join petSize PS
on pet.pet_size_code = PS.pet_size_code
join petEnergy EN
on pet.pet_energy_code = EN.pet_energy_code
join petcolor pc
on pet.pet_color_code = PC.pet_color_code
join petlearningskill LS
on pet.pet_learn_code = LS.pet_learn_code
join petSpace SP
on pet.petspace_id = SP.pet_space_code
where TP.pet_type_name = PETTYPE
and B.pet_race_name = BREED
and PS.pet_size = PETSIZE
and PC.pet_color = COLOR
and EN.pet_energy_level = ENERGY
and LS.pet_learn_skill = TS
and SP.pet_space = SPACE;
Написание этого означает, что вы можете отказаться от всех этих маленьких поисков в начале процедуры.
Я опустил следующие таблицы. Вы не предоставили никаких критериев для их ограничения, поэтому они не влияют на набор результатов.
pettreatmentspetConditionpetsicknesspetmedicineveterinaryperson