IF ELSE STATEMENTS in SQL

Question

I have this pl/sql stored procedure that add a player to new cleub

its search if this player is already in same cleub so it just update his cleub value
but if this player not inscribe in any cleub so INSERT him completlty like a new n-tuple

so the first part work corectely but the seconde part not work and i cant find the probleme where is it. thats the code:

CREATE OR REPLACE PROCEDURE INSERT_PLAYER_IN_CLEUB( n_joueur IN VARCHAR2,n_cleub IN VARCHAR2, d IN DATE,n_maillot IN INT )
AS
  v_id_j   INT;
  v_id_c   INT;
  id_exist INT := 0;
BEGIN
  SELECT id_j INTO v_id_j FROM joueur WHERE nom=n_joueur;
  dbms_output.put_line(v_id_j);
  SELECT id_c INTO v_id_c FROM cleub WHERE nom_cleub=n_cleub;
  dbms_output.put_line(v_id_c);
  SELECT id_j INTO id_exist FROM JOUEUR_CLEUB WHERE v_id_j=ID_J;
  dbms_output.put_line(id_exist);
  IF (id_exist = 0) THEN
    INSERT INTO joueur_cleub VALUES(v_id_j,v_id_c,d,n_maillot);
  ELSE
    UPDATE JOUEUR_CLEUB SET id_c=v_id_c,DATE_debut=d ,NUMERO_MAILLOT=n_maillot WHERE id_j=v_id_j;
  END IF;
END;

i must update if the nuples is exist if not i must create a new ntuples with insert — mugiwaradz, Jul 13 '14 at 14:29
[Merge](http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9016.htm) aka `UPSERT` - (Update Insert) should help you — Maheswaran Ravisankar, Jul 13 '14 at 14:33
possible duplicate of [Oracle: how to UPSERT (update or insert into a table?)](http://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table) — Barmar, Jul 13 '14 at 14:36

score 1 · Accepted Answer · answered Jul 13 '14 at 14:40

MERGE INTO JOUEUR_CLEUB J
USING DUAL ON (ID_J = v_id_j)
WHEN MATCHED THEN
  UPDATE SET id_c=v_id_c,DATE_debut=d 
WHEN NOT MATCHED THEN
  INSERT VALUES(v_id_j,v_id_c,d,n_maillot);

Instead of DUAL, you can use other tables as well, to be if you want to check for record existence, based on a JOIN

score 0 · Answer 2 · answered Jul 14 '14 at 11:02

problem is you are not checking whether the record exist in table or not.

For example you have written ---

SELECT id_j INTO v_id_j FROM joueur WHERE nom=n_joueur;

in case there is no record with nom =n_joueur in table joueur, your code will throw an exception. make sure that you are checking the existence prior to allocating id to any variable.

this can be achieved via ---

select count(*) from joueur WHERE nom=n_joueur;

if this is zero then no record exist, thus no need to allocate the id to any variable in this case, else go by the normal flow.

thanks man thats the error i know it after same tests but finaly i choose to implement with MERGE its work better then if/else — mugiwaradz, Jul 14 '14 at 14:03

IF ELSE STATEMENTS in SQL

2 Answers2