主页 / coding / 问题 / 79158965
Accepted
NablaDelta
Asked: 2024-11-05 19:49:30 +0800 CST

从对象或表类型声明记录类型

  • 772

我在数据库中创建了一个对象类型，并想在包中声明一个记录类型，以便使用引用游标循环提取数据，但这似乎不适用于对象类型。如何在不创建虚拟表的情况下实现这一点？我想使用如下所示的on可能会奏效，但我不知道该怎么做。type_objecttype_record%ROWTYPEtype_table

/* type_object has been created before */

declare
    type type_table is table of type_object;
    l_rec         type_table%ROWTYPE; -- doesn't work
    l_refcursor   sys_refcursor;
begin
    open l_refcursor for 'select * from mytable';
    loop
      fetch l_refcursor into l_rec;
      exit when l_refcursor%notfound;
      -- do something with l_rec
    end loop;
    close l_refcursor;
end;
plsql

1 个回答

  1. Best Answer

    你不能，语法不支持它。

    但是，您可以使用光标的CURSOR然后：%ROWTYPE

    declare
  CURSOR l_cursor IS select * from mytable;
  l_rec         l_cursor%ROWTYPE;
begin
  open l_cursor;
  loop
    fetch l_cursor into l_rec;
    exit when l_cursor%notfound;
    -- do something with l_rec
  end loop;
  close l_cursor;
end;
/

    或者MYTABLE%ROWTYPE：

    declare
  l_rec         MYTABLE%ROWTYPE;
  l_refcursor   sys_refcursor;
begin
  open l_refcursor for 'select * from mytable';
  loop
    fetch l_refcursor into l_rec;
    exit when l_refcursor%notfound;
    -- do something with l_rec
  end loop;
  close l_refcursor;
end;
/

    或者类似地，使用强类型游标：

    declare
  TYPE MyTableCurTyp IS REF CURSOR RETURN MyTable%ROWTYPE;
  l_rec         MYTABLE%ROWTYPE;
  l_refcursor   MyTableCurTyp;
begin
  open l_refcursor for select * from mytable;
  loop
    fetch l_refcursor into l_rec;
    exit when l_refcursor%notfound;
    -- do something with l_rec
  end loop;
  close l_refcursor;
end;
/

    或者在查询中使用type_object并作为中间变量的类型：

    declare
  l_type        type_object;
  l_refcursor   sys_refcursor;
begin
  open l_refcursor for 'select type_object(col1, col2, col3, col4)
                        from mytable';
  loop
    fetch l_refcursor into l_type;
    exit when l_refcursor%notfound;
    -- do something with l_type
  end loop;
  close l_refcursor;
end;
/

    小提琴

    • 2

相关问题