迭代 Oracle 中的对象表 [英] Iterate over table of objects in Oracle

问题描述

我应该如何遍历这样的对象表?

How should I iterate over a table of objects like this?

CREATE TYPE SOME_OBJECT AS OBJECT (
  ATTR1 VARCHAR2(20)
, ATTR2 VARCHAR2(30)
);
/

CREATE TYPE C_SOME_OBJECT AS TABLE OF SOME_OBJECT;
/

而且...我想将其用作过程的参数.必须在程序中初始化吗?

And... I want to use this as a parameter for a procedure. Have to initialize it in procedure?

PROCEDURE SOME_PROCEDURE(OBJECT IN C_SOME_OBJECT)
IS
BEGIN
  --Some code here iterating IN parameter
END;

推荐答案

-- NOTE #1
-- You cant use Object as name for your input variable because it is a type
-- (Types are reserved words)
PROCEDURE SOME_PROCEDURE(p_SOME_TABLE IN C_SOME_OBJECT)
IS
BEGIN
  FOR i IN p_SOME_TABLE.FIRST .. p_SOME_TABLE.LAST
  LOOP
     -- NOTE #2 Manage it like this
     -- Current iteration: p_SOME_TABLE(i)
     -- Access Example   : p_SOME_TABLE(i).ATTR1

     -- NOTE #3 Or you can assign current iteration to a variable
     -- then use that variable thought it is not by reference if you
     -- do it like this.
  END LOOP;
END;

更新:

还要注意不要将记录与对象混淆.

Also be careful not to confuse RECORDS with OBJECTS.

OBJECT 是一种 SQL 类型，而 RECORD 是一种 PL/SQL 类型，您可以将其更多地视为 C/C++ 之类的 struct.

An OBJECT is an SQL type but RECORD on the other hand is a PL/SQL type, you could think of it more of a C/C++ like struct.

更新_2:

请注意，如果表为空，这不会引发任何异常，因此您需要处理没有数据的情况，例如您可以像这样使用 .COUNT:

Note that this wont raise any exception if the table is empty thus you need to handle if there are no data, for example you could use .COUNT like this:

IF p_SOME_TABLE.COUNT = 0 THEN
   RAISE my_exception;
END IF;

这篇关于迭代 Oracle 中的对象表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！