Mega Code Archive
Insert a specified number of suppliers and products per supplier
<![CDATA[
SQL>
SQL> create table supplier(
2 supplier_no integer primary key
3 ,supplier_name varchar2(50)
4 ,address varchar(30)
5 ,city varchar(20)
6 ,state varchar2(2)
7 ,area_code varchar2(3)
8 ,phone varchar2(8)
9 );
Table created.
SQL> -- supplier table inserts
SQL> insert into supplier(supplier_no, supplier_name)values(10,'ABC Gift Supply Co.');
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(12,'Z Gift Supply Co.');
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(13,'XYZ Gift Supply Co.');
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(14,'R and R Gift Supply Co.');
1 row created.
SQL> insert into supplier(supplier_no, supplier_name)values(17,'Z Gift Supply Co.');
1 row created.
SQL>
SQL>
SQL> ACCEPT p_add PROMPT 'Enter the number of suppliers to add '
Enter the number of suppliers to add ACCEPT p_prod PROMPT 'Enter the number of products to add per supplier '
SQL>
SQL> declare
2 v_ctr number := &p_add;
3 v_ctr number := &p_prod;
4 v_loop number := 1;
5
6 v_curr_supplier supplier.supplier_no%TYPE ;
7
8 begin
9
10 WHILE v_loop <= v_ctr LOOP
11 INSERT INTO supplier (SUPPLIER_NO, SUPPLIER_NAME)
12 VALUES (supplier_seq.NEXTVAL, 'Acme Supply #'||supplier_seq.CURRVAL);
13
14 SELECT supplier_seq.CURRVAL INTO v_curr_supplier FROM dual ;
15
16 p_add_prod(v_curr_supplier, v_ctr);
17
18 v_loop := v_loop + 1 ;
19 END LOOP;
20 COMMIT;
21 end;
22 /
SQL> drop table supplier;
Table dropped.
]]>