首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >存储过程中的Oracle For循环未循环

问存储过程中的Oracle For循环未循环
EN

Stack Overflow用户
提问于 2012-11-30 02:00:38
回答 1查看 5.6K关注 0票数 0

在尝试提高系统中SP的可维护性时,我认为使用循环比硬编码一个值数组(在本例中是表名)更好,并尝试相应地重构代码,以便向系统添加或删除表不需要编辑数组。暂时抛开循环的原因不谈(我非常清楚反对它们的理由),有人能解释一下发生了什么吗?

假设有两个用户,SourceUser和DestUser都在同一个数据库中,每个用户的表都在同一个表空间中。出于报告目的,SourceUser中的大量存储过程将数据从SourceUser填充到DestUser中。作为其中的一部分,要运行的第一个过程删除DestUser中的所有表并重新创建它们。再说一次,这里不讨论这样做的相对优点。

SourceUser在DestUser上具有Drop Any Table和Create Any Table权限。在DestUser中有一个我们想要保留的表。因此,我在过程中构造的SQL如下所示:

代码语言:javascript
复制
Begin
  For T In (SELECT TABLE_NAME FROM all_tables WHERE TABLE_NAME != 'MIDBLOG' AND OWNER = sTarget_DB) Loop
    Begin
      Execute Immediate('Drop Table ' || sTarget_DB || '.' || T.TABLE_NAME);

    Exception
      When Others Then
        --Don't care if we get an exception here as most likely the table wasn't there to be dropped in the first place.
        NULL;
    End;
  End Loop;
End;

在本例中,sTarget_DB设置为DestUser,并且针对SourceUser运行此代码。

当该过程运行时,我发现没有表被删除(在开始之前,我确认有几十个表,其中包括一个名为MIDBLOG的表)。我在SQL Developer调试模式下运行它,执行甚至从未到达循环的内部,因为它似乎认为它没有要处理的行,但我确定select语句将返回几十个表名。

接下来,我将其修改为:

代码语言:javascript
复制
Begin
  For T In (SELECT TABLE_NAME FROM all_tables WHERE OWNER = sTarget_DB) Loop
    Begin
      If T.TABLE_NAME != 'MIDBLOG' THEN
        Execute Immediate('Drop Table ' || sTarget_DB || '.' || T.TABLE_NAME);
      End If;
    Exception
      When Others Then
        --Don't care if we get an exception here as most likely the table wasn't there to be dropped in the first place.
        NULL;
    End;
  End Loop;
End;

在运行这个之后,唯一被删除的表就是我不想删除的那个表!更奇怪的是,循环只执行一次,就好像select查询只返回一行一样。如果我在SQL Developer 3.2上运行debug中的过程,我可以看到它的发生。我们在SQL Developer (可能是3.1)上的同事PC上做了同样的事情,循环只执行了一次,但这一次它正确地决定不删除表MIDBLOG,并再次忽略了其他所有事情。

如果我在SQL Developer中以匿名块的形式运行上面的任何一个示例,它所做的正是我期望它所做的事情。我尝试了更详细的显式游标声明,得到了与前面相同的结果。在任何时候我都没有得到任何例外。

所有这些都是在Oracle 10g企业版10.2.0.4.0 (64位)上实现的。在Oracle11g企业版11.2.0.1.0 (64位)上一试,它就工作得很好。为什么如此基本的需求在两个版本中表现出如此巨大的差异?使用相同的代码,它可以在两个版本中按我想要的那样工作吗?

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2012-11-30 03:12:47

我的猜测是问题与权限有关,而不是Oracle的版本。是否通过一个数据库中的角色和另一个数据库中的直接授权将DestUser上的权限授予SrcUser?

在运行匿名PL/SQL块之前,如果先禁用角色,会发生什么情况?

代码语言:javascript
复制
set role none;
<<run the anonymous PL/SQL block>>

如果将检测添加到代码中,则对all_tables的查询是否会返回预期的表集?我的猜测是,当代码失败时,它在定义者的权限存储过程中,该过程的所有者可以通过角色访问DestUser表。由于通过角色授予的权限在定义者的权限存储过程中不可见,这将导致循环中的SELECT语句返回0行(尽管交互地运行相同的查询将返回预期的行)。另一方面,如果直接授予DestUser表上的特权,则相同的定义者权限存储过程将成功运行。并且它可以在匿名PL/SQL块中工作。

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/13631859

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档