oracle 存储过程返回数组的方法: 1.建立包 create or replace package test is TYPE filename_array IS TABLE OF varchar2(1); filename filename_array; end test; 2. 建立存储过程 create or replace procedure test_array(v_cfjg out test.filename_array ) is begin DECLARE i number; D_cfjg dic_cfjg%rowTYPE; -- D_nr dic_cfjg%rowTYPE; cursor c1 is SELECT * FROM dic_cfjg; BEGIN i:=0; v_cfjg := test.filename_array(); --数组初始化open c1; LOOP fetch c1 into D_cfjg; EXIT WHEN c1%NOTFOUND ; i:=i+1; v_cfjg.EXTEND; -- DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_cfjg.dm)); v_cfjg(v_cfjg.count):=D_cfjg.dm; DBMS_OUTPUT.PUT_LINE(v_cfjg(v_cfjg.count)); -- 测试 -- FETCH C1 INTO D_cfjg; -- EXIT WHEN c1%NOTFOUND ; END LOOP; end; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(sqlerrm); end test_array;
create type tab_num as table of number; /create function sum_it(tb tab_num) return number as ret number := 0; begin for i in 1..tb.count loop ret := ret + tb(i); end loop; return ret; end; /select sum_it(tab_num(2,3,5)) from dual;drop function sum_it; drop type tab_num;
Create or Replace type OneDimArray is varray(10) of varchar2(30);Create or Replace type TwoDimArray is varray(5) of OneDimArray;Create or replace procedure proc_csdn(va TwoDimArray) as begin test OneDimArray:=OneDimArray(); for i in 1..va.count loop test.extend; test:=va(i); for j in 1..test.count loop dbms_output.put_line(test(j)); end loop; end loop; end;--测试 declare t TwoDimArray:=TwoDimArray(); o OneDimArray:=OneDimArray(); begin o.extend; o(1):='1'; o.extend; o(2):='2'; t.extend; t(1):=o; proc_csdn(t); end;--测试环境: Oracle 9i Case:PL/SQL 6.0
DECLARE TYPE emp_type IS RECORD(emp_no emp.empno%TYPE,emp_name emp.ename%TYPE );--定义emp记录类型 TYPE emp_type_array IS TABLE OF emp_type INDEX BY BINARY_INTEGER;--定义存放emp记录的数组类型 emp_rec emp_type;--声明变量,类型:emp记录类型 emp_rec_array emp_type_array;--声明变量,类型:存放emp记录的数组类型BEGIN --数组赋值 emp_rec.emp_no:=1001; emp_rec.emp_name:='fwyang'; emp_rec_array(1):=emp_rec;
--循环输出数组元素 FOR i IN 1..emp_rec_array.count LOOP DBMS_OUTPUT.PUT_LINE( 'i='||i|| ',emp_no='||emp_rec_array(i).emp_no|| ',emp_name='||emp_rec_array(i).emp_name ); END LOOP; END;
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;---------------------- 多维数组 ------------------------DECLARETYPE 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注:在PL/SQL中是没有数组(Array)概念的。但是如果程序员想用Array的话,就得变通一下,用TYPE 和Table of Record来代替多维数组,一样挺好用的。emp_type 就好象一个table 中的一条record 一样,里面有id, name,gender等。emp_type_array 象个table, 里面含有一条条这样的record (emp_type),就象多维数组一
create table employee_table(emp_id int,emp_name varchar2(100),emp_gender varchar2(100) );
:) 分真多 有钱人
非常感谢!
不过我想要知道的是数组要传进去要如何实现,与在存储过程中如何解析!
我也明白存储过程中如何定义与使用数组,
现在难就难在我如何传进去,存储过程的参数要定义成什么格式!再次感谢 hongqi162(失踪的月亮)
1.建立包
create or replace package test is
TYPE filename_array IS TABLE OF varchar2(1);
filename filename_array;
end test;
2. 建立存储过程
create or replace procedure test_array(v_cfjg out test.filename_array ) is
begin DECLARE i number;
D_cfjg dic_cfjg%rowTYPE;
-- D_nr dic_cfjg%rowTYPE;
cursor c1 is SELECT * FROM dic_cfjg;
BEGIN
i:=0;
v_cfjg := test.filename_array(); --数组初始化open c1;
LOOP fetch c1 into D_cfjg;
EXIT WHEN c1%NOTFOUND ;
i:=i+1;
v_cfjg.EXTEND;
-- DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_cfjg.dm));
v_cfjg(v_cfjg.count):=D_cfjg.dm;
DBMS_OUTPUT.PUT_LINE(v_cfjg(v_cfjg.count));
-- 测试
-- FETCH C1 INTO D_cfjg;
-- EXIT WHEN c1%NOTFOUND ;
END LOOP;
end;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(sqlerrm);
end test_array;
=======================
谢谢你,不过我想要的是怎么把二维数组给当做存储过程传进去并解析!
不是给传出来,传出来用游标就行了!
/create function sum_it(tb tab_num) return number
as
ret number := 0;
begin
for i in 1..tb.count loop
ret := ret + tb(i);
end loop;
return ret;
end;
/select sum_it(tab_num(2,3,5)) from dual;drop function sum_it;
drop type tab_num;
begin
test OneDimArray:=OneDimArray();
for i in 1..va.count loop
test.extend;
test:=va(i);
for j in 1..test.count loop
dbms_output.put_line(test(j));
end loop;
end loop;
end;--测试
declare t TwoDimArray:=TwoDimArray();
o OneDimArray:=OneDimArray();
begin
o.extend;
o(1):='1';
o.extend;
o(2):='2';
t.extend;
t(1):=o;
proc_csdn(t);
end;--测试环境: Oracle 9i Case:PL/SQL 6.0
DECLARE
TYPE emp_type IS RECORD(emp_no emp.empno%TYPE,emp_name emp.ename%TYPE );--定义emp记录类型
TYPE emp_type_array IS TABLE OF emp_type INDEX BY BINARY_INTEGER;--定义存放emp记录的数组类型
emp_rec emp_type;--声明变量,类型:emp记录类型
emp_rec_array emp_type_array;--声明变量,类型:存放emp记录的数组类型BEGIN
--数组赋值
emp_rec.emp_no:=1001;
emp_rec.emp_name:='fwyang';
emp_rec_array(1):=emp_rec;
emp_rec.emp_no:=1002;
emp_rec.emp_name:='liwan';
emp_rec_array(2):=emp_rec;
--循环输出数组元素
FOR i IN 1..emp_rec_array.count LOOP
DBMS_OUTPUT.PUT_LINE(
'i='||i||
',emp_no='||emp_rec_array(i).emp_no||
',emp_name='||emp_rec_array(i).emp_name
);
END LOOP;
END;