Oracle:创建存储过程，将另一个存储过程的结果插入到表中

Question

我有一个Oracle存储过程spFinTest，我在SSRS报告中使用。

我希望创建另一个存储过程spFinTestInsert，它接受spFinTest的输出并将其插入表sbceybudget_financial_year。

我可以创建一个将数据插入目标表的所有存储过程，但是我希望实现的是只有一个存储过程可以对数据提取进行代码更新，而不必更新具有相同代码的第二个单独的插入存储过程。

因此，只在一个地方更新并重用相同的存储过程。

下面是主存储过程的简化版本:

create or replace procedure spFINTEST
(s1 OUT SYS_REFCURSOR)
AS
BEGIN
OPEN s1 FOR
SELECT
FIN_YR + 1 AS FIN_YR,
SCHOOL_YEAR_WEEKS
FROM
sbceybudget_financial_year
WHERE
fin_yr = 2021
;
END spFINTEST;

这个存储过程只有"输出"变量。

最后的意图是，一旦我能做到这一点，然后我将调用spFinTestInsert从一个"执行SQL任务";

对于如何创建第二个存储过程并调用第一个存储过程并将结果插入到命名表中，我有点困惑，所以如果有人可以帮助我，我将非常感激。

Answer 1

我会在包中这样做:它允许您轻松声明光标类型，使其更清晰。

测试表:

create table sbceybudget_financial_year(
fin_yr            int,
SCHOOL_YEAR_WEEKS int
)
/

包装规格:

create or replace package pkg_spFIN as
type spFIN_RowType is record(
FIN_YR            sbceybudget_financial_year.FIN_YR%type,
SCHOOL_YEAR_WEEKS sbceybudget_financial_year.SCHOOL_YEAR_WEEKS%type
);

type spFIN_CurType IS REF CURSOR RETURN spFIN_RowType;
type spFIN_tab is table of spFIN_RowType;
procedure spFINTEST (s1 OUT SYS_REFCURSOR);
procedure spFinTestInsert;
end pkg_spFIN;
/

包体:

create or replace package body pkg_spFIN as
function get_cursor(n int) return spFIN_CurType is
c spFIN_CurType;
begin
open c for
SELECT
t.FIN_YR + 1 AS FIN_YR,
t.SCHOOL_YEAR_WEEKS
FROM
sbceybudget_financial_year t
WHERE
t.fin_yr = n;
return c;
end;

procedure spFINTEST (s1 OUT SYS_REFCURSOR)
is
begin
s1:=get_cursor(2021);
end spFINTEST;
procedure spFinTestInsert
is
cur spFIN_CurType;
tab spFIN_tab;
begin
pkg_spFIN.spFINTEST(cur);
loop
fetch cur bulk collect into tab limit 100;
exit when tab.count()=0;
for i in 1..tab.count loop
dbms_output.put_line(tab(i).FIN_YR);
dbms_output.put_line(tab(i).SCHOOL_YEAR_WEEKS);
-- or insert:
-- insert into sbceybudget_financial_year(fin_yr, SCHOOL_YEAR_WEEKS)
--  values(tab(i).FIN_YR, SCHOOL_YEAR_WEEKS)
-- you can change it to FORALL insert
end loop;
end loop;
end spFinTestInsert;
end pkg_spFIN;
/

测试数据:

begin
insert into sbceybudget_financial_year(fin_yr, SCHOOL_YEAR_WEEKS) values(2019,19);
insert into sbceybudget_financial_year(fin_yr, SCHOOL_YEAR_WEEKS) values(2020,20);
insert into sbceybudget_financial_year(fin_yr, SCHOOL_YEAR_WEEKS) values(2021,21);
commit;
end;
/

最后测试调用:

call pkg_spFIN.spFinTestInsert();

关于DBFiddle的完整示例:https://dbfiddle.uk/?rdbms=oracle_18&fiddle=c31a5714e5db74eaa3a83fae03964349

Answer 2

我没有你的表格，所以我将使用Scott的DEPT作为说明。

这是目标"表;通过ref游标获取的值将被插入其中:

SQL> create table test_dept as select deptno, dname from dept where 1 = 2;
Table created.

这是我期望的数据:

SQL> select deptno, dname from dept where deptno <= 20;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH

这是你当前的程序:

SQL> create or replace procedure spfintest (s1 out sys_refcursor)
2  as
3  begin
4    open s1 for select deptno, dname from dept where deptno <= 20;
5  end spfintest;
6  /
Procedure created.

这是一个调用spfintest并将值插入test_dept的过程:

SQL> create or replace procedure spfitestinsert as
2    rc sys_refcursor;
3    --
4    l_deptno dept.deptno%type;
5    l_dname  dept.dname%type;
6  begin
7    spfintest(rc);
8    loop
9      fetch rc into l_deptno, l_dname;
10      exit when rc%notfound;
11
12      insert into test_dept (deptno, dname)
13        values (l_deptno, l_dname);
14    end loop;
15  end;
16  /
Procedure created.

测试:

SQL> exec spfitestinsert;
PL/SQL procedure successfully completed.
SQL> select * from test_dept;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
SQL>

一切都在这里，所以我想它可以工作。