| pl/sql: please help! [message #369739] |
Fri, 29 September 2000 19:04  |
jack
Messages: 123
Registered: September 2000
|
Senior Member |
|
|
Hi,
I have a table that contains information about 100 customers. I needs to be divided these customers to 4 different ranks
according to access time. For example, highest 25 people go to the rank 1 and the next highest 25 people go to the rank 2.
How can I write a pl/sql to store this information in a ranking table below?
CREATE TABLE ranking(
ranking_no NUMBER(6),
user_name VARCHAR2(30),
access_times NUMBER(8),
:
: );
I'm doing the following pl/sql to accomplish this. But only goes to rank 4 and rank 3,2,1 doesn't populate it. Do I need to
declare 4 different group_size for each insert? My script inserts all to rank 4 and doesn't divided rows....
DECLARE
v_ranking_no ranking.ranking_no%TYPE;
v_group_size NUMBER;
v_group_no NUMBER;
v_count NUMBER;
CURSOR c_user IS
SELECT user_name, COUNT(*) times, ...
FROM customer
SORT BY times DESC
GROUP BY user_name;
BEGIN
v_group_no := 4;
v_count := 0;
FOR r_user IN c_user
LOOP
v_group_size := ROUND(c_user%ROWCOUNT / 4);
v_ranking_no := v_ranking_no + 1;
-- define the rank category
v_count := v_count + 1;
IF v_count > v_group_size
THEN
v_group_no := v_group_no - 1;
v_count := 0;
ENDIF;
v_rank_category := 'Q' + v_group_no;
INSERT INTO ranking (ranking_no, user_name, access_times, ...)
VALUES (v_ranking_no, r_user.user_name, r_user.times, ...);
END LOOP;
END;
|
|
|
|
| Re: pl/sql: please help! [message #369742 is a reply to message #369739] |
Mon, 02 October 2000 09:03  |
andreas
Messages: 4
Registered: October 2000
|
Junior Member |
|
|
Hi!
c_user%ROWCOUNT does not return the total number
of records that will be fetched by the cursor's
select-statement.
Instead, c_user%ROWCOUNT returns the number of
current record. It will return 1,2,3,4,5,6,...
and finally 100.
Solution:
Compute the total number of record before
using the cursor:
SELECT COUNT(DISTINCT(user_name))
INTO v_total_num_cust
FROM customer;
FOR....
I hope this helps.
Andreas
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
