I have some stored procedures in Firebird that I need to migrate to a new database in Oracle. Here's a simplified example of one of them:
create or alter procedure CALCULA_ARTIC (
FECHA_INICIO date,
FECHA_FIN date)
returns (
COSTE varchar(100),
PROVEEDOR varchar(100),
IDARTICULO integer)
as
begin
for select CS.COSTE, P.PROVEEDOR,
AA.IDARTICULO
from ALBARAN A
join COSTE_SUBGRUPO CS on CS.IDCOSTESUBGRUPO = A.IDCOSTESUBGRUPO
join PROVEEDOR P on P.IDPROVEEDOR = A.IDPROVEEDOR
where ALB.FECHA between :FECHA_INICIO and :FECHA_FIN
into :COSTE, :PROVEEDOR, :IDARTICULO
do
begin
suspend;
end
end
To call this procedure in Firebird, I would just do
Select * from CALCULAR_ARTIC(:FECHA_INICIO, :FECHA_FIN)
How can I achieve the same behaviour in Oracle?
I've tried using functions that would return me a refcursor, but then I have trouble using a select over that cursor, because I don't want to print them using dbms_output, I need the values to be return in a table format such as:
COSTE PROVEEDOR IDARTICULO
----- --------- ----------
13 1 125
7,5 2 89
This is one option, which uses custom-declared types and a function. In this example, I'm passing department number and returning list of employees (their number, name and salary).
SQL> CREATE OR REPLACE TYPE t_row IS OBJECT (empno NUMBER, ename VARCHAR2 (10), sal NUMBER);
2 /
Type created.
SQL> CREATE OR REPLACE TYPE t_tab IS TABLE OF t_row;
2 /
Type created.
SQL> CREATE OR REPLACE FUNCTION f_test (par_deptno IN dept.deptno%TYPE)
2 RETURN t_tab
3 IS
4 retval t_tab;
5 BEGIN
6 SELECT t_row (empno, ename, sal)
7 BULK COLLECT INTO retval
8 FROM emp
9 WHERE deptno = par_deptno;
10
11 RETURN retval;
12 END f_test;
13 /
Function created.
Testing:
SQL> SELECT * FROM TABLE (f_test (10));
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
SQL>
You said you want a procedure; you can, with an OUT parameter, but - for result you posted - I'd say that function is a better choice.