在IN子句中使用Oracle表类型 – 编译失败

发布时间：2020-12-12 13:09:26 所属栏目：百科来源：网络整理

导读：只需尝试为我指定的ID获取光标. CREATE OR REPLACE PACKAGE some_package AS TYPE t_cursor IS REF CURSOR; TYPE t_id_table IS TABLE OF NVARCHAR(38) INDEX BY PLS_INTEGER; PROCEDURE someentity_select( p_ids IN t_id_table,p_results OUT t_cursor);EN

只需尝试为我指定的ID获取光标.

CREATE OR REPLACE PACKAGE some_package AS

  TYPE t_cursor IS REF CURSOR;
  TYPE t_id_table IS TABLE OF NVARCHAR(38) INDEX BY PLS_INTEGER;

  PROCEDURE someentity_select(
    p_ids     IN  t_id_table,p_results OUT t_cursor);

END;

CREATE OR REPLACE PACKAGE BODY some_package AS

  PROCEDURE someentity_select(
    p_ids     IN  t_guid_table,p_results OUT t_cursor)
  IS
  BEGIN

    OPEN p_results FOR 
      SELECT * 
      FROM someschema.someentity 
      WHERE id IN (SELECT column_value FROM TABLE(p_ids)); - fails here

  END;

END;

注意：someschema.someentity.id是一个NVARCHAR2(38)

PL / SQL：ORA-00382：表达式类型错误
PL / SQL：ORA-22905：无法访问非嵌套表项的行

我哪里错了？

在12.2之前的Oracle版本中,您只能通过CREATE TYPE语句从数据库中定义的集合类型中进行SELECT,而不是关联数组：

CREATE TYPE t_id_table IS TABLE OF NVARCHAR(38);

CREATE OR REPLACE PACKAGE some_package AS

  PROCEDURE someentity_select(
    p_ids     IN  t_guid_table,p_results OUT SYS_REFCURSOR);

END;

CREATE OR REPLACE PACKAGE BODY some_package AS

  PROCEDURE someentity_select(
    p_ids     IN  t_guid_table,p_results OUT SYS_REFCURSOR)
  IS
  BEGIN

    OPEN p_results FOR 
      SELECT * 
      FROM someschema.someentity 
      WHERE id IN (SELECT column_value FROM TABLE(p_ids));

  END;

END;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!