正在使用“select *”。对于PL / SQL中的游标认为是坏编程？ [英] Is using "select *" for a cursor in PL/SQL considered bad programming?
问题描述
我通常以这种方式使用游标：
Often I use cursors in this way:
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 *.
You are shipping more data from the SQL engine to your code than necessary, which has a negative effect on performance.
您回收的信息必须是必需的，放置在变量（例如记录变量）中。
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计算。
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.
点。
这篇关于正在使用“select *”。对于PL / SQL中的游标认为是坏编程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
