时间戳

首页 > 解决方案 > 在 Oracle 中使用集合作为参数执行查询

oracle - 在 Oracle 中使用集合作为参数执行查询

问题描述

有什么方法可以立即执行以集合为参数的查询。

我想定义一个类型type my_type as table of number，然后使用execute immediate QUERY using COLLECTION.

当我编写这样的代码时，我得到PLS-00457 expressions has to be of SQL types.

标签： oracleplsqldynamicqueryvarray

解决方案

来自 oracle 文档：https://docs.oracle.com/cd/B12037_01/appdev.101/b10807/11_dynam.htm 示例 7-4 对象类型和集合的动态 SQL

下面的示例说明了对象和集合的使用。假设你定义了对象类型 Person 和 VARRAY 类型的 Hobbies，如下：

CREATE TYPE Person AS OBJECT (name VARCHAR2(25), age NUMBER);
CREATE TYPE Hobbies IS VARRAY(10) OF VARCHAR2(25);

使用动态 SQL，您可以编写使用以下类型的包：

CREATE OR REPLACE PACKAGE teams AS
   PROCEDURE create_table (tab_name VARCHAR2);
   PROCEDURE insert_row (tab_name VARCHAR2, p Person, h Hobbies);
   PROCEDURE print_table (tab_name VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY teams AS
   PROCEDURE create_table (tab_name VARCHAR2) IS
   BEGIN
      EXECUTE IMMEDIATE 'CREATE TABLE ' || tab_name ||
         ' (pers Person, hobbs Hobbies)';
   END;

   PROCEDURE insert_row (
      tab_name VARCHAR2,
      p Person,
      h Hobbies) IS
   BEGIN
      EXECUTE IMMEDIATE 'INSERT INTO ' || tab_name ||
         ' VALUES (:1, :2)' USING p, h;
   END;

   PROCEDURE print_table (tab_name VARCHAR2) IS
      TYPE RefCurTyp IS REF CURSOR;
      cv RefCurTyp;
      p  Person;
      h  Hobbies;
   BEGIN
      OPEN cv FOR 'SELECT pers, hobbs FROM ' || tab_name;
      LOOP
         FETCH cv INTO p, h;
         EXIT WHEN cv%NOTFOUND;
         -- print attributes of 'p' and elements of 'h'
      END LOOP;
      CLOSE cv;
   END;
END;
/

从匿名块中，您可以调用包 TEAMS 中的过程：

DECLARE
   team_name VARCHAR2(15);
BEGIN
   team_name := 'Notables';
   teams.create_table(team_name);
   teams.insert_row(team_name, Person('John', 31),
      Hobbies('skiing', 'coin collecting', 'tennis'));
   teams.insert_row(team_name, Person('Mary', 28),
      Hobbies('golf', 'quilting', 'rock climbing'));
   teams.print_table(team_name);
END;
/

推荐阅读