- --固定数组
- declare
- type type_array is varray(10) of varchar2(20);
- var_array type_array:=type_array('ggs','jjh','wsb','csl','dd','bb');
- begin
- for i in 1..var_array.count loop
- dbms_output.put_line(var_array(i));
- end loop;
- end;
- --可变数组
- declare
- type type_array is table of varchar2(20) index by binary_integer;
- var_array type_array;
- begin
- var_array(1):='aa';
- var_array(2):='bb';
- for i in 1..var_array.count loop
- dbms_output.put_line( var_array(i));
- end loop;
- end;
- --可变数组取表
- declare
- begin
- end;
- create or replace procedure proc_stock(n number)
- as
- var_stock_code varchar2(10);
- var_stock_price number;
- begin
- for i in 1..n loop
- var_stock_code:= lpad(STR1 =>i ,LEN =>6 ,PAD =>'0' ) ;
- var_stock_price:=trunc(dbms_random.value*100)+1;
- --dbms_output.put_line(var_stock_code);
- --dbms_output.put_line(var_stock_price);
- insert into t_stock (stockcode,stockprice)
- values(var_stock_code,var_stock_price);
- commit;
- end loop;
- end;
- declare
- begin
- proc_stock(1000000);
- end;
- --用游标访问 14.578秒 13.5 13.8
- declare
- cursor cur is select * from t_stock;
- row_stock t_stock%rowtype;
- begin
- open cur;
- loop
- fetch cur into row_stock;
- exit when cur%notfound;
- null;
- end loop;
- close cur;
- end;
- --用数组实现 4.813 1.953 2
- declare
- type type_array is table of t_stock%rowtype index by binary_integer;
- var_array type_array;
- begin
- select * bulk collect into var_array from t_stock;
- for i in 1..var_array.count loop
- null;
- end loop;
- end;
- --访问自定义表
- declare
- type type_record is record(
- username varchar2(20),
- sex varchar2(2)
- );
- type_record_user type_record;
- type type_array is table of type_record_user%type index by binary_integer;
- var_array type_array;
- begin
- select username,sex bulk collect into var_array from tuser;
- for i in 1..var_array.count loop
- dbms_output.put_line(var_array(i).username);
- dbms_output.put_line(var_array(i).sex);
- end loop;
- end;
CREATE OR REPLACE TYPE ARR_OBJECT AS OBJECT(
TRADENO VARCHAR2(32),
DEAL_TYPE VARCHAR2(2),
TURNOVER VARCHAR2(20)
);
CREATE OR REPLACE TYPE WEALTH_DEAL_DETAIL_ARRAY IS TABLE OF ARR_OBJECT;
PROCEDURE pro_wealth_deal_detail_array(P_FUND_ACCNO IN VARCHAR2,
P_OPEN_DEALNO IN VARCHAR2,
P_HOLD_ACCNO IN VARCHAR2,
P_MARKET_NO IN VARCHAR2,
P_SECURNO IN VARCHAR2,
P_TRADE_DATE IN DATE,
P_ARR OUT WEALTH_DEAL_DETAIL_ARRAY)
IS
CURSOR CUR_WEALTH_DEAL_DETAIL IS
SELECT TRADENO,DEAL_TYPE,TURNOVER FROM WEALTH_DEAL_DETAIL T
WHERE T.FUND_ACCNO LIKE P_FUND_ACCNO
AND T.OPEN_DEALNO LIKE P_OPEN_DEALNO
AND T.HOLD_ACCNO LIKE P_HOLD_ACCNO
AND T.MARKETNO LIKE P_MARKET_NO
AND T.SECURNO LIKE P_SECURNO
AND T.TRADE_DATE <= TRUNC(P_TRADE_DATE) + 1
AND T.TRADE_DATE >= TRUNC(P_TRADE_DATE)
ORDER BY TRADENO ASC;
V_WEALTH_DEAL_DETAIL CUR_WEALTH_DEAL_DETAIL%ROWTYPE;
V_IDX INT := 0;
BEGIN
P_ARR := WEALTH_DEAL_DETAIL_ARRAY();
OPEN CUR_WEALTH_DEAL_DETAIL;
LOOP
FETCH CUR_WEALTH_DEAL_DETAIL INTO V_WEALTH_DEAL_DETAIL;
EXIT WHEN CUR_WEALTH_DEAL_DETAIL%NOTFOUND;
P_ARR.EXTEND;
P_ARR(V_IDX) := ARR_OBJECT(V_WEALTH_DEAL_DETAIL.TRADENO,V_WEALTH_DEAL_DETAIL.DEAL_TYPE,V_WEALTH_DEAL_DETAIL.TURNOVER);
V_IDX := V_IDX + 1;
END LOOP;
CLOSE CUR_WEALTH_DEAL_DETAIL;
END pro_wealth_deal_detail_array;
首先,我自定义了一个Object ARR_OBJECT.然后定义了一个类型WEALTH_DEAL_DETAIL_ARRAY,指明是ARR_OBJECT类型。这样就相当于Java中的List<Object>。在存储过程中使用WEALTH_DEAL_DETAIL_ARRAY作为OUT类型参数,根据传入的5个IN类型参数从WEALTH_DEAL_DETAIL表查询符合条件的数据,并放入WEALTH_DEAL_DETAIL_ARRAY中。