procedura insert into ... Błąd podczas wywołania

Witam serdecznie,
mam 3 tab. employess, jobs i departments. napisałam procedurę dodającą wiersz do tab employess:

CREATE TABLE employess (
  id NUMBER(4) NOT NULL PRIMARY KEY,
  first_name VARCHAR2(30),
  last_name VARCHAR2(30),
  email VARCHAR2(40),
  phone  NUMBER(12),
  jobs_id NUMBER(4),
  salary NUMBER(4),
  departments_name VARCHAR2(30)
);
ALTER TABLE employess ADD FOREIGN KEY (departments_name) REFERENCES departments(name);

ALTER TABLE employess ADD FOREIGN KEY (jobs_id) REFERENCES jobs(id);

Procedura nie wyświetla żadnych błędów :) Jednak pojawia mi się błąd podczas jej wywołania:

ORA-01422: exact fetch returns more than requested number of rows.

Domyślam się, że jest to błąd wywołany kluczem obcym, ponieważ działają mi procedury dodające wiersz do tab bez FOREIGN KEY.

Moja procedura przedstawia się następująco:

CREATE OR REPLACE PROCEDURE dodajWiersz (v_id employess.id%TYPE, v_first_name employess.first_name%TYPE,
										 v_last_name employess.last_name%TYPE, v_email employess.email%TYPE,
										 v_phone employess.phone%TYPE, v_jobs_id employess.jobs_id%TYPE,
										 v_salary employess.salary%TYPE,
										 v_departments_name employess.departments_name%TYPE) IS
j_id jobs.id%TYPE;
d_name departments.name%type;
   BEGIN
   
select id INTO j_id from jobs;
if(j_id != v_jobs_id)
	then raise_application_error( -20000, 'Nie istnieje takie stanowisko pracy '); 
end if;
select name INTO d_name from departments;
if(d_name != v_departments_name)
	then raise_application_error( -20001, 'Nie istnieje taki dział'); 
end if;
for c in (select d_name from departments)
loop
if(c.d_name = v_departments_name)
then
	for w in (select j_id from jobs)
		loop
			if(w.j_id= v_jobs_id)
			then
 INSERT INTO employess VALUES(v_id, v_first_name, v_last_name, v_email, v_phone, v_jobs_id, v_salary, v_departments_name );
 
		end if;
	end loop;
end if;
end loop;   	
	 EXCEPTION 
     WHEN dup_val_on_index THEN 
         dbms_output.put_line('Zły ID');
   END dodajWiersz;

Proszę uprzejmie o jakąś wskazówkę w rozwiązaniu tego błędu ...?

Instrukcja SELECT INTO zwraca więcej niż jeden rekord.

1. Ustaw jakieś warunki w WHERE które wyelimują problem lub
2. Obsłuż exception TOO_MANY_ROWS lub
3. Zamiast kursora niejawnego zadeklaruj jawny.

SELECT id INTO j_id FROM jobs;

SELECT name INTO d_name FROM departments;

jak myślisz ile każde zapytanie zwróci Ci rekordów :>

FOR c IN (SELECT d_name FROM departments)
loop
IF(c.d_name = v_departments_name)
THEN

co to za potwór???, ta druga pętla też. Wiesz co to zawężanie wyniku do potrzebnych danych??

SELECT d_name FROM departments WHERE d_name = v_departments_name

masz dokładnie to samo, a przy dużej ilości rekordów i odpowiednim indeksie wykona się duuuużo szybciej.
Druga

jak zmieniłam

SELECT d.name, j.id INTO dn,ji  FROM departments d, jobs j WHERE dn = v_departments_name and ji= v_jobs_id;

to mi się pojawił błąd :

ORA-01403: no data found

....?

Procedurę przerobiłam w ten sposób.

CREATE OR REPLACE PROCEDURE dodajWiersz (v_id employess.id%TYPE, v_first_name employess.first_name%TYPE,
										 v_last_name employess.last_name%TYPE, v_email employess.email%TYPE,
										 v_phone employess.phone%TYPE, v_jobs_id employess.jobs_id%TYPE,
										 v_salary employess.salary%TYPE,
										 v_departments_name employess.departments_name%TYPE) IS
