Mega Code Archive
Store pre-defined constants in VARRAY
<![CDATA[
SQL>
SQL> create table ord(
2 order_no integer
3 ,cust_no integer
4 ,order_date date not null
5 ,total_order_price number(7,2)
6 ,deliver_date date
7 ,deliver_time varchar2(7)
8 ,payment_method varchar2(2)
9 ,emp_no number(3,0)
10 ,deliver_name varchar2(35)
11 ,gift_message varchar2(100)
12 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE p_add_orders (v_ord_ctr IN number, v_item_ctr IN number,
2 v_cust_no IN number, v_emp_no IN number)
3 AS
4 v_loop number := 1;
5 v_type_ctr number := 1;
6 v_curr_order ord.order_no%TYPE;
7
8 TYPE PayMethods IS VARRAY(10) OF VARCHAR2(2);
9 v_paymethods PayMethods := PayMethods('VS','CA','VG','AX','CK','MC','DI','CA','CK','VS');
10
11 TYPE Orderdates IS VARRAY(10) OF DATE;
12 v_odates Orderdates := Orderdates(add_months(sysdate, -45),
13 add_months(sysdate, -14),
14 add_months(sysdate, -22),
15 add_months(sysdate, -38),
16 add_months(sysdate, -46),
17 add_months(sysdate, -59),
18 add_months(sysdate, -19),
19 add_months(sysdate, -11),
20 add_months(sysdate, -74),
21 add_months(sysdate, -6));
22 begin
23 WHILE v_loop <= v_ord_ctr LOOP
24 IF v_type_ctr > 10 THEN
25 v_type_ctr := 1;
26 END IF;
27
28 INSERT INTO ord (ORDER_NO, CUST_NO, ORDER_DATE, TOTAL_ORDER_PRICE, DELIVER_DATE,
29 PAYMENT_METHOD, EMP_NO)
30 VALUES (999, v_cust_no, v_odates(v_type_ctr), 0, v_odates(v_type_ctr) + 10,
31 v_paymethods(v_type_ctr), v_emp_no );
32
33 SELECT 11111
34 INTO v_curr_order
35 FROM dual ;
36
37 v_loop := v_loop + 1 ;
38 v_type_ctr := v_type_ctr + 1 ;
39 END LOOP;
40 end;
41 /
Procedure created.
SQL>
SQL> show error
No errors.
SQL>
SQL>
SQL> drop table ord;
Table dropped.
SQL>
SQL>
SQL> --
]]>