Question

Having a hard time figuring out a bug in some SQL code. The point is to add a column called "stars" to table "emp". The value of stars should be a line of asterisks for each $1000 in the employee's salary. Any ideas what I'm doing wrong?

Answer 1

DROP TABLE emp;
CREATE TABLE emp AS SELECT * FROM employees;
ALTER TABLE emp ADD stars VARCHAR2(50);

set serveroutput on

DECLARE
v_empno emp.employee_id%TYPE := 176;
v_asterisk emp.stars%TYPE := NULL;
v_sal emp.salary%TYPE;
BEGIN
SELECT salary INTO v_sal FROM emp
WHERE employee_id = v_empno;
LOOP
INSERT INTO emp(stars)
VALUES (v_asterisk);
v_sal := v_sal - 1000;
v_asterisk := v_asterisk || '*';
EXIT WHEN v_sal <= 0;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_sal || ' ' || v_asterisk);
END;
/