|
SQL>
SQL>
SQL> CREATE TABLE customer_region(
2 region_id NUMBER(4) PRIMARY KEY,
3 region_name VARCHAR2(11) NOT NULL
4 );
Table created.
SQL>
SQL>
SQL> INSERT INTO customer_region VALUES (1,'REGION1');
1 row created.
SQL> INSERT INTO customer_region VALUES (2,'REGION2');
1 row created.
SQL> INSERT INTO customer_region VALUES (3,'REGION3');
1 row created.
SQL> INSERT INTO customer_region VALUES (4,'REGION4');
1 row created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 Type regionRecord IS Record(region_id NUMBER(4),region_name VARCHAR2(10));
3 Type region_tbl IS TABLE of regionRecord INDEX BY BINARY_INTEGER;
4 regionRecords region_tbl;
5 returnCode NUMBER;
6 Ret_errorMessage VARCHAR2(1000);
7 Procedure load_regions (regionRecords IN region_tbl,
8 returnCode OUT NUMBER,
9 errorMessage OUT VARCHAR2)
10 Is
11 BEGIN
12
13 DELETE FROM customer_region;
14
15 FOR i in regionRecords.FIRST..regionRecords.LAST LOOP
16 INSERT INTO customer_region
17 values (regionRecords(i).region_id,regionRecords(i).region_name);
18 END LOOP;
19
20 COMMIT;
21
22 EXCEPTION WHEN OTHERS THEN
23
24 returnCode :=SQLCODE;
25
26 errorMessage :=SQLERRM;
27
28 END;
29 BEGIN
30 FOR i IN 1..5 LOOP
31 regionRecords(i).region_id :=i;
32 regionRecords(i).region_name :='REGION'||i;
33 END LOOP;
34 Load_regions(regionRecords,returnCode,ret_errorMessage);
35 EXCEPTION WHEN OTHERS THEN
36 RAISE_APPLICATION_ERROR(-20111,SQLERRM);
37 END;
38 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table customer_region;
Table dropped.
SQL>
SQL>
|