模块化代码有哪些好的做法? [英] What are some good practices of modular code?
问题描述
在 PL-SQL 中，有一些奇特的新概念，例如表函数、对象，可能还有其他我尚未发现的概念.
In PL-SQL, there are some fancy new concepts, like table functions, objects, and probably others I have not discovered yet.
但话说回来，还有简单的代码生成(动态 pl-sql)，您可以立即执行".
But then again, there is also plain simple code generation (dynamic pl-sql) that you can "execute immediately".
这些有助于代码重用.
据我所知，表函数和对象可以帮助创建模块化代码，但仍然不足以删除整个代码(也许我没有使用它们中最好的；我不得不承认我的对象只包含数据暂时没有逻辑).
From what I can tell, table functions and objects can help with creating modular code, but still not enough to remove the entire of it (maybe I am not using the best of them; I have to admit my objects only contain data for now and no logic).
另一方面，代码生成要简单得多，并且可以更多地减少重复代码.但是很难理解代码生成逻辑背后的实际业务.
On the other side, the code generating is much more simple, and can reduce duplicate code more. But it is kind of hard to read what the actual business is behind the code generation logic.
我想要模块化且不重复的代码.我应该坚持使用纯代码生成吗?每种方法的优缺点是什么?
I want modular and not-duplicate code. Should I stick with plain code generation? What are some pros and cons of each?
推荐答案
动态 SQL 通常优于高级 PL/SQL 功能，如表函数、对象关系类型、数据盒、ANY* 类型等.一些简单的技巧，您可以避免动态 SQL 的陷阱并使用它来创建模块化系统.
Dynamic SQL is generally better than advanced PL/SQL features like table functions, object-relational types, data cartridge, the ANY* types, etc. With a few simple tips you can avoid the pitfalls of dynamic SQL and use it to create modular systems.
高级 PL/SQL 功能很酷，在某些时候您至少需要使用它们.它们非常适合解决奇怪的特定问题.但是您几乎肯定会后悔创建一个以这些特性之一为中心的 Oracle 系统.我已经在上述每个 PL/SQL 功能上浪费了数周或数月的时间.
Advanced PL/SQL features are cool, and at some point you'll have to use them at least a little. They're great for solving weird, specific problems. But you will almost certainly regret creating an Oracle system that is centered around one of those features. I've wasted weeks or months of my life on each of the above PL/SQL features.
动态 SQL
Pro - 它始终有效.这可能很痛苦，但总是有一种方法可以让它在 SQL 中工作并使其运行得更快.
Pro - It always works. It might be painful, but there's always a way to make it work in SQL and make it run fast.
Con - 阅读和编写有点困难.
Con - A little harder to read and write.
高级 PL/SQL
Pro - 很酷的功能，优雅的代码，可以完美解决某些问题.
Pro - Cool features, elegant code that can perfectly solve certain problems.
Con - 关键时刻会让你失望.
Con - Will let you down at a critical moment.
如果不写小说，很难给出高级 PL/SQL 失败的例子.这些故事通常是这样的:我们结合了功能 A、B、C……我们遇到了错误 X、Y、Z……每个人都生气了……我们花了一个月的时间重新编写它."
It's hard to give examples of advanced PL/SQL failures without writing a novel. The stories typically go something like this: "We combined features A, B, C ... we hit bugs X, Y, Z ... everyone got angry ... we spent a month re-writing it."
动态 SQL 不必那么糟糕.这只是需要一些纪律.
Dynamic SQL doesn't have to be so bad. It just takes some discipline.
良好的格式设置和检测.确保动态 SQL 看起来很漂亮，并且可以轻松打印出来进行调试.遵循良好的编程习惯——缩进、添加注释、使用有意义的名称等.当 IDE 上的美化"按钮对他们没有帮助时，点击式程序员会感到震惊.不要让任何人使用草率的代码——仅仅因为它在技术上是一个字符串，不应该允许任何人避免通用的样式规则.
Good formatting and instrumenting. Make sure the dynamic SQL looks beautiful and it can be easily printed out for debugging. Follow good programming practices - indent, add comments, use meaningful names, etc. It will be a shock to the point-and-click programmers when the "Beautifier" button on the IDE doesn't help them. Don't let anyone get away with sloppy code - just because it's technically a string shouldn't allow anybody to avoid common style rules.
替代引用机制.使用 q 语法来避免不断地转义.例如，q'[我想用单引号！]' 而不是 'I''我想用单引号！'.
Alternative quoting mechanism. Use the q syntax to avoid constantly escaping things. For example, q'[I'll use single quotes if I want to!]' instead of 'I''ll use single quotes if I want to!'.
模板而不是串联.在不间断的块中编写代码，然后稍后替换动态部分.将它与 q 字符串组合在一起，以避免代码中出现一百万个引号和管道.例如:
Templates instead of concatenation. Write the code in un-interrupted blocks and then replace the dynamic parts later. Combine it with the q strings to avoid a million quotation marks and pipes in the code. For example:
v_dynamic_sql_template constant varchar2(32767) :=
q'[
select a, b, $DYNAMIC_SELECT_LIST$
from table1
$DYNAMIC_JOIN_1$
where table1.a > 1
$DYNAMIC_WHERE_1$
]';
...
v_dyanmic_sql := replace(v_dynamic_sql_template, '$DYNAMIC_SELECT_LIST$', v_variable);
...
(在这个问题中，我假设您是中级或高级 Oracle 开发人员.如果您是初学者，答案可能是静态 SQL 语句，但您还没有看到足够多的 SQL 特性来实现这一点.)
(In this question I assume you are an intermediate or advanced Oracle developer. If you're a beginner, the answer is probably static SQL statements but you haven't seen enough SQL features to realize that yet.)
这篇关于模块化代码有哪些好的做法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
