Reputation: 13233
I have two stored procedures in oracle.
First stored procedure proc1
, returns some rows from table emp
.
In another stored procedure proc2
, i need to call proc1
and insert its result set into another table.
Is it possible to do this or is there any other better way to do this?
-- this is proce_2
create or replace PROCEDURE FIND_EMPLOYEES
(
IN_REGION_ID IN NUMBER,
<< more params >> ,
employee_cursor OUT SYS_REFCURSOR
)
IS
BEGIN
open employee_cursor for
select * from employee where <<some conditions>> ;
END;
Upvotes: 0
Views: 9501
Reputation: 25763
Below there are two examples to copy one row from EMPLOYEES
to EMPLOYEES_COPY
.
I hope it will help you.
cursors
and packages
:DROP TABLE EMPLOYEES;
/
DROP TABLE EMPLOYEES_COPY;
/
CREATE TABLE EMPLOYEES
(
ID INT,
FIRST_NAME varchar(30),
LAST_NAME varchar(30)
);
insert into EMPLOYEES values(1,'AA','BBB');
insert into EMPLOYEES values(2,'CC','GGG');
insert into EMPLOYEES values(3,'EEE','MMM');
insert into EMPLOYEES values(4,'FFF','ZZZ');
/
CREATE TABLE EMPLOYEES_COPY
(
ID INT,
FIRST_NAME varchar(30),
LAST_NAME varchar(30)
);
/
CREATE OR REPLACE PACKAGE EMPLOYEE as
type t_cursor is ref cursor;
procedure FIND(id_emp int, curEMPLOYEE out t_cursor);
procedure INSERT_COPY(id_emp int);
end EMPLOYEE;
/
CREATE OR REPLACE PACKAGE BODY EMPLOYEE as
---------------------------------------------
procedure FIND(id_emp int,curEMPLOYEE out t_cursor) as
begin
open curEMPLOYEE for SELECT id, FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE id= id_emp;
end FIND;
---------------------------------------------
procedure INSERT_COPY(id_emp int) as
cur t_cursor;
emp employees%rowtype;
begin
FIND(id_emp=>id_emp,curEMPLOYEE=>cur);
LOOP
FETCH cur INTO emp;
EXIT WHEN cur%NOTFOUND;
insert into EMPLOYEES_COPY
values(emp.id,emp.FIRST_NAME,emp.LAST_NAME);
END LOOP;
end INSERT_COPY;
end EMPLOYEE;
/
Packages are very useful, because you can group function of a single entity. In example, I have grouped function working on employees.
More informations
DROP TABLE tempEMPLOYEES;
/
DROP TABLE EMPLOYEES;
/
DROP TABLE EMPLOYEES_COPY;
/
CREATE TABLE EMPLOYEES
(
ID INT,
FIRST_NAME varchar(30),
LAST_NAME varchar(30)
);
insert into EMPLOYEES
values(1,'AA','BBB');
insert into EMPLOYEES
values(2,'CC','GGG');
insert into EMPLOYEES
values(3,'EEE','MMM');
insert into EMPLOYEES
values(4,'FFF','ZZZ');
/
CREATE TABLE EMPLOYEES_COPY
(
ID INT,
FIRST_NAME varchar(30),
LAST_NAME varchar(30)
);
/
CREATE GLOBAL TEMPORARY TABLE tempEMPLOYEES (
ID INT,
FIRST_NAME varchar(30),
LAST_NAME varchar(30)
) ON COMMIT DELETE ROWS;
/
CREATE OR REPLACE PACKAGE EMPLOYEE as
procedure INSERT_COPY(id_emp int);
end EMPLOYEE;
/
CREATE OR REPLACE PACKAGE BODY EMPLOYEE as
---------------------------------------------
procedure COPY_TO_TEMP(id_emp int) as
begin
insert into tempEMPLOYEES
SELECT id, FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE id= id_emp;
end COPY_TO_TEMP;
---------------------------------------------
procedure INSERT_COPY(id_emp int) as
begin
COPY_TO_TEMP(id_emp);
insert into EMPLOYEES_COPY
SELECT id, FIRST_NAME, LAST_NAME
FROM tempEMPLOYEES;
--WHERE id= id_emp; <---You can add this If you need.
end INSERT_COPY;
end EMPLOYEE;
/
Here is more informations about views.
You can test both examples using:
DECLARE
ID_EMP NUMBER;
BEGIN
ID_EMP := 4;
EMPLOYEE.INSERT_COPY ( ID_EMP => ID_EMP) ;
commit;
END;
Upvotes: 3
Reputation: 67802
You would use the REF CURSOR as a regular explicit cursor:
SQL> CREATE TABLE employee AS
2 SELECT rownum id, 'XXX' name
3 FROM DUAL CONNECT BY level <= 10;
Table created.
SQL> CREATE OR REPLACE PROCEDURE find_employees (
2 emp_id NUMBER,
3 employee_cursor OUT SYS_REFCURSOR
4 ) IS
5 BEGIN
6 open employee_cursor for
7 select * from employee WHERE id = emp_id;
8 END;
9 /
Procedure created.
SQL> CREATE OR REPLACE PROCEDURE do_something IS
2 cc sys_refcursor;
3 l employee%rowtype;
4 BEGIN
5 find_employees(1, cc);
6 LOOP
7 FETCH cc INTO l;
8 EXIT WHEN cc%NOTFOUND;
9 dbms_output.put_line(l.id||'-'||l.name);
10 END LOOP;
11 END;
12 /
Procedure created.
SQL> EXEC do_something;
1-XXX
Upvotes: 3