程序中带有表参数的编译错误调用函数 [英] Compilation error calling Function with Table Parameter from Procedure

问题描述

在一个包裹中，我有:

1. 数字表的类型
2. 将上述数字表类型作为参数的函数
3. 需要使用数字表类型调用所述函数的过程

我知道Type起作用，我知道Function起作用.但是，当我尝试在过程中调用函数时，出现以下编译错误:

I know the Type works, I know the Function works. But when I try to call the Function within the Procedure, I get the following Compilation errors:

错误(262,16):PLS-00306:调用中的参数数目或类型错误 到[[FUNCTION]'错误(262,16):PL/SQL:ORA-00904: "[PACKAGE]"."[FUNCTION]":无效的标识符错误(264,41):PLS-00642: SQL语句中不允许使用本地集合类型

Error(262,16): PLS-00306: wrong number or types of arguments in call to '[FUNCTION]' Error(262,16): PL/SQL: ORA-00904: "[PACKAGE]"."[FUNCTION]": invalid identifier Error(264,41): PLS-00642: local collection types not allowed in SQL statements

包装标题:

CREATE OR REPLACE PACKAGE [PACKAGE] AS 

    TYPE NUMBER_LIST IS TABLE OF NUMBER;

    FUNCTION [FUNCTION] (
        [LIST_PARAM]       NUMBER_LIST
      ) RETURN VARCHAR2;

    PROCEDURE [PROCEDURE] (
        RECORDS_AFFECTED    OUT NUMBER
      );

END [PACKAGE];

问题代码:

  PROCEDURE [PROCEDURE](
      RECORDS_AFFECTED    OUT NUMBER
  )AS
      [ID_LIST]  NUMBER_LIST;
  BEGIN
      ...

      SELECT  [COLUMN]
      BULK COLLECT INTO    [ID_LIST]
      FROM    [TABLE]

      INSERT INTO [TABLE]( [COLUMN] )
      SELECT   [FUNCTION]( LIST_PARAM => [ID_LIST] )
      FROM    [OTHER TABLE];

      ...  
  END [PROCEDURE];

错误围绕这一行:

SELECT   [FUNCTION]( LIST_PARAM => [ID_LIST] )

我尝试过:

[ID_LIST]  NUMBER_LIST;
[ID_LIST]  [PACKAGE].NUMBER_LIST;

SELECT   [FUNCTION]( [ID_LIST] )
SELECT   [FUNCTION]( LIST_PARAM => [ID_LIST] )
SELECT   [PACKAGE].[FUNCTION]( [ID_LIST] )
SELECT   [PACKAGE].[FUNCTION]( LIST_PARAM => [ID_LIST] )

但是我觉得问题出在[ID_LIST]的传递方式上.

But I feel like the problem is in the way the [ID_LIST] is being passed.

是否有人将这个Type TABLE OF NUMBER参数传递给函数对我做错了什么?

Any thoughts on what I'm doing wrong passing this Type TABLE OF NUMBER parameter to the function?

推荐答案

问题不是函数本身的参数列表，而是您正在尝试在SQL语句中使用本地定义的类型NUMBER_LIST( PLS-00642错误). SQL引擎对此类型一无所知.您必须为此创建一个数据库类型，例如:

The problem isn't the parameter list to the function per se, it's that you're trying to use the locally defined type NUMBER_LIST in an SQL statement (that's the PLS-00642 error). The SQL engine knows nothing about this type. You would have to create a database type for this to succeed e.g.:

CREATE OR REPLACE  TYPE NUMBER_LIST IS TABLE OF NUMBER;

或其他类似的东西:

  PROCEDURE [PROCEDURE](
      RECORDS_AFFECTED    OUT NUMBER
  )AS
      [ID_LIST]  NUMBER_LIST;
      fnc_ret    VARCHAR2;
  BEGIN
      ...

      SELECT  [COLUMN]
      BULK COLLECT INTO    [ID_LIST]
      FROM    [TABLE]

      fnc_ret := [FUNCTION]( LIST_PARAM => [ID_LIST] );

      INSERT INTO [TABLE]( [COLUMN] )
      VALUES (fnc_ret);

      ...  
  END [PROCEDURE];

您可能需要对其进行修改以适合您的实际代码，但这就是这个主意.您不能在SQL语句中使用ID_LIST而不将其设置为数据库定义的类型，因为SQL引擎看不到它.

You'll likely need to modify this to suit your real code, but this is the idea. You just can't use ID_LIST in an SQL statement without making it a database defined type, as the SQL engine can't see it.

PL/SQL是RDBMS中的一个引擎".它具有一个语句处理器，当遇到SQL语句时，会将其交给SQL引擎"进行处理.我认为您可以理解为什么会导致问题.

PL/SQL is one "engine" within the RDBMS. It has a statement processor, and when it encounters a SQL statement, it hands it off to the SQL "engine" for processing. I think you can see why that causes the problem.

这篇关于程序中带有表参数的编译错误调用函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！