| A good Question on stored proc [message #38492] |
Mon, 22 April 2002 14:47  |
Appala
Messages: 1
Registered: April 2002
|
Junior Member |
|
|
Hi,
I'm in a situation like this.
A column has the data like this.
1
1
1
2
2
1
1
I wanna write a stored proc such that the output should be 1 , 2, 1. ( group all the first 1's until 2 comes, then again group 2 until 1 comes again, again 1 ).
Is it possible to write a stored proc for this??
It is very crucial for me.. Please reply ASAP..
|
|
|
|
| Re: A good Question on stored proc [message #38493 is a reply to message #38492] |
Mon, 22 April 2002 15:25  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Since this is obviously not an ordered list, you will need some other driving column to sort the result set on. In other words, make sure your SQL statement is solid and repeatable before even thinking about the PL/SQL part.
You will need something like a timestamp column to sort on. You cannot rely on the order of insertion.
|
|
|
|
| Re: A good Question on stored proc [message #38495 is a reply to message #38492] |
Mon, 22 April 2002 22:18 |
Jack Smith
Messages: 1
Registered: April 2002
|
Junior Member |
|
|
I think you want something like the following. Instead of using DBMS_OUTPUT you could make the procedure return the output in a collection of some sort.
CREATE OR REPLACE PROCEDURE Compact_Rows
AS
CURSOR myCursor IS SELECT columnC FROM myTable;
cur_val myTable.columnC%TYPE;
next_val myTable.columnC%TYPE;
BEGIN
OPEN myCursor;
-- Initialize the first and next values with the first row's value
FETCH myCursor INTO cur_val;
next_val := cur_val;
IF myCursor%FOUND THEN
DBMS_OUTPUT.PUT_LINE (cur_val);
-- Step through the rows. If the next row is not
-- the same as the last row then output it's value once.
LOOP
FETCH myCursor INTO next_val;
EXIT WHEN myCursor%NOTFOUND;
IF ( next_val != cur_val ) THEN
DBMS_OUTPUT.PUT_LINE (next_val);
cur_val := next_val;
END IF;
END LOOP;
END IF;
END;
|
|
|
|
| Re: A good Question on stored proc [message #38508 is a reply to message #38492] |
Tue, 23 April 2002 22:07 |
Amit
Messages: 166
Registered: February 1999
|
Senior Member |
|
|
--This function will return all the required values for ur manipulation apart from printing the values...
Check the syntax....
CREATE OR REPLACE FUNCTION f1 (
b OUT pkg.ct)
return integer
AS
i BINARY_INTEGER := 0;
j BINARY_INTEGER := 1;
------define it in a package pkg--------
type ct is table of number(10) index by binary_integer;
-----------------
a pkg.ct;
b pkg.ct;
BEGIN
for myrec in (select no from table_name)
LOOP
i := i + 1;
b[[i]] := myrec.customer_name;
if (i>1) then
begin
if (b[[i]]<> b[[i-1]] ) then
begin
a[[j]] = b[[i]];
j :=j+1;--u can do some more manipulation
dbms_output.put_line(b[[j]]);
end;
end if;
end;
end if;
END LOOP;
RETURN 0;
END f1;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
