Creating a PL/SQL code with cursor

Question

I need to output the total sales of each employee, but recieving the error

ORA-06550: line 7, column 1:
PLS-00103: Encountered the symbol "CURSOR" when expecting one of the following:

:= ( ; not null range default character
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Here is my code, I believe it has to do with my grouping function, but I clearly have the coulmns identifed to thier respected tables. Any help will be appreciated

SET SERVEROUTPUT ON
Declare 

v_employee_fname employees2.first_name%TYPE;
v_employee_lname employees2.last_name%TYPE;
v_amount all_sales.amount%TYPE

CURSOR v_fullcount_cursor IS 
SELECT e2.first_name, e2.last_name, sum(alls.amount) as total_sales
FROM employees2 e2 join all_sales alls on e2.employee_id = alls.EMP_ID
GROUP BY e2.first_name, sum(alls.amount);
BEGIN 
OPEN v_fullcount_cursor;
LOOP
FETCH v_fullcount_cursor 
INTO v_employee_fname, v_employee_lname, v_amount;
EXIT WHEN v_fullcount_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_employee_fname ||' '|| v_employee_lname ||' total sales are $'|| 
v_amount);
END LOOP;
CLOSE v_fullcount_cursor;
END;
/

Implicit cursor for loops are faster, simpler, and less error prone. You should almost never use the keyword cursor anymore. — Jon Heller
– Jon Heller, Commented Nov 27, 2013 at 4:27

Dba · Accepted Answer · 2013-11-27 03:45:40Z

You have missed a semicolon in line6.

v_amount all_sales.amount%TYPE;

One more issue is with group by clause. You need to group the data with employee firstname and lastname. Try like this,

SET SERVEROUTPUT ON
DECLARE 
     v_employee_fname employees2.first_name%TYPE;
     v_employee_lname employees2.last_name%TYPE;
     v_amount all_sales.amount%TYPE;

     CURSOR v_fullcount_cursor IS 
     SELECT e2.first_name, e2.last_name, sum(alls.amount) AS total_sales
     FROM   employees2 e2 JOIN all_sales alls ON e2.employee_id = alls.EMP_ID
     GROUP BY e2.first_name, e2.last_name;

     BEGIN 
          OPEN v_fullcount_cursor;
          LOOP
               FETCH v_fullcount_cursor 
               INTO v_employee_fname, v_employee_lname, v_amount;
               EXIT WHEN v_fullcount_cursor%NOTFOUND;
               DBMS_OUTPUT.PUT_LINE(v_employee_fname ||' '|| v_employee_lname ||' total sales are $'|| v_amount);
          END LOOP;
          CLOSE v_fullcount_cursor;
     END;
/

Collectives™ on Stack Overflow

Creating a PL/SQL code with cursor

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related