How do I display the result of the procedure Oracle?

Question

In my code there is a function that displays a message on the screen. But when I start, I do not see the result.

I guess this line does not work.

UPDATE EMPLOYEES SET COMMISSION_PCT = NEWCOMMISSION WHERE LINES.COMMISSION_PCT IS NULL;

Is it so?

I'm using Oracle XE 112, I'm using a browser.

Full code

CREATE OR replace PROCEDURE Zadacha31
IS
  lengthphone      INTEGER;
  substrcommission VARCHAR2(50);
  newcommission    FLOAT;
  info             FLOAT;
  CURSOR get_data IS
    SELECT *
    FROM   employees;
BEGIN
    FOR lines IN get_data LOOP
        IF lines.commission_pct IS NULL THEN
          lengthphone := Length(lines.phone_number);

          substrcommission := Substr(lines.phone_number, lengthphone - 1, 2);

          newcommission := To_number('.'
                                     ||substrcommission);

          UPDATE employees
          SET    commission_pct = newcommission
          WHERE  lines.commission_pct IS NULL;

          dbms_output.Put_line(lines.commission_pct);
        END IF;
    END LOOP;
END;  

Answer 1

It seems that you have an error in your logic.

Consider. You execute the UPDATE only if this test is true:

IF lines.commission_pct IS NULL THEN

Your code calculates a new commission and updates all the records (hint: this is probably not what you want but is irrelevant to your question).

UPDATE employees
SET    commission_pct = newcommission
WHERE  lines.commission_pct IS NULL;

However, your output is displaying the unamended original value and nothing else.

dbms_output.Put_line(lines.commission_pct);

So your procuedure may well be updating and displaying something, but all it's displaying is a NULL so it looks as though nothing is occuring.

To solve this, use the new value:

dbms_output.Put_line('new commission = ' ||newcommission);

To solve the updating all records use a better where condition:

UPDATE employees e
SET    e.commission_pct = newcommission
WHERE  e.emp_id = lines.emp_id; -- or whatever the PK is

And why not make the output message more useful while we're at it?

dbms_output.Put_line('emp# '||  lines.emp_id||' new commission = ' ||newcommission);