DECLARE TYPE t_table IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; v_table t_table; v_cnt NUMBER; BEGIN v_table(1) := '1'; v_table(2) := '3'; v_table(3) := '9';
v_cnt := v_table.COUNT; FOR i IN1 .. v_cnt LOOP dbms_output.put_line(v_table(i)); END LOOP; END;
多维数组
1 Create Table
create table XXUSER ( USER_ID NUMBER, USER_NAME VARCHAR2(255), SEX VARCHAR2(2), AGE NUMBER(3), ADDRESS VARCHAR2(2000) )
2 定义结果集(Record)
存放xxuser的部分字段
DECLARE -- only 2 fileds TYPE t_record_user IS RECORD( user_id xxuser.user_id%type, user_name xxuser.user_name%type);
TYPE t_user IS TABLE OF t_record_user INDEX BY BINARY_INTEGER;
v_arry_user t_user; BEGIN SELECT user_id, user_name BULK COLLECT INTOv_arry_user FROMxxuser; FOR i IN1 .. v_arry_user.COUNT LOOP dbms_output.put_line(v_arry_user(i).user_name); END LOOP; END;
3 使用ROWTYPE
存放xxuser的全部字段,比Record简洁。
DECLARE -- ALL,XXUser(user_id, user_name, sex, age, address) TYPE t_user IS TABLE OF xxuser%ROWTYPE INDEX BY BINARY_INTEGER;
v_arry_user t_user; BEGIN SELECT * BULK COLLECT INTO v_arry_user FROM xxuser; FOR i IN1 .. v_arry_user.COUNT LOOP dbms_output.put_line(v_arry_user(i).user_name || v_arry_user(i).sex); END LOOP; END;