CODEWITHSUNDEEP
oracle-database
muk_arp
muk_arp

Reputation: 1

How can I call cursor from a stored procedure in oracle

I have a problem in which I have to calculate sum of a col in table without using "sum" function, I have designed a cursor for this task but don't know how to use it in stored procedure so that it gets saved in memory :

Below is my cursor, how can I use it in stored procedure in oracle?

DECLARE CURSOR TEMP_CURSOR IS SELECT MARKS FROM TEMP;
SUMA NUMBER:=0;
TMP TEMP.MARKS%TYPE;
BEGIN
OPEN TEMP_CURSOR();
LOOP
FETCH TEMP_CURSOR INTO TMP;
EXIT WHEN TEMP_CURSOR%NOTFOUND;
SUMA:=SUMA + TMP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF NUMBER IS ' || SUMA);
CLOSE TEMP_CURSOR;
END;

Upvotes: 0

Views: 112

Answers (1)

Ravi
Ravi

Reputation: 31407

 CREATE OR REPLACE PROCEDURE PROCEDURE_NAME IS/AS
    //DECLARE YOUR VARIABLE/CURSORE
    BEGIN
    //WRITE YOUR CURSOR CODE
    END;

Example:

CREATE OR REPLACE PROCEDURE PRO_NAME IS
   CURSOR TEMP_CURSOR IS SELECT MARKS FROM TEMP;
   SUMA NUMBER:=0;
   TMP TEMP.MARKS%TYPE;
BEGIN
  OPEN TEMP_CURSOR();
  LOOP
    FETCH TEMP_CURSOR INTO TMP;
    EXIT WHEN TEMP_CURSOR%NOTFOUND;
    SUMA:=SUMA + TMP;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('SUM OF NUMBER IS ' || SUMA);
 CLOSE TEMP_CURSOR;
END;

Upvotes: 3

Related Questions