Home » SQL & PL/SQL » SQL & PL/SQL » Loop SP out cursor inside a stored procedure (11.0.2.10)
Loop SP out cursor inside a stored procedure [message #662707] |
Wed, 10 May 2017 02:26 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I have a situation where I need to call a stored procedure that returns a ref cursor from another stored procedure and loop over the returned cursor to insert with a condition that prevents duplication of a certain key.
My example is as follows:
create table temp_src_int
(
id number,
v1 varchar2(10),
v2 varchar2(10),
ddate date
);
insert all
into TEMP_SRC_INT values (1,10,20,sysdate)
into TEMP_SRC_INT values (2,20,30,sysdate)
into TEMP_SRC_INT values (3,110,210,sysdate)
into TEMP_SRC_INT values (4,201,301,sysdate)
into TEMP_SRC_INT values (5,170,910,sysdate)
into TEMP_SRC_INT values (6,801,101,sysdate)
select * from dual;
create table temp_dst_int
(
id number,
v1 varchar2(10),
v2 varchar2(10),
ddate date
);
insert all
into TEMP_DST_INT values (1,10,20,sysdate)
into TEMP_DST_INT values (3,110,210,sysdate)
select * from dual;
create or replace PROCEDURE P_get_src_test
(
mcur out sys_refcursor
)
IS
BEGIN
open mcur for
select * from TEMP_SRC_INT where id <> 4;
END P_get_src_test;
create or replace PROCEDURE P_insert_dst_test
(
S_APP_USER IN VARCHAR2
)
IS
i_counter number :=1;
MCUR SYS_REFCURSOR;
BEGIN
P_get_src_test(MCUR);
FOR MREC IN MCUR --<-- problem 1: how to loop over mcur
LOOP
insert into TEMP_DST_INT(id, v1, v2, ddate)
values (MREC.id, MREC.v1, MREC.v2,MREC.ddate);
where id <> mrec.id; --<---- problem 2: how to add a condition to prevent duplication of id value
END LOOP;
END P_insert_dst_test;
I have a problem in looping over the cursor and another problem regarding the way I refer to cursor field to compare them to table field in order to avoid duplication.
Many thanks,
Ferro
|
|
|
|
|
Re: Loop SP out cursor inside a stored procedure [message #662711 is a reply to message #662710] |
Wed, 10 May 2017 02:46 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
No you can declare just one record variable but you need a strong ref cursor declaration:
SQL> declare
2 type deptrefcursor is ref cursor return dept%rowtype;
3 rc deptrefcursor;
4 rec dept%rowtype;
5 begin
6 open rc for select * from dept;
7 loop
8 fetch rc into rec;
9 exit when rc%notfound;
10 dbms_output.put_line('dept: '||rec.deptno||' '||rec.dname);
11 end loop;
12 close rc;
13 end;
14 /
dept: 10 ACCOUNTING
dept: 20 RESEARCH
dept: 30 SALES
dept: 40 OPERATIONS
PL/SQL procedure successfully completed.
[Edit: add close to proper code]
[Updated on: Wed, 10 May 2017 02:53] Report message to a moderator
|
|
|
|
|
|
Re: Loop SP out cursor inside a stored procedure [message #662721 is a reply to message #662716] |
Wed, 10 May 2017 03:39 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
%ROWTYPE means to get a record type definition from a table description, so can't be applied to an object type.
In addition, to declare it in ref cursor the base type must be a record:
SQL> declare
2 TYPE OBJ_MycurType is record
3 (
4 f1 NUMBER,
5 f2 NUMBER
6 );
7 type deptrefcursor is ref cursor return OBJ_MycurType;
8 type deptarray is table of OBJ_MycurType;
9 rc deptrefcursor;
10 res deptarray;
11 begin
12 open rc for select d1.deptno f1, d2.deptno from dept d1, dept d2 where d1.deptno = d2.deptno;
13 fetch rc bulk collect into res;
14 for i in 1..res.count loop
15 dbms_output.put_line('dept: '||res(i).f1||' '||res(i).f2);
16 end loop;
17 close rc;
18 end;
19 /
dept: 10 10
dept: 20 20
dept: 30 30
dept: 40 40
PL/SQL procedure successfully completed.
They could be types declared in a package specification you can refer in your procedures.
[Edit: typo]
[Updated on: Thu, 11 May 2017 01:20] Report message to a moderator
|
|
|
Re: Loop SP out cursor inside a stored procedure [message #662749 is a reply to message #662721] |
Thu, 11 May 2017 06:17 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Thanks a lot Michel this is another post I learned from.
For the sake of completing the example with Where Does Not Exists part, here is my full example:
CREATE OR REPLACE PACKAGE test_pack AS
TYPE MycurType is record
(
id number,
v1 varchar2(10),
v2 varchar2(10),
ddate date
);
type Myrefcursor is ref cursor return MycurType;
type Myrefarray is table of MycurType;
PROCEDURE P_get_src_test
(
mcur out sys_refcursor
);
PROCEDURE P_insert_dst_test
(
S_APP_USER IN VARCHAR2
);
END test_pack;
CREATE OR REPLACE PACKAGE BODY test_pack AS
PROCEDURE P_get_src_test
(
mcur out sys_refcursor
)
IS
BEGIN
open mcur for
select * from TEMP_SRC_INT where id <> 4;
END P_get_src_test;
PROCEDURE P_insert_dst_test
(
S_APP_USER IN VARCHAR2
)
IS
i_counter number :=1;
rc Myrefcursor;
res Myrefarray;
BEGIN
P_get_src_test(rc);
fetch rc bulk collect into res;
for i in 1..res.count loop
INSERT INTO TEMP_DST_INT
SELECT res(i).id, res(i).v1, res(i).v2, res(i).ddate
FROM dual
WHERE NOT EXISTS (SELECT id
FROM TEMP_DST_INT
WHERE id = res(i).id
);
end loop;
close rc;
END P_insert_dst_test;
END test_pack;
Thanks a lot,
Ferro
[Updated on: Thu, 11 May 2017 06:18] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 06:55:56 CDT 2024
|