Why can't I modify the value in this PL/SQL code?

25 views Asked by At

Here's the code:

-- Creating Tables
CREATE TABLE sports_teams (
team_id NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL
);

CREATE TABLE players (
id NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
jersey_number NUMBER,
team_id NUMBER,
CONSTRAINT fk_team FOREIGN KEY (team_id) REFERENCES sports_teams(team_id)
);

\-- Inserting Data
INSERT INTO sports_teams VALUES (1, 'Barcelona');

INSERT INTO players VALUES (1, 'Messi', 10, 1);
INSERT INTO players VALUES (2, 'Iniesta', 8, 1);

\-- Inserting a player without a team
INSERT INTO players VALUES (3, 'Lajos', 99, NULL);

SELECT \* FROM players;

DELETE FROM players
WHERE team_id IS NULL;

SELECT \* FROM players;

\-- Inserting Data --function
INSERT INTO players VALUES (3, 'Lajos', 29, NULL);

CREATE OR REPLACE FUNCTION increase_even_jersey_numbers
RETURN NUMBER
AS
BEGIN
UPDATE players
SET jersey_number = jersey_number \* 1.5
WHERE MOD(jersey_number, 2) = 0;

    COMMIT;
    
    RETURN 1;

END increase_even_jersey_numbers;
/

\-- Displaying Results
DECLARE
v_result NUMBER;
BEGIN
v_result := increase_even_jersey_numbers;
DBMS_OUTPUT.PUT_LINE('Function result: ' || v_result);
END;
/

SELECT \* FROM players;

\-- Accepting User Input
ACCEPT p_percentage_reduction CHAR PROMPT 'Enter the reduction percentage: ';

DECLARE
p_percentage_reduction NUMBER;
v_modified_rows NUMBER;
BEGIN
\-- Reading percentage from the user
p_percentage_reduction := TO_NUMBER('&p_percentage_reduction');

    -- Executing the UPDATE statement
    UPDATE players
    SET jersey_number = jersey_number * (1 - p_percentage_reduction / 100)
    WHERE jersey_number > 50;
    
    -- Committing the changes
    COMMIT;
    
    -- Retrieving the number of modified rows
    SELECT COUNT(*) INTO v_modified_rows
    FROM players
    WHERE jersey_number > 25;
    
    -- Displaying the number of modified rows
    DBMS_OUTPUT.PUT_LINE('Number of modified rows: ' || v_modified_rows);

END;
/

SELECT \* FROM players;

The last procedure (important thing is that it should be a procedure not a function) cannot modify the expected value even though I've inserted multiple datas which were above 25.

I've tried chatgpt, but it didn't give me a good answer, I've also searched many questions here, and modify my code as well but even it was sintactically the same it didn't work at all.

0

There are 0 answers