CODEWITHSUNDEEP
sqloracle-databaseoracle11g
James Henrick
James Henrick

Reputation: 71

Creating a PL/SQL code with cursor

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;
/

Upvotes: 0

Views: 615

Answers (1)

Dba
Dba

Reputation: 6649

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;
/

Upvotes: 2

Related Questions