A few days ago I tried to use an Array in Oracle, so I wanted to share here a simple example.
In this small piece of code you can find:
- declaration of DB Table, Oracle Type with three columns, Array
- Stored Procedure that writes the given array in the table, in delete-insert
- code needed to launch and test the procedure generating random input data
- query used for randomly loading the table
Hope it helps : )
P.S. If you need it, there’s the Oracle Collections and Records Documentation
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 |
---------------------------------------------------------------- -- DDL Table, Type, Array CREATE TABLE RESOURCE_POOL ( OBJ_ID VARCHAR2(20 CHAR), RESOURCE_ID NUMBER, EFFORT NUMBER(2,0) , CONSTRAINT RESOURCE_POOL_PK PRIMARY KEY (OBJ_ID, RESOURCE_ID) ); CREATE OR REPLACE TYPE T_RESOURCE FORCE AS OBJECT ( RESOURCE_ID NUMBER, EFFORT NUMBER(2,0) ) NOT final; / CREATE OR REPLACE TYPE T_RESOURCE_ARRAY FORCE IS TABLE OF T_RESOURCE; / ---------------------------------------------------------------- -- Basic procedure that writes the given matrix into the table in delete - insert CREATE OR REPLACE PROCEDURE P_ARRAY_TEST( p_OBJ_ID IN VARCHAR2, p_RESOURCE_POOL IN T_RESOURCE_ARRAY, p_OUT OUT VARCHAR2 ) AS v_ts1 TIMESTAMP; v_ts2 TIMESTAMP; BEGIN dbms_output.put_line('p_OBJ_ID ' || p_OBJ_ID); -- Print Rows FOR i IN 1..p_RESOURCE_POOL.COUNT LOOP dbms_output.put_line(TO_CHAR(p_RESOURCE_POOL(i).RESOURCE_ID ) || ' ' || TO_CHAR(p_RESOURCE_POOL(i).EFFORT) ); END LOOP; v_ts1 := systimestamp; dbms_output.put_line(v_ts1 ); DELETE RESOURCE_POOL WHERE OBJ_ID = p_OBJ_ID; dbms_output.put_line('post delete ' || systimestamp); INSERT INTO RESOURCE_POOL SELECT p_OBJ_ID, RESOURCE_ID, EFFORT FROM TABLE(p_RESOURCE_POOL) ; dbms_output.put_line('post insert ' || systimestamp); COMMIT; p_OUT := 'OK'; v_ts2 := systimestamp; dbms_output.put_line(v_ts2 ); dbms_output.put_line('time ' || TO_CHAR( v_ts2 - v_ts1 ) ); END; / ---------------------------------------------------------------- -- PL/SQL Code for testing purpose Declare v_res T_RESOURCE; v_pool T_RESOURCE_ARRAY; v_count NUMBER; v_objId VARCHAR2(80); outPar varchar2(255); begin v_objId := 'SCARPIE_01'; -- Create test Array: Manual /* v_pool := T_RESOURCE_ARRAY(); v_pool.EXTEND(2); v_res := T_RESOURCE(1, 11); v_pool(1) := v_res; v_res := T_RESOURCE(2, 22); v_pool(2) := v_res; */ -- Create test Array: 1000 Random elements v_pool := T_RESOURCE_ARRAY(); FOR rec IN ( SELECT dbms_random.random AS RESOURCE_ID, round(dbms_random.value * 10) as EFFORT FROM dual CONNECT BY ROWNUM <= 1000 ) loop v_pool.extend; v_res := T_RESOURCE(rec.RESOURCE_ID, rec.EFFORT); v_pool(v_pool.count) := v_res; END loop; P_ARRAY_TEST(v_objId, v_pool, outPar); END; / ---------------------------------------------------------------- -- Load table INSERT INTO RESOURCE_POOL SELECT dbms_random.string('U', 20) OBJ_ID, dbms_random.random as RESOURCE_ID, round(dbms_random.value * 10) as EFFORT FROM dual CONNECT BY ROWNUM <= 100000; -- Drop Everything DROP PROCEDURE P_ARRAY_TEST; DROP TYPE T_RESOURCE_ARRAY; DROP TYPE T_RESOURCE; DROP TABLE RESOURCE_POOL; |
Source: PL/SQL Object Oriented – TutorialsPoint
Interesting Oracle Type Reading: Steven Feuerstein – Use TREAT to Access Attributes of Object Subtypes
Photo credit: Iconexperience.com
