在存储过程中重用大型 SQL 查询 [英] Reusing large SQL queries in stored procedures

问题描述

我想在 PL/SQL 包中使用许多长 SQL 选择查询(150 行以上).该包具有执行 SQL 查询并将结果插入到单独的表中、将 SQL 结果与另一个表进行比较、删除行等的过程

I have a number of long SQL select queries( 150 lines+) that I want to use in a PL/SQL package. The package has procedures to execute the SQL queries and insert the results into a separate table, compare the SQL results to another table, delete rows etc

存储 SQL 结果相当容易:

Its fairly easy to store the SQL results with:

INSERT into TABLE1
SELECT .... (150 line ugly select query goes here)

问题是，我想将选择 SQL 存储在游标/函数/视图/任何工作中，这样我就不必将 150 行查询粘贴到使用 SQL 的每个过程中.

Problem is, I want to store the select SQL in a cursor/function/view/whatever-works so I don't have to paste the 150 lines query into each procedure where the SQL is used.

我可以将 SQL 存储为游标，然后在包过程中循环游标，获取每一行并例如插入到我的表中.但是考虑到我使用光标的唯一动机是减少我的包的行数，这似乎非常低效.

I can store the SQL as a cursor then loop through the cursor within a package procedure, fetching each row and eg inserting into my table. But this seems very inefficient considering my only motivation for using a cursor is reducing the amount of lines my package.

有没有更好的方法在不同的过程中调用SQL select查询而不用复制&粘贴所有 150 行?如果这是一个脚本，我会将 SQL 存储在一个文本文件中，然后将文本文件读入一个变量，并在需要时将该变量传递给 sqlplus.但我对 PL/SQL 不是很熟悉.

Is there a better way to call the SQL select query in different procedures without copying & pasting all 150 lines? If this was a script, I would store the SQL in a text file then just read the text file into a variable and pass the variable to sqlplus when needed. But I'm not very familiar with PL/SQL.

代码:

CREATE OR REPLACE PACKAGE BODY MyPackage
as
Cursor my_cursor
select (150+ lines goes here)

PROCEDURE PopulateTable
is 
TYPE fetch_array IS TABLE OF my_cursor%ROWTYPE;
s_array fetch_array;
BEGIN

  open my_cursor;

  LOOP
    FETCH tran_cursor BULK COLLECT INTO s_array;
    FORALL counter in 1..s_array.COUNT
        INSERT INTO my_table VALUES s_array(counter);
    EXIT when s_array%NOTFOUND;
  END LOOP;  

  close my_cursor;
  COMMIT;

END PopulateTable;
END MyPackage;

推荐答案

我不确定这是否是最好的方法，但我想到的是可变游标.您可以使用 SYS_REFCURSOR 做到这一点.您可以构建一个包含您的查询并返回 ref curosr 的函数.在您的所有过程中，您只需调用该函数即可.这将节省您在每个过程中编写 150 多行查询.更重要的是，它会将您的程序限制为查询的一个副本，因此易于维护.

I am not sure if this would be the best way to do, but what came to my mind, is a variable cursor. You could do that using SYS_REFCURSOR. You can build a function that contains your query, and returns ref curosr. In all your procedures, you can just call that function. This will save you writing 150+ lines query in every procedure. More important, it will limit your program to one copy of the query, and therefore easy to maintain.

返回引用游标的函数可能是这样的:

The function that returns the ref cursor, could be something like this:

CREATE OR REPLACE FUNCTION my_ugly_query() 
                           RETURN SYS_REFCURSOR
AS
  my_cursor_ref SYS_REFCURSOR;
BEGIN
  OPEN my_cursor_ref FOR
       SELECT -- 150+ lines of query;
  RETURN my_cursor_ref;
END;

这是如何使用它:

CREATE OR REPLACE PACKAGE BODY MyPackage
as
PROCEDURE PopulateTable
IS 
  l_cur_refcur   SYS_REFCURSOR;
  s_array        fetch_array;
BEGIN
  l_cur_refcur := my_ugly_query();

  LOOP
    FETCH tran_cursor BULK COLLECT INTO s_array;
    EXIT when s_array%NOTFOUND;
    FORALL counter in 1..s_array.COUNT
        INSERT INTO my_table VALUES s_array(counter);
  END LOOP;  

  CLOSE my_cursor;
  COMMIT;

END PopulateTable;
END MyPackage;

这篇关于在存储过程中重用大型 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！