这是我的oracle存储过程。当我把数据传递给过程时,我想删除某些id,但当我执行这个过程时。我删除了我的trable的所有内容。
有人能查一下吗
CREATE OR REPLACE PROCEDURE Delete(
id IN number)
AS
BEGIN
DELETE FROM Users WHERE Users.id_user= id;
END;
/
我试着放:
DELETE FROM Users WHERE Users.id_user == id;
然后我得到编译错误。
您尝试过DELETE FROM Users WHERE id_user = id;
吗?
使用==
不是用于相等性检查的有效SQL。
我仍然不明白你为什么要删除所有的行。DELETE FROM Users WHERE Users.id_user = id
应正常工作。
有关DELETE STATION语法的详细信息。
我也有同样的问题。当我传递一个表中不存在的Id时,所有内容都会被删除。
如果表中存在id,那么该过程将按我希望的方式运行。
PROCEDURE tests
( jobnr IN varchar2,
x IN number,
menu in varchar2 default '1')
IS
counter number := 0;
BEGIN
delete from table_test where jobnr = jobnr;
if sql%notfound or sql%rowcount=0 then
rollback;
end if;
WHILE counter < x
LOOP
insert into table_test ( cskey, telnr, seq, jobnr, menu ) values ('Test'||counter, counter, counter, jobnr, menu );
counter := counter + 1;
END LOOP;
commit;
END;
试试这个:将输入存储到本地变量中,然后在查询中使用这个变量。这对我有效。
CREATE OR REPLACE PROCEDURE Delete(
id IN number)
AS
v_id Number;
BEGIN
v_id := id;
DELETE FROM Users WHERE Users.id_user= v_id;
END;
CREATE TABLE USERS(ID NUMBER);
TABLE CREATED.
CREATE OR REPLACE PROCEDURE DeleteIDS(
id IN number)
AS
BEGIN
DELETE FROM Users WHERE id= id;
END;
/
PROCEDURE CREATED.
这里,在表中users id就在那里,如果您在过程声明中指定id,那么该过程将删除所有数据。所以,
CREATE OR REPLACE PROCEDURE DeleteIDS(
idS IN number)
AS
BEGIN
DELETE FROM Users WHERE id= idS;
END;
/
PROCEDURE CREATED.
现在,
INSERT INTO USERS VALUES(10);
1 ROW INSERTED.
INSERT INTO USER VALUES(20);
1 ROW INSERTED.
COMMIT;
现在,执行程序
SQL>EXEC DELETEIDS(10);
PL/SQL PROGRAM SUCCESSFULLY COMPLETED.
SELECT * FROM USERS;
ID
---
2