SQL>
SQL> create table product(
2 product_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> -- product Table Inserts:
SQL> insert into product(product_id, price, description, onhand, reorder)values (1,2.50,'PC',100,20);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (2,23.00,'Disk',null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (3,null,'Monitor',null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (4,1.50,'Mouse',50,10);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (5,10.50,'Vase',100,20);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (6,45.00,'Keyboard',null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (7,19.99,'Cable',3,5);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (8,4.50,'Notebook',null,null);
1 row created.
SQL>
SQL> CREATE SEQUENCE Product_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 product.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) - 1;
25 ELSE
26 newOrder := v_onhand(idIndex) + 5;
27 END IF;
28
29 INSERT INTO product (PRODUCT_ID, PRICE, DESCRIPTION, ONHAND, REORDER, SUPPLIER_NO)
30 VALUES (product_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>
SQL> drop table product;
Table dropped.
SQL> drop sequence product_seq;
Sequence dropped.
SQL>
SQL>