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> insert into product(product_id, price, description, onhand, reorder)values (1,2.50,'Oracle',100,20);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (2,23.00,'SQL Server',null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (3,null,'MySQL',null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (4,1.50,'DB2',50,10);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (5,10.50,'Java',100,20);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (6,45.00,'C++',null,null);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (7,19.99,'Javascript',3,5);
1 row created.
SQL> insert into product(product_id, price, description, onhand, reorder)values (8,4.50,'Ruby',null,null);
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE p_add_prod (v_supplier IN number, v_ctr IN number)
2 AS
3 v_loop number := 1;
4 v_type_ctr number := 1;
5 v_reorder product.reorder%TYPE;
6
7 TYPE Prod_Names IS VARRAY(10) OF VARCHAR2(75);
8 v_names Prod_Names := Prod_Names('Widget ','Gadget');
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 WHILE v_loop <= v_ctr LOOP
18 IF v_type_ctr > 10 THEN
19 v_type_ctr := 1;
20 END IF;
21 IF v_onhand(v_type_ctr) >= 30 THEN
22 v_reorder := v_onhand(v_type_ctr) - 10;
23 ELSE
24 v_reorder := v_onhand(v_type_ctr) - 5;
25 END IF;
26
27 INSERT INTO product (PRODUCT_ID, PRICE, DESCRIPTION, ONHAND, REORDER, SUPPLIER_NO)
28 VALUES (11111, v_prices(v_type_ctr), v_names(v_type_ctr),
29 v_onhand(v_type_ctr), v_reorder, v_supplier);
30
31 v_loop := v_loop + 1 ;
32 v_type_ctr := v_type_ctr + 1;
33
34 END LOOP;
35 end;
36 /
Procedure created.
SQL> show error
No errors.
SQL>
SQL> exec p_add_prod(1,2);
BEGIN p_add_prod(1,2); END;
*
ERROR at line 1:
ORA-00001: unique constraint (RNTSOFT.SYS_C006114) violated
ORA-06512: at "RNTSOFT.P_ADD_PROD", line 27
ORA-06512: at line 1
SQL>
SQL> select * from product;
PRODUCT_ID PRICE DESCRIPTION ONHAND REORDER SUPPLIER_NO
---------- ---------- --------------------------------------------------------------------------- ---------- ---------- -----------
1 2.5 Oracle 100 20
2 23 SQL Server
3 MySQL
4 1.5 DB2 50 10
5 10.5 Java 100 20
6 45 C++
7 19.99 Javascript 3 5
8 4.5 Ruby
8 rows selected.
SQL>
SQL>
SQL> drop table product;
Table dropped.
SQL>
SQL> --