| parameterized view [message #1743] |
Sat, 25 May 2002 14:29  |
lve3162
Messages: 1
Registered: May 2002
|
Junior Member |
|
|
In Microsoft Sql server, i can do the following :
Create proc MYTest
(@StartDate varchar(32),@EndDate varchar(32))
as
select a.col1,sum(b.col2)
from
table1 as a,table2 as b
where a.col1=b.col1 and
a.HireDate between @StartDate and @EndDate
group by a.col1
How can I do this in oracle?
Lve
|
|
|
|
| Re: parameterized view [message #1750 is a reply to message #1743] |
Sun, 26 May 2002 14:38  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
*********************************************
you need to create the stored procedure like shown.
i beleive your sqlserver (tsql?) code will return or display the values in the screen.
this oracle stored procedure also does the same.
else if you want, you can also have an out parameter defined, so that the output of procedure can be passed to another proc or any front-end.
************************************************
SQL> get mytest
1 Create or replace procedure MYTest
2 (StartDate in varchar,
3 EndDate in varchar)
4 as
5 cursor c1 is select a.col1 "col1",sum(b.col2) "sum"
6 from
7 table1 a,table2 b
8 where a.col1=b.col1 and
9 a.HireDate between StartDate and EndDate
10 group by a.col1
11 begin
12 for mag in c1 loop
13 exit when c1%notfound;
14 dbms_output.put_line(mag.col1||',||mag.sum);
15 end loop;
16* end;
*******************************************************
this is sample using oracle demo tables
*****************************************************
Create or replace procedure MYTest
(StartDate in varchar,
EndDate in varchar)
as
cursor c1 is select a.loc loc ,sum(b.sal) sum
from
dept a,emp b
where a.deptno=b.deptno and
b.HireDate between StartDate and EndDate
group by a.loc;
begin
for mag in c1 loop
exit when c1%notfound;
dbms_output.put_line(mag.loc||','||mag.sum);
end loop;
end;
/
Procedure created.
******************************************************
first we'll test the sql
******************************************************
SQL> ed
Wrote file afiedt.buf
1 select a.loc loc ,sum(b.sal) sum
2 from
3 dept a,emp b
4 where a.deptno=b.deptno and
5 b.HireDate between '17-dec-80' and sysdate
6* group by a.loc
SQL> /
LOC SUM
------------- ----------
CHICAGO 9400
DALLAS 7100
NEW YORK 8750
**************************************
now the procedure
**************************************
SQL> exec mytest('17-dec-80',sysdate);
CHICAGO,9400
DALLAS,7100
NEW YORK,8750
PL/SQL procedure successfully completed.
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
