oracle 将 Select 分配给存储过程中的变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16260068/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Assign a Select to a variable in a stored procedure
提问by Mathieu M. Gélinas
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 :
我尝试在我的 Oracle 数据库中创建一个过程，但无法将查询结果分配给我的变量。这是我正在尝试调试的内容：
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.
它清楚地在 Oracle Developper 中说，我的 Select 不取悦他，无法弄清楚原因。我阅读的 Oracle 库、论坛和代码示例并没有太大帮助。
回答by hol
You need to use SELECT INTO. Also there are no braces in PL/SQLyou need to use THENand 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):
您需要使用SELECT INTO. 也没有大括号PL/SQL需要使用THENand END IF。我不太确定你对结果做了什么。你想还给它吗？那么你需要一个FUNCTION. 它应该看起来像这样（未经测试）：
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;
回答by TonyB
I don't think you can assign values to variables like that. Try SELECT INTO like this:
我不认为你可以为这样的变量赋值。像这样尝试 SELECT INTO：
create or replace
PROCEDURE PCD_COMBAT (identifier_perso NUMBER, identifier_advers 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 { ATT_PERSO := 1 };
END PCD_COMBAT;