ji jobs.id%TYPE;
dn departments.name%type;
   BEGIN
 
SELECT d.name, j.id INTO dn,ji  FROM departments d, jobs j WHERE dn = v_departments_name and ji= v_jobs_id;

if(ji= v_jobs_id and dn = v_departments_name ) then
     INSERT INTO employess VALUES(v_id, v_first_name, v_last_name, v_email, v_phone, v_jobs_id, v_salary, v_departments_name );
end if;
	   	
	 EXCEPTION 
     WHEN dup_val_on_index THEN 
         dbms_output.put_line('Zły ID');
   END dodajWiersz;

Próbuję przerobić też na ten kursor jawny, ale jeszcze nie zdążyłam :)

zrobiłam ten kursor jawny :) Nie ma błędów , jak kompiluje procedurę to zwraca, że dodało ale jak sprawdzam tabele employess to nic nie dodało :(

tu już za bardzo nie wiem , bo nie ma żadnych błędów Nie wiem co mam poprawić...?
tak napisałam procedurę z kursorem :

CREATE OR REPLACE PROCEDURE dodajWiersz (v_id employess.id%TYPE, v_first_name employess.first_name%TYPE,
										 v_last_name employess.last_name%TYPE, v_email employess.email%TYPE,
						                                 v_phone employess.phone%TYPE, v_jobs_id employess.jobs_id%TYPE,
                                                                                 v_salary employess.salary%TYPE,
                                                                                 v_departments_name employess.departments_name%TYPE) IS
CURSOR kursor IS 
SELECT d.name, j.id  FROM departments d, jobs j WHERE d.name = v_departments_name and j.id = v_jobs_id;
j_id NUMBER(3); d_name VARCHAR2(30);
   BEGIN
   OPEN kursor;
   LOOP
        FETCH kursor INTO j_id, d_name;
	EXIT WHEN kursor%NOTFOUND;
	IF (j_id = v_jobs_id and d_name = v_departments_name ) THEN
		INSERT INTO employess VALUES(v_id, v_first_name, v_last_name, v_email, v_phone, v_jobs_id, v_salary, v_departments_name );
	END IF;
   END LOOP;
   
   
	 EXCEPTION 
     WHEN dup_val_on_index THEN 
         dbms_output.put_line('Zły ID');
	CLOSE kursor;
   END dodajWiersz;

o jakąś wskazówkę można prosić ?

to może od początku - co i kiedy (przy jakich warunkach) ma zostać dodane do employess

chciałabym dodać wiersz do tab employess. Wygląda tak:

CREATE TABLE employess (
  id NUMBER(4) NOT NULL PRIMARY KEY,
  first_name VARCHAR2(30),
  last_name VARCHAR2(30),
  email VARCHAR2(40),
  phone  NUMBER(12),
  jobs_id NUMBER(4),-- FOREIGN KEY REFERENCES jobs(id) ,
  salary NUMBER(4),
  departments_name VARCHAR2(30)-- FOREIGN KEY REFERENCES departments(id)
);

ALTER TABLE employess
ADD FOREIGN KEY (departments_name)
REFERENCES departments(name);

ALTER TABLE employess
ADD FOREIGN KEY (jobs_id)
REFERENCES jobs(id);

Jeśli będzie dodane stanowisko do tab jobs:

CREATE TABLE jobs (
  id NUMBER(4) NOT NULL PRIMARY KEY,
  job_title VARCHAR2(30),
  max_salary NUMBER(4),
  min_salary NUMBER(4)
);

i będzie dodany dział do tab departments:

CREATE TABLE departments(
  id NUMBER(4) NOT NULL ,
  name VARCHAR2(30) PRIMARY KEY,
  locations_id NUMBER(4) -- FOREIGN KEY REFERENCES locations(id)
);

ALTER TABLE departments
ADD FOREIGN KEY (locations_id)
REFERENCES locations(id);

Jeśli dodawany wiersz będzie zawierał rekordy które będą zapisane w tab jobs i departments. Procedura doda nowy wiersz do tabeli employess. Jeśli któryś z rekordów nie będzie istniał, wyświetli się odpowiedni komunikat. Chodzi mi o sprawdzanie:
id z tab. jobs
name z tab. departments

ponieważ w tab employess są klucze obce to tych tab.

:)

