Good morning, I will start out saying that I have already done 97% of this as yes, it is homework. The ONLY part I am confused with is not understanding the Error that is given. I had to create a loop that would take 2 numbers given and then output which numbers both are commonly divisible by. Here is that code:
SET SERVEROUTPUT ON SIZE UNLIMITED
--DROP TABLE IF IT EXISTS
DROP TABLE TESTER1 cascade constraints;
--CREATE TESTER1 TABLE
CREATE TABLE TESTER1 (xnum number, num1 number, num2 number);
--DECLARE VARIABLES
DECLARE
Test_Number1 number := 10;
Test_Number2 number := 20;
x number := 1;
--BEGIN
BEGIN --OUTSIDE LOOP
LOOP
BEGIN --INSIDE LOOP
INSERT INTO TESTER1(xnum,num1,num2)
VALUES(x,MOD(Test_Number1,x),MOD(Test_Number2,x));
x := x + 1;
EXIT WHEN NOT x < 20;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('error');
END;
END LOOP; --INSIDE LOOP
END; --OUTSIDE LOOP
/
I then wrote a SELECT statement to find where both have a common divisor:
SELECT xnum FROM tester1
where num1=num2;
My Question is: How do I loop through the results from the table Tester1 and use dbms_output.put_line() so each result from the SELECT statement is inserted until it loops through 3 rows? I hope I explained it well enough. I am using Oracle 12c.
Ok, so the answer I have found and just need to try and incorporate into my previous statement is:
BEGIN
FOR r_xnum IN (SELECT xnum FROM tester1
where num1=num2)
LOOP
dbms_output.put_line('Common factor: ' ||r_xnum.xnum);
END LOOP;
END;
/
Thanks again for all that pointed me in the right direction. It has been awhile since I posted here last so not sure how to close out the question without completely deleting it, so I will keep it how it is unless someone else knows how. EDIT I have to wait 2 days until I can accept my own answer
Final run:
SET SERVEROUTPUT ON SIZE UNLIMITED
--DROP TABLE IF IT EXISTS
DROP TABLE TESTER1 cascade constraints;
--CREATE TESTER1 TABLE
CREATE TABLE TESTER1 (xnum number, num1 number, num2 number);
--DECLARE VARIABLES
DECLARE
Test_Number1 number := '&input1';
Test_Number2 number := '&input2';
x number := 1;
--BEGIN
BEGIN --LOOP 1
LOOP --LOOP FOR INSERT
BEGIN --INSIDE LOOP
INSERT INTO TESTER1(xnum,num1,num2)
VALUES(x,MOD(Test_Number1,x),MOD(Test_Number2,x));
x := x + 1;
EXIT WHEN NOT x < 20;
END;
END LOOP; --LOOP 1
BEGIN --LOOP FOR SELECT
dbms_output.put_line('The two numbers enetered are ' ||Test_Number1|| 'and '||Test_Number2);
FOR r_xnum IN (SELECT xnum FROM tester1
where num1=num2)
LOOP
dbms_output.put_line('Common factor: ' ||r_xnum.xnum);
END LOOP;
END;
END;
/