CODEWITHSUNDEEP
sqloracle-databaseplsql
DarnellG1999
DarnellG1999

Reputation: 37

How do I loop through a row while using a cursor

create table ranks (
    rank varchar(20)
);

create table people (
    name varchar(20)
);

insert into people values('Sam', 'Bob', 'Tim');

declare cursor c1 is (select substr(name, -1) from people)
begin
for i in c1
loop
    update ranks
    set rank = 'S'
    where i = 'S';
end loop;
end;

Hello, I am trying to use the last letter of the people table to decide who gets the S rank, but it isn't working. I keep getting - expression is of wrong type - error. Please help.

Upvotes: 0

Views: 998

Answers (3)

Pradeep Hombali
Pradeep Hombali

Reputation: 11

A simplified version of using cursors is to declare the cursor and then use it 
multiple times if required later. This way opening similar dataset in multiple 
cursors can be avoided. A good practice to develop when working on code 
intensive procedures.

SQL> Declare
      cur_people is SELECT name FROM people;
     BEGIN
       FOR cur_r IN cur_people
       LOOP
          UPDATE people
             SET RANK = 'S'
           WHERE     name = cur_r.name
                 AND SUBSTR (name, 1, 1) = 'S';
       END LOOP;
     END;
    /

Upvotes: 0

MT0
MT0

Reputation: 168061

insert into people values('Sam', 'Bob', 'Tim');

Will fail as you only have one column and not three. You want to either use multiple inserts:

insert into people (name) values('Sam');
insert into people (name) values('Bob');
insert into people (name) values('Tim');

Or, use an INSERT ... SELECT ...

insert into people (name)
SELECT 'Sam' FROM DUAL UNION ALL
SELECT 'Bob' FROM DUAL UNION ALL
SELECT 'Tim' FROM DUAL;

Then you want something like:

begin
  for i in (select substr(name, -1) AS last_character from people)
  loop
    update ranks
    set rank = 'S'
    where i.last_character = 'S';
  end loop;
end;
/

But that can be simplified to get rid of the cursor and use a single UPDATE statement:

UPDATE ranks
SET    rank = 'S'
WHERE EXISTS(
  SELECT 1
  FROM   people
  WHERE  name LIKE '%S'
);

But neither of those will do anything as:

  1. The ranks table contains zero rows.
  2. None of the people have a name ending in S.

If you fix both of those then you will just end up updating every row in the ranks table as there is no relationship between a person and a rank.

db<>fiddle here

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142788

Data model looks wrong. That should be only one table with two columns.

SQL> CREATE TABLE people
  2  (
  3     name   VARCHAR2 (20),
  4     RANK   VARCHAR2 (1)
  5  );

Table created.

SQL> INSERT INTO people (name) VALUES ('Sam');

1 row created.

SQL> INSERT INTO people (name) VALUES ('Bob');

1 row created.

SQL> INSERT INTO people (name) VALUES ('Tim');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM people;

NAME                 RANK
-------------------- -----
Sam
Bob
Tim

SQL>

Then, you don't need PL/SQL - a simple UPDATE will do. However, code you posted doesn't make much sense either - substr(name, -1) selects the last letter; nobody has a name that ends with an S so - no rows will ever be updated (at least, not for sample data). That's why I modified it to use the 1st letter.

SQL> UPDATE people
  2     SET RANK = 'S'
  3   WHERE SUBSTR (name, 1, 1) = 'S';

1 row updated.

SQL> SELECT * FROM people;

NAME                 R
-------------------- -
Sam                  S
Bob
Tim

SQL>

If it has to be PL/SQL (because you're learning it), then you'd

SQL> ROLLBACK;

Rollback complete.

SQL> BEGIN
  2     FOR cur_r IN (SELECT name FROM people)
  3     LOOP
  4        UPDATE people
  5           SET RANK = 'S'
  6         WHERE     name = cur_r.name
  7               AND SUBSTR (name, 1, 1) = 'S';
  8     END LOOP;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM people;

NAME                 RANK
-------------------- ----
Sam                  S
Bob
Tim

SQL>

Upvotes: 2

Related Questions