męczę się z tym dalej :(

działa mi dodawanie, tylko jak zrobić te sprawdzanie? Chciałabym prosić o jakąś wskazówkę ..

To kod na czym skończyłam, próbowałam to na funkcje przerobić z sprawdzaniem ale nie wyszło.

set serveroutput on
CREATE OR REPLACE PROCEDURE dodajWiersz (v_id employess.id%TYPE, v_first_name employess.first_name%TYPE,
										 v_last_name employess.last_name%TYPE, v_email employess.email%TYPE,
										 v_phone employess.phone%TYPE, v_jobs_id employess.jobs_id%TYPE,
										 v_salary employess.salary%TYPE,
										 v_departments_name employess.departments_name%TYPE) 
										 IS 
j_id jobs.id%TYPE;
d_name departments.name%type;
errJobs EXCEPTION;
errDepartments EXCEPTION;
odp VARCHAR2(50);
BEGIN									 
--SELECT d.name, j.id INTO d_name, j_id  FROM departments d, jobs j WHERE d_name =v_departments_name AND j_id=v_jobs_id ;
	
if( j_id != v_jobs_id)
	then --odp := 'nie ma takiego' || j_id || '.';
	 RAISE errJobs;
END IF;
if(d_name != v_departments_name)
	then  ---odp := 'nie ma takiego' || d_name || '.';
     RAISE errDepartments;	 
END IF;	
	INSERT INTO employess VALUES(v_id, v_first_name, v_last_name, v_email, v_phone, v_jobs_id, v_salary, v_departments_name );
     --odp := 'dodano nowego pracownika.';
   EXCEPTION 
     WHEN errJobs THEN 
        dbms_output.put_line('nie ma takiego' || j_id || '. ');
      WHEN errDepartments THEN 
        dbms_output.put_line('nie ma takiego' || d_name || '. '); 
END;
/
show err 

Pojawia mi się błąd jak dodaje nie istniejący rekord id z tab jobs:

ORA- 002291: integrity constraint ("user.SYS_C004036")violated - parent key not found

ma ktoś może jakiś pomysł ?

Dlaczego tu
CREATE TABLE employess (
...
departments_name VARCHAR2(30)-- FOREIGN KEY REFERENCES departments(id)
);
Nie jest po ID???

Poza tym to co próbujesz zrobić nie trzyma się kupy. Skoro wymuszasz podanie do procedury jobs_id (btw pole powinno się nazywać job_id po to jest id jednego konkretnego stanowiska) to powinnaś założyć, że podane jest ok, a jak nie to wyleci błąd. Jeśli już pisze się taką procedure to działa ona trochę inaczej -
a) podajesz job_name
b) sprawdzasz czy job z takim job_name istnieje
b1) jeśli tak to masz jej ID i to wstawiasz do tabelki employess
b2) jeśli nie to
b21) dodajesz do tabelki jobs nowy rekord z job_name i masz jej ID
b22) albo krzyczysz, że zły job_name
To co próbujesz tu zrobić lekko mija się z celem, bo aby wywołać prockę to musisz w programie i tak najpierw pobrać job_id i zaczyna się robić kupa, znaczy sprawdzanie czegoś kilka razy

dziękuje za pomoc :)

mam pytanko dobrą drogą podążam?

