Oracle语法-我们是否必须在新旧之间进行选择? [英] Oracle syntax - should we have to choose between the old and the new?

问题描述

我在一个由大约8个开发人员组成的团队中开发了大约1'000'000行代码的代码库.我们的代码基本上是使用Oracle数据库的应用程序，但是代码随着时间的流逝而发展(我们从90年代中期开始就有大量的源代码！).

I work on a code base in the region of about 1'000'000 lines of source, in a team of around eight developers. Our code is basically an application using an Oracle database, but the code has evolved over time (we have plenty of source code from the mid nineties in there!).

团队之间就我们用于查询Oracle数据库的语法发生了争执.目前，绝大多数查询都使用旧的" Oracle语法进行联接，这意味着我们的代码看起来像这样……

A dispute has arisen amongst the team over the syntax that we are using for querying the Oracle database. At the moment, the overwhelming majority of our queries use the "old" Oracle Syntax for joins, meaning we have code that looks like this...

内部联接示例

select customers.*
       , orders.date
       , orders.value 
from customers, orders
where customers.custid = orders.custid

外部连接的示例

select customers.custid
       , contacts.ContactName
       , contacts.ContactTelNo 
from customers, contacts 
where customers.custid = contacts.custid(+)

随着新开发人员加入团队，我们注意到其中一些人似乎更喜欢使用SQL-92查询，如下所示:

As new developers have joined the team, we have noticed that some of them seem to prefer using SQL-92 queries, like this:

内部联接示例

select customers.*
       , orders.date
       , orders.value 
from customers inner join orders 
     on (customers.custid = orders.custid)

外部连接的示例

select customers.custid
      , contacts.ContactName
      , contacts.ContactTelNo
from customers left join contacts 
      on (customers.custid = contacts.custid)

A组说每个人都应该使用旧"语法-我们有很多这种格式的代码，并且我们应该重视一致性.现在我们没有时间去遍历所有代码来重写数据库查询，如果有的话，也不会付钱给我们.他们还指出，这是我们一直以来做到的方式，我们对此感到满意..."

Group A say that everyone should be using the the "old" syntax - we have lots of code in this format, and we ought to value consistency. We don't have time to go all the way through the code now rewriting database queries, and it wouldn't pay us if we had. They also point out that "this is the way we've always done it, and we're comfortable with it..."

B组说他们同意我们没有时间回去更改现有查询，我们确实应该对从这里开始编写的代码采用新"语法.他们说开发人员实际上只一次只看一个查询，只要开发人员知道这两种语法，严格地坚持旧的语法就不会有任何收获，因为将来可能会弃用旧的语法.

Group B however say that they agree that we don't have the time to go back and change existing queries, we really ought to be adopting the "new" syntax on code that we write from here on in. They say that developers only really look at a single query at a time, and that so long as developers know both syntax there is nothing to be gained from rigidly sticking to the old syntax, which might be deprecated at some point in the future.

我没有宣布我的忠诚度属于哪一组，我有兴趣听取公正观察员的意见-因此，让比赛开始吧！

Without declaring with which group my loyalties lie, I am interested in hearing the opinions of impartial observers - so let the games commence!

马丁.

Ps.我将其设置为社区Wiki，以免被公然追逐问题点...

Ps. I've made this a community wiki so as not to be seen as just blatantly chasing after question points...

推荐答案

此处类似，但开发人员不多，代码也不如以前.我正在使用更新的东西，年龄较大的家伙正在使用较旧的样式，但我们俩都知道对方正在尝试做什么.

Similar thing here, but not as many devs, and not as old of code. I'm using the newer stuff, the older guys are using the older style, but we both know what the other is trying to do.

就个人而言，我想说哪种样式更适合单个开发人员使用.除非您运行基准测试，然后发现一个基准测试比另一个基准测试要快(例如，相差足够大，以至于不算大)，并且新老版本都可以读取&了解他们所看到的查询，没有理由对其进行更改.

