I am trying to create a stored procedure that deletes, inserts and updates is what I was asked for, I know it can be done separately or in a package but I want to know how it would be using the if else then

CREATE OR REPLACE PROCEDURE PA_INSERT_UPDATE_DELETE(
    PA_JOB_ID IN JOBS.JOB_ID%TYPE,
    PA_JOB_TITLE IN JOBS.JOB_TITLE%TYPE,
    PA_MIN_SALARY IN JOBS.MIN_SALARY%TYPE,
    PA_MAX_SALARY IN JOBS.MAX_SALARY%TYPE
    )
    IS
    VALOR NUMBER;
BEGIN
    IF PA_JOB_ID%NOTFOUND THEN
        INSERT INTO EMPLOYEES VALUES(PA_JOB_ID,PA_JOB_TITTLE,PA_MIN_SALARY,PA_MAX_SALARY);   
        DBMS_OUTPUT.PUT_LINE ('REGISTRO INSERTADO');
    ELSIF PA_JOB_ID%NOTFOUND THEN
        UPDATE EMPLOYEES SET JOB_TITLE=PA_JOB_TITLE,MIN_SALARY=PA_MIN_SALARY,MAX_SALARY=PA_MAX_SALARY
        WHERE JOB_ID=PA_JOB_ID;
        DBMS_OUTPUT.PUT_LINE ('REGISTRO ACTUALIZADO');
    ELSE
        DELETE FROM JOBS WHERE JOB_ID=PA_JOB_ID;
        DBMS_OUTPUT.PUT_LINE ('REGISTRO BORRADO');
    END IF;
END;

EXEC PA_INSERT_UPDATE_DELETE;

CodePudding user response：

Re-read comments below your question.

Here's one option which shows how you might be doing it.

SQL> create or replace procedure p_iud
  2    (par_action      in varchar2,
  3     par_result      out varchar2,
  4     par_employee_id in employees.employee_id%type,
  5     par_first_name  in employees.first_name%type   default null,
  6     par_last_name   in employees.last_name%type    default null,
  7     par_hire_date   in employees.hire_date%type    default null,
  8     par_salary      in employees.salary%type       default null
  9    )
 10  is
 11  begin
 12    if par_action = 'I' then
 13       insert into employees (employee_id, first_name, last_name, hire_date, salary)
 14         values (par_employee_id, par_first_name, par_last_name, par_hire_date, par_salary);
 15       par_result := sql%rowcount || ' row inserted';
 16    elsif par_action = 'U' then
 17       update employees set
 18         first_name = nvl(par_first_name, first_name),
 19         last_name  = nvl(par_last_name , last_name),
 20         hire_date  = nvl(par_hire_date , hire_date),
 21         salary     = nvl(par_salary    , salary)
 22       where employee_id = par_employee_id;
 23       par_result := sql%rowcount || ' row updated';
 24    elsif par_action = 'D' then
 25       delete from employees where employee_id = par_employee_id;
 26       par_result := sql%rowcount || ' row deleted';
 27    end if;
 28  exception
 29    when others then
 30      par_result := 'Error: ' || sqlerrm;
 31      raise;
 32  end;
 33  /

Procedure created.

Testing: current table contents:

SQL> select * from employees;

EMPLOYEE_ID FIRST_NAME      LAST_NAME       HIRE_DATE      SALARY
----------- --------------- --------------- ---------- ----------
        100 Malik           Makkes          01.01.2010       9000
        200 Velik           Kemmes          05.03.2020       6000

Update record:

SQL> set serveroutput on
SQL> declare
  2    l_msg varchar2(200);
  3  begin
  4    p_iud(par_action      => 'U',
  5          par_result      => l_msg,
  6          par_employee_id => 100,
  7          par_first_name  => 'Little'
  8         );
  9    dbms_output.put_line('Result: ' || l_msg);
 10  end;
 11  /
Result: 1 row updated

PL/SQL procedure successfully completed.

SQL> select * from employees;

EMPLOYEE_ID FIRST_NAME      LAST_NAME       HIRE_DATE      SALARY
----------- --------------- --------------- ---------- ----------
        100 Little          Makkes          01.01.2010       9000
        200 Velik           Kemmes          05.03.2020       6000

Delete:

SQL> declare
  2    l_msg varchar2(200);
  3  begin
  4    p_iud(par_action      => 'D',
  5          par_result      => l_msg,
  6          par_employee_id => 100
  7         );
  8    dbms_output.put_line('Result: ' || l_msg);
  9  end;
 10  /
Result: 1 row deleted

PL/SQL procedure successfully completed.

SQL> select * from employees;

EMPLOYEE_ID FIRST_NAME      LAST_NAME       HIRE_DATE      SALARY
----------- --------------- --------------- ---------- ----------
        200 Velik           Kemmes          05.03.2020       6000

SQL>

It kind of works; adjust it to suit your needs, if you have to.