在存储过程中将Select分配给变量 [英] Assign a Select to a variable in a stored procedure

问题描述

我尝试在Oracle数据库中创建一个过程，但无法将查询结果分配给我的变量.这是我要调试的内容:

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;

在Oracle Developper中清楚地表明，我的Select不能使他满意，也无法弄清楚原因.我读过的Oracle库，论坛和代码示例没有太大帮助.

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.

推荐答案

您需要使用SELECT INTO.同样，在PL/SQL中没有括号，您需要使用THEN和END IF.我不太确定您如何处理结果.您要还给我吗?然后，您需要一个FUNCTION.它应该看起来像这样(未经测试):

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;

这篇关于在存储过程中将Select分配给变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！