| update doesn't work [message #10801] |
Tue, 17 February 2004 22:32  |
Riaz
Messages: 112
Registered: February 2002
|
Senior Member |
|
|
Hi to all..
I have the following procedure to calculate rank and then I want to update another table (it2). But it does not work. Any Idea plase?
1 create or replace procedure rank1
2 AS
3 CURSOR c1 IS
4 SELECT it.regno,
5 grn.faculty,
6 ROUND (it.sgpa, 2) AS sgpa
7 FROM it, grn
8 WHERE it.regno = grn.rn
9 AND it.sc = 3
10 AND it.sy = 2003
11 ORDER BY SUBSTR (it.regno, 1, 4), grn.faculty, sgpa DESC;
12 a NUMBER := 0;
13 x c1%ROWTYPE;
14 v_sgpa NUMBER := 0;
15 v_regno VARCHAR2(4);
16 v_faculty grn.faculty%TYPE;
17 BEGIN
18 FOR x IN c1 LOOP
19 IF SUBSTR (x.regno, 1, 4) <> v_regno OR x.faculty <> v_faculty THEN
20 a := 1;
21 ELSE
22 IF x.sgpa <> v_sgpa THEN
23 a := a + 1;
24 END IF;
25 END IF;
26 -- DBMS_OUTPUT.PUT_LINE (x.regno || ' ' || x.faculty || ' ' || x.sgpa ||' '||a);
27 update it2 set rank=a
28 where it2.regno=x.regno
29 and it2.sc=3
30 and it2.sc=2003;
31 v_sgpa := x.sgpa;
32 v_regno := SUBSTR (x.regno, 1, 4);
33 v_faculty := x.faculty;
34 end loop;
35* end rank1;
SQL> /
Procedure created.
SQL> exec rank1;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select regno,sgpa,rank from it2
2 where sc=3 and sy=2003;
REGNO SGPA RANK
---------- ---------- ----------
2000189 1.40428571
2000191 1.165625
2000192 2.932
2000193 2.353125
2000194 2.86833333
2000195 .393636364
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