set serveroutput on
CREATE OR REPLACE PROCEDURE dodajWiersz (v_id employess.id%TYPE, v_first_name employess.first_name%TYPE,
										 v_last_name employess.last_name%TYPE, v_email employess.email%TYPE,
										 v_phone employess.phone%TYPE, v_jobs_title jobs.job_title%TYPE,
										 v_salary employess.salary%TYPE,
										 v_departments_name departments.name%TYPE) IS 
err EXCEPTION;
get_job_title jobs.job_title%TYPE;
get_id  jobs.id%TYPE;
get_departments_name departments.name%TYPE;
BEGIN									 
	SELECT j.id, j.job_title, d.name INTO get_id, get_job_title, get_departments_name 
	FROM jobs j ,departments d, employess e 
	WHERE  e.jobs_id = j.id AND d.name = e.departments_name;
	if( get_job_title = v_jobs_title and v_departments_name = get_departments_name)
	then 
		INSERT INTO employess VALUES(v_id, v_first_name, v_last_name,
		v_email, v_phone,  get_id, v_salary, get_departments_name );
	ELSE
	RAISE err;
	END IF;
EXCEPTION
     WHEN err THEN
        dbms_output.put_line('nie odpowiednie dane ');
 	WHEN too_many_rows THEN
		dbms_output.put_line('bład');
END;
/
show err

działa, ale sypie się na wyjątku too_many_rows Wydaje mi się, że mam już dobrze skonstruowane zapytanie WHERE , ale chyba jednak źle jak mówi, że jest za dużo wierszy
ma ktoś jakiś pomysł?

jak wyświetlam select bez group by działa, mi ale powtarzają się dane. W MySQL pewnie to by działało ale nie wiem czemu w Oracle pojawia się błąd w takim zapytaniu :

select j.id, j.job_title, d.name 
from departments d 
inner join  employess e on e.departments_name = d.name 
inner join jobs j  on j.id = e.jobs_id 
group by j.job_title;

ORA-00979: not a GROUP BY expression

może to jest główna przyczyna , z której nie mogę wybrnąć?

Błąd bierze się z tego, że nie znasz podstaw SQL. W GROUP BY należy umieścić wszystkie nieagregowane wyrażenia z SELECT.

ok, to juz zadziałało DZIĘKUJE :)

select j.id, j.job_title, d.name 
from departments d 
inner join  employess e on e.departments_name = d.name 
inner join jobs j  on j.id = e.jobs_id 
group by j.id, j.job_title, d.name ;

DZIĘKUJE JUŻ MI DZIAŁA

:>

podaje kod może komuś się przyda ..

set serveroutput on
CREATE OR REPLACE PROCEDURE dodajWiersz (v_id employess.id%TYPE, v_first_name employess.first_name%TYPE,
										 v_last_name employess.last_name%TYPE, v_email employess.email%TYPE,
										 v_phone employess.phone%TYPE, v_jobs_title jobs.job_title%TYPE,
										 v_salary employess.salary%TYPE,
										 v_departments_name departments.name%TYPE) IS 
get_job_title jobs.job_title%TYPE;
get_id  jobs.id%TYPE;
get_departments_name departments.name%TYPE;
err exception;
BEGIN					

	SELECT j.id, j.job_title, d.name INTO get_id ,get_job_title,get_departments_name 
	FROM departments d 
	INNER JOIN  employess e ON  d.name =  v_departments_name
	INNER JOIN jobs j  ON j.job_title = v_jobs_title
	GROUP BY j.id, j.job_title, d.name ;

	if( get_job_title = v_jobs_title and v_departments_name = get_departments_name )
	then 
		INSERT INTO employess VALUES(v_id, v_first_name, v_last_name,
		v_email, v_phone,  get_id, v_salary, get_departments_name );
	else    
		RAISE err;   
	END IF;
   
EXCEPTION
WHEN err THEN 
dbms_output.put_line('aaaaaaaaaaaaa');
 	WHEN too_many_rows THEN
		dbms_output.put_line('bład');
	WHEN no_data_found THEN 
	    dbms_output.put_line('nie poprawne dane');
END;
/
show err

i przepraszam za spowolnione moje programowanie