假設我有一個表孩子，我想在一個程序中從 1 到 5 中隨機選擇表中的隨機值并列印它們。我該怎么做？
create table child(name varchar2(20), age number);
insert into child(name, age) values('A',5);
insert into child(name, age) values('B',12);
insert into child(name, age) values('C',7);
insert into child(name, age) values('D',4);
create or replace procedure random_child
as
l_name child.name%type;
l_age child.age%type;
begin
for i in(select dbms_random.value(1,5)
from (select name from child sample(50)))
loop
DBMS_OUTPUT.put_line(i.name);
end loop;
end;
它給了我一個PLS-00302：必須宣告名字
uj5u.com熱心網友回復：
您可以使用：
CREATE PROCEDURE random_child
AS
BEGIN
FOR i in(
SELECT name,
FLOOR(DBMS_RANDOM.VALUE(1,6)) AS value
FROM child
SAMPLE(50)
)
LOOP
DBMS_OUTPUT.put_line(i.name || ' ' || i.value);
END LOOP;
END;
/
然后：
BEGIN
random_child();
END;
/
可能隨機輸出：
B 3 C 2 D 2
db<>在這里擺弄
uj5u.com熱心網友回復：
那里沒有i.name；第 6 行末尾缺少別名：
SQL> create or replace procedure random_child
2 as
3 l_name child.name%type;
4 l_age child.age%type;
5 begin
6 for i in(select dbms_random.value(1,5) as name --> here
7 from (select name from child sample(50)))
8 loop
9 DBMS_OUTPUT.put_line(i.name);
10 end loop;
11 end;
12 /
Procedure created.
SQL> exec random_child
1,30966411991963041689918865935551009464
1,13993832387089615287177388489291237644
3,85292920191145794430114472793297022632
PL/SQL procedure successfully completed.
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/367862.html