oracle query is taking too much time to execute , though it doesn't finish except by terminating it , it faces a problem in the update part , and makes no updates or takes too long time , see the syntax right here and if anyone knows anything please help
create or replace function sp_addClient(CITIZENNAME VARCHAR2,
NATIONALNUMBER VARCHAR2 default null,
ADDRESS VARCHAR2 default null,
PHONE VARCHAR2 default null,
MOBILE VARCHAR2 default null,
FAX VARCHAR2 default null,
EMAIL VARCHAR2 default null,
FATHERNAME VARCHAR2 default null,
MOTHERNAME VARCHAR2 default null,
PSURNAME VARCHAR2 default null,
REGISTERNUMBER VARCHAR2 default null,
REGISTERPLACE VARCHAR2 default null,
BIRTHDATE VARCHAR2 default null,
BIRTHPLACE VARCHAR2 default null,
COMPANYNAME varchar2 default null,
P_NATIONALITY INTEGER default 1,
P_PASSEPORT_NO VARCHAR2 default null,
P_STAY_CARD VARCHAR default null)
return integer is
Result integer := null;
cr types.ref_cursor;
begin
open cr for SELECT a.citizen_id
FROM citizens a
WHERE (a.national_number = nationalNumber and
nationalNumber is not null)
or ((a.citizen_name = citizenName) and (a.father_name = fatherName) and
(a.surname = Psurname) and
(a.mother_name = motherName or motherName is null));
fetch cr into result;
close cr;
if result = -1 or result is null then
SELECT citizens_seq.NEXTVAL INTO Result FROM DUAL;
INSERT INTO citizens
VALUES
(Result,
CITIZENNAME,
NATIONALNUMBER,
ADDRESS,
PHONE,
MOBILE,
FAX,
EMAIL,
FATHERNAME,
MOTHERNAME,
PSURNAME,
REGISTERNUMBER,
REGISTERPLACE,
to_date(BIRTHDATE, 'dd-mm-yyyy'),
BIRTHPLACE,
COMPANYNAME,
null,
null,
null,
0,
null,
P_NATIONALITY,
P_PASSEPORT_NO,
P_STAY_CARD);
else
update citizens set
national_number = nationalNumber,
birth_date =to_date(BIRTHDATE, 'dd-mm-yyyy'),
birth_place = BIRTHPLACE,
register_number = REGISTERNUMBER,
register_place = REGISTERPLACE,
nationality = P_NATIONALITY,
passeport_no = P_PASSEPORT_NO,
stay_card = P_STAY_CARD,
--address = ADDRESS,
phone = PHONE,
mobile = MOBILE,
fax = FAX,
email = EMAIL,
company_name = COMPANYNAME
where citizen_id = result;
END if;
return(Result);
end sp_addClient;
Tags: Oracle