Insert a specified number of suppliers and products per supplier : Data Insert « PL SQL « Oracle PL / SQL






  1. Oracle PL / SQL
  2. PL SQL
  3. Data Insert

Insert a specified number of suppliers and products per supplier

    
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.








Related examples in the same category

1.Use in parameter to pass value and insert value to a table
2.Data insert in a procedure
3.Insert data in procedure
4.Insert value passed in by parameter to a table
5.Insert value to a table after calculation
6.Insert value to product and productcategory with stored procedure
7.Insert value to table with for loop
8.Insert 100000 rows into a table with for loop
9.Loop through all to do a bulk insert
10.An anonymous block program to write the record to a row
11.This script demonstrates returning clause
12.Bulk insert with insert ... select
13.Hard code value and insert