Oracle 存储过程中的 FOR 类型 IN

发布于 2024-10-19 16:55:44 字数 451 浏览 4 评论 0原文

我有一个名为 fatorial_type 的类型和一个从该类型创建的表因子，当执行下面的代码时，我收到错误“PLS-00302：必须声明组件'SET_NUM_FATORIAL'”，是的，该过程已声明。

SET serveroutput ON format wraped;
DECLARE
    fat fatorial_type;
BEGIN  
    FOR fat IN
        (SELECT value(f) FROM fatorial f)
    LOOP
        fat.SET_NUM_FATORIAL(5);
    END LOOP;
END;

如果我

SELECT value(f) into fat FROM fatorial f

这样做是有效的，也许问题与 FOR IN 有关，它不能像 into 那样工作

原文

I have a type called fatorial_type and a table fatorial created from that type, when executing this code below I got the error "PLS-00302: component 'SET_NUM_FATORIAL' must be declared", and yes that procedure was declared.

SET serveroutput ON format wraped;
DECLARE
    fat fatorial_type;
BEGIN  
    FOR fat IN
        (SELECT value(f) FROM fatorial f)
    LOOP
        fat.SET_NUM_FATORIAL(5);
    END LOOP;
END;

If I do

SELECT value(f) into fat FROM fatorial f

thats works, maybe the problem is related with FOR IN that don't work like into

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

羁〃客ぐ 2024-10-26 16:55:44

在 PL/SQL 中循环结果集有几种略有不同的语法。它们为我们提供了对获取的不同级别的控制。最简单的解决方案是不声明游标变量并让 Oracle 解决问题。但您确实需要在投影中为返回值添加别名。

所以，这是一个带有方法的简单类型...

create or replace type test_t as object
  ( n number
    , member procedure do_something(x number)
   );
/

create or replace type body test_t as 
  member procedure do_something (x number)
  is
  begin
    dbms_output.put_line(self.n + x);
  end;    
end;
/

我们在该类型上创建一个表...

create table t23 of test_t
/

insert into t23 values (41)
/
insert into t23 values (68)
/
insert into t23 values (71)
/

现在让我们开始：

SQL>
SQL> set serveroutput on
SQL>
SQL> begin
  2      for r in ( select value(f) as whatever
  3                 from t23 f )
  4      loop
  5          r.whatever.do_something(1);
  6      end loop;
  7  end;
  8  /
42
69
72

PL/SQL procedure successfully completed.

SQL>

There are several slightly different syntaxes for looping through resultsets in PL/SQL. They give us different levels of control over the fetch. The simplest solution is to not declare a cursor variable and let Oracle resolve things. But you do need to alias the returned value in the projection.

So, here is a simple type with a method ...

create or replace type test_t as object
  ( n number
    , member procedure do_something(x number)
   );
/

create or replace type body test_t as 
  member procedure do_something (x number)
  is
  begin
    dbms_output.put_line(self.n + x);
  end;    
end;
/

We create a table on that type ....

create table t23 of test_t
/

insert into t23 values (41)
/
insert into t23 values (68)
/
insert into t23 values (71)
/

Now let's roll:

SQL>
SQL> set serveroutput on
SQL>
SQL> begin
  2      for r in ( select value(f) as whatever
  3                 from t23 f )
  4      loop
  5          r.whatever.do_something(1);
  6      end loop;
  7  end;
  8  /
42
69
72

PL/SQL procedure successfully completed.

SQL>

回复收藏 0 原文