删除语句不删除记录 [英] delete statement not deleting records
问题描述
任何人都知道为什么不从Student表中删除记录.当表为空并且我先运行匿名块时,它运行良好,但是随后当我再次运行它时,我会得到有关重复主键的错误,但这无关紧要,因为每次我运行该块时,它都应该从中删除所有记录桌子??我是SQL的新手,所以可以提供任何帮助.
Anyone know why its not deleting the records from the student table. When the table is empty and i first run the anonymous block it runs fine, but then when i run it again i get errors about duplicate primary keys, but this shouldn't matter as each time i run the block it should delete all records from the table?? I'm relatively new to SQL so any help is appreciated.
我应该补充一点,因为运行代码时出现dopl,所以该过程似乎工作正常.
I should add, the procedure seems to work fine as the dopl appears when i run the code.
这是我的功能:
CREATE OR REPLACE FUNCTION DELETE_ALL_STUDENTS RETURN NUMBER AS
BEGIN
DELETE FROM STUDENTS;
END;
这是我的程序:
create or replace PROCEDURE DELETE_ALL_STUDENTS_VIASQLDEV AS
BEGIN
dbms_output.put_line('--------------------------------------------');
dbms_output.put_line('Deleting all student rows');
END;
这是我正在运行的匿名块,以查看是否起作用:
and this is the anonymous block i was running to see if it worked:
begin
DELETE_ALL_STUDENTS_VIASQLDEV;
ADD_STUDENT_VIASQLDEV(1,'Fred Smith');
ADD_STUDENT_VIASQLDEV(2,'Sue Davis');
ADD_STUDENT_VIASQLDEV(3,'Emma Jones');
end;
推荐答案
我的功能不执行删除吗?"
"doesnt my function carry out the deleting?"
如果您调用它,它会,但是a,您不会.
It would if you called it, but alas you don't.
这不是SQL问题,而是逻辑问题.如果我们不洗碗,那碗就很脏.同样,如果您不调用删除记录的例程,则不会删除记录.
This is not a SQL problem, it's a logic problem. If we don't do the washing up the dishes remain dirty. Similarly if you don't call the routine which deletes the records the records are not deleted.
您需要在过程中调用该函数.不知道为什么要使它成为函数,并且无论如何它都不会编译,因为它没有RETURN子句.所以,我们也修复它.
You need to call the function in the procedure. Not sure why you've made it a function, and it won't compile anyway, because it doesn't have a RETURN clause. So, let's fix that too.
CREATE OR REPLACE FUNCTION DELETE_ALL_STUDENTS RETURN NUMBER AS
BEGIN
DELETE FROM STUDENTS;
return sql%rowcount; -- how many rows were deleted
END;
/
现在我们称之为:
create or replace PROCEDURE DELETE_ALL_STUDENTS_VIASQLDEV AS
n number;
BEGIN
dbms_output.put_line('--------------------------------------------');
dbms_output.put_line('Deleting all student rows');
n := DELETE_ALL_STUDENTS;
dbms_output.put_line('No of students deleted = '|| to_char(n));
END;
因此,当您运行匿名块时,现有学生将被删除,并用新学生替换.
So, when you run your anonymous block the existing students will be deleted and replaced with the new ones.
这篇关于删除语句不删除记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!