enter image description hereuse PL/SQL with SQL commands in manipulating the database (PL/SQL structure, Data types, Variable, DBMS Output and Conditions) and this is my codes and what i have understandenter image description here
CREATE TABLE Grades(
STUDENT_ID INT NOT NULL PRIMARY KEY,
SName VARCHAR2(50),
Grades NUMBER,
Top_Average NUMBER(3)
);
INSERT INTO Grades(STUDENT_ID, SName, Grades, Top_Average)VALUES(1001,'Carmina',75);
INSERT INTO Grades(STUDENT_ID, SName, Grades, Top_Average)VALUES(1002,'Danielle',90,100);
INSERT INTO Grades(STUDENT_ID, SName, Grades, Top_Average)VALUES(1003,'Sophia',80,100);
INSERT INTO Grades(STUDENT_ID, SName, Grades, Top_Average)VALUES(1004,'Zoe',85,100);
INSERT INTO Grades(STUDENT_ID, SName, Grades, Top_Average)VALUES(1005,'Carlo',78,100);
SELECT * FROM Grades;
DECLARE
Grades NUMBER:= 75;
Top_Average NUMBER:= 100;
BEGIN
dbms_output.put_line('The sum of ' || Grades ||' and ' || Top_Average || ' is ' || (Grades + Top_Average));
dbms_output.put_line('The difference of ' || Grades ||' and ' || Top_Average || ' is ' || (Grades - Top_Average));
dbms_output.put_line('The product of ' || Grades ||' and ' || Top_Average || ' is ' || (Grades * Top_Average));
if Top_Average = grades then
dbms_output.put_line('The quotient of ' || Grades ||' and ' || Top_Average|| ' is ' || ' FAILED');
ELSE
dbms_output.put_line('The quotient of ' || Grades ||' and ' || Top_Average || ' is ' || (Grades / Top_Average));
end if;
end;
/
Advertisement
Answer
The sample code for me does not run. It gives
INSERT INTO Grades(STUDENT_ID, SName, Grades, Top_Average)VALUES(1001,'Carmina',75)
Error at Command Line : 8 Column : 59
Error report -
SQL Error: ORA-00947: not enough values
00947. 00000 - "not enough values"
The reason is that you have 4 columns in your statement but only 3 values in the “values” section for the first insert statement. Either remove the “top_average” column or add a value.
However, that issue has nothing to do with the error you are getting. The screenshot gives it away. You are selecting the following 3 lines
DECLARE
Grades NUMBER:= 75;
Top_Average NUMBER:= 100;
and then running that as a statement. If you select a number of lines in sqldeveloper it will try to run those selected lines only as a statement. It’s not a complete statement so it fails.
To solve it. Put your cursor in before or in the pl/sql block and run the statement. It runs without errors but it doesn’t display anything because the set serveroutput on
is missing before the block.
Here is the properly formatted code
- Properly ident pl/sql
- changed keywords to upper case, identifiers to lowercase (for readability)
- renamed variables in pl/sql – see comment.
DROP TABLE grades;
CREATE TABLE grades(
student_id INT NOT NULL PRIMARY KEY,
sname VARCHAR2(50),
grades NUMBER,
top_average NUMBER(3)
);
INSERT INTO grades(student_id, sname, grades) VALUES (1001,'Carmina',75);
INSERT INTO grades(student_id, sname, grades,top_average) VALUES (1002,'Danielle',90,100);
INSERT INTO grades(student_id, sname, grades,top_average) VALUES (1003,'Sophia',80,100);
INSERT INTO grades(student_id, sname, grades,top_average) VALUES (1004,'Zoe',85,100);
INSERT INTO grades(student_id, sname, grades,top_average) VALUES (1005,'Carlo',78,100);
SELECT * FROM grades;
set serveroutput on size 999999
--clear screen
DECLARE
-- do NOT name your variables the same as your column names. It's confusing. Instead prefix with "l_" (local variable)
l_grades NUMBER:= 75;
l_top_average NUMBER:= 100;
BEGIN
dbms_output.put_line('The sum of ' || l_grades ||' and ' || l_top_average || ' is ' || (l_grades + l_top_average));
dbms_output.put_line('The difference of ' || l_grades ||' and ' || l_top_average || ' is ' || (l_grades - l_top_average));
dbms_output.put_line('The product of ' || l_grades ||' and ' || l_top_average || ' is ' || (l_grades * l_top_average));
IF l_top_average = l_grades THEN
dbms_output.put_line('The quotient of ' || l_grades ||' and ' || l_top_average|| ' is ' || ' FAILED');
ELSE
dbms_output.put_line('The quotient of ' || l_grades ||' and ' || l_top_average || ' is ' || (l_grades / l_top_average));
END IF;
END;
/
Table GRADES created.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
STUDENT_ID SNAME GRADES TOP_AVERAGE
---------- -------------------------------------------------- ---------- -----------
1001 Carmina 75
1002 Danielle 90 100
1003 Sophia 80 100
1004 Zoe 85 100
1005 Carlo 78 100
The sum of 75 and 100 is 175
The difference of 75 and 100 is -25
The product of 75 and 100 is 7500
The quotient of 75 and 100 is .75
PL/SQL procedure successfully completed.