函数/包问题 PL/SQL

Question

提问by Daniel o Keeffe

I am trying to create a package with four functions. Each function will add a set of numbers and subtract one from the total. I have been having lots of trouble getting the syntax correct. The functions below work on their own, and i try calling the first function at the end.

我正在尝试创建一个具有四个功能的包。每个函数都会添加一组数字并从总数中减去一个。我在获得正确的语法时遇到了很多麻烦。下面的函数独立工作，我尝试在最后调用第一个函数。

When I try to create the package i get an error where on line 7, " Encountered the symbol "END" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior 0.05 seconds"

当我尝试创建包时，我在第 7 行收到错误消息，“在预期以下情况之一时遇到符号“END”：开始函数编译指示过程子类型当前光标删除存在于 0.05 秒之前”

In the package body it says "name is already in use by an existing object". I don't understand as it has to be declared in the specification of the package anyway, and create or replace should solve this if the error is that there is already a package named functionbyfour.

在包正文中，它说“名称已被现有对象使用”。我不明白，因为无论如何它都必须在包的规范中声明，如果错误是已经有一个名为 functionbyfour 的包，则创建或替换应该可以解决这个问题。

And finally, when I try to use a function in the package, it says " Encountered the symbol "BEGIN" when expecting one of the following: := . ( @ % ; not null range default character The symbol ";" was substituted for "BEGIN" to continue. ORA-06550: line 5, column 43: PLS-00103: Encountered the symbol "FROM" when expecting one of the following: . ( * % & = - + ; < / > at in is mod remainder not rem <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset me".

最后，当我尝试使用包中的一个函数时，它说“遇到符号“BEGIN”，当期待以下之一时： := . ( @ % ; not null range default character 符号“;”被替换为"BEGIN" 继续。ORA-06550：第 5 行，第 43 列：PLS-00103：在期望以下之一时遇到符号“FROM”： . ( * % & = - + ; </ > at in 是 mod 余数不是 rem <> 或 != 或 ~= >= <= <> 和或 like2 like4 likec 在 || multiset me 之间”。

I am using ORACLE EXPRESS edition 11g and am new to PL/SQL(4 weeks).

我正在使用 ORACLE EXPRESS 11g 版并且是 PL/SQL 的新手（4 周）。

Any input is greatly appreciated.

非常感谢任何输入。

CREATE OR REPLACE FUNCTION functionbyfour AS

 FUNCTION functone( first number, second number) RETURN NUMBER ;
 FUNCTION functtwo( first number, second number, third number) RETURN NUMBER ;
 FUNCTION functthree(first number, second number, third number, fourth number) RETURN     NUMBER ;
 FUNCTION functfour( first number, second number, third number, fourth number,fifth   number) RETURN NUMBER ;

END  functionbyfour;
/

CREATE OR REPLACE PACKAGE functionbyfour AS

FUNCTION functone (first number, second number ) RETURN number AS total number;
 BEGIN
total:=first + second – 1;
RETURN total;
DBMS_OUTPUT.PUT_LINE(total);
END functone;


FUNCTION functtwo (first number, second number, third number ) RETURN number AS  total     number;
BEGIN
total:=first + second + third – 1;
RETURN total;
DBMS_OUTPUT.PUT_LINE(total);
END functtwo;

FUNCTION functthree (first number, second number,third number, fourth number )     RETURN     number AS total number;
BEGIN
total:=first + second + third + fourth – 1;
RETURN total;
DBMS_OUTPUT.PUT_LINE(total);
END functthree;


FUNCTION functfour (first number, second number, third number, fourth number, fifth     number ) RETURN number AS total number;
BEGIN
total:=first + second + third + fourth + fifth – 1;
RETURN total;
DBMS_OUTPUT.PUT_LINE(total);
END functfour;

/

BEGIN

SELECT functionbyfour.functone(1,2) FROM DUAL;

END;

/?

Answer 1

回答by Justin Cave

You would need to create a package named FunctionByFour(CREATE OR REPLACE PACKAGE)

您需要创建一个名为FunctionByFour( CREATE OR REPLACE PACKAGE)的包

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE PACKAGE functionbyfour AS
  2   FUNCTION functone( first number, second number) RETURN NUMBER ;
  3   FUNCTION functtwo( first number, second number, third number) RETURN NUMBER ;
  4   FUNCTION functthree(first number, second number, third number, fourth number) RETURN     NUMBER ;
  5   FUNCTION functfour( first number, second number, third number, fourth number,fifth   number) RETURN NUMBER ;
  6* END  functionbyfour;
  7  /

Package created.

and then a corresponding package body (CREATE OR REPLACE PACKAGE BODY). You'll also need an ENDfor the package body (right now, your code ends at the end of the fourth function)

然后是相应的包体 ( CREATE OR REPLACE PACKAGE BODY)。你还需要一个END包体（现在，你的代码在第四个函数的末尾结束）

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE PACKAGE BODY functionbyfour AS
  2    FUNCTION functone (first number, second number ) RETURN number AS total number;
  3    BEGIN
  4      total:=first + second - 1;
  5      RETURN total;
  6      DBMS_OUTPUT.PUT_LINE(total);
  7    END functone;
  8    FUNCTION functtwo (first number, second number, third number ) RETURN number AS  total     number;
  9    BEGIN
 10      total:=first + second + third - 1;
 11      RETURN total;
 12      DBMS_OUTPUT.PUT_LINE(total);
 13    END functtwo;
 14    FUNCTION functthree (first number, second number,third number, fourth number )     RETURN     number AS total number;
 15    BEGIN
 16      total:=first + second + third + fourth - 1;
 17      RETURN total;
 18      DBMS_OUTPUT.PUT_LINE(total);
 19    END functthree;
 20    FUNCTION functfour (first number, second number, third number, fourth number, fifth     number ) RETURN number AS total number;
 21    BEGIN
 22      total:=first + second + third + fourth + fifth - 1;
 23      RETURN total;
 24      DBMS_OUTPUT.PUT_LINE(total);
 25    END functfour;
 26* END functionbyfour;
SQL> /

Package body created.

Once you've done that, you can use the function

完成后，您可以使用该功能

SQL> SELECT functionbyfour.functone(1,2) FROM DUAL;

FUNCTIONBYFOUR.FUNCTONE(1,2)
----------------------------
                           2

If you want to put the SELECTstatement in a PL/SQL block, you'd need to declare a local variable and do a SELECT INTOto populate the local variable with the result of the function (you could also just assign the local variable the result of the function call without needing to use a SELECT).

如果你想把SELECT语句放在 PL/SQL 块中，你需要声明一个局部变量并SELECT INTO用函数的结果填充局部变量（你也可以只将局部变量赋值为函数调用而无需使用SELECT)。

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2    l_result NUMBER;
  3  BEGIN
  4    -- First approach
  5    l_result := functionByFour.functOne(1,2);
  6    dbms_output.put_line( l_result );
  7    -- Second approach
  8    SELECT functionByFour.functOne(1,2)
  9      INTO l_result
 10      FROM dual;
 11    dbms_output.put_line( l_result );
 12* END;
 13  /
2
2

PL/SQL procedure successfully completed.

Also, be aware that putting a DBMS_OUTPUT.PUT_LINEafter your RETURNstatement is pointless. That code can never be reached. If you want to print the result to the DBMS_OUTPUTbuffer, that would need to come before the RETURN.

另外，请注意，DBMS_OUTPUT.PUT_LINE在您的RETURN声明之后放置 a是没有意义的。该代码永远无法访问。如果要将结果打印到DBMS_OUTPUT缓冲区，则需要在RETURN.

Answer 2

回答by Dave

The line

CREATE OR REPLACE FUNCTION functionbyfour AS

should be

CREATE OR REPLACE PACKAGE functionbyfour AS

The line

CREATE OR REPLACE PACKAGE functionbyfour AS

should be

CREATE OR REPLACE PACKAGE BODY functionbyfour AS

The word second is a key word and you can't use it as a parameter name
You need an
```
END functionbyfour;
```
After your END functfour to end the package body
Your dbms_outputs will never be executed as they are after the return

You could do all this in one function

 FUNCTION functall(FIRST NUMBER
                     ,sec NUMBER DEFAULT 0
                     ,third NUMBER DEFAULT 0
                     ,fourth NUMBER DEFAULT 0
                     ,fifth NUMBER DEFAULT 0)
  RETURN NUMBER
AS
  total NUMBER;
BEGIN
  total := first + sec + third + fourth + fifth - 1;

  dbms_output.PUT_LINE(total);

  RETURN total;
END functall;

What a strange thing to want to do? :-)

