更新时间:2022-12-03 23:33:43
您需要使用动态SQL来实现;像这样:
You need to use dynamic SQL to achieve this; something like:
DECLARE
TYPE cur_type IS REF CURSOR;
CURSOR client_cur IS
SELECT DISTING username
FROM all_users
WHERE length(username) = 3;
emails_cur cur_type;
l_cur_string VARCHAR2(128);
l_email_id <type>;
l_name <type>;
BEGIN
FOR client IN client_cur LOOP
dbms_output.put_line('Client is '|| client.username);
l_cur_string := 'SELECT id, name FROM '
|| client.username || '.org';
OPEN emails_cur FOR l_cur_string;
LOOP
FETCH emails_cur INTO l_email_id, l_name;
EXIT WHEN emails_cur%NOTFOUND;
dbms_output.put_line('Org id is ' || l_email_id
|| ' org name ' || l_name);
END LOOP;
CLOSE emails_cur;
END LOOP;
END;
/
已编辑以更正两个错误,并为一个示例. 已编辑,以使内部光标查询字符串变量.
Edited to correct two errors, and to add links to 10g documentation for OPEN-FOR
and an example.
Edited to make the inner cursor query a string variable.