What is wrong with my PL / SQL procedure?

Question

I built an Oracle database (version 9.2, I know that it is outdated, but this is the version installed at my university and I have no choice) with a list of users and their closest friends. I am working on a set of queries to list the user’s “network”, including friends of his friends, and to calculate the degree of separation between the user and other members.

I tested the three queries separately and I confirm that they work.

Now I would like to create a PL / SQL procedure to combine the three queries in order to be able to provide the player identifier as a parameter, as well as to run three queries using the same Execute statement.

However, I cannot start the procedure. I can’t even convert the first request to a procedure (see my code below).

Can someone explain what I'm doing wrong?

Here are the queries (they work):

INSERT INTO UserFriendsCopy
SELECT *
FROM UserFriends
WHERE PlayerID < FriendsWith;

INSERT INTO Degrees_Separation
   SELECT PlayerID, FriendsWith, LEVEL AS Degree
      FROM UserFriendsCopy
      START WITH PlayerID = 1
      CONNECT BY PRIOR FriendsWith = PlayerID;

SELECT FriendsWith, MIN(Degree)
FROM Degrees_Separation
GROUP BY FriendsWith;

Here is the procedure I wrote to replace the first request. This does not work. Why?

CREATE OR REPLACE PROCEDURE procDegrees1
AS
DECLARE
    CURSOR UserFriendsNoDupl IS SELECT * FROM UserFriends WHERE PlayerID < FriendsWith;
BEGIN
    FOR record IN UserFriendsNoDupl
LOOP
    EXIT WHEN UserFriendsNoDupl%NOTFOUND;
    INSERT INTO UserFriendsCopy VALUES(record.PlayerID, record.FriendsWith);
END LOOP;
END;/

EXECUTE procDegrees1/

[UPDATE] I rewrote the procedure as follows to take into account the comments received. However, this will not work.

CREATE OR REPLACE PROCEDURE procDegrees1
AS
BEGIN
FOR record IN (SELECT * FROM UserFriends WHERE PlayerID < FriendsWith)
LOOP
    INSERT INTO UserFriendsCopy(PlayerID,FriendsWith) VALUES(record.PlayerID, record.FriendsWith);
END LOOP;
END;/

By the way, here are the table definitions, as well as some dummy entries:

DROP TABLE Degrees_Separation;
DROP TABLE UserFriends;
DROP TABLE UserFriendsCopy;

CREATE TABLE UserFriends (
    PlayerID INT NOT NULL,
    FriendsWith INT NOT NULL,
    CONSTRAINT pkUserFriends
        PRIMARY KEY (PlayerID, FriendsWith),
    CONSTRAINT fkPlayerIDThird
        FOREIGN KEY (PlayerID)
        REFERENCES Player (PlayerID),
    CONSTRAINT fkPlayerIDFourth
        FOREIGN KEY (FriendsWith)
        REFERENCES Player (PlayerID)
);

CREATE TABLE UserFriendsCopy (
    PlayerID INT NOT NULL,
    FriendsWith INT NOT NULL,
    CONSTRAINT pkUserFriendsBis
        PRIMARY KEY (PlayerID, FriendsWith)
);

INSERT INTO UserFriends (PlayerID, FriendsWith)
VALUES (1, 2);

INSERT INTO UserFriends (PlayerID, FriendsWith)
VALUES (2, 1);

INSERT INTO UserFriends (PlayerID, FriendsWith)
VALUES (2, 3);

INSERT INTO UserFriends (PlayerID, FriendsWith)
VALUES (3, 2);

INSERT INTO UserFriends (PlayerID, FriendsWith)
VALUES (2, 4);

INSERT INTO UserFriends (PlayerID, FriendsWith)
VALUES (4, 2);

INSERT INTO UserFriends (PlayerID, FriendsWith)
VALUES (1, 4);

INSERT INTO UserFriends (PlayerID, FriendsWith)
VALUES (4, 1);

INSERT INTO UserFriends (PlayerID, FriendsWith)
VALUES (5, 6);

INSERT INTO UserFriends (PlayerID, FriendsWith)
VALUES (6, 5);

INSERT INTO UserFriends (PlayerID, FriendsWith)
VALUES (3, 8);

INSERT INTO UserFriends (PlayerID, FriendsWith)
VALUES (8, 3);


CREATE TABLE Degrees_Separation (
    PlayerID INT NOT NULL,
    FriendsWith INT NOT NULL,
    Degree INT NOT NULL,
    CONSTRAINT fkPlayerIDSeventh
        FOREIGN KEY (PlayerID)
        REFERENCES Player (PlayerID),
    CONSTRAINT fkPlayerIDEighth
        FOREIGN KEY (FriendsWith)
        REFERENCES Player (PlayerID)
);

Thanks LC

Answer 1

SQL * Plus link says :

SQL * Plus PL/SQL , SQL, , (;) . PL/SQL, (.) . PL/SQL, (/) .

, end;, . :

CREATE OR REPLACE PROCEDURE procDegrees1
AS
BEGIN
  FOR record IN (SELECT * FROM UserFriends WHERE PlayerID < FriendsWith)
  LOOP
    INSERT INTO UserFriendsCopy(PlayerID,FriendsWith)
    VALUES(record.PlayerID, record.FriendsWith);
  END LOOP;
END;
/

execute, , :

EXECUTE procDegrees1;

SQL * Plus , , execute, , :

BEGIN
  procDegrees1;
END;
/

, . , execute SQL, . SQL * Plus SQL Developer , .

, , Squirrel. , Squirrel execute, , , . , , .

---

Google , execute , :

CALL procDegrees1

... , ...