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;
您好，我正在嘗試使用 people 表的最后一個字母來決定誰獲得 S 級，但它不起作用。我不斷收到 - 運算式型別錯誤 - 錯誤。請幫忙。
uj5u.com熱心網友回復：
資料模型看起來不對。那應該只有一張有兩列的表格。
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>
然后，您不需要 PL/SQL - 一個簡單的 UPDATE 就可以了。但是，您發布的代碼也沒有多大意義 -substr(name, -1)選擇最后一個字母；沒有人的名稱以Sso結尾- 不會更新任何行（至少，對于示例資料不會）。這就是為什么我修改它以使用第一個字母。
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>
如果它必須是 PL/SQL（因為你正在學習它），那么你會
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>
uj5u.com熱心網友回復：
insert into people values('Sam', 'Bob', 'Tim');
會失敗，因為您只有一列而不是三列。您想使用多個插入：
insert into people (name) values('Sam');
insert into people (name) values('Bob');
insert into people (name) values('Tim');
或者，使用 INSERT ... SELECT ...
insert into people (name)
SELECT 'Sam' FROM DUAL UNION ALL
SELECT 'Bob' FROM DUAL UNION ALL
SELECT 'Tim' FROM DUAL;
然后你想要這樣的東西：
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;
/
但這可以簡化為擺脫游標并使用單個UPDATE陳述句：
UPDATE ranks
SET rank = 'S'
WHERE EXISTS(
SELECT 1
FROM people
WHERE name LIKE '%S'
);
但這些都不會做任何事情：
- 該
ranks表包含零行。 - 沒有人的名字以
S.
如果您修復了這兩個，那么您最終只會更新ranks表中的每一行，因為 aperson和 a之間沒有關系rank。
db<>在這里擺弄
uj5u.com熱心網友回復：
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;
/
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/385715.html