Personally, I'd say go with whichever style is easier for the individual developer to use. Unless you run benchmarks and find out that one is faster than the other (as in, enough of a difference to be significant), and both new and old can read & understand the queries they see, there's no reason to change them.

但是，我个人的投票是将旧内容保持原样，并使用较新的语法编写新查询，因为使用JOIN s和USING和ON等更容易阅读，然后知道发生了什么，然后在WHERE部分中有一堆AND x.col = y.col AND z.col = a.col.

However, my personal vote would be to leave the old stuff as-is, and write new queries using the newer syntax, as using JOINs and USING and ON etc. are a lot easier to read, and know what's going on, then having a bunch of AND x.col = y.col AND z.col = a.col in the WHERE section.

那，新人可能会更长一些，所以他们最终会走上路...

That, and the new guys are probably going to be around longer, so they're gonna get their way eventually...

不知道其余的人，但是我讨厌不得不尝试使用老式的加入方式来弄清楚这样的事情(或写这样的事情):

Don't know about the rest of you, but I'd hate having to try figuring something like this out (or write this) using the old-style of joining:

SELECT DISTINCT product_zone_map_id, zh.name_english, zh.name_french, zone_id, ad.attribute_value_english AS bullprep_region_type,
        product_zone_type_id, ad.attribute_value_english, language_english, product_code, office_code,
        (
            SELECT attribute_value_english
            FROM presentation p JOIN presentation_details ad USING(presentation_id)
            WHERE dimension_id = 4
              AND object_id = product_zone_map_id
              AND attribute_type = 'BULLPREP PARENT ID'
              AND p.usage_start_date <= TO_TIMESTAMP('2010-05-12', 'yyyy-mm-dd hh24:mi:ss')
              AND (p.usage_end_date >= TO_TIMESTAMP('2010-05-12', 'yyyy-mm-dd hh24:mi:ss') OR p.usage_end_date IS NULL)
        ) AS bullprep_parent_id,
        (
            SELECT attribute_value_english
            FROM presentation p JOIN presentation_details ad USING(presentation_id)
            WHERE dimension_id = 4
              AND object_id = product_zone_map_id
              AND attribute_type = 'BULLPREP GROUP ID'
              AND p.usage_start_date <= TO_TIMESTAMP('2010-05-12', 'yyyy-mm-dd hh24:mi:ss')
              AND (p.usage_end_date >= TO_TIMESTAMP('2010-05-12', 'yyyy-mm-dd hh24:mi:ss') OR p.usage_end_date IS NULL)
        ) AS bullprep_group_id, product_zone_seq
FROM zone z JOIN zone_history zh ON(z.zone_id = zh.zone_id)
     JOIN product_zone_map pzm ON(z.zone_id = pzm.zone_id)
     JOIN product USING(product_id)
     JOIN product_history ph USING(product_id)
     JOIN language_reference USING(language_id)
     LEFT OUTER JOIN product_zone_attribute_details pzad USING(product_zone_map_id)
     LEFT OUTER JOIN attribute_details ad USING(attribute_id)
     JOIN zone_geocode_map USING(zone_id)
     JOIN geocode USING(geocode_id)
WHERE zh.usage_start_date <= TO_TIMESTAMP('2010-05-12', 'yyyy-mm-dd hh24:mi:ss')
  AND (zh.usage_end_date >= TO_TIMESTAMP('2010-05-12', 'yyyy-mm-dd hh24:mi:ss') OR zh.usage_end_date IS NULL)
  AND pzm.usage_start_date <= TO_TIMESTAMP('2010-05-12', 'yyyy-mm-dd hh24:mi:ss')
  AND (pzm.usage_end_date >= TO_TIMESTAMP('2010-05-12', 'yyyy-mm-dd hh24:mi:ss') OR pzm.usage_end_date IS NULL)
  AND (attribute_type = 'BULLPREP REGION TYPE' OR attribute_type IS NULL)
  AND product_id = 2075
ORDER BY product_zone_seq

这篇关于Oracle语法-我们是否必须在新旧之间进行选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！