线

CREATE OR REPLACE FUNCTION functionbyfour AS

应该

CREATE OR REPLACE PACKAGE functionbyfour AS

线

CREATE OR REPLACE PACKAGE functionbyfour AS

应该

CREATE OR REPLACE PACKAGE BODY functionbyfour AS

单词 second 是关键字，不能用作参数名称
你需要一个
```
END functionbyfour;
```
在你的 END functfour 结束包体之后
您的 dbms_outputs 永远不会像返回后那样执行

你可以在一个函数中完成所有这些

 FUNCTION functall(FIRST NUMBER
                     ,sec NUMBER DEFAULT 0
                     ,third NUMBER DEFAULT 0
                     ,fourth NUMBER DEFAULT 0
                     ,fifth NUMBER DEFAULT 0)
  RETURN NUMBER
AS
  total NUMBER;
BEGIN
  total := first + sec + third + fourth + fifth - 1;

  dbms_output.PUT_LINE(total);

  RETURN total;
END functall;

想要做什么奇怪的事情？:-)

Answer 3

回答by APC

You are confused about standalone programs and packages.

您对独立程序和软件包感到困惑。

CREATE FUNCTION can only be used to create a standalone functiion. What you have there should be:

CREATE FUNCTION 只能用于创建独立函数。你所拥有的应该是：

CREATE OR REPLACE PACKAGE functionbyfour AS

A package consists of two parts, a specification and a body. The spec is the public face of the API, the body is the implementation. What you have as the package (spec) is the package body. So change that second chunk of code to start

一个包由两部分组成，一个规范和一个主体。规范是 API 的公开面，主体是实现。您拥有的包（规格）是包体。因此，更改第二段代码以开始

CREATE OR REPLACE PACKAGE BODY functionbyfour AS

and at least you'll have your program structured correctly.

并且至少您的程序结构正确。

The Oracle PL/SQL documentation is online, comnprehensive and free. I urge you to read it. Find out more.

Oracle PL/SQL 文档在线、全面且免费。我敦促你阅读它。了解更多。

函数/包问题 PL/SQL

提问by Daniel o Keeffe

回答by Justin Cave

回答by Dave

回答by APC

相关推荐

最近更新

标签

函数/包问题 PL/SQL

提问by Daniel o Keeffe

回答by Justin Cave

回答by Dave

回答by APC

相关推荐

如何使用 SQL 查询显示 Oracle 模式大小？

SQL 从 SELECT 插入但更改一列？

SQL 将 JDE Julian 日期转换为 Gregorian

SQL 如何在 ssrs 报告中格式化日期和时间？

相关推荐

最近更新

标签