CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
Wizard
Wizard

Reputation: 11295

Oracle stored delete procedure

CREATE TABLE kategorijos(
kat_id number(11) not null,
kat_pavadinimas varchar(255),
CONSTRAINT kategorijos_pk PRIMARY KEY(kat_id)
);

create or replace procedure delete_kategorijos (kat_id number)
is
begin
delete from kategorijos where kat_id = kat_id;
end;

I call procedure: 

BEGIN
delete_kategorijos(1);
END;

This procedure delete ALL records in database, but it really must delete only row where kat_id = 1 , what is wrong in my procedure ?

Upvotes: 0

Views: 163

Answers (3)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

Best practice is to always use aliases.

create or replace procedure delete_kategorijos (kat_id number)
is
begin
  delete from kategorijos k where k.kat_id = delete_kategorijos.kat_id;
end;

Upvotes: 1

user3552288
user3552288

Reputation: 11

It is better to give the parameter another name, as follows:

create or replace procedure delete_kategorijos (l_kat_id number) is begin delete from kategorijos where kat_id = l_kat_id; end;

But think about the following: - what if kat_id = 1 does not exist? - what if the record is locked by another transaction?

Upvotes: 1

Md.Rajibul Ahsan
Md.Rajibul Ahsan

Reputation: 853

You are using the below line of code for deleting records

   delete from kategorijos where kat_id = kat_id;

Where in condition filed you can find that 

    where kat_id = kat_id;

Which is always true cause you are comparing same value thats why its deleting all the records

Upvotes: 3

Related Questions