SQL> create table gift(
2 gift_id integer primary key
3 ,price number(7,2)
4 ,description varchar2(75)
5 ,onhand number(5,0)
6 ,reorder number(5,0)
7 ,supplier_no integer
8 );
Table created.
SQL> -- gift Table Inserts:
SQL> insert into gift(gift_id, price, description, onhand, reorder)values (1,2.50,'Happy Birthday',100,20);
1 row created.
SQL> insert into gift(gift_id, price, description, onhand, reorder)values (2,23.00,'Happy Birthday',null,null);
1 row created.
SQL> insert into gift(gift_id, price, description, onhand, reorder)values (3,null,'Happy New Year',null,null);
1 row created.
SQL> insert into gift(gift_id, price, description, onhand, reorder)values (4,1.50,'Happy New Year',50,10);
1 row created.
SQL>
SQL>
SQL> CREATE SEQUENCE gift_seq
2 INCREMENT BY 1
3 START WITH 9
4 NOMAXVALUE
5 NOCYCLE;
Sequence created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE p_add_prod (v_supplier IN number, v_ctr IN number)
2 AS
3 i number := 1;
4 idIndex number := 1;
5 newOrder gift.reorder%TYPE;
6
7 TYPE names IS VARRAY(10) OF VARCHAR2(75);
8 v_names names := names('A','B','C','D','E','F','G','H','I','J');
9
10 TYPE Prod_Prices IS VARRAY(10) OF NUMBER(7,2);
11 v_prices Prod_prices := Prod_prices(2,2.25,3,4.2,6,12.4,11.7,9.25,5,7.5);
12
13 TYPE Prod_Onhand IS VARRAY(10) OF NUMBER;
14 v_onhand Prod_Onhand := Prod_Onhand(70,20,10,40,30,50,60,80,90,55);
15
16 begin
17
18 WHILE i <= v_ctr LOOP
19 IF idIndex > 10 THEN
20 idIndex := 1;
21 END IF;
22
23 IF v_onhand(idIndex) >= 30 THEN
24 newOrder := v_onhand(idIndex) - 10;
25 ELSE
26 newOrder := v_onhand(idIndex) - 5;
27 END IF;
28
29 INSERT INTO gift (gift_ID, PRICE, DESCRIPTION, ONHAND, REORDER, SUPPLIER_NO)
30 VALUES (gift_seq.NEXTVAL, v_prices(idIndex), v_names(idIndex), v_onhand(idIndex), newOrder, v_supplier);
31
32 i := i + 1 ;
33 idIndex := idIndex + 1;
34
35 END LOOP;
36 end;
37 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> drop SEQUENCE gift_seq;
Sequence dropped.
SQL>
SQL> drop table gift;
Table dropped.
SQL>
SQL>