oracle 如何在oracle中为记录类型编写循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3282170/
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
how to write loop for a record type in oracle
提问by learn_plsql
I have the following statement which compiles fine in my package:
我有以下语句在我的包中编译得很好：
package header:
包头：
TYPE role_user_type IS RECORD (
ROLE_ID some_table.ROLE_ID%TYPE,
SUBGROUP some_table.USER_ID%TYPE
);
body:
身体：
ROLE_USER_REC MY_PACKAGE.ROLE_USER_TYPE;
SELECT B.USER_ID, B.ROLE INTO ROLE_USER_REC
FROM some_table where user_id like 'M%'
what is the skeleton for looping through ROLE_USER_REC? can we even loop through it?
循环的骨架是什么ROLE_USER_REC？我们甚至可以遍历它吗？
回答by Peter Lang
There is nothing to loop.
没有什么可以循环的。
role_user_typedefines a single record, that you can access via:
role_user_type定义单个记录，您可以通过以下方式访问：
dbms_output.put_line( role_user_rec.role_id || ', ' || role_user_rec.subgroup );
Your SELECT ... INTOwill fail as soon as more than one row is returned.
SELECT ... INTO一旦返回多于一行，您将失败。
If you need to store several of those records, you can use nested tableslikeTYPE role_user_tab IS TABLE OF role_user_type:
如果您需要存储其中几条记录，您可以使用嵌套表，例如TYPE role_user_tab IS TABLE OF role_user_type：
Example:
示例：
DECLARE
TYPE role_user_type IS RECORD (
ROLE_ID VARCHAR2(10),
SUBGROUP VARCHAR2(10)
);
TYPE role_user_tab IS TABLE OF role_user_type;
role_user_rec role_user_tab;
BEGIN
SELECT 'A', 'B'
BULK COLLECT INTO role_user_rec
FROM dual;
FOR i IN role_user_rec.FIRST .. role_user_rec.LAST LOOP
dbms_output.put_line( role_user_rec(i).role_id || ', ' || role_user_rec(i).subgroup );
END LOOP;
END;
回答by DCookie
You can use a cursor FOR loop:
您可以使用游标 FOR 循环：
BEGIN
FOR role_user_type IN ('SELECT B.USER_ID, B.ROLE FROM some_table where user_id like ''M%'')
LOOP
dbms_output.put_line('User ID: '||role_user_type.user_id);
etc...
END LOOP;
END;
Another alternative:
另一种选择：
DECLARE
CURSOR C IS
SELECT B.USER_ID, B.ROLE
FROM some_table
where user_id like 'M%';
BEGIN
FOR role_user_type IN C LOOP
dbms_output.put_line('User ID: '||role_user_type.user_id);
etc...
END LOOP;
END;
回答by Ankitjee
You can use cursors for this
您可以为此使用游标
FOR i in (/* Your Select query*/)
loop
/* You can use value of the column fetched from select query like i.column_name
and use it which ever way you want */
end loop;
