Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2947
  • Last Modified:

Cursor in Oracle Procedure

I'm trying to create a procedure that retrieves from a table, the register with the highest value (without using Max() function :) )for certain field, but i keep getting an error:

CREATE OR REPLACE PROCEDURE OBTENERDNIMASALTO AS
cursor c1 is
SELECT * from clientes
curregister CLIENTES%rowtype;
maxdni dni%type;
curdni dni%type;
BEGIN
open c1
loop
fetch c1 into curregister;
curdni:=curregister.dni;
if(curdni > maxdni) THEN
  maxdni:= curdni;
end if;
end loop;
if curregister%ISOPEN then
close curregister;
end if;
  DBMS_OUTPUT.PUT_LINE(maxdni);
END OBTENERDNIMASALTO;



CLIENTES is the table name, dni is the field where i woud like to extract the hightest value.
0
axtur
Asked:
axtur
1 Solution
 
lwadwellCommented:
Hi axtur,

Why don't you want to use a MAX()? ... and what error are you getting?

Also, please try the below...

lwadwell
CREATE OR REPLACE PROCEDURE OBTENERDNIMASALTO AS
  cursor c1 is 
    SELECT * from clientes;
  maxdni dni%type  := 0;
  curdni dni%type;
BEGIN
  FOR curregister IN c1
  LOOP
    curdni := curregister.dni;
    IF (curdni > maxdni) THEN
      maxdni := curdni;
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(maxdni);
END OBTENERDNIMASALTO;

Open in new window

0
 
jwahlCommented:
there are two ";" missing:


CREATE OR REPLACE PROCEDURE obtenerdnimasalto AS
    CURSOR c1 IS
        SELECT *
        FROM   clientes;  -- <-- missing ;
    curregister clientes%ROWTYPE;
    maxdni dni%TYPE;
    curdni dni%TYPE;
BEGIN
    OPEN c1;-- <-- missing ;
    LOOP
        FETCH c1
        INTO  curregister;
 
        curdni := curregister.dni;
 
        IF (curdni > maxdni) THEN
            maxdni := curdni;
        END IF;
    END LOOP;
 
    IF curregister%ISOPEN THEN
        CLOSE curregister;
    END IF;
 
    DBMS_OUTPUT.put_line (maxdni);
END obtenerdnimasalto;
/

Open in new window

0
 
axturAuthor Commented:
I dont want to use max, to train myself in pl/sql :) I'm checking out now the code :)
0
 
sventhanCommented:
where do you use MAX()? I do not see it in the code snippet.
You could try row_number or rank analytical funtions if you do not want to use max.
 
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php 
0
 
axturAuthor Commented:
Ok, now it looks to compile, I'm trying to execute it, I'll open another question for that :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now