Mega Code Archive

 

Categories / Oracle PLSQL / PL SQL

 

VARRAY of VARCHAR2 and Varray of number

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> --