Oracle Stored Procedure to fetch Orders

Oracle Stored Procedure to fetch Orders  using PL/SQL oracle 11g

1)   Creating a Stored Procedure which accepts  two input params
            1.Order From Date
            2.Order To Date.

2) Displays Order Details

3) Executing Stored Procedure from SQLPLUS.


This procedure fetches rows between the dates stored in cursor.
loop through cursor to fetch record by record.

create or replace procedure sp_orders(orderDateFrom Date,OrderDateTo Date)
as

cursor c1 is select * from orders between orderDateFrom and OrderDateTo;
begin
for i in c1
loop
dbms_output.put_line(i.orderid||' '||i.customerid||' '||i.shipcountry);
end loop;

end;

Executing Stored Procedure from PL/SQL anonymous block

declare
f date;
t date;
begin
f:=to_date('1997-JAN-01','YYYY-MON-DD');
t:=to_date('1997-DEC-31','YYYY-MON-DD');
sp_orders(f,t);
end;

SQL>/

10771 ERNSH Austria
10772 LEHMS Germany
10773 ERNSH Austria
10774 FOLKO Sweden
10775 THECR USA
10776 ERNSH Austria
10777 GOURL Brazil
10778 BERGS Sweden
10779 MORGK Germany
10780 LILAS Venezuela
10781 WARTH Finland
10782 CACTU Argentina
10783 HANAR Brazil
10784 MAGAA Italy
10785 GROSR Venezuela
10786 QUEEN Brazil

Executing a Stored Procedure using Call Statement

SQL> call sp_orders(to_date('1997-JAN-01','YYYY-MON-DD'),
to_date('1997-DEC-31','YYYY-MON-DD')
)

Note:Output as shown above

 Executing  a Stored Procedure using Named Parameters

Note: Params can be in any order, because Param names are specifed.

SQL>call sp_orders(OrderDateTo=>to_date('1997-DEC-01','YYYY-MON-DD'),
OrderDateFrom=>to_date('1997-JAN-31','YYYY-MON-DD')
)

Note:Output as shown above

Tags: Executing  a Stored Procedure using Named Parameters,
Executing a Stored Procedure using Call Statement,
Executing Stored Procedure from PL/SQL anonymous block,Creating a Stored Procedure which accepts,
Oracle Stored Procedure to fetch Orders,oracle stored procedures creation and execution.oracle 11g stored procedures