正在使用“选择 *"PL/SQL 中的游标被认为是糟糕的编程? [英] Is using "select *" for a cursor in PL/SQL considered bad programming?

问题描述

我经常这样使用游标:

for rec in (select * from MY_TABLE where MY_COND = ITION) loop
    if rec.FIELD1 = 'something' then
        do_something();
    end if;

    if rec.FIELD2 <> 'somethingelse' then
        blabla();
    end if;
end loop;

我的团队领导告诉我不要使用 select * 因为它是糟糕的编程，但我不明白为什么(在这种情况下).

My team leader told me not to use select * because it is bad programming, but I don't understand why (in this context).

推荐答案

在你的代码中使用 select * 就是我所说的惰性编程，有几个讨厌的副作用.您经历这些副作用的程度会有所不同，但绝不会是积极的.

Using select * in your code is what I would call lazy programming, with several nasty side effects. How much you experience those side effects, will differ, but it's never positive.

我将使用其他答案中已经提到的一些要点，但可以随意编辑我的答案并添加一些关于使用 select * 的负面观点.

I'll use some of the points already mentioned in other answers, but feel free to edit my answer and add some more negative points about using select *.

1. 您从 SQL 引擎向您的代码传送了不必要的数据，这会对性能产生负面影响.

1. You are shipping more data from the SQL engine to your code than necessary, which has a negative effect on performance.

您返回的信息需要放在变量中(例如记录变量).这将需要更多的 PGA 内存.

The information you get back needs to be placed in variables (a record variable for example). This will take more PGA memory than necessary.

通过使用 select *，您将永远不会单独使用索引来检索所需的信息，您还必须始终访问该表(前提是不存在包含所有列的索引)表).同样，对性能有负面影响.

By using select * you will never use an index alone to retrieve the wanted information, you'll always have to visit the table as well (provided no index exists which holds all columns of the table). Again, with a negative effect on performance.

对于维护你的代码的人来说，你的意图是什么不太清楚.他们需要深入研究代码以发现所有出现的记录变量以了解正在检索的内容.

Less clear for people maintaining your code what your intention is. They need to delve into the code to spot all occurrences of your record variable to know what is being retrieved.

您不会使用 SQL 函数来执行计算，而是始终依赖 PL/SQL 或 Java 计算.您可能会错过一些重要的 SQL 改进，例如分析函数、模型子句、递归子查询分解等.

You will not use SQL functions to perform calculations, but always rely on PL/SQL or Java calculations. You are possibly missing out on some great SQL improvements like analytic functions, model clause, recursive subquery factoring and the like.

从 Oracle11 开始，在列级别跟踪依赖关系，这意味着当您使用 select * 时，您的代码在数据字典中被标记为依赖于所有列"那张桌子.当其中一列发生问题时，您的程序将失效.因此，使用 select * 意味着您的代码将比必要的更频繁地失效.

From Oracle11 onwards, dependencies are being tracked on column level, meaning that when you use select *, your code is being marked in the data dictionary as "dependent on all columns" of that table. Your procedure will be invalidated when something happens to one of those columns. So using select * means your code will be invalidated more often than necessary.

同样，请随意添加您自己的分数.

Again, feel free to add your own points.

这篇关于正在使用“选择 *"PL/SQL 中的游标被认为是糟糕的编程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！