在PL/SQL中是没有数组(Array)概念的。但是如果程序员想用Array的话,就得变通一下,用TYPE 和Table of Record来代替多维数组,一样挺好用的。 emp_type 就好象一个table 中的一条record 一样,里面有id, name,gender等。emp_type_array 象个table, 里面含有一条条这样的record (emp_type),就象多维数组一样。 --单维数组 DECLARE TYPE emp_ssn_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; best_employees emp_ssn_array; worst_employees emp_ssn_array;
BEGIN best_employees(1) := '123456'; best_employees(2) := '888888';
FOR i IN 1..best_employees.count LOOP DBMS_OUTPUT.PUT_LINE('i='|| i || ', best_employees= ' ||best_employees(i) || ', worst_employees= ' ||worst_employees(i)); END LOOP;
END; --多维数组 DECLARE
TYPE emp_type IS RECORD ( emp_id employee_table.emp_id%TYPE, emp_name employee_table.emp_name%TYPE, emp_gender employee_table.emp_gender%TYPE );
TYPE emp_type_array IS TABLE OF emp_type INDEX BY BINARY_INTEGER;
emp_rec_array emp_type_array; emp_rec emp_type;
BEGIN emp_rec.emp_id := 300000000; emp_rec.emp_name := 'Barbara'; emp_rec.emp_gender := 'Female';
FOR i IN 1..emp_rec_array.count LOOP DBMS_OUTPUT.PUT_LINE('i='||i ||', emp_id ='||emp_rec_array(i).emp_id ||', emp_name ='||emp_rec_array(i).emp_name ||', emp_gender = '||emp_rec_array(i).emp_gender); END LOOP;
SQL> create or replace type myvarray_list as varray(10) of varchar2(50); 2 /类型已创建。SQL> create or replace procedure show_list( 2 p_varlist in myvarray_list 3 ) 4 is 5 str varchar2(50); 6 begin 7 for x in 1..p_varlist.count loop 8 dbms_output.put_line('p_varlist('||x||')='||p_varlist(x)); 9 end loop; 10 end; 11 /过程已创建。SQL> exec show_list(myvarray_list('Oracle','DB2','Sql Server','mySql','DBA')); p_varlist(1)=Oracle p_varlist(2)=DB2 p_varlist(3)=Sql Server p_varlist(4)=mySql p_varlist(5)=DBAPL/SQL 过程已成功完成。SQL>在别人哪里找来的
table of number 怎么在存储过程中做成传入参数呢?
-----例子 SQL> SQL> create or replace type numArr as table of number; 2 /Type createdSQL> SQL> create or replace procedure dolist(varArr in numArr) is 2 begin 3 for i in 1 .. varArr.count loop 4 dbms_output.put_line('第' || i || '元素:' || varArr(i)); 5 end loop; 6 end; 7 /Procedure createdSQL> set serveroutput on; SQL> select dolist(numArr(1,2,3,4,5,6)) from dual;select dolist(numArr(1,2,3,4,5,6)) from dualORA-00904: "DOLIST": 标识符无效SQL> execute dolist(numArr(1,2,3,4,5,6));第1元素:1 第2元素:2 第3元素:3 第4元素:4 第5元素:5 第6元素:6PL/SQL procedure successfully completedSQL>
http://topic.csdn.net/t/20060112/17/4513467.html
集合:是具有相同定义的元素的聚合。Oracle有两种类型的集合:
可变长数组(VARRAY):可以有任意数量的元素,但必须预先定义限制值。
嵌套表:视为表中之表,可以有任意数量的元素,不需要预先定义限制值。
在PL/SQL中是没有数组(Array)概念的。但是如果程序员想用Array的话,就得变通一下,用TYPE 和Table of Record来代替多维数组,一样挺好用的。
emp_type 就好象一个table 中的一条record 一样,里面有id, name,gender等。emp_type_array 象个table, 里面含有一条条这样的record (emp_type),就象多维数组一样。
--单维数组
DECLARE
TYPE emp_ssn_array IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
best_employees emp_ssn_array;
worst_employees emp_ssn_array;
BEGIN
best_employees(1) := '123456';
best_employees(2) := '888888';
worst_employees(1) := '222222';
worst_employees(2) := '666666';
FOR i IN 1..best_employees.count LOOP
DBMS_OUTPUT.PUT_LINE('i='|| i || ', best_employees= ' ||best_employees(i)
|| ', worst_employees= ' ||worst_employees(i));
END LOOP;
END;
--多维数组
DECLARE
TYPE emp_type IS RECORD
( emp_id employee_table.emp_id%TYPE,
emp_name employee_table.emp_name%TYPE,
emp_gender employee_table.emp_gender%TYPE );
TYPE emp_type_array IS TABLE OF emp_type INDEX BY BINARY_INTEGER;
emp_rec_array emp_type_array;
emp_rec emp_type;
BEGIN
emp_rec.emp_id := 300000000;
emp_rec.emp_name := 'Barbara';
emp_rec.emp_gender := 'Female';
emp_rec_array(1) := emp_rec;
emp_rec.emp_id := 300000008;
emp_rec.emp_name := 'Rick';
emp_rec.emp_gender := 'Male';
emp_rec_array(2) := emp_rec;
FOR i IN 1..emp_rec_array.count LOOP
DBMS_OUTPUT.PUT_LINE('i='||i
||', emp_id ='||emp_rec_array(i).emp_id
||', emp_name ='||emp_rec_array(i).emp_name
||', emp_gender = '||emp_rec_array(i).emp_gender);
END LOOP;
END;
/*-------------- Result --------------
i=1, emp_id =300000000, emp_name =Barbara, emp_gender = Female
i=2, emp_id =300000008, emp_name =Rick, emp_gender = Male */
SQL> create or replace type myvarray_list as varray(10) of varchar2(50);
2 /类型已创建。SQL> create or replace procedure show_list(
2 p_varlist in myvarray_list
3 )
4 is
5 str varchar2(50);
6 begin
7 for x in 1..p_varlist.count loop
8 dbms_output.put_line('p_varlist('||x||')='||p_varlist(x));
9 end loop;
10 end;
11 /过程已创建。SQL> exec show_list(myvarray_list('Oracle','DB2','Sql Server','mySql','DBA'));
p_varlist(1)=Oracle
p_varlist(2)=DB2
p_varlist(3)=Sql Server
p_varlist(4)=mySql
p_varlist(5)=DBAPL/SQL 过程已成功完成。SQL>在别人哪里找来的
-----例子
SQL>
SQL> create or replace type numArr as table of number;
2 /Type createdSQL>
SQL> create or replace procedure dolist(varArr in numArr) is
2 begin
3 for i in 1 .. varArr.count loop
4 dbms_output.put_line('第' || i || '元素:' || varArr(i));
5 end loop;
6 end;
7 /Procedure createdSQL> set serveroutput on;
SQL> select dolist(numArr(1,2,3,4,5,6)) from dual;select dolist(numArr(1,2,3,4,5,6)) from dualORA-00904: "DOLIST": 标识符无效SQL> execute dolist(numArr(1,2,3,4,5,6));第1元素:1
第2元素:2
第3元素:3
第4元素:4
第5元素:5
第6元素:6PL/SQL procedure successfully completedSQL>