重构 PL/SQL

发布于 2024-11-29 15:48:55 字数 699 浏览 1 评论 0原文

我在工作中遇到了下面的PL/SQL代码（修改了变量）：

PROCEDURE test(i_w IN a.w%type,o_result IN OUT resu_cur_type) IS
BEGIN
    IF i_w IS NULL THEN
      open o_result for SELECT a.x, b.y FROM a,b WHERE a.z=b.z;
    ELSE
      open o_result for SELECT a.x, b.y FROM a,b WHERE a.z=b.z AND a.w=i_w;
    END IF;
END test;

我认为上面的内容等同于下面的内容：

PROCEDURE test(i_w IN a.w%type,o_result IN OUT resu_cur_type) IS
BEGIN
      open o_result for SELECT a.x, b.y FROM a,b WHERE a.z=b.z AND NVL(a.w,1)=NVL(NVL(i_w,a.w),1);
END test;

不是一样的吗？有人可以解释一下是否有任何理由使用原来的版本？请注意，原始游标是一个 50 行查询，因此重构可能会提高其可读性并保持简单。

编辑：

为了解决 Kevin Burton 的答案中的问题，我更新了 WHERE 条件。

原文

I encountered the following PL/SQL code(variables modified) at work:

PROCEDURE test(i_w IN a.w%type,o_result IN OUT resu_cur_type) IS
BEGIN
    IF i_w IS NULL THEN
      open o_result for SELECT a.x, b.y FROM a,b WHERE a.z=b.z;
    ELSE
      open o_result for SELECT a.x, b.y FROM a,b WHERE a.z=b.z AND a.w=i_w;
    END IF;
END test;

I think the above is equivalent to the following:

PROCEDURE test(i_w IN a.w%type,o_result IN OUT resu_cur_type) IS
BEGIN
      open o_result for SELECT a.x, b.y FROM a,b WHERE a.z=b.z AND NVL(a.w,1)=NVL(NVL(i_w,a.w),1);
END test;

Won't it be the same?. Could somebody please explain is there any reason to use the
original version?.
Please note that the original cursor is a 50 line query, so refactoring might improve its readability and keep it simple.

Edit:

To address the problem in Kevin Burton's answer, I've updated the WHERE condition.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

囚你心 2024-12-06 15:48:55

正如凯文·伯顿所说，您的新查询并不等同。但是

PROCEDURE test(i_w IN a.w%type,o_result IN OUT resu_cur_type) IS
BEGIN
      open o_result for SELECT a.x, b.y FROM a,b
                        WHERE a.z=b.z 
                        AND (i_w IS NULL OR a.w=i_w);
END test;

，原始代码可能更有效，因为当 i_w 不为空时可以使用 aw 上的索引，但当它为空时会执行不同的操作 - 即它根据所使用的参数定制查询。

As Kevin Burton said, your new query is not equivalent. This is though:

PROCEDURE test(i_w IN a.w%type,o_result IN OUT resu_cur_type) IS
BEGIN
      open o_result for SELECT a.x, b.y FROM a,b
                        WHERE a.z=b.z 
                        AND (i_w IS NULL OR a.w=i_w);
END test;

However, the original code may be more efficient as can use an index on a.w when i_w is not null but do something different when it is null - i.e. it tailors the query to the parameters used.

回复收藏 0 原文