17,086
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE PROCEDURE delete_folder(folder_name_to_delete IN VARCHAR(50))
IS
DECLARE done BOOLEAN DEFAULT 0;
next_delete VARCHAR(50);
CURSOR temp_list_cursor IS
SELECT folder_name
FROM
temp_middle_list;
BEGIN
CREATE TEMPORARY TABLE temp_middle_list(folder_name VARCHAR(50));
EXECUTE findChildList(folder_name_to_delete);
OPEN temp_list_cursor;
LOOP
FETCH temp_list_cursor INTO next_delete;
DELETE FROM folder_delete WHERE folder_delete.file_name = temp_middle_list.folder_name;
EXIT WHEN temp_list_cursor%notfound;
END LOOP;
close temp_list_cursor;
END delete_folder;
CREATE OR REPLACE PROCEDURE findChildList(folder_name_to_delete IN VARCHAR(50))
IS
DECLARE out INT DEFAULT 0;
iterator_element VARCHAR(50);
CURSOR list_cursor IS
SELECT file_name
FROM
folder_delete
WHERE
parent_name = folder_name_to_delete;
BEGIN
INSERT INTO temp_middle_list VALUES(folder_name_to_delete);
OPEN list_cursor;
LOOP
FETCH list_cursor INTO iterator_element;
EXECUTE findChildList(iterator_element);
EXIT WHEN list_curso%notfound;
END LOOP;
CLOSE list_cursor;
END findChildList;
CREATE OR REPLACE PROCEDURE findChildList(folder_name_to_delete IN VARCHAR) IS
iterator_element VARCHAR(50);
CURSOR list_cursor is
SELECT file_name
FROM folder_delete
WHERE parent_name = folder_name_to_delete;
BEGIN
dbms_output.put_line('准备添加到临时表中:'||folder_name_to_delete);
INSERT INTO temp_middle_list VALUES (folder_name_to_delete);
dbms_output.put_line('添加完成:'||folder_name_to_delete);
OPEN list_cursor;
WHILE list_cursor%found LOOP--我这里用的是while 游标存在
FETCH list_cursor
INTO iterator_element;
findChildList(iterator_element);
END LOOP;--这里结束循环,应该也是OK的
CLOSE list_cursor;
END findChildList;
/
CREATE OR REPLACE PROCEDURE findChildList(folder_name_to_delete IN VARCHAR) IS
iterator_element VARCHAR(50);
CURSOR list_cursor is
SELECT file_name
FROM folder_delete
WHERE parent_name = folder_name_to_delete;
BEGIN
INSERT INTO temp_middle_list VALUES (folder_name_to_delete);
OPEN list_cursor;
LOOP
FETCH list_cursor INTO iterator_element;
EXIT WHEN list_cursor%notfound; -- 这两行交换了一下
findChildList(iterator_element); -- 这两行交换了一下
END LOOP;
CLOSE list_cursor;
END findChildList;
/
CREATE TABLE IF NOT EXISTS folder_delete
(
file_id int NOT NULL PRIMARY KEY,
file_name varchar(50) NOT NULL,
file_type int NOT NULL,
parent_name varchar(50)
);
INSERT ALL INTO folder_delete
VALUES
('1','A','1','C')
INTO folder_delete
VALUES
('2','B','1','NULL')
INTO folder_delete
VALUES
('3','C','1','B')
INTO folder_delete
VALUES
('4','D','0','A')
INTO folder_delete
VALUES
('5','E','0','A')
select 1 from dual;
下面是临时表
CREATE GLOBAL TEMPORARY TABLE temp_middle_list (folder_name VARCHAR(50)) ON PRESERVE DELETE ROWS;
CREATE GLOBAL TEMPORARY TABLE temp_middle_list (folder_name VARCHAR(50)) ON COMMIT DELETE ROWS;
CREATE OR REPLACE PROCEDURE delete_folder(folder_name_to_delete IN VARCHAR(50))
IS
DECLARE done BOOLEAN DEFAULT 0;
next_delete VARCHAR(50);
BEGIN
EXECUTE findChildList(folder_name_to_delete);
DELETE FROM folder_delete WHERE folder_delete.file_name = temp_middle_list.folder_name;
END delete_folder;
CREATE OR REPLACE PROCEDURE findChildList(folder_name_to_delete IN VARCHAR(50))
IS
DECLARE out INT DEFAULT 0;
iterator_element VARCHAR(50);
CURSOR list_cursor IS
SELECT file_name
FROM
folder_delete
WHERE
parent_name = folder_name_to_delete;
BEGIN
INSERT INTO temp_middle_list VALUES(folder_name_to_delete);
OPEN list_cursor;
LOOP
FETCH list_cursor INTO iterator_element;
EXECUTE findChildList(iterator_element);
EXIT WHEN list_curso%notfound;
END LOOP;
CLOSE list_cursor;
END findChildList;