oracle PL SQL 循环遍历 id 列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25134986/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL SQL loop through list of ids
提问by TV Nath
I have a list of names.
我有一个名单。
john, sam, peter, Hyman
约翰、山姆、彼得、Hyman
I want to query the same sql with each of above as the filter. Each query will give me a unique employee id, which I want to use to delete some other records.
我想使用上述每个查询相同的 sql 作为过滤器。每个查询都会给我一个唯一的员工 ID，我想用它来删除其他一些记录。
select emp_id from employee where emp_name like '%john%';
Let's say for the first query, I get the id as 1001. So the delete queries would be like following.
假设对于第一个查询，我得到的 id 为 1001。因此删除查询将如下所示。
delete from account_details where emp_id = 1001;
delete from hr_details where emp_id = 1001;
delete from pay_role_details where emp_id = 1001;
I have to repeat this for a list of employees. Pseudocode would be like following.
我必须为员工列表重复此操作。伪代码如下所示。
var emp_list = ['john', 'Hyman', 'kate', 'peter', 'sam',...]
for each :employee_name in emp_list
select emp_id as :var_emp_id from employee where emp_name like '%:employee_name%';
delete from account_details where emp_id = :var_emp_id;
delete from hr_details where emp_id = :var_emp_id;
delete from pay_role_details where emp_id = :var_emp_id;
end loop
I want a PL-SQL query to do this. Please help. Thanks.
我想要一个 PL-SQL 查询来做到这一点。请帮忙。谢谢。
What I tried is something like the following.
我尝试的是类似以下内容。
set serveroutput on;
begin
loop x in ('john','Hyman', 'kate') loop as :name
select emp_id as var_emp_id from employee where emp_name like '%:name%';
// delete queries
end loop;
end;
P.S. Although accoring to the question, like query may result in multiple records, in actual scenario, it is guaranteed to be only one record. Why I use like is that in actual scenario, it is a list of reference numbers instead of names. The reference number has some other pre texts and post texts and my comma seperated list has only the numbers.
PS虽然根据问题，像查询可能会产生多条记录，但在实际场景中，它保证只有一条记录。我之所以使用like，是因为在实际场景中，它是一个参考编号列表，而不是名称。参考号有一些其他的前文和后文，我的逗号分隔列表只有数字。
回答by Bob Jarvis - Reinstate Monica
Perhaps the following will help:
也许以下内容会有所帮助：
BEGIN
FOR aName IN (SELECT 'john' AS EMP_NAME FROM DUAL
UNION ALL
SELECT 'sam' AS EMP_NAME FROM DUAL
UNION ALL
SELECT 'peter' AS EMP_NAME FROM DUAL
UNION ALL
SELECT 'Hyman' AS EMP_NAME FROM DUAL)
LOOP
FOR emp IN (SELECT * FROM EMPLOYEE WHERE EMP_NAME LIKE '%' || aName.EMP_NAME || '%')
LOOP
DELETE FROM ACCOUNT_DETAILS a WHERE a.EMP_ID = emp.EMP_ID;
DELETE FROM HR_DETAILS h WHERE h.EMP_ID = emp.EMP_ID;
DELETE FROM PAY_ROLE_DETAILS p WHERE p.EMP_ID = emp.EMP_ID;
DBMS_OUTPUT.PUT_LINE('Deleted data for employee with EMP_ID=' || emp.EMP_ID);
END LOOP; -- emp
END LOOP; -- aName
END;
Study this until you understand how and why it works.
研究这一点，直到您了解它的工作原理和原因。
Share and enjoy.
分享和享受。
回答by ah_hau
Do you really need a cursor to do so? Try to skip cursor if possible to avoid poor performance/memory usage on huge data.
你真的需要一个游标来这样做吗？如果可能，请尝试跳过光标以避免在大量数据上性能/内存使用率低。
delete from account_details inner join employee on account_details.emp_id = employee.emp_id where WHERE CONTAINS(employee.emp_name, '"John" OR "Sam" OR "Max"', 1) >0;
delete from hr_details inner join employee on hr_details.emp_id = employee.emp_id where WHERE CONTAINS(employee.emp_name, '"John" OR "Sam" OR "Max"', 1) >0;
delete from pay_role_details inner join employee on pay_role_details.emp_id = employee.emp_id where WHERE CONTAINS(employee.emp_name, '"John" OR "Sam" OR "Max"', 1) >0;
回答by daZza
Use a PL/SQL cursor to select all the IDs you want to delete and then just loop it and issue the DELETE statements with every pass.
使用 PL/SQL 游标选择要删除的所有 ID，然后循环它并在每次通过时发出 DELETE 语句。
In-depth info on cursors can be found here: http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23plsql-1906474.html
可以在此处找到有关游标的深入信息：http: //www.oracle.com/technetwork/issue-archive/2013/13-mar/o23plsql-1906474.html
For dynamic SQL see here: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS627
对于动态 SQL，请参见此处：http: //docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS627
Code example:
代码示例：
PROCEDURE delete_stuff
IS
id AS NUMBER;
CURSOR your_cursor IS
SELECT emp_id FROM employee WHERE CONTAINS(employee.emp_name, '"John" OR "Sam" OR "Max"', 1) > 0;
OPEN your_cursor;
LOOP
FETCH your_cursor INTO id;
EXIT WHEN your_cursor%NOTFOUND;
EXECUTE IMMEDIATE 'DELETE FROM account_details WHERE emp_id = :id' USING id;
EXECUTE IMMEDIATE 'DELETE FROM hr_details WHERE emp_id = :id' USING id;
EXECUTE IMMEDIATE 'DELETE FROM pay_role_details WHERE emp_id = :id' USING id;
CLOSE your_cursor;
END LOOP;
EXCEPTION
WHEN OTHERS THEN NULL;
END delete_stuff;
