Assign a Select to a variable in a stored procedure

oracle, plsql, stored-procedures, variables

Solution

You need to use `SELECT INTO`. Also there are no braces in `PL/SQL` you need to use `THEN` and `END IF`. I am not quite sure what you do with the result. Do you want to give it back? Then you need a `FUNCTION`. It should look something like this (untested):

create or replace
FUNCTION PCD_COMBAT (identifier_perso NUMBER, identifier_advers NUMBER) 
RETURN NUMBER
AS
  ATT_PERSO NUMBER;
  OFF_PERSO NUMBER;
  DEF_ADVERS NUMBER; 
BEGIN     
  SELECT OFFENSE_PERSO 
    INTO OFF_PERSO 
    FROM PERSONNAGE 
   WHERE ID_PERSO = identifier_perso;
  SELECT DEFENSE_ADVERSAIRE 
    INTO DEF_ADVERS 
    FROM PERSONNAGE 
   WHERE ID_ADVERSAIRE = identifier_advers;

  ATT_PERSO := OFF_PERSO - DEF_ADVERS;
  IF ATT_PERSO < 1 THEN 
     ATT_PERSO := 1;
  END IF

  RETURN ATT_PERSO;

END PCD_COMBAT;

Problem

I try to create a procedure in my Oracle Database and can't achieve to assign the result of a query to my variables. Here is what i'm trying to debug : ``` create or replace PROCEDURE PCD_COMBAT (identifier_perso NUMBER, identifier_advers NUMBER) AS ATT_PERSO NUMBER; OFF_PERSO NUMBER; DEF_ADVERS NUMBER; BEGIN OFF_PERSO := SELECT OFFENSE_PERSO FROM PERSONNAGE WHERE ID_PERSO = identifier_perso; DEF_ADVERS := SELECT DEFENSE_ADVERSAIRE FROM PERSONNAGE WHERE ID_ADVERSAIRE = identifier_advers; ATT_PERSO := OFF_PERSO - DEF_ADVERS; IF ATT_PERSO < 1 { ATT_PERSO := 1 }; END PCD_COMBAT; ``` It clearly says in Oracle Developper that my Select doesnt please him and can't figure out why. Oracle library, forums and code samples I read didnt help much.

Original source