首页 > 解决方案 > PLS-00049: 创建过程时绑定变量错误

问题描述

您好我正在尝试创建一个程序,其代码如下:

CREATE PROCEDURE profit_calcs(profit OUT NUMBER,
                              em     IN NUMBER,
                              eq     IN NUMBER,
                              p      IN NUMBER,
                              u      IN NUMBER,
                              income IN NUMBER,
                              ex     IN NUMBER) AS
BEGIN
  SELECT SUM(amount) INTO em FROM a_em;
  SELECT SUM(amount) INTO eq FROM a_eq;
  SELECT SUM(amount) INTO p FROM a_p;
  SELECT SUM(amount) INTO u FROM a_u;
  SELECT SUM(amount) INTO income FROM sales;
  ex     := :em + :eq + :p + :u;
  profit := :income - :ex;
END;
/

每当我尝试创建它时,我都会收到以下错误:

LINE/COL  ERROR
--------- -------------------------------------------------------------
10/7      PLS-00049: bad bind variable 'EM'
10/13     PLS-00049: bad bind variable 'EQ'
10/18     PLS-00049: bad bind variable 'P'
10/22     PLS-00049: bad bind variable 'U'
11/11     PLS-00049: bad bind variable 'INCOME'
11/21     PLS-00049: bad bind variable 'EX'

如果有人帮助我解决此错误,那将非常有帮助。

标签: oraclestored-proceduresplsqloracle11gr2

解决方案


因为,您需要在关键字之后的声明部分定义这些变量ASBEGIN例如

CREATE OR REPLACE PROCEDURE profit_calcs(profit OUT NUMBER,
                                         ex     OUT NUMBER) AS
  em     INT;
  eq     INT;
  p      INT;
  u      INT;
  income INT;                             
BEGIN

将它们转换OUT为此存储过程的类型参数,例如

CREATE OR REPLACE PROCEDURE profit_calcs(profit OUT NUMBER,
                                         em     OUT NUMBER,
                                         eq     OUT NUMBER,
                                         p      OUT NUMBER,
                                         u      OUT NUMBER,
                                         income OUT NUMBER,
                                         ex     OUT NUMBER) AS                           
BEGIN

在哪里

  • 不要忘记删除这些变量前缀的冒号,例如 :em, :eqshould be em,eq分别
  • REPLACECREATE PROCEDURE考虑到即将进行的编译,不应省略命令选项

推荐阅读