問題描述
PL/SQL 塊和循環練習 (PL/SQL block and LOOP exercise)
I have to create a PL/SQL block to insert 10 to 100 multiples of 10 in a table called TEN_MULTIPLES that I have to create... (SCHEMA ‑> TEN_MULTIPLES(numbervalue)). I will have to insert inside the table only 10,20,30,...,100 but exluding 50 and 90. So far I have done this... is it correct?
DECLARE
CREATE TABLE ten_multiples
(numbervalue NUMBER (3));
BEGIN
FOR i IN 9..101 LOOP
IF (i = 50 OR i = 90) THEN
ELSIF (i%10 = 0) THEN
INSERT INTO ten_multiples
VALUE (i);
END IF;
END LOOP;
END;
When I use 10..100 are 10 and 100 included and evaluated as 'i' in the loop?
I need also to find the MAXIMUM number from that table using a cursor, so in this case 100, store it in a variable 'num' declared in the DECLARE part and print it out...
DECLAR
CURSOR my_cursor IS
SELECT MAX(v_number) FROM ten_multiples;
num NUMBER;
BEGIN
OPEN my_cursor;
FETCH my_cursor INTO (num);
DBMS_OUTPUT.PUT_LINE(‘Maximum number is ‘ | num);
CLOSE my_cursor;
END;
Is this right?
I really thank you in advance :)
參考解法
方法 1:
Why is so much PL/SQL coursework consists of exercises in how not to use PL/SQL?
insert into ten_multiples
with data as ( select level*10 as mult
from dual
connect by level <=10)
select * from data
where mult not in (50,90)
/
方法 2:
First part:
- You cannot execute the CREATE TABLE statement directly in a PL/SQL context. You must use the DBMS_DDL package or dynamic SQL via the EXECUTE IMMEDIATE command, if you must execute within PL/SQL context.
- Yes number literals in the for loop are included.
Second part:
- Use
DECLARE
notDECLAR
. - Your
SELECT
member must be a column of the table, notv_number
. - Your single quote character is incorrect, use ', not ‘.
- Use double pipe for concatenation, not single.
Finally:
- Actually run these commands through SQL*Plus and listen to the tool.
- Trying is your friend.
(by user2179694、APC、Michael O'Neill)