Hi i have a procedure which is used to create new account.
1. i want to avoid duplicate entries
2.if account already exists then i want to just make STATUS as ACTIVE
3. If not exists then i want insert account details
this is my code
create or replace procedure insert_data_employees(p_name in varchar2, p_acess_level in NUMBER, O_RETURN_CODE out Number )
as
v_uname number;
Begin
if p_name is not null then
BEGIN
select COUNT(1) into v_uname from employees
where
upper(p_name) = upper(name);
END;
End if;
INSERT INTO employees
(ID,emp_number, name,rol_id, status, PRA_ID, creation_date) VALUES
( EMP_TEMP_SEQ.nextVal,
EMPLOYEES_DATA_SEQ.nextVal,
SUBSTR (TRIM (p_name), 1, LENGTH (TRIM (p_name)) - 11),
p_acess_level,
'ACTIVE',
1,
sysdate
);
IF SQL%NOTFOUND THEN
o_return_code := 1;
dbms_output.put_line('Records not inserted');
ELSIF SQL%FOUND THEN
o_return_code := 0;
dbms_output.put_line('Records inserted');
End if;
end insert_data_